Thread: Return serial from insert
I would like to return the autoincremented serial number resulting from a new insert into a table, ideally as a result from the insert statement. Presumably this is the way to avoid race conditions between inserts into the database, because if I do 1) insert then 2) get max sequence val, 2 might be wrong. Thanks Rory -- Rory Campbell-Lange <rory@campbell-lange.net> <www.campbell-lange.net>
You should get the nextval() from the sequence and use that for the new insert and all related inserts. > -----Original Message----- > From: pgsql-novice-owner@postgresql.org > [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Rory > Campbell-Lange > Sent: Friday, November 08, 2002 10:30 AM > To: pgsql-novice@postgresql.org > Subject: [NOVICE] Return serial from insert > > > I would like to return the autoincremented serial number resulting from > a new insert into a table, ideally as a result from the insert > statement. > > Presumably this is the way to avoid race conditions between inserts into > the database, because if I do 1) insert then 2) get max sequence val, 2 > might be wrong. > > Thanks > Rory > > -- > Rory Campbell-Lange > <rory@campbell-lange.net> > <www.campbell-lange.net> > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > >
You misunderstand. I wrote "...get the nextval() from the sequence and use
that for the NEW INSERT..."
If you table definition is like:
create "myTable"
(
id serial primary key,
foo char(5),
bar numeric(10,2)
)
The sequence created would be "myTable_id_seq". To do what you want:
1) get the nextval -- (select nextval("myTable_id_seq"::text);)
2) name the image file using the value from nextval
3) insert the record using the value from nextval: (insert into "myTable"
(id,foo,bar) values (valueFromNextVal,'foob',5.4);)
2 & 3 are interchangeable
> -----Original Message-----
> From: Rory Campbell-Lange [mailto:mail@campbell-lange.net]
> Sent: Friday, November 08, 2002 11:02 AM
> To: Rod Kreisler
> Cc: Rory Campbell-Lange; pgsql-novice@postgresql.org
> Subject: Re: [NOVICE] Return serial from insert
>
>
> The problem is that I need to use the id for naming an image file.
> Consequently I need the id from the insert. You will note (from my
> original messgage) that I'm already using a SERIAL type field for the id
> column.
>
> On 08/11/02, Rod Kreisler (rod@23net.net) wrote:
> > You should get the nextval() from the sequence and use that for the new
> > insert and all related inserts.
> >
> > > -----Original Message-----
> > > From: pgsql-novice-owner@postgresql.org
> > > [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Rory
> > > Campbell-Lange
> > > Sent: Friday, November 08, 2002 10:30 AM
> > > To: pgsql-novice@postgresql.org
> > > Subject: [NOVICE] Return serial from insert
> > >
> > >
> > > I would like to return the autoincremented serial number
> resulting from
> > > a new insert into a table, ideally as a result from the insert
> > > statement.
>
> --
> Rory Campbell-Lange
> <rory@campbell-lange.net>
> <www.campbell-lange.net>
>
>
The problem is that I need to use the id for naming an image file. Consequently I need the id from the insert. You will note (from my original messgage) that I'm already using a SERIAL type field for the id column. On 08/11/02, Rod Kreisler (rod@23net.net) wrote: > You should get the nextval() from the sequence and use that for the new > insert and all related inserts. > > > -----Original Message----- > > From: pgsql-novice-owner@postgresql.org > > [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Rory > > Campbell-Lange > > Sent: Friday, November 08, 2002 10:30 AM > > To: pgsql-novice@postgresql.org > > Subject: [NOVICE] Return serial from insert > > > > > > I would like to return the autoincremented serial number resulting from > > a new insert into a table, ideally as a result from the insert > > statement. -- Rory Campbell-Lange <rory@campbell-lange.net> <www.campbell-lange.net>
On Fri, Nov 08, 2002 at 16:02:07 +0000, Rory Campbell-Lange <mail@campbell-lange.net> wrote: > The problem is that I need to use the id for naming an image file. > Consequently I need the id from the insert. You will note (from my > original messgage) that I'm already using a SERIAL type field for the id > column. Then you can select currval of the associated sequence. currval returns the last value of the sequence assigned by this backend. So as long as it is the same connection that did the insert and nothing else using this connection has modified the sequence since the insert, this will work.