Query for records with empty array with Rails and Postgres array type?

Question

How can I query for all records with an empty array, using the Postgres array data type?

 create_table "users", force: :cascade do |t|
   t.string   "email", limit: 255, default: "",null: false
   t.string   "roles", default: [], array: true
 end

I want to query for all records with an empty roles array.

Tried User.where("roles @> ?", '{}') but that did not work (returned 0 records).

have you tried just compare to nil or []?

Малъ Скрылевъ
– Малъ Скрылевъ

2016-05-25 22:37:12 +00:00
Commented May 25, 2016 at 22:37 — Малъ Скрылевъ
– Малъ Скрылевъ, Commented May 25, 2016 at 22:37
you did create a record right?

PhilVarg
– PhilVarg

2016-05-25 22:50:43 +00:00
Commented May 25, 2016 at 22:50 — PhilVarg
– PhilVarg, Commented May 25, 2016 at 22:50

Petr Gazarov · Accepted Answer · 2016-05-25 23:07:25Z

16

Here is the syntax you can use:

User.where("roles = '{}'")

answered May 25, 2016 at 23:07

Petr Gazarov

3,8412 gold badges23 silver badges38 bronze badges

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

4 Comments

wuliwong Over a year ago

Shouldn't it be: User.where("roles = '[]'") ? Since @chrismanderson defined the default to be [] not {}.

joost Over a year ago

@wuliwong that was indeed the case for me

snrlx Over a year ago

Using curly braces should be the working solution following the Postgres Documentation: postgresql.org/docs/9.1/static/arrays.html

Martin Over a year ago

@wuliwong Does not work in postres 10. Gives error: PG::InvalidTextRepresentation: ERROR: malformed array literal: "[]" Example with curly braces work fine

Joshua Brian Davis · Accepted Answer · 2019-01-16 23:39:56Z

3

You can also do: User.where(roles: [])

I'm using Rails 5.2 (although the syntax may have been introduced earlier). I realize this is an old question, but in case anyone finds it from a Google search (like me).

answered Jan 16, 2019 at 23:39

Joshua Brian Davis

391 silver badge3 bronze badges

1 Comment

Seralto Over a year ago

This approach did not work for me ('rails', '~> 5.2.0').

Alexander Meshkov · Accepted Answer · 2017-07-10 10:52:39Z

0

Try this:

where("roles = ?", '{null}')

answered Jul 10, 2017 at 10:52

Alexander Meshkov

1015 bronze badges

Collectives™ on Stack Overflow

Query for records with empty array with Rails and Postgres array type?

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related