Обсуждение: Insert and obtain the pk

On Thu, Nov 06, 2003 at 12:26:48PM -0600, Eliu Montoya wrote:

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

Given that you are already using a sequence, you can obtain the last
value given to you by means of currval().  No, this doesn't have any
concurrency problems with multiple backends inserting at the same time;
that's exactly the problem it solves.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"In Europe they call me Niklaus Wirth; in the US they call me Nickel's worth.
That's because in Europe they call me by name, and in the US by value!"

Eliu Montoya writes:

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

If you know the name of the sequence then you can use the function
currval() to get the last assigned value in your session.

--
Peter Eisentraut   peter_e@gmx.net

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

Actually, it's tablename_columname_seq , unless that exceeds namedatalen.  It shouldn't.  Mine never do... But if it
does,there is an algorithm for truncating the table and/or field name to fit. 

>>> Adam Ruth <aruth@intercation.com> 11/06/03 11:46AM >>>
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???