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 my_table (
   col1 int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   col2 int NOT NULL,
   col3 real,
   col4 int
);

CREATE UNIQUE INDEX my_table_col2_uni_idx ON my_table (col2)
WHERE col3 IS NULL;

CREATE UNIQUE INDEX my_table_col2_col3_uni_idx ON my_table (col2, col3)
WHERE col3 IS NOT NULL;

CREATE TABLE



CREATE INDEX



CREATE INDEX


WITH input(col2, col3, col4) AS (
   VALUES
     (3, NULL::real, 5)
   , (3, 4, 5)
   )
, upsert1 AS (
   INSERT INTO my_table AS t(col2, col3, col4)
   SELECT * FROM input          WHERE col3 IS NOT NULL
   ON     CONFLICT (col2, col3) WHERE col3 IS NOT NULL
   DO     UPDATE
   SET    col4 = EXCLUDED.col4
   WHERE  t.col4 IS DISTINCT FROM EXCLUDED.col4
   )
INSERT INTO my_table AS t(col2, col3, col4)
SELECT * FROM input    WHERE col3 IS NULL
ON     CONFLICT (col2) WHERE col3 IS NULL
DO     UPDATE SET col4 = EXCLUDED.col4
WHERE  t.col4 IS DISTINCT FROM EXCLUDED.col4;

INSERT 0 1


-- including the ctid to show that empty UPDATEs are cancelled
SELECT ctid, * from  my_table;


ctid
col1
col2
col3
col4


(0,1)
1
3
null
5

(0,2)
2
3
4
5



SELECT 2


-- now conflicting, but col4 would not change:
WITH input(col2, col3, col4) AS (
   VALUES
     (3, NULL::real, 5)
   , (3, 4, 5)
   )
, upsert1 AS (
   INSERT INTO my_table AS t(col2, col3, col4)
   SELECT * FROM input          WHERE col3 IS NOT NULL
   ON     CONFLICT (col2, col3) WHERE col3 IS NOT NULL
   DO     UPDATE
   SET    col4 = EXCLUDED.col4
   WHERE  t.col4 IS DISTINCT FROM EXCLUDED.col4
   )
INSERT INTO my_table AS t(col2, col3, col4)
SELECT * FROM input    WHERE col3 IS NULL
ON     CONFLICT (col2) WHERE col3 IS NULL
DO     UPDATE SET col4 = EXCLUDED.col4
WHERE  t.col4 IS DISTINCT FROM EXCLUDED.col4;

INSERT 0 0


-- including the ctid to show that empty UPDATEs are cancelled
SELECT ctid, * from  my_table;


ctid
col1
col2
col3
col4


(0,1)
1
3
null
5

(0,2)
2
3
4
5



SELECT 2


-- now conflicting, and col4 actually changes
WITH input(col2, col3, col4) AS (
   VALUES
     (3, NULL::real, 6)
   , (3, 4, 6)
   )
, upsert1 AS (
   INSERT INTO my_table AS t(col2, col3, col4)
   SELECT * FROM input          WHERE col3 IS NOT NULL
   ON     CONFLICT (col2, col3) WHERE col3 IS NOT NULL
   DO     UPDATE
   SET    col4 = EXCLUDED.col4
   WHERE  t.col4 IS DISTINCT FROM EXCLUDED.col4
   )
INSERT INTO my_table AS t(col2, col3, col4)
SELECT * FROM input    WHERE col3 IS NULL
ON     CONFLICT (col2) WHERE col3 IS NULL
DO     UPDATE SET col4 = EXCLUDED.col4
WHERE  t.col4 IS DISTINCT FROM EXCLUDED.col4;

INSERT 0 1


-- including the ctid to show that empty UPDATEs are cancelled
SELECT ctid, * from  my_table;


ctid
col1
col2
col3
col4


(0,3)
1
3
null
6

(0,4)
2
3
4
6



SELECT 2