Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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
I want to query for all records with an empty roles array.
Tried User.where("roles @> ?", '{}') but that did not work (returned 0 records).
                Shouldn't it be: User.where("roles = '[]'") ? Since @chrismanderson defined the default to be [] not {}.
– wuliwong
                Jul 26, 2017 at 21:42
                Using curly braces should be the working solution following the Postgres Documentation: postgresql.org/docs/9.1/static/arrays.html
– snrlx
                May 8, 2018 at 14:33
                @wuliwong Does not work in postres 10. Gives error:  PG::InvalidTextRepresentation: ERROR:  malformed array literal: "[]" Example with curly braces work fine
– Martin Zinovsky
                Aug 1, 2018 at 10:44
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.