Postgres 15

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 target (
    id SERIAL NOT NULL PRIMARY KEY,
    foo TEXT NOT NULL
);

CREATE TABLE


INSERT
  INTO target (foo)
  VALUES
    ('alpha'),
    ('beta'),
    ('gamma'),
    ('delta'),
    ('epsilon')
;
SELECT * FROM target;

INSERT 0 5




id
foo


1
alpha

2
beta

3
gamma

4
delta

5
epsilon



SELECT 5


WITH
   updates as (
       SELECT * FROM unnest(
         ARRAY[2,4],
         ARRAY['hello', 'world']
       ) as x(target_id, new_foo)
   )
UPDATE target t
    SET foo = u.new_foo
    FROM updates u
    WHERE t.id = u.target_id
;
SELECT * FROM target;

UPDATE 2




id
foo


1
alpha

3
gamma

5
epsilon

2
hello

4
world



SELECT 5