ActiveRecord: Find where column is nil or empty array

Question

I have an array column in postgres and I want to find all the records where this column is either nil or []. I can search one or the other fine but trying both fails.

This is what I attempted

Model.where(column: [nil, []])

But I get the error

ActiveRecord::StatementInvalid (PG::InvalidTextRepresentation: ERROR:  malformed array literal: "{NULL,{}}")
DETAIL:  Unexpected "{" character.

Burseraceous

which version of Rails are you using ? – Barricade

Empty array column in postgres is selected with '{}'. That said: Model.where(column: [nil, "'{}'"]). – Chiaroscuro

@mudasobwa I don't think that will help, that would just end up with a different problem with {NULL, '{}'}, no? – Betteann

@muistooshort ah, indeed; the main point of my comment was a hint on how to select the empty array in postgres; I never trust AR to build queries due to too much magic and accidental failures on complicated queries. – Chiaroscuro

Answer 1

This worked for me

Model.where('column IS NULL OR column = ?', '{}')

Burseraceous

Right. The underlying problem is that where(column: [nil, a]) usually means column is null or column = a but, since column is an array and ActiveRecord knows it, it tries to say column = '{some confused nonsense}'. You could also look at array_length(coalesce(column, array[])) is null. – Betteann

Nice work @Qwertie. If you don't mind me asking what did you use an array column for? – Muriel

@DaryllSantos It's probably not the best solution but when I have records that can be tagged I use an int array for the tag IDs as it seems a bit much to create a new table to hold the tag ID and the taggable ID. Really I should be making the column non null and setting the default to [] – Burseraceous

Answer 2

Model.where("column = ? OR column = ?", nil, [])

This will execute SQL query

SELECT "models".* FROM "models" WHERE (column = NULL OR column = NULL)

But you need can do this only by

Model.where("column = ?", nil)

Lawana

Have you checked this? I doubt even "column = ?", nil would work correctly. – Chiaroscuro

Doesn't seem to work. Shows no results when I can see there are a few with nil and []. – Burseraceous