Postgres 12

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 (
  id   BIGSERIAL PRIMARY KEY
, type VARCHAR(60) UNIQUE NOT NULL 
);

CREATE TABLE bar (
  id          BIGSERIAL PRIMARY KEY
, description VARCHAR(40) UNIQUE NOT NULL
, foo_id      BIGINT NOT NULL REFERENCES foo ON DELETE RESTRICT
);

CREATE TABLE



CREATE TABLE


-- plain INSERT fails due to missing rows in table "foo"
INSERT INTO bar (description, foo_id)
SELECT val.description, f.id
FROM  (
   VALUES
      (text 'testing', text 'blue')  -- explicit type declaration
    , ('another row' , 'red' )
    , ('new row1'    , 'purple')     -- purple does not exist in foo, yet
    , ('new row2'    , 'purple')
   ) val (description, type)
LEFT   JOIN foo f USING (type);

ERROR:  null value in column "foo_id" violates not-null constraint
DETAIL:  Failing row contains (1, testing, null).


WITH sel AS (
   SELECT val.description, val.type, f.id AS foo_id
   FROM  (
      VALUES
         (text 'testing', text 'blue')
       , ('another row', 'red'   )
       , ('new row1'   , 'purple')
       , ('new row2'   , 'purple')
      ) val (description, type)
   LEFT   JOIN foo f USING (type)
   )
, ins AS ( 
   INSERT INTO foo (type)
   SELECT DISTINCT type
   FROM   sel
   WHERE  foo_id IS NULL
   RETURNING id AS foo_id, type
   )
INSERT INTO bar (description, foo_id)
SELECT sel.description, COALESCE(sel.foo_id, ins.foo_id)
FROM   sel
LEFT   JOIN ins USING (type);

INSERT 0 4


TABLE foo;


id
type


1
red

2
purple

3
blue



SELECT 3


TABLE bar;


id
description
foo_id


2
testing
3

3
new row2
2

4
new row1
2

5
another row
1



SELECT 4


CREATE TYPE foobar AS (description text, type text);

CREATE OR REPLACE FUNCTION f_insert_foobar(VARIADIC _val foobar[])
  RETURNS void
  LANGUAGE sql AS
$func$
   WITH val AS (SELECT * FROM unnest(_val))    -- well-known row type
   ,    typ AS (
      SELECT v.type, f.id                      -- id NOT NULL where type already exists
      FROM  (SELECT DISTINCT type FROM val) v  -- DISTINCT!
      LEFT   JOIN foo f USING (type)           -- assuming no concurrent update/delete on foo
                                               -- else you might lock rows here.
      )
   ,    ins AS ( 
      INSERT INTO foo AS f (type)
      SELECT type
      FROM   typ
      WHERE  id IS NULL
      ON     CONFLICT (type) DO UPDATE         -- RARE cases of concurrent inserts
      SET    type = EXCLUDED.type              -- overwrite to make visible
      RETURNING f.type, f.id
      )
   INSERT INTO bar AS b (description, foo_id)
   SELECT v.description, COALESCE(t.id, i.id)  -- assuming most types pre-exist
   FROM        val v
   LEFT   JOIN typ t USING (type)              -- already existed
   LEFT   JOIN ins i USING (type)              -- newly inserted
   ON     CONFLICT (description) DO UPDATE     -- description already exists
   SET    foo_id = EXCLUDED.foo_id             -- real UPSERT this time
   WHERE  b.foo_id <> EXCLUDED.foo_id          -- only if actually changed
$func$;

CREATE TYPE



CREATE FUNCTION


SELECT f_insert_foobar(
     '(testing,blue)'
   , '(another row,red)'
   , '(new row1,purple)'
   , '(new row2,green)'
   , '("with,comma",green)'  -- added to demonstrate row syntax
   );


f_insert_foobar


null



SELECT 1


TABLE foo;


id
type


1
red

2
purple

3
blue

4
green



SELECT 4


TABLE bar;


id
description
foo_id


2
testing
3

4
new row1
2

5
another row
1

3
new row2
4

10
with,comma
4



SELECT 5