Re: [GENERAL] getting val of serial field after insert

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.