Thread: INSERT ... RETURNING as Oracle
Hi, Sample table: create table basket ( id serial NOT NULL PRIMARY KEY, timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ); How can I make a one step insert into the table and get values of 'ID' ? I am trying to find a similar solution as in the oracle's INSERT ... RETURNING. If I use at first the INSERT, and after SELECT MAX(ID), the result will be uncertain. my system version: 7.0.2 Thx, Andras s-andras@freemail.hu
Sipos Andras writes: > create table basket ( > id serial NOT NULL PRIMARY KEY, > timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP > ); > > How can I make a one step insert into the table and get values of 'ID' ? > I am trying to find a similar solution as in the oracle's INSERT ... > RETURNING. We have this sort of thing on the wish list, but it doesn't exist yet. Some people feel it's better to calculate the default in the client program and insert it explicitly. This may be a reasonable alternative for some applications. > If I use at first the INSERT, and after SELECT MAX(ID), the result will be > uncertain. If you are worried about other transactions getting in the way then you should do SET TRANSACTION ISOLATION LEVEL SERIABLIZABLE. -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
"Sipos Andras" wrote: >create table basket (
> id serial NOT NULL PRIMARY KEY,
> timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
>);
>
>How can I make a one step insert into the table and get values of 'ID' ?
>I am trying to find a similar solution as in the oracle's INSERT ...
>RETURNING.
>
>If I use at first the INSERT, and after SELECT MAX(ID), the result will be
>uncertain.
The serial data type is actually an INT4 with a sequence, as you will have
seen when you created your table. Use currval after the insert to get the
latest value of the sequence in your current session.
junk=# create table basket (
junk(# id serial NOT NULL PRIMARY KEY,
junk(# timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
junk(# );
NOTICE: CREATE TABLE will create implicit sequence 'basket_id_seq' for SERIAL column 'basket.id'
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'basket_pkey' for table 'basket'
CREATE
junk=# insert into basket (timestamp) values (now());
INSERT 2091655 1
junk=# select currval('basket_id_seq');
currval
---------
1
(1 row)
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"Give, and it will be given to you. A good measure,
pressed down, taken together and running over,
will be poured into your lap. For with the same
measure that you use, it will be measured to
you." Luke 6:38
And using MAX is also horrifically slow once you start having any significant volumes of data.
Why not write a function to add the info, which selects from a sequence, inserts the new row using the sequence number, and then returns the sequence number? I've done it a number of times, and it's been quite successful so far.
Cheers...
MikeA
-----Original Message-----
From: Peter Eisentraut
To: Sipos Andras
Cc: pgsql-general@postgresql.org
Sent: 3-4-01 8:04 PM
Subject: Re: [GENERAL] INSERT ... RETURNING as Oracle
Sipos Andras writes:
> create table basket (
> id serial NOT NULL PRIMARY KEY,
> timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
> );
>
> How can I make a one step insert into the table and get values of 'ID'
?
> I am trying to find a similar solution as in the oracle's INSERT ...
> RETURNING.
We have this sort of thing on the wish list, but it doesn't exist yet.
Some people feel it's better to calculate the default in the client
program and insert it explicitly. This may be a reasonable alternative
for some applications.
> If I use at first the INSERT, and after SELECT MAX(ID), the result
will be
> uncertain.
If you are worried about other transactions getting in the way then you
should do SET TRANSACTION ISOLATION LEVEL SERIABLIZABLE.
--
Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
Nick West - Global Infrastructure Manager.
This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.
www.mimesweeper.com
**********************************************************************
On Sun, 4 Mar 2001, Peter Eisentraut wrote: > > If I use at first the INSERT, and after SELECT MAX(ID), the result will be > > uncertain. > > If you are worried about other transactions getting in the way then you > should do SET TRANSACTION ISOLATION LEVEL SERIABLIZABLE. I have some uncertainty about somethings that I've read from the FAQ. If I have a client INSERT a value in a table (all happens inside a BEGIN WORK ---- COMMIT WORK), which has a SERIAL column, and I want to reference that serial value on another INT column which is a foreign key of that SERIAL. Using currval() function will give me exact value as the FAQs say, and why? Is it the transaction that makes this work this way? Saludos... :-) System Administration: It's a dirty job, but someone told I had to do it. ----------------------------------------------------------------- Martín Marqués email: martin@math.unl.edu.ar Santa Fe - Argentina http://math.unl.edu.ar/~martin/ Administrador de sistemas en math.unl.edu.ar -----------------------------------------------------------------
"Martin A. Marques" <martin@math.unl.edu.ar> writes:
> If I have a client INSERT a value in a table (all happens inside a BEGIN
> WORK ---- COMMIT WORK), which has a SERIAL column, and I want to
> reference that serial value on another INT column which is a foreign key
> of that SERIAL. Using currval() function will give me exact value as the
> FAQs say, and why? Is it the transaction that makes this work this way?
No, it has nothing to do with transaction boundaries; it'd work the same
even if you did the currval() in a later transaction. The reason it
works is that each backend remembers the last nextval() result it got
for each sequence it's nextval'd in the current session. currval() just
pulls that value out of the local table without ever touching the shared
sequence object. Simple, eh?
Of course, you can still get burnt if you have triggers and such that
do nextval()s on the same sequence your main-line code is touching.
Then one of them might do a nextval() "behind your back", so to speak.
But that sort of foulup is a straight programming error that doesn't
have anything to do with parallel behavior of multiple clients.
regards, tom lane