PostgreSQL nested INSERTs / WITHs for foreign key insertions

Question

I'm using PostgreSQL 9.3, and I'm trying to write a SQL script to insert some data for unit tests, and I've run into a bit of a problem. Let's say we have three tables, structured like this: ``` ------- Table A ------- -------- Table B -------- -------- Table C -------- id | serial NOT NULL id | serial NOT NULL id | serial NOT NULL foo | character varying a_id | integer NOT NULL b_id | integer NOT NULL bar | character varying baz | character varying ``` The columns `B.a_id` and `C.b_id` are foreign keys to the `id` column of tables `A` and `B`, respectively. What I'm trying to do is to insert a row into each of these three tables with pure SQL, without having the ID's hard-coded into the SQL (making assumptions about the database before this script is run seems undesirable, since if those assumptions change I'll have to go back and re-compute the proper ID's for all of the test data). Note that I do realize I could do this programatically, but in general writing pure SQL is way less v…

Accepted Answer

Don't nest the common table expressions, just write one after the other:

```
WITH Y AS (
  INSERT INTO A (foo)
  VALUES ('abc')
  RETURNING id
), X as (
  INSERT INTO B (a_id, bar)
  SELECT id, 'def'
  FROM Y
  RETURNING id
)
INSERT INTO C (b_id, baz)
SELECT id, 'ghi'
FROM X;
```

PostgreSQL nested INSERTs / WITHs for foreign key insertions

Solution

Problem