Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
You need to join ins and t3 –  a_horse_with_no_name Mar 20 at 15:23
    
I tried that as well and it still only calculated the value only once. But perhaps I didn't have my join quite correct. I will edit my post to show what I tried with that. –  Joishi Bodio Mar 20 at 15:28
1  
You are inserting only one row into t1 and not supplying any value for t1.col1. Where should the data come for that column? Is t1.col1 related to t2.col1? –  ypercube Mar 20 at 15:31
    
ypercube - t1.col1 is allowed to be NULL and will be inserted in a later process. Because I was referencing the CTE as a SUBSELECT in the actual row values, I figured it would be executed more than once - but it turns out that I was incorrect in that assumption .. which is why I'm asking this question here. I've already tried researching for an answer on google for the past few hours and have been unable to find what's correct yet. And no, t1.col1 is not related to t2.col1 .. sorry for that confusion. –  Joishi Bodio Mar 20 at 15:36
1  
Still, INSERT INTO t1 (t1_id) VALUES (DEFAULT) inserts only 1 row into t1. So i t doesn't matter if you put the ins in the FROM clause and join it to t3 or not. Can you show us how you would insert 2 (or more) rows into t1? And more important, how you know which of the 2 (or more) t1.id values would be matched to the rows inserted into t2? –  ypercube Mar 20 at 15:43

1 Answer 1

up vote 2 down vote accepted

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);
share|improve this answer
    
The reason why I need the two tables is because there is another table that also will need to store values in t1.. (t1 will have links to both t2 and t4) t1 is meant to be a table for contact information (with fkeys to address, email, and phone number tables) and t2 and t4 are both entities in different domains that will need to have contact information associated with them.. I may have some of my vocabulary incorrect, but that's essentially why. Thank you for the answer - I will go test it out. –  Joishi Bodio Mar 20 at 16:40
    
Edited a minor mistake. Use the latest version. –  ypercube Mar 20 at 16:41
    
OK, it makes sense then. But you may not need the t2_id at all. Seems you can you use the t2(t1_id) as the PK of t2. –  ypercube Mar 20 at 16:51
    
:) It's giving me a syntax error with DEFAULT at the moment - trying to figure out what it might be. ERROR: syntax error at or near "DEFAULT" LINE 2: DEFAULT AS contact_detail_id –  Joishi Bodio Mar 20 at 16:56
    
Hm, It seems DEFAULT can't be used that way. Nor returning the t.pk –  ypercube Mar 20 at 17:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.