CODEWITHSUNDEEP
ruby-on-railsarrayspostgresql
Sifnos
Sifnos

Reputation: 1171

Rails/PostgreSQL : query value inside an integer array column

What I wrong with me or this query?

I have a Shop model with an opening days column, which is an array of integers (something you can do with Postgres):

days= [1,1,1,1,0,0,0]

When I query:

shops = Shop.where('days[0] = 1')

I get an empty ActiveRecord Relation.

=> #<ActiveRecord::Relation []>

When I take a shop with this kind of array…

shop = Shop.first
=> #<Shop id: 215, days: [1, 1, 1, 1, 0, 0, 0],…

If I do

shop.days[0]

I get

=> 1

I really don't get it.

Upvotes: 1

Views: 1229

Answers (1)

D-side
D-side

Reputation: 9485

By default PostgreSQL uses a one-based numbering convention for arrays, that is, an array of n elements starts with array[1] and ends with array[n].

— Source

It's just your example. Your index is out of bounds, so it doesn't match any records, days[0] is NULL. Everywhere. Fire up rails db and figure:

SELECT * FROM shops WHERE days[0] IS NULL;

But what's with that "by default"? Is it possible to define array bounds on schema level so this never becomes an issue?

Well... it's Rails' fault, I'm afraid. If it's even considered an "issue" at all. In order for an array to be zero-indexed it should be saved as such in SQL. I tried that in SQL, it works:

INSERT INTO shops
        (days,           created_at,        updated_at)
  values('[0:2]={1, 1, 0}', current_timestamp, current_timestamp);

Unfortunately, Rails loses bounds for some reason:

 Shop.create(days: '[0:3]={6, 7, 8, 9}')
 > INSERT ... [["days", "{6,7,8,9}"], ...]

Upvotes: 3

Related Questions