How to search psql array elements by index in rails

Question

In my rails psql application I have an emails array column. The element in the first index of this column is the primary email. I want to be able to search the db for all people with primary email x.

fphilipe · Accepted Answer · 2019-08-15 05:50:24Z

1

No need to test for array inclusion. Just get the first element and compare that to the string:

User.where("emails[1] = ?", "[email protected]")

answered Aug 15, 2019 at 5:50

fphilipe

10.1k1 gold badge42 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ryan2johnson9 Over a year ago

Thanks, easier to understand. I did some Benchmark.measure to compare with the Array inclusion answer and there seems to be little difference in Performance.

ryan2johnson9 · Accepted Answer · 2019-08-15 05:16:12Z

0

I could not see this in the documentation but thanks to this answer I can see that in Postgres 9.5 or older you can use

arr[1:1]

syntax to look only in the first element

So this is the answer

User.where("emails[1:1] @> ARRAY[?]", "[email protected]")

answered Aug 15, 2019 at 5:16

ryan2johnson9

7346 silver badges21 bronze badges

Collectives™ on Stack Overflow

How to search psql array elements by index in rails

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related