Let's say we have a table:
CREATE TABLE rows(
a int NOT NULL,
b int,
c int
);
INSERT INTO rows(a, b, c) VALUES (1, 1, NULL), (2, NULL, 1), (3, 1, 1);
Why Postgres does not delete the row using the following query?
DELETE FROM rows WHERE (a, b, c) IN ((1, 1, NULL));
-- DELETE 0
It works if there is no NULL value in one of the columns:
DELETE FROM rows WHERE (a, b, c) IN ((3, 1, 1));
-- DELETE 1
The same problem if we'll try to use one of the following queries:
WITH to_delete AS (
SELECT * FROM UNNEST (
ARRAY[1, 2],
ARRAY[1, NULL],
ARRAY[NULL, 1]
) data(a, b, c)
)
DELETE FROM rows
USING to_delete
WHERE
rows.a = to_delete.a AND
rows.b = to_delete.b AND
rows.c = to_delete.c;
-- DELETE 0
WITH to_delete AS (
SELECT * FROM UNNEST (
ARRAY[1, 2],
ARRAY[1, NULL],
ARRAY[NULL, 1]
) data(a, b, c)
)
DELETE FROM rows
WHERE EXISTS (
SELECT 1 FROM to_delete
WHERE
to_delete.a = rows.a AND
to_delete.b = rows.b AND
to_delete.c = rows.c
)
-- DELETE 0
WITH to_delete AS (
SELECT * FROM UNNEST (
ARRAY[1, 2],
ARRAY[1, NULL],
ARRAY[NULL, 1]
) data(a, b, c)
)
DELETE FROM rows
WHERE (a, b, c) IN (SELECT * FROM to_delete)
-- DELETE 0
The previous 3 queries delete rows successfully when each column is not NULL.
So there are 2 questions:
- Why these SQL-queries don't delete rows where one of the columns is
NULL? - How to solve it? I use CTE where I'm receiving the rows I should delete. One of the columns in these rows is always
NULL.