Thread: getting val of serial field after insert

Hi All...

I have been inserting records into a table using the SQL insert statement
via the perl5 Pg module. One of the field types is serial, and I have been
trying to figure out how to get the value that was assigned in the field as
a result of the insert. The serial typed field is the only one guaranteed
to be unique, so I can't really do a search, and there are several people
adding data at once, so I can't reliable guess. Can anyone help?

Thanks VERY much!!!

Jim

Jim Archer wrote:

> I have been inserting records into a table using the SQL insert statement
> via the perl5 Pg module. One of the field types is serial, and I have been
> trying to figure out how to get the value that was assigned in the field as
> a result of the insert. The serial typed field is the only one guaranteed
> to be unique, so I can't really do a search, and there are several people
> adding data at once, so I can't reliable guess. Can anyone help?


From the SQL reference manual
-----------------------------
Usage

Create an ascending sequence called serial, starting at 101:

CREATE SEQUENCE serial START 101;


Select the next number from this sequence

SELECT NEXTVAL ('serial');

nextval
-------
    114


Use this sequence in an INSERT:

INSERT INTO distributors VALUES (NEXTVAL('serial'),'nothing');


Set the sequence value after a COPY FROM:

CREATE FUNCTION distributors_id_max() RETURNS INT4
       AS 'SELECT max(id) FROM distributors'
       LANGUAGE 'sql';
BEGIN;
COPY distributors FROM 'input_file';
SELECT setval('serial', distributors_id_max());
END;

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Christian Rudow                 E-Mail: Christian.Rudow@thinx.ch
ThinX networked business services    Stahlrain 10, CH-5200 Brugg
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Christian,

> Jim Archer wrote:
>
>> I have been inserting records into a table using the SQL insert statement
>> via the perl5 Pg module. One of the field types is serial, and I have been
>> trying to figure out how to get the value that was assigned in the field as
>> a result of the insert. The serial typed field is the only one guaranteed
>> to be unique, so I can't really do a search, and there are several people
>> adding data at once, so I can't reliable guess. Can anyone help?
>
>
>From the SQL reference manual
> -----------------------------
> Usage
>
> Create an ascending sequence called serial, starting at 101:
>
> CREATE SEQUENCE serial START 101;

Jim doesn't ask for a sequence _named_ serial, but for a field _typed_ serial
like:

CREATE TABLE distributors (
  id SERIAL,
  what CHAR(20));

what's the same like:

CREATE SEQUENCE distributors_id_seq;
CREATE TABLE distributors (
  id int4 not null default nextval('distributors_id_seq'),
  what CHAR(20)),

The type SERIAL is internal represented as SEQUENCE and the
name of that sequence is build temporary from the name of
the relation, the name of the attribute and a trailing 'seq'.
Use currval(), nextval() or setval() to query or modify the value.
Or simple query

SELECT last_value FROM distributors_id_seq;

to obtain the last value of 'id'.

Gerald.

At 16:32 +0300 on 09/07/1999, postgres@taifun.interface-business.de wrote:


> The type SERIAL is internal represented as SEQUENCE and the
> name of that sequence is build temporary from the name of
> the relation, the name of the attribute and a trailing 'seq'.
> Use currval(), nextval() or setval() to query or modify the value.
> Or simple query
>
> SELECT last_value FROM distributors_id_seq;
>
> to obtain the last value of 'id'.

BEEEEEEP... Wrong, wrong, wrong!

The last_value in the sequence table may not be the value you just
inserted, because another user may have also added a row at the same time,
and got another number, and that will be the last one...

The proper way to get the actual number that *you* entered, is to use the
function currval( 'distributors_id_seq' ). This function returns the last
value issued from the sequence to your session.

We have discussed it several times before, either on the SQL list or here.
It would be a good idea to look in the list archives before asking a
question.

There really should be a FAQ item about this - this subject is raised again
and again. It is certainly frequently asked.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma