agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedHow to script inserts where id is needed as fk
6+ messages / 3 participants
[nested] [flat]
* How to script inserts where id is needed as fk
@ 2013-11-08 19:44 Michael Schmidt <css.liquid@gmail.com>
2013-11-08 19:48 ` Re: How to script inserts where id is needed as fk Jonathan S. Katz <jonathan.katz@excoventures.com>
0 siblings, 1 reply; 6+ messages in thread
From: Michael Schmidt @ 2013-11-08 19:44 UTC (permalink / raw)
To: pgsql-sql
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.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to script inserts where id is needed as fk
2013-11-08 19:44 How to script inserts where id is needed as fk Michael Schmidt <css.liquid@gmail.com>
@ 2013-11-08 19:48 ` Jonathan S. Katz <jonathan.katz@excoventures.com>
2013-11-09 20:09 ` Re: How to script inserts where id is needed as fk Michael Schmidt <css.liquid@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Jonathan S. Katz @ 2013-11-08 19:48 UTC (permalink / raw)
To: Michael Schmidt <css.liquid@gmail.com>; +Cc: pgsql-sql
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to script inserts where id is needed as fk
2013-11-08 19:44 How to script inserts where id is needed as fk Michael Schmidt <css.liquid@gmail.com>
2013-11-08 19:48 ` Re: How to script inserts where id is needed as fk Jonathan S. Katz <jonathan.katz@excoventures.com>
@ 2013-11-09 20:09 ` Michael Schmidt <css.liquid@gmail.com>
2013-11-09 21:15 ` Re: How to script inserts where id is needed as fk Jonathan S. Katz <jonathan.katz@excoventures.com>
2013-11-09 21:55 ` Re: How to script inserts where id is needed as fk David Johnston <polobo@yahoo.com>
0 siblings, 2 replies; 6+ messages in thread
From: Michael Schmidt @ 2013-11-09 20:09 UTC (permalink / raw)
To: Jonathan S. Katz <jonathan.katz@excoventures.com>; +Cc: pgsql-sql
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 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
>
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?
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to script inserts where id is needed as fk
2013-11-08 19:44 How to script inserts where id is needed as fk Michael Schmidt <css.liquid@gmail.com>
2013-11-08 19:48 ` Re: How to script inserts where id is needed as fk Jonathan S. Katz <jonathan.katz@excoventures.com>
2013-11-09 20:09 ` Re: How to script inserts where id is needed as fk Michael Schmidt <css.liquid@gmail.com>
@ 2013-11-09 21:15 ` Jonathan S. Katz <jonathan.katz@excoventures.com>
1 sibling, 0 replies; 6+ messages in thread
From: Jonathan S. Katz @ 2013-11-09 21:15 UTC (permalink / raw)
To: Michael Schmidt <css.liquid@gmail.com>; +Cc: pgsql-sql
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 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
>>
> 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?
You can chain CTEs, see pseudocode below:
WITH a AS (
-- INSERT code
), b AS (
-- INSERT code
-- SELECT *
-- FROM a
)
INSERT INTO c
SELECT *
FROM b
Jonathan
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to script inserts where id is needed as fk
2013-11-08 19:44 How to script inserts where id is needed as fk Michael Schmidt <css.liquid@gmail.com>
2013-11-08 19:48 ` Re: How to script inserts where id is needed as fk Jonathan S. Katz <jonathan.katz@excoventures.com>
2013-11-09 20:09 ` Re: How to script inserts where id is needed as fk Michael Schmidt <css.liquid@gmail.com>
@ 2013-11-09 21:55 ` David Johnston <polobo@yahoo.com>
2013-11-09 23:20 ` Re: How to script inserts where id is needed as fk Michael Schmidt <css.liquid@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: David Johnston @ 2013-11-09 21:55 UTC (permalink / raw)
To: pgsql-sql
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-tp5777525p577...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to script inserts where id is needed as fk
2013-11-08 19:44 How to script inserts where id is needed as fk Michael Schmidt <css.liquid@gmail.com>
2013-11-08 19:48 ` Re: How to script inserts where id is needed as fk Jonathan S. Katz <jonathan.katz@excoventures.com>
2013-11-09 20:09 ` Re: How to script inserts where id is needed as fk Michael Schmidt <css.liquid@gmail.com>
2013-11-09 21:55 ` Re: How to script inserts where id is needed as fk David Johnston <polobo@yahoo.com>
@ 2013-11-09 23:20 ` Michael Schmidt <css.liquid@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Michael Schmidt @ 2013-11-09 23:20 UTC (permalink / raw)
To: pgsql-sql
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-tp5777525p577...
> 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!
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2013-11-09 23:20 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-11-08 19:44 How to script inserts where id is needed as fk Michael Schmidt <css.liquid@gmail.com>
2013-11-08 19:48 ` Jonathan S. Katz <jonathan.katz@excoventures.com>
2013-11-09 20:09 ` Michael Schmidt <css.liquid@gmail.com>
2013-11-09 21:15 ` Jonathan S. Katz <jonathan.katz@excoventures.com>
2013-11-09 21:55 ` David Johnston <polobo@yahoo.com>
2013-11-09 23:20 ` Michael Schmidt <css.liquid@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox