How to write a WHERE clause for NULL value in ARRAY type column?

I created a table which contains a column of string ARRAY type as:

CREATE TABLE test
(
    id integer NOT NULL,
    list text[] COLLATE pg_catalog."default",
    CONSTRAINT test_pkey PRIMARY KEY (id)
)

I then added rows which contain various values for that array, including an empty array and missing data (null):

insert into test (id, list) values (1, array['one', 'two', 'three']);
insert into test (id, list) values (2, array['four']);
insert into test (id, list) values (3, array['']);
insert into test (id, list) values (4, array[]::text[]); // empty array
insert into test (id, list) values (5, null); // missing value

pgAdmin shows table like this:

I am trying to get a row which contains a null value ([null]) in the list column but:

select * from test where list = null;

…returns no rows and:

select * from test where list = '{}';

…returns row with id = 4.

How to write WHERE clause which would target NULL value for column of ARRAY type?

Answer

demo:db<>fiddle

... WHERE list IS NULL

Advertisement

Answer