SQL function for getting the last inserted ID
Hi, A typical question that is probably asked before, but I can't find an answer in the archives or in the doc. Is there a SQL function for getting the last inserted ID for a auto increment or serial field? PostgreSQL has lastval(). I know it is returned in the query result, but I need to have the value inside a SQL function. Also I know that if you manually create a sequence for ID generation you can get the next value with: SELECT NEXT VALUE FOR seq_freq INTO freqid; But we are trying to keep our queries as generic as possible (close to impossible, but still) hence we want to use the SERIAL type. Or is there a way to find out what is the name of the sequence made when giving a SERIAL type to a column? thanks, -- Gijs Molenaar http://www.astro.uva.nl/people/gijs-molenaar/
On Tue, Mar 12, 2013 at 03:49:40PM +0100, Gijs Molenaar wrote:
Hi,
A typical question that is probably asked before, but I can't find an answer in the archives or in the doc.
Is there a SQL function for getting the last inserted ID for a auto increment or serial field? PostgreSQL has lastval(). I know it is returned in the query result, but I need to have the value inside a SQL function. Also I know that if you manually create a sequence for ID generation you can get the next value with:
SELECT NEXT VALUE FOR seq_freq INTO freqid;
But we are trying to keep our queries as generic as possible (close to impossible, but still) hence we want to use the SERIAL type.
Or is there a way to find out what is the name of the sequence made when giving a SERIAL type to a column? The values is returned through the mapi result.
Niels
thanks,
-- Gijs Molenaar http://www.astro.uva.nl/people/gijs-molenaar/
_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
-- Niels Nes, Centrum Wiskunde & Informatica (CWI) Science Park 123, 1098 XG Amsterdam, The Netherlands room L3.14, phone ++31 20 592-4098 sip:4098@sip.cwi.nl url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl
On 12-03-2013 18:38:41 +0100, Niels Nes wrote:
On Tue, Mar 12, 2013 at 03:49:40PM +0100, Gijs Molenaar wrote:
Hi,
A typical question that is probably asked before, but I can't find an answer in the archives or in the doc.
Is there a SQL function for getting the last inserted ID for a auto increment or serial field? PostgreSQL has lastval(). I know it is returned in the query result, but I need to have the value inside a SQL function. Also I know that if you manually create a sequence for ID generation you can get the next value with:
SELECT NEXT VALUE FOR seq_freq INTO freqid;
But we are trying to keep our queries as generic as possible (close to impossible, but still) hence we want to use the SERIAL type.
Or is there a way to find out what is the name of the sequence made when giving a SERIAL type to a column? The values is returned through the mapi result.
Alternatively, first retrieve the NEXT VALUE FOR <sequence> and use that value in your insert, instead of relying on the DEFAULT to call that for you. -- Fabian Groffen fabian@monetdb.org column-store pioneer http://www.monetdb.org/Home
Op 12-3-2013 18:44, Fabian Groffen wrote:
On 12-03-2013 18:38:41 +0100, Niels Nes wrote:
On Tue, Mar 12, 2013 at 03:49:40PM +0100, Gijs Molenaar wrote:
Hi,
A typical question that is probably asked before, but I can't find an answer in the archives or in the doc.
Is there a SQL function for getting the last inserted ID for a auto increment or serial field? PostgreSQL has lastval(). I know it is returned in the query result, but I need to have the value inside a SQL function. Also I know that if you manually create a sequence for ID generation you can get the next value with:
SELECT NEXT VALUE FOR seq_freq INTO freqid;
But we are trying to keep our queries as generic as possible (close to impossible, but still) hence we want to use the SERIAL type.
Or is there a way to find out what is the name of the sequence made when giving a SERIAL type to a column? The values is returned through the mapi result. Alternatively, first retrieve the NEXT VALUE FOR <sequence> and use that value in your insert, instead of relying on the DEFAULT to call that for you.
yes I tried both solutions but it would make the query much more different than the PostgreSQL version and we want to maintain both. Is there a way to discover the name of the <sequence> without having to create it manually? I think i'll just have to accept that it is impossible to have a set of generic or quite similar SQL statements for both MonetDB and PostgreSQL.
participants (3)
-
Fabian Groffen -
Gijs Molenaar -
Niels Nes