Insert values and add values to foreign key

Question

I have two tables:

CREATE TABLE est (
  est_id serial PRIMARY KEY
, est_nom varchar(70)
);

CREATE TABLE bm (
  id serial PRIMARY KEY
, est_nom int
, nom varchar(70)
, CONSTRAINT FK_bm_est FOREIGN KEY(est_nom) REFERENCES est(est_id) ON DELETE SET NULL
);

I want to insert this data in bm

INSERT INTO bm
VALUES
(netx serial,'DAPR','leopold stotch'),
(netx serial,'LDGA','fredderik gauss'),
(netx serial,'SDCD','edward gortz')

Input values in column 2 are strings, corresponding to est.est_nom. I need the matching est.est_id instead.

I want to get existing values from est.est_nom and use them for the insert into bm.est_id.

If not found, first insert new rows in est and use the resulting serial est_nom for the insert into bm.

Answer 1

So you want to get IDs from a lookup table, or generate new ones on the fly if missing.
First, sanitize your relational design.

Proper design

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
);

We need a UNIQUE constraint on est.est_nom to check for duplicates efficiently. And bm.est_id really shouldn't be named bm.est_nom. It holds the ID not the name.

Basically, insert new values into est returning newly generated IDs in a data-modifying CTE first, then insert into bm. Basics:

• Insert data in 3 tables at a time using Postgres

But pre-existing rows and concurrent write access can complicate matters.

Concurrent writes not possible

If writes from concurrent transactions to any involved rows are not possible:

WITH input AS (  -- input here!
   SELECT e.est_id, i.est_nom, i.bm_nom
   FROM  (                               -- input once here
      VALUES
     ('DAPR','leopold stotch')
   , ('LDGA','fredderik gauss')
   , ('SDCD','edward gortz')
   , ('SDCD','fritz gortz')              -- added row to show 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
   -- no concurrent writes, so no race condition
   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 *;

fiddle

If there can be duplicate values for est_nom in the input (the typical case), then that needs some extra attention. The added DISTINCT takes care of it.

Here, I fetch existing IDs first, so we don't burn serial numbers and make it slightly cheaper. This can fail with concurrent write load.

Concurrent writes possible

WITH input(est_nom, bm_nom) AS (  -- input once 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 lock 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 *;

fiddle

Concurrency is a sophisticated matter that needs some understanding.
Concurrency combined with UPSERT in a CTE can get tricky. See:

• How to use RETURNING with ON CONFLICT in PostgreSQL?

Answer 2

Join with est and don't insert the id column, so that it is auto-generated:

INSERT INTO bm (est_nom, nom)
SELECT est.est.id, v.nom
FROM est
   JOIN (VALUES
            ('DAPR', 'leopold stotch'),
            ('LDGA', 'fredderik gauss'),
            ('SDCD', 'edward gortz')
        ) AS v(est_nom, nom)
      ON est.est_nom = v.est_nom;