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 sample (
  id        bigserial PRIMARY KEY
, lastname  text
, firstname text
-- UNIQUE (firstname, lastname) -- ?
);

CREATE TABLE sample1 (
  user_id    bigserial PRIMARY KEY
, sample_id  bigint REFERENCES sample
, adddetails text
);

CREATE TABLE sample2 (
  id      bigserial PRIMARY KEY
, user_id bigint REFERENCES sample1
, value   text
);

CREATE TABLE



CREATE TABLE



CREATE TABLE


WITH ins1 AS (
   INSERT INTO sample(firstname, lastname)
   VALUES ('fai55', 'shaggk')
-- ON     CONFLICT DO NOTHING         -- optional addition in Postgres 9.5+
   RETURNING id AS sample_id
   )
, ins2 AS (
   INSERT INTO sample1 (sample_id, adddetails)
   SELECT sample_id, 'ss' FROM ins1
   RETURNING user_id
   )
INSERT INTO sample2 (user_id, value)
SELECT user_id, 'ss2' FROM ins2;

INSERT 0 1


WITH data(firstname, lastname, adddetails, value) AS (
   VALUES                              -- provide data here
      ('fai55', 'shaggk', 'ss', 'ss2') -- see below
    , ('fai56', 'XXaggk', 'xx', 'xx2') -- works for multiple input rows
       --  more?                      
   )
, ins1 AS (
   INSERT INTO sample (firstname, lastname)
   SELECT firstname, lastname          -- DISTINCT? see below
   FROM   data
   -- ON     CONFLICT DO NOTHING       -- UNIQUE constraint? see below
   RETURNING firstname, lastname, id AS sample_id
   )
, ins2 AS (
   INSERT INTO sample1 (sample_id, adddetails)
   SELECT ins1.sample_id, d.adddetails
   FROM   data d
   JOIN   ins1 USING (firstname, lastname)
   RETURNING sample_id, user_id
   )
INSERT INTO sample2 (user_id, value)
SELECT ins2.user_id, d.value
FROM   data d
JOIN   ins1 USING (firstname, lastname)
JOIN   ins2 USING (sample_id);

INSERT 0 2


TABLE sample;


id
lastname
firstname


1
shaggk
fai55

2
shaggk
fai55

3
XXaggk
fai56



SELECT 3


TABLE sample1;


user_id
sample_id
adddetails


1
1
ss

2
2
ss

3
3
xx



SELECT 3


TABLE sample2;


id
user_id
value


1
1
ss2

2
2
ss2

3
3
xx2



SELECT 3