Postgres 16

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 (
  id            text PRIMARY KEY
, top           bigint  NOT NULL
, top_timestamp bigint  NOT NULL
, ballast       text
);

INSERT INTO tbl VALUES
  ('some-id', 121, 888, 'foo')
, ('next-id', 100, 666, 'bar')
;

CREATE TABLE



INSERT 0 2


-- To return the whole pre-update row:
INSERT INTO tbl AS t (id, top, top_timestamp)
VALUES ('some-id', 123, 999)
ON     CONFLICT (id) DO UPDATE
SET    top = EXCLUDED.top                      -- !
     , top_timestamp = EXCLUDED.top_timestamp  -- !
WHERE  t.top < EXCLUDED.top                    -- !
RETURNING (SELECT t_old FROM tbl t_old
           WHERE  t_old.id = t.id
           AND    t.xmax <> 0                  -- actually was an UPDATE!
          ).*                                  -- !!!


id
top
top_timestamp
ballast


some-id
121
888
foo



INSERT 0 1


TABLE tbl;


id
top
top_timestamp
ballast


next-id
100
666
bar

some-id
123
999
foo



SELECT 2


-- To olny return selected columns:
WITH ups AS (
   INSERT INTO tbl AS t (id, top, top_timestamp)
   VALUES ('some-id', 124, 1000)
   ON     CONFLICT (id) DO UPDATE
   SET    top = EXCLUDED.top
        , top_timestamp = EXCLUDED.top_timestamp
   WHERE  t.top < EXCLUDED.top
   RETURNING (SELECT t_old FROM tbl t_old
              WHERE  t_old.id = t.id
              AND    t.xmax <> 0
             ) AS t_old
   )
SELECT id, top , top_timestamp  -- just the columns you want
FROM  (SELECT (t_old).* FROM ups) sub;


id
top
top_timestamp


some-id
123
999



SELECT 1


TABLE tbl;


id
top
top_timestamp
ballast


next-id
100
666
bar

some-id
124
1000
foo



SELECT 2