Thread: Serial Field Autoincrementing

Serial Field Autoincrementing

From
Jim Beckstrom
Date:
(Not sure which list to post this to, so I'm doing novice and php).

        Using php, I create a table with a SERIAL field. In the same
script, the last thing I do is a COPY ... FROM ... USING DELIMITERS...to
load data I have outputted from another system into a text delimited
file.  I didn't get the autoincrementing of the serial field to work, so
I modified my output-from-another-system program to "manually" set the
serial field value.  That worked.  However, it does not seem to have
done a default SETVAL.  Do I do a "manual" SETVAL of 1 greater than the
highest value from the input text file, or am I missing something in the
create and copy process?


Jim Beckstrom


Re: Serial Field Autoincrementing

From
"Michael Paesold"
Date:
Jim Beckstrom write:

> (Not sure which list to post this to, so I'm doing novice and php).
>
>         Using php, I create a table with a SERIAL field. In the same
> script, the last thing I do is a COPY ... FROM ... USING DELIMITERS...to
> load data I have outputted from another system into a text delimited
> file.  I didn't get the autoincrementing of the serial field to work, so
> I modified my output-from-another-system program to "manually" set the
> serial field value.  That worked.  However, it does not seem to have
> done a default SETVAL.  Do I do a "manual" SETVAL of 1 greater than the
> highest value from the input text file, or am I missing something in the
> create and copy process?

The SERIAL field is basically an INTEGER field with an automatically
created SEQUENCE. This sequence will not be set or incremented, if
you manually insert any value into the field (like you did with COPY).

As you suggested, you must do a "manual" SETVAL. You can set it to
the highest value of the field. The next time NEXTVAL will be called,
it will increment the sequence before returning it.

# SELECT setval('name_of_sequence', (SELECT max(field) FROM tablename));

Regards,
Michael