Thread: Getting last insert value
Hello, I have a table with many fields, and one of the fields is of type serial. I will do many inserts, and there will be many repeats of the other fields in the table, but, obviously, the serial field will be unique for all the rows. How can I get the value of serial field of the last row I inserted. I know I can select the maximum value from the serial field, but is there a better way, preferably something that takes constant time. Thanks, Yasir Malik
Le Samedi 15 Novembre 2003 15:18, vous avez écrit : > Hello, > I have a table with many fields, and one of the fields is of type serial. > I will do many inserts, and there will be many repeats of the other fields > in the table, but, obviously, the serial field will be unique for all the > rows. How can I get the value of serial field of the last row I inserted. > I know I can select the maximum value from the serial field, but is there > a better way, preferably something that takes constant time. > Thanks, > Yasir Malik > Doing a "select currval() from my_table" after your insert should work. For more details, see http://www.postgresql.org/docs/7.3/interactive/functions-sequence.html -- Guillaume <!-- http://absfr.tuxfamily.org/ http://pgsql-fr.tuxfamily.org/ -->.
Le Samedi 15 Novembre 2003 16:40, vous avez écrit :
> Doing a "select currval() from my_table" after your insert should work.
>
Actually, this is select currval('my_sequence')
Sorry about this.
> For more details, see
> http://www.postgresql.org/docs/7.3/interactive/functions-sequence.html
--
Guillaume <!-- http://absfr.tuxfamily.org/ http://pgsql-fr.tuxfamily.org/ -->.
Guillaume LELARGE <gleu@wanadoo.fr> writes:
> Doing a "select currval() from my_table" after your insert should work.
That's "select currval('my_table_pkcol_seq')" actually.
The above would have called the currval() function for every record of the
table which isn't what you want and in any case currval takes an argument.
--
greg
Thank you all for your help.
Yasir Malik
On Sat, 15 Nov 2003, Greg Stark wrote:
> Date: 15 Nov 2003 12:50:28 -0500
> From: Greg Stark <gsstark@mit.edu>
> To: Guillaume LELARGE <gleu@wanadoo.fr>
> Cc: Yasir Malik <ymalik@cs.stevens-tech.edu>, pgsql-sql@postgresql.org
> Subject: Re: [SQL] Getting last insert value
>
>
> Guillaume LELARGE <gleu@wanadoo.fr> writes:
>
> > Doing a "select currval() from my_table" after your insert should work.
>
> That's "select currval('my_table_pkcol_seq')" actually.
>
> The above would have called the currval() function for every record of the
> table which isn't what you want and in any case currval takes an argument.
>
> --
> greg
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
what does that mean the 'last row'? if client 1 inserts then client 2, the last row is client 2. do you want to know what row client 1 inserted? Personally I think your question is too broad. What are you trying to accomplish? Ted --- Yasir Malik <ymalik@cs.stevens-tech.edu> wrote: > Hello, > I have a table with many fields, and one of the > fields is of type serial. > I will do many inserts, and there will be many > repeats of the other fields > in the table, but, obviously, the serial field will > be unique for all the > rows. How can I get the value of serial field of > the last row I inserted. > I know I can select the maximum value from the > serial field, but is there > a better way, preferably something that takes > constant time. > Thanks, > Yasir Malik > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org __________________________________ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree
Guillaume,
Thank you very much for your response.
Yasir
On Sat, 15 Nov 2003, Guillaume LELARGE wrote:
> Date: Sat, 15 Nov 2003 17:41:41 +0000
> From: Guillaume LELARGE <gleu@wanadoo.fr>
> To: pgsql-sql@postgresql.org
> Subject: Re: [SQL] Getting last insert value
>
> Le Samedi 15 Novembre 2003 16:40, vous avez écrit :
> > Doing a "select currval() from my_table" after your insert should work.
> >
> Actually, this is
> select currval('my_sequence')
> Sorry about this.
>
> > For more details, see
> > http://www.postgresql.org/docs/7.3/interactive/functions-sequence.html
>
>
> --
> Guillaume
> <!-- http://absfr.tuxfamily.org/
> http://pgsql-fr.tuxfamily.org/ -->.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>