What's the proper way to test if a Postgresql array field is either null or empty
arrays, comparison, function, null, postgresql
Solution
`{}` seems to represent an empty array, which would explain both the value being `NOT NULL` and `ARRAY_LENGTH({})` not returning anything – though I'd expect it to return 0 on `{}`, perhaps that's a PostgreSQL peculiarity with which I'm unfamiliar.
Is there a reason you can't just test the return value of `ARRAY_LENGTH`, as e.g.
SELECT id FROM table WHERE ARRAY_LENGTH(black_list, 1) IS NULL OR ARRAY_LENGTH(black_list, 1) < 1
Assuming `ARRAY_LENGTH()` doesn't lose its mind on empty values such as that for `id=12` in the example above, it seems like that'd do the trick.
Problem
I have a table defined with an integer_array field. It contains the following data: ``` id | black_list ----+------------ 4 | 5 | 8 | 12 | 6 | 7 | 10 | {5} 13 | {5} 3 | {} 9 | {3} 11 | {} 14 | {} 1 | {} 2 | {} 15 | {} 16 | {} 17 | {} (17 rows) ``` I need to write a query to see if the array field is empty - NULL or otherwise. The trouble is that the `{}` values are not null, nor do they return any length from the ARRAY_LENGTH function. None of the other array functions listed at http://www.postgresql.org/docs/8.4/static/functions-array.html seem to be what I need either. I've found that I can write `ARRAY_LENGTH(0 || black_list)` to get all of them to return a length of 1 or more, but that seems like a nasty hack. What's the proper way to test this? Bonus question: What exactly is `{}` representative of? I've been unable to write a select statement that will return that value. `ARRAY[]` throws an error, `ARRAY[""]` returns `{""}`, `ARRAY[NULL]` returns `{NULL}`, etc.