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 est (
  est_id serial PRIMARY KEY
, est_nom varchar(70) UNIQUE NOT NULL  -- must be unique!
);

CREATE TABLE bm (
  id serial PRIMARY KEY
, est_id int                           -- fixed the deceiving name!
, bm_nom varchar(70)
, CONSTRAINT fk_bm_est_id FOREIGN KEY(est_id) REFERENCES est(est_id) ON DELETE SET NULL
);

CREATE TABLE



CREATE TABLE


-- concurrent writes possible
WITH input(est_nom, bm_nom) AS (  -- input here !
   VALUES
     ('DAPR','leopold stotch')
   , ('LDGA','fredderik gauss')
   , ('SDCD','edward gortz')
   , ('SDCD','fritz gortz')    -- added row to show duplicate est_nom !
   )
, ins_est AS (
   INSERT INTO est (est_nom)
   SELECT DISTINCT est_nom
   FROM   input
   ON     CONFLICT (est_nom) DO UPDATE
   SET    est_nom = EXCLUDED.est_nom
   WHERE  false                      -- only locks existing rows!
   RETURNING est_nom, est_id
   )
INSERT INTO bm (est_id, bm_nom)
SELECT COALESCE(e.est_id, ie.est_id), i.bm_nom
FROM   input i
LEFT   JOIN ins_est ie USING (est_nom)
LEFT   JOIN est e USING (est_nom)
RETURNING *;


id
est_id
bm_nom


1
1
leopold stotch

2
3
fredderik gauss

3
2
fritz gortz

4
2
edward gortz



INSERT 0 4


-- without concurrent writes
WITH input AS (  -- input here!
   SELECT e.est_id, i.est_nom, i.bm_nom
   FROM  (
      VALUES
        ('LDGA','fredderik gauss II')  -- existing est_nom
      , ('SDCD','edward gortz II')
      , ('SDCD','fritz gortz II')      -- duplicate est_nom
      , ('NEW','leopold stotch')       -- new est_nom
      , ('NEW2','john doe')            -- new duplicate est_nom
      , ('NEW2','jane doe')            -- new duplicate est_nom
      ) i(est_nom, bm_nom)
   LEFT JOIN est e USING (est_nom)
   )
, ins_est AS (
   INSERT INTO est (est_nom)
   SELECT DISTINCT est_nom
   FROM   input i
   WHERE  i.est_id IS NULL
   -- ON     CONFLICT (est_nom) DO UPDATE  -- no conflict possible
   -- SET    est_nom = EXCLUDED.est_nom WHERE  false
   RETURNING est_nom, est_id
   )
INSERT INTO bm (est_id, bm_nom)
SELECT COALESCE(i.est_id, ie.est_id), i.bm_nom
FROM   input i
LEFT   JOIN ins_est ie USING (est_nom)
RETURNING *;


id
est_id
bm_nom


5
2
fritz gortz II

6
2
edward gortz II

7
3
fredderik gauss II

8
4
jane doe

9
4
john doe

10
5
leopold stotch



INSERT 0 6

id	est_id	bm_nom
1	1	leopold stotch
2	3	fredderik gauss
3	2	fritz gortz
4	2	edward gortz

id	est_id	bm_nom
5	2	fritz gortz II
6	2	edward gortz II
7	3	fredderik gauss II
8	4	jane doe
9	4	john doe
10	5	leopold stotch