Postgres 12

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 A (
  id int PRIMARY KEY
, column1 int NOT NULL  -- NOT NULL!
, column2 int NOT NULL
, column3 int NOT NULL
);

CREATE TABLE B (
  id int PRIMARY KEY
, column1 int  -- can be NULL!
, column2 int
, column3 int
, column4 int
);

INSERT INTO A VALUES
  (1,1,1,9)
, (2,4,4,4)
, (3,5,6,7);

INSERT INTO B VALUES
  (1,1,1,1,1)
, (2,2,2,2,2)
, (3,3,3,3,3);

CREATE TABLE



CREATE TABLE



INSERT 0 3



INSERT 0 3


-- All columns NOT NULL!
UPDATE b
SET   (column1, column2, column3, column4)
    = (COALESCE(ab.column1, b.column1)
     , COALESCE(ab.column2, b.column2)
     , COALESCE(ab.column3, b.column3)
     , COALESCE(ab.column4, b.column4)
      )
FROM (
   SELECT *
   FROM   a
   NATURAL LEFT JOIN  b -- append missing columns
   WHERE  a.id = 1
   AND    b.id IS NULL  -- only if anything actually changes
   ) ab
WHERE  b.id = ab.id
RETURNING b.*;



id
column1
column2
column3
column4


1
1
1
9
1



UPDATE 1


-- Result
SELECT * FROM b ORDER BY id;


id
column1
column2
column3
column4


1
1
1
9
1

2
2
2
2
2

3
3
3
3
3



SELECT 3