22

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 verbose than writing program code to execute SQL, so it makes more sense for test suite data.

Anyway, here's the query I wrote which I figured would work:

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

However, this doesn't work, and results in PostgreSQL telling me:

ERROR:  WITH clause containing a data-modifying statement must be at the top level

Is there a correct way to write this type of query in general, without hard-coding the ID values?

(You can find a fiddle here which contains this example.)

CmdrMoozy
  • 3,870
  • 3
  • 19
  • 31
  • 3
    It's better to post the `create table` statements rather than the plain structure. That way it's easier for us to create a test environment with your tables. –  Jan 27 '14 at 17:10
  • I had actually made a fiddle with this example in it, but you'd already answered my question when I came back to edit it in. In the future, I'll add it up-front. – CmdrMoozy Jan 27 '14 at 17:16
  • [There can only be one](http://www.youtube.com/watch?v=sqcLjcSloXs) with statement! – Denis de Bernardy Jan 27 '14 at 17:40

1 Answers1

44

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;
Arlen Beiler
  • 15,336
  • 34
  • 92
  • 135