Re: GET LAST ID INSERT

On Tue, 2003-09-23 at 09:24, Maurizio Faini wrote:
> there is a way to get last id inserted into db or i have to make a new
> query?

Typically, you will have created the table with a SERIAL column, whose
default value is taken from a sequence:

junk=# CREATE TABLE xxx (
junk(#    id  SERIAL PRIMARY KEY,
junk(#    yyy TEXT
junk(# );
NOTICE:  CREATE TABLE will create implicit sequence "xxx_id_seq" for
SERIAL column "xxx.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"xxx_pkey" for table "xxx"
CREATE TABLE

Insert a row:
junk=# INSERT INTO xxx VALUES (DEFAULT, 'ljhldwtuy');
INSERT 1341077 1

Now get the value just created by the sequence:
junk=# SELECT currval('xxx_id_seq');
 currval
---------
       1
(1 row)

junk=# SELECT * FROM xxx WHERE id = 1;
 id |    yyy
----+-----------
  1 | ljhldwtuy
(1 row)


Another way to do it, is to use the value returned by the INSERT
statement.  If the table has oids, the first number returned (1341077 in
the example above) is the oid of the row just inserted.  However, this
only works when a single row is inserted in a table with oids.

junk=# SELECT * FROM xxx WHERE oid = 1341077;
 id |    yyy
----+-----------
  1 | ljhldwtuy
(1 row)

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                             http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "And we know that all things work together for good to
      them that love God, to them who are the called
      according to his purpose."
                                   Romans 8:28