Change {NULL} in Postgres ARRAY to NULL

Question

I have table t with an array column z in Postgres 9.5. I want to select id where z is either NULL OR {NULL}.

id |   z
---+--------
 1 | {NULL} 
 2 |  null

See DBFIDDLE

I tried changing {NULL} to NULL with array_remove():

SELECT id, 
array_remove(z,NULL) as removed
from t;

Returns:

id |    z   | removed 
---+--------+-------
 1 | {NULL} |   {}      
 2 |  null  |  null

However, if I query this:

select id, z from t where removed is null;

I still get id 1. Ideally, I'd like to avoid unnesting and grouping back up.

The question is misleading, it would be good if you could make it clearer. If I understand the title well, you need nullif(z, '{null}') — klin
– klin, Commented Jan 5, 2019 at 21:50
I edited the question as best I could and added a dbfiddle (demo)[dbfiddle.uk/… — moonshot
– moonshot, Commented Jan 5, 2019 at 22:28
It's still not clear, what you are trying to achieve: The table definition in your fiddle has an integer field z. You write "where z is either null OR {NULL}" but z can't be {NULL} given your table definition. — clamp
– clamp, Commented Jan 5, 2019 at 23:15

Erwin Brandstetter · Accepted Answer · 2024-12-10 16:58:23Z

10

To replace an array containing a single NULL element ('{NULL}') with NULL, I suggest NULLIF:

SELECT id, NULLIF(z, '{NULL}') AS z
FROM   t;

db<>fiddle here

'{NULL}' is an (untyped) array literal and the same value as resulting from ARRAY[NULL] - which defaults to the data type text[] without explicit input type or casting.

The above works for any array type: int[], date[], ... because the literal is coerced to the type of z implicitly.

An empty array ('{}') or an array with 1 or more NULL elements ('{NULL}', '{NULL, NULL}', ...) are not the same as NULL. array_remove() is not the right tool.

edited Dec 10, 2024 at 16:58

answered Jan 6, 2019 at 0:56

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

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

2 Comments

Constantine Over a year ago

Correct link for array literal

Erwin Brandstetter Over a year ago

@Constantine: While my link wasn't wrong, yours is better. I updated accordingly, thanks.

Collectives™ on Stack Overflow

Change {NULL} in Postgres ARRAY to NULL

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related