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 tbl(
  tbl_id integer
, name text
);

INSERT INTO tbl VALUES
  (1, 'John Doe')
, (2, 'Jane Doe')
, (3, 'Old Guy')
, (4, 'Old Gal')
;

CREATE TABLE



INSERT 0 4


UPDATE tbl x
SET    tbl_id = 23
     , name = 'New Guy'
FROM   tbl y                -- using the FROM clause
WHERE  x.tbl_id = y.tbl_id  -- must be UNIQUE NOT NULL
AND    x.tbl_id = 3
RETURNING y.tbl_id AS old_id, y.name AS old_name
        , x.tbl_id          , x.name;


old_id
old_name
tbl_id
name


3
Old Guy
23
New Guy



UPDATE 1


-- safe under concurrent write load
UPDATE tbl x
SET    tbl_id = 24
     , name = 'New Gal'
FROM  (SELECT tbl_id, name FROM tbl WHERE tbl_id = 4 FOR UPDATE) y 
WHERE  x.tbl_id = y.tbl_id
RETURNING y.tbl_id AS old_id, y.name AS old_name
        , x.tbl_id          , x.name;
        


old_id
old_name
tbl_id
name


4
Old Gal
24
New Gal



UPDATE 1