Re: Insert and obtain the pk - Mailing list pgsql-general

From Adam Ruth
Subject Re: Insert and obtain the pk
Date
Msg-id F023D5B1-1091-11D8-8291-000A959D1424@intercation.com
Whole thread Raw
In response to Insert and obtain the pk  ("Eliu Montoya" <eliu@itam.mx>)
List pgsql-general
You can call currval on the sequence that provided the value for the
primary key.  It's guaranteed to be the last value retrieved from that
sequence *for your session*.


insert into table1 (f1, f2) values ('a', 'b');


select currval('table1_seq');


Use the name of your sequence.  If the primary key is a SERIAL type,
then there will be a sequence in the background.  I believe it's
tablename_seq or something like that.


regards, adam


On Nov 6, 2003, at 11:26 AM, Eliu Montoya wrote:


<excerpt><fontfamily><param>Arial</param><x-tad-bigger> </x-tad-bigger></fontfamily>


<fontfamily><param>Arial</param><x-tad-bigger> </x-tad-bigger></fontfamily>


<fontfamily><param>Times New Roman</param><bigger><bigger> </bigger></bigger></fontfamily>


<fontfamily><param>Times New Roman</param><bigger><bigger>Hello
everybody,</bigger></bigger></fontfamily>


<fontfamily><param>Times New Roman</param><bigger><bigger>Does anyone
know an easy way to  insert a record (without a value for the primary
key because it is a sequence)  and at the same time obtain the value
of primary key that was assign to this new record.</bigger></bigger></fontfamily>


<fontfamily><param>Times New Roman</param><bigger><bigger> </bigger></bigger></fontfamily>


<fontfamily><param>Times New Roman</param><bigger><bigger>I always try
insert and then select with the same values that I used for the
insert, but I believe this is not a good way to do it.</bigger></bigger></fontfamily>


<fontfamily><param>Times New Roman</param><bigger><bigger> </bigger></bigger></fontfamily>


<fontfamily><param>Times New Roman</param><bigger><bigger>Any
suggestions???</bigger></bigger></fontfamily>

</excerpt>You can call currval on the sequence that provided the value for the
primary key.  It's guaranteed to be the last value retrieved from that
sequence *for your session*.

insert into table1 (f1, f2) values ('a', 'b');

select currval('table1_seq');

Use the name of your sequence.  If the primary key is a SERIAL type,
then there will be a sequence in the background.  I believe it's
tablename_seq or something like that.

regards, adam

On Nov 6, 2003, at 11:26 AM, Eliu Montoya wrote:

>  
>
>  
>
>  
>
> Hello everybody,
>
> Does anyone know an easy way to  insert a record (without a value for
> the primary key because it is a sequence)  and at the same time obtain
> the value of primary key that was assign to this new record.
>
>  
>
> I always try insert and then select with the same values that I used
> for the insert, but I believe this is not a good way to do it.
>
>  
>
> Any suggestions???

pgsql-general by date:

Previous
From: Manfred Koizar
Date:
Subject: Re: Help on update that subselects other records in table, uses joins
Next
From: "Ian Harding"
Date:
Subject: Re: Insert and obtain the pk