0

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:

  1. Why these SQL-queries don't delete rows where one of the columns is NULL?
  2. 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.
1
  • 1
    NULL can nit be compared that way, there is no equal (=) Commented Feb 17, 2024 at 22:28

1 Answer 1

2

NULL can only be detected if you use IS

like

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);
CREATE TABLE
INSERT 0 3
DELETE FROM rows WHERE a= 1 AND  b =  1 AND  C IS NULL;
DELETE 1
INSERT INTO rows(a, b, c) VALUES (1, 1, NULL)
INSERT 0 1
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) OR (rows.a IS NULL AND to_delete.a IS NULL)) AND
    ((rows.b = to_delete.b) OR (rows.b IS NULL AND to_delete.b IS NULL)) AND
   ((rows.c = to_delete.c) OR (rows.c IS NULL AND to_delete.c IS NULL));
DELETE 2

fiddle

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

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.