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

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 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.

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