Re: SQL Help - Obtaining ID of record INSERTED

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Check out http://www.postgresql.org/docs/8.0/interactive/functions-sequence.html

You'll want to use something like "SELECT currval('<name of sequence>')". The thing
is, your orderid field in orderstemp has a sequence which it uses to grab the
next value. The currval function called with the name of the sequence as the
parameter will give you results as you are accustomed to. However, it is a
separate query and must be treated as such. Note that you need to call it
using the same database connection. And sequences are special - they always
update and do not roll back with failed transactions.

If you used default serial notation in your create table statement, your table is
likely named "orderid_id_seq":

SELECT currval('orderid_id_seq'::text);

Jason

-----Original Message-----
From: pgsql-admin-owner@xxxxxxxxxxxxxx
[mailto:pgsql-admin-owner@xxxxxxxxxxxxxx]On Behalf Of
josh@xxxxxxxxxxxxxxxx
Sent: Tuesday, August 16, 2005 4:09 PM
To: pgsql-admin@xxxxxxxxxxxxxx
Subject: [ADMIN] SQL Help - Obtaining ID of record INSERTED


Greetings.

INSERT INTO
    orderstemp (customerid,datecreated)
VALUES
    ('5443','8/16/2005 12:00PM')

The table orderstemp has a unique identifier field, orderid (of type 
SERIAL).

How can I obtain the orderid of the record inserted in the INSERT INTO 
statement in postgresql?

MSSQL does it like this:

INSERT INTO
    orderstemp (customerid,datecreated)
VALUES
    ('5443','8/16/2005 12:00PM')
SELECT @@identity as orderid

but this doens't work in postgresql.

Any ideas?  Thanks in advance.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
       message can get through to the mailing list cleanly


[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux