Postgresql – multiple insert queries after a set of with’s

insertpostgresqlpostgresql-10subquery

I am trying to have a reproducible way to populate a database with some test data.

Suppose (an overly simplified example with) three base tables: name, city, job, and two relationship tables: name-city, and name-job. I need to create an entry into each of three base tables and use the aforementioned entries to create entries in the TWO relationship tables.

What I have already is a way of using a series of with queries to create entries in the 3 base tables and insert values into ONE relationship table. 

with x as 
(INSERT INTO "public"."name" VALUES(DEFAULT) RETURNING "id"),
y as 
(INSERT INTO "public"."job" VALUES(DEFAULT) RETURNING "id"),
z as 
(INSERT INTO "public"."city" VALUES(DEFAULT) RETURNING "id")

INSERT INTO "public"."name-job"("name", "job")  
select x.id, y.id from x,y;

I would really like to add a second insert statement

INSERT INTO "public"."name-city"("name", "city")  
select x.id, z.id from x,z;

after the first insert but don't know how. I have tried separating the two insert statements with commas, and enclosing them in parentheses and then separating them with commas, and some other ways but nothing works.

It is not entirely an option to use a brand new statement with the second insert because I need the same values of x, y, z to be used. Given my lack of expertise/experience, it is entirely possible I am missing something obvious… so any ideas on how I can do this, ideally, without highly sophisticated tools, would be most welcome.

FWIW, I'm using Postgres (10.x)

Best Answer

You need to put the INSERT INTO "name-job" into another CTE. You could also put the last insert into "name-city" into a CTE as well, and add a SELECT to get all the inserted ids (and anything else you need from the inserted rows) back.

WITH 
  x AS 
  (INSERT INTO public.name VALUES (DEFAULT) RETURNING id),
  y AS 
  (INSERT INTO public.job VALUES (DEFAULT) RETURNING id),
  z AS
  (INSERT INTO public.city VALUES (DEFAULT) RETURNING id),
  xy AS
  (INSERT INTO public."name-job" (name, job)  
   SELECT x.id, y.id FROM x,y
   RETURNING name, job),
  xz AS
  (INSERT INTO public."name-city" (name, city)  
   SELECT x.id, z.id FROM x,z
   RETURNING name, city)
SELECT x.id, y.id, z.id, xy.*, xz.*
FROM x, y, z, xy, xz ;
  • If you want to insert more than one row in the tables, the query would need to be more complicated (use joins and not implicit cross joins).
  • The naming of the tables and columns is going to be a constant trouble and pain. I have found it much easier in Postgres if all table and column names are lower case and no special characters are used (like the dash you have used). Only underscores and digits. You can avoid the need of using double quotes everywhere that way.
Related Question