postgresql empty array

Question

I have the following table in PostgreSQL:

   Column    |          Type          |                         Modifiers
-------------+------------------------+-----------------------------------------------------------
 description | text                   | not null
 sec_r       | integer[]              |

My two array of integers sec_r have some fields that have "null" values, but I guess it isn't null?

Whenever I try to select * from this table where sec_r = null I get 0 rows.

|  description     |  sec_r  |
+------------------+---------+
| foo bar foo bar  |         |
| foo bar foo bar  | {1,2,3} |
| foo bar foo bar  |         |
| foo bar foo bar  | {9,5,1} |
(4 rows)

Doing select * from TheTable where 1 = ANY (sec_r) returns the correct rows however.

How do I select the rows where the array is blank?

Jonas · Accepted Answer · 2011-06-03 19:37:20Z

5

You should use IS NULL and not = NULL in SQL.

Try:

SELECT * FROM the_table WHERE sec_r IS NULL

answered Jun 3, 2011 at 19:37

Jonas

130k103 gold badges330 silver badges408 bronze badges

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

2 Comments

Incognito Over a year ago

Genius. Hahaa, okay I'll accept this in the 11 minute timeout.

Grzegorz Szpetkowski Over a year ago

You can also enable transform_null_equals property, so sec_r = null will be treated as sec_r IS NULL or use ISNULL shorthand, but your best friend is SQL standard way IS NULL.

David Chan · Accepted Answer · 2011-06-03 19:39:17Z

2

you are looking for IS NULL.

answered Jun 3, 2011 at 19:39

David Chan

7,5352 gold badges30 silver badges50 bronze badges

Collectives™ on Stack Overflow

postgresql empty array

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related