Postgres 14

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 foo (foo_id serial PRIMARY KEY, foo text NOT NULL);
INSERT INTO foo(foo) VALUES
  ('first'),
  ('second');

CREATE TABLE



INSERT 0 2


-- title unique per query
WITH sel AS (
   SELECT foo_id, foo
   FROM   foo
   WHERE  foo_id IN (1,2)
   ORDER  BY foo_id
   )
, ins AS (
   INSERT INTO foo (foo)
   SELECT foo FROM sel
   RETURNING foo_id, foo
   )
SELECT i.foo_id, s.foo_id AS from_id
FROM   ins i
JOIN   sel s USING (foo);


foo_id
from_id


3
1

4
2



SELECT 2


-- else (at least id is unique per table)
WITH sel AS (
   SELECT *, row_number() OVER (ORDER BY foo_id) AS rn
   FROM   foo
   WHERE  foo_id IN (1,2)
   ORDER  BY foo_id
   )
, ins AS (
   INSERT INTO foo (foo)
   SELECT foo FROM sel
   RETURNING foo_id
   )
SELECT i.foo_id, s.foo_id AS from_id
FROM  (SELECT *, row_number() OVER (ORDER BY foo_id) AS rn FROM ins) i
JOIN   sel s USING (rn);

TABLE foo;


foo_id
from_id


5
1

6
2



SELECT 2




foo_id
foo


1
first

2
second

3
first

4
second

5
first

6
second



SELECT 6