I created a table which contains a column of string ARRAY
type as:
x
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?
Advertisement
Answer
WHERE list IS NULL