PostgreSQL 9.2 – Using CTE INSERT to Provide Unique ID Values

postgresqlpostgresql-9.2

I am writing a job to transform data from an old design into a new design. In this process, I need to take the id from an insert into a separate table and use that in an insert to the target table, as such:

CREATE TABLE t1 {
  t1_id BIGSERIAL,
  col1 VARCHAR
};
CREATE TABLE t2 {
  t2_id BIGSERIAL,
  col2 VARCHAR, -- renamed from col1 to avoid confusion
  t1_id BIGINT REFERENCES t1.t1_id
};

I have the SQL defined that matches the following form:

WITH ins AS (
  INSERT INTO t1 (t1_id) VALUES (DEFAULT) RETURNING t1_id
) INSERT INTO t2
  (col1, t1_id)
SELECT
  a.val1, (SELECT * FROM ins)
FROM t3 a;

I wanted this to run the SELECT * FROM ins for every row of the SELECT .. but instead it only runs it once and uses that value for all rows in the SELECT. How can I restructure my SQL to get the desired behavior?

edit4

t1 ends up looking like:

1,<NULL>
(1 row)

t2 ends up looking like:

10,'a',1
11,'b',1 -- problem with id from t1 being 1
12,'c',1 -- problem with id from t1 being 1
.
.

What I want t1 to look like:

1,<NULL>
2,<NULL>
3,<NULL>
.
.

What I want t2 to look like:

10,'a',1
11,'b',2 -- id from t1 of 2
12,'c',3 -- id from t1 of 3
.
.

edit
To address what a_horse_with_no_name said, I also tried this (with the same result):

WITH ins AS (
  INSERT INTO t1 (t1_id) VALUES (DEFAULT) RETURNING t1_id
) INSERT INTO t2
  (col1, t1_id)
SELECT
  a.val1, b.t1_id
FROM t3 a
JOIN ins b ON TRUE;

edit2
I just tried directly referencing the appropriate SEQUENCE in my query, and that DOES work – but I don't like that solution very much at all (mostly because I don't like hard-coding object names.) If there is ANY solution other than directly referencing the name of the SEQUENCE I would appreciate it. 🙂

edit3
I suppose another solution would be to make use of a PROCEDURE to do the INSERT instead of a CTE .. but I'd still appreciation options/suggestions.

Best Answer

I don't understand why you need 2 tables if they have only 1-1 relationship. But here it is (pk is the primary key of t3):

WITH ins AS (
  INSERT INTO t1 (col1) 
    SELECT NULL FROM t3 
  RETURNING t1_id
) 
, r AS
( SELECT t1_id, ROW_NUMBER() OVER () AS rn
  FROM ins
) 
, t AS
( SELECT *, ROW_NUMBER() OVER () AS rn
  FROM t3
) 
INSERT INTO t2
  (col1, t1_id)
SELECT
  t.val1, r.t1_id
FROM t 
  JOIN r USING (rn) ;

If your t3 is the results of a SELECT instead of a preexisting table, you can implement it as such so that you don't have to repeat the t3 query twice:

WITH t3 AS (
  SELECT ...
), ins AS (
  INSERT INTO t1 (col1)
    SELECT NULL FROM t3
  RETURNING t1_id
), r AS (
  SELECT t1_id, ROW_NUMBER() OVER () AS rn
  FROM ins
), t AS (
  SELECT *, ROW_NUMBER() OVER () AS rn
  FROM t3
) INSERT INTO t2
  (col1, t1_id)
SELECT
  t.val1, r.t1_id
FROM t 
  JOIN r USING (rn);
Related Question