Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE test(
 column1 timestamp NOT NULL,
 column2 uuid,
 status  integer NOT NULL,
 id      uuid NOT NULL,
 CONSTRAINT upsert_conflict UNIQUE (id, status)
);

INSERT INTO test AS t
       (column1              , column2, status, id) 
VALUES ('2021-05-28 22:27:06', NULL   , 0     , '5beb004a-63ec-4f01-8604-37296a208e5f');

-- The added ctid reveals whether a new row version has been written
SELECT ctid, * FROM test;
1 rows affected



ctid
column1
column2
status
id


(0,1)
2021-05-28 22:27:06
null
0
5beb004a-63ec-4f01-8604-37296a208e5f


-- nothing would change, so do nothing. QUERY 2:
INSERT INTO test AS t
       (column1              , column2, status, id) 
VALUES ('2021-05-28 22:27:06', NULL   , 0     , '5beb004a-63ec-4f01-8604-37296a208e5f')
ON     CONFLICT ON CONSTRAINT upsert_conflict
DO     UPDATE
SET    column1 = EXCLUDED.column1
     , column2 = EXCLUDED.column2
WHERE (t.column1 IS NULL
    OR t.column2 IS NULL)
AND   (t.column1 IS DISTINCT FROM EXCLUDED.column1
    OR t.column2 IS DISTINCT FROM EXCLUDED.column2);

SELECT ctid, * FROM test;


ctid
column1
column2
status
id


(0,1)
2021-05-28 22:27:06
null
0
5beb004a-63ec-4f01-8604-37296a208e5f


-- column2 changes. QUERY 2:
INSERT INTO test AS t
       (column1              , column2                               , status, id) 
VALUES ('2021-05-28 22:27:06', '6beb004a-63ec-4f01-8604-37296a208e5f', 0     , '5beb004a-63ec-4f01-8604-37296a208e5f')
ON     CONFLICT ON CONSTRAINT upsert_conflict
DO     UPDATE
SET    column1 = EXCLUDED.column1
     , column2 = EXCLUDED.column2
WHERE (t.column1 IS NULL
    OR t.column2 IS NULL)
AND   (t.column1 IS DISTINCT FROM EXCLUDED.column1
    OR t.column2 IS DISTINCT FROM EXCLUDED.column2);

SELECT ctid, * FROM test;
1 rows affected



ctid
column1
column2
status
id


(0,2)
2021-05-28 22:27:06
6beb004a-63ec-4f01-8604-37296a208e5f
0
5beb004a-63ec-4f01-8604-37296a208e5f


-- column2 would change back to NULL, so QUERY 1 does nothing:
INSERT INTO test AS t
       (column1              , column2, status, id) 
VALUES ('2021-05-28 22:27:06', NULL   , 0     , '5beb004a-63ec-4f01-8604-37296a208e5f')
ON     CONFLICT ON CONSTRAINT upsert_conflict
DO     UPDATE
SET    column1 = COALESCE(t.column1, EXCLUDED.column1)
     , column2 = COALESCE(t.column2, EXCLUDED.column2)
WHERE (t.column1 IS NULL
    OR t.column2 IS NULL)
AND   (t.column1 IS DISTINCT FROM COALESCE(t.column1, EXCLUDED.column1)
    OR t.column2 IS DISTINCT FROM COALESCE(t.column2, EXCLUDED.column2));

SELECT ctid, * FROM test;


ctid
column1
column2
status
id


(0,2)
2021-05-28 22:27:06
6beb004a-63ec-4f01-8604-37296a208e5f
0
5beb004a-63ec-4f01-8604-37296a208e5f


-- column2 would change back to NULL, and QUERY 2 would allow it
-- BUT since neither column1 nor column2 are NULL, still nothing happens:
INSERT INTO test AS t
       (column1              , column2, status, id) 
VALUES ('2021-05-28 22:27:06', NULL   , 0     , '5beb004a-63ec-4f01-8604-37296a208e5f')
ON     CONFLICT ON CONSTRAINT upsert_conflict
DO     UPDATE
SET    column1 = EXCLUDED.column1
     , column2 = EXCLUDED.column2
WHERE (t.column1 IS NULL
    OR t.column2 IS NULL)
AND   (t.column1 IS DISTINCT FROM EXCLUDED.column1
    OR t.column2 IS DISTINCT FROM EXCLUDED.column2);

SELECT ctid, * FROM test;


ctid
column1
column2
status
id


(0,2)
2021-05-28 22:27:06
6beb004a-63ec-4f01-8604-37296a208e5f
0
5beb004a-63ec-4f01-8604-37296a208e5f