Обсуждение: 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!