What is the correct way to check if a Postgresql array field is null or empty

Question

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 find out if the array field is empty - NULL or otherwise. The problem is that the {} values ​​are non-zero and do not 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 need me either. I found that I could write ARRAY_LENGTH(0 || black_list) to get all of them to return a length of 1 or more, but this seems like an unpleasant hack. What is the correct way to test this?

Bonus question: what exactly does {} represent? I could not write a select statement that will return this value. ARRAY[] throws an error, ARRAY[""] returns {""} , ARRAY[NULL] returns {NULL} , etc.

Answer 1

{} appears to be an empty array, which explains that the value NOT NULL and ARRAY_LENGTH({}) does not return anything, although I expect it to return 0 on {} , it is possible that a PostgreSQL feature that I am unfamiliar with.

Is there a reason why you cannot just check the return value of ARRAY_LENGTH , for example.

 SELECT id FROM table WHERE ARRAY_LENGTH(black_list, 1) IS NULL OR ARRAY_LENGTH(black_list, 1) < 1 

Assuming ARRAY_LENGTH() does not lose the mind about empty values, for example, for id=12 in the above example, it looks like this would do the trick.