Thread: How to script inserts where id is needed as fk

Hi guys,

i need to script some insert statements. To simplify it a little bit so 
assume i got a

table "User" and a table called "Article". Both tables have an serial id 
column. In "Articles" there is a column i need to fill with the user id 
lets call it "create_user_id".

I want to do something like:

Insert into User (name) values ('User1');

Insert into Article ('create_user_id') values (1);
Insert into Article ('create_user_id') values (1);

Insert into User (name) values ('User2');

Insert into Article ('create_user_id') values (2);
Insert into Article ('create_user_id') values (2);

So you see i have set it to 1 and 2 this not good cause it might not be 
1 and 2.
I probably need the id returned by the "insert into User" query to use 
it for the "insert into Article" query.

On Nov 8, 2013, at 2:44 PM, Michael Schmidt wrote:

> Hi guys,
>
> i need to script some insert statements. To simplify it a little bit so assume i got a
>
> table "User" and a table called "Article". Both tables have an serial id column. In "Articles" there is a column i
needto fill with the user id lets call it "create_user_id". 
>
> I want to do something like:
>
> Insert into User (name) values ('User1');
>
> Insert into Article ('create_user_id') values (1);
> Insert into Article ('create_user_id') values (1);
>
> Insert into User (name) values ('User2');
>
> Insert into Article ('create_user_id') values (2);
> Insert into Article ('create_user_id') values (2);
>
> So you see i have set it to 1 and 2 this not good cause it might not be 1 and 2.
> I probably need the id returned by the "insert into User" query to use it for the "insert into Article" query.

If you are on PG 9.1 and above, you can use a writeable CTE to do this:
WITH users AS (    INSERT INTO User (name)    VALUES ('user1')    RETURNING id)INSERT INTO Article
('create_user_id')SELECTidFROM users; 

Jonathan

Am 08.11.2013 20:48, schrieb Jonathan S. Katz:
> On Nov 8, 2013, at 2:44 PM, Michael Schmidt wrote:
>
>> Hi guys,
>>
>> i need to script some insert statements. To simplify it a little bit so assume i got a
>>
>> table "User" and a table called "Article". Both tables have an serial id column. In "Articles" there is a column i
needto fill with the user id lets call it "create_user_id".
 
>>
>> I want to do something like:
>>
>> Insert into User (name) values ('User1');
>>
>> Insert into Article ('create_user_id') values (1);
>> Insert into Article ('create_user_id') values (1);
>>
>> Insert into User (name) values ('User2');
>>
>> Insert into Article ('create_user_id') values (2);
>> Insert into Article ('create_user_id') values (2);
>>
>> So you see i have set it to 1 and 2 this not good cause it might not be 1 and 2.
>> I probably need the id returned by the "insert into User" query to use it for the "insert into Article" query.
> If you are on PG 9.1 and above, you can use a writeable CTE to do this:
>
>     WITH users AS (
>         INSERT INTO User (name)
>         VALUES ('user1')
>         RETURNING id
>     )
>     INSERT INTO Article ('create_user_id')
>     SELECT id
>     FROM users;
>
> Jonathan
>
Thanks.

This will work if i am trying to insert just one article but i have 
multiple.

To extend my scenario a little bit i have a third level called 
'Incredient' and they need the Article id. I dont think this will work 
anymore with the with clause. Any new approaches?

On Nov 9, 2013, at 3:09 PM, Michael Schmidt wrote:

> Am 08.11.2013 20:48, schrieb Jonathan S. Katz:
>> On Nov 8, 2013, at 2:44 PM, Michael Schmidt wrote:
>>
>>> Hi guys,
>>>
>>> i need to script some insert statements. To simplify it a little bit so assume i got a
>>>
>>> table "User" and a table called "Article". Both tables have an serial id column. In "Articles" there is a column i
needto fill with the user id lets call it "create_user_id". 
>>>
>>> I want to do something like:
>>>
>>> Insert into User (name) values ('User1');
>>>
>>> Insert into Article ('create_user_id') values (1);
>>> Insert into Article ('create_user_id') values (1);
>>>
>>> Insert into User (name) values ('User2');
>>>
>>> Insert into Article ('create_user_id') values (2);
>>> Insert into Article ('create_user_id') values (2);
>>>
>>> So you see i have set it to 1 and 2 this not good cause it might not be 1 and 2.
>>> I probably need the id returned by the "insert into User" query to use it for the "insert into Article" query.
>> If you are on PG 9.1 and above, you can use a writeable CTE to do this:
>>
>>     WITH users AS (
>>         INSERT INTO User (name)
>>         VALUES ('user1')
>>         RETURNING id
>>     )
>>     INSERT INTO Article ('create_user_id')
>>     SELECT id
>>     FROM users;
>>
>> Jonathan
>>
> Thanks.
>
> This will work if i am trying to insert just one article but i have multiple.
>
> To extend my scenario a little bit i have a third level called 'Incredient' and they need the Article id. I dont
thinkthis will work anymore with the with clause. Any new approaches? 

You can chain CTEs, see pseudocode below:
WITH a AS (    -- INSERT code), b AS (    -- INSERT code    -- SELECT *    -- FROM  a)INSERT INTO cSELECT *FROM b

Jonathan

Michael Schmidt-2 wrote
> To extend my scenario a little bit i have a third level called 
> 'Incredient' and they need the Article id. I dont think this will work 
> anymore with the with clause. Any new approaches?

Why don't you just use a procedural language function?  plpgsql should be
sufficient for most requirements.

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/How-to-script-inserts-where-id-is-needed-as-fk-tp5777525p5777610.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

Am 09.11.2013 22:55, schrieb David Johnston:
> Michael Schmidt-2 wrote
>> To extend my scenario a little bit i have a third level called
>> 'Incredient' and they need the Article id. I dont think this will work
>> anymore with the with clause. Any new approaches?
> Why don't you just use a procedural language function?  plpgsql should be
> sufficient for most requirements.
>
> David J.
>
>
>
>
> --
> View this message in context:
http://postgresql.1045698.n5.nabble.com/How-to-script-inserts-where-id-is-needed-as-fk-tp5777525p5777610.html
> Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
>
>
My solution will be using select currval('atricle_id_seq') for the 
script. PL/pgSQL will be the nicer solution cause you can store the id 
and you dont need to select it all the time.

Thanks all!