Re: [GENERAL] RV: Serial fields

On Fri, Aug 20, 1999 at 12:52:20PM +0200, Pablo Sentis wrote:
>
> -----Mensaje original-----
> De: Pablo Sentis <grupoitem(at)ctv(dot)es>
> Para: PostgreSQL mailing list <pgsql-general(at)postgreSQL>
> Fecha: viernes, 20 de agosto de 1999 11:59
> Asunto: Serial fields
>
>
> Hi All!
>
> Working with PostgreSQL 6.5.1 on Intel platf.
>
>
> I´m trying to migrate an Acces database to postgress . First of all I´ve created the database structure in the Postgres machine with SQL table creation statements and this works properly . But the problem comes when I try to transfer the data via a flat file : If I try to do (from a Windows program) the data transfer when the table has a serial field , even though the original data is written in the postgres table all subsequent INSERTS from psql get a 'Duplicate index' error . In the exported flat file included the original serial values :
>
> MDB TEXT FILE POSTG table
> ==== ======= ========
>
> 1, NAME1 1, NAME1 1, NAME1
> 2, NAME2 2, NAME2 2, NAME2
> 3, NAME3 3, NAME3 3, NAME3
> 5, NAME5 5, NAME5 5, NAME5
>
> After this if I try an INSERT : INSERT INTO table (NAME) values ('NAME6') I get the error
>
> Of course I know I should not write on a read-only field so as
> I need to import the original serial values as they are referrenced in other tables in the database

Ah, I think here lies the answer to solving your confusion. Serial
fields in PostgreSQL are different than 'automatic' fields in MS-Access:
they're _not_ readonly. Instead, they're just and int4 field with a
special default value that comes from a sequence.

For example, lets say you do this:

CREATE TABLE mynames ( nameid serial, name text);

insert into mynames (name) values ('Fred');
insert into mynames (name) values ('Angela');

if you dump the database with this table you'll see:

CREATE SEQUENCE "mynames_nameid_seq" start 2 increment 1 maxvalue
2147483647 minvalue 1 cache 1 ;
SELECT nextval ('mynames_nameid_seq');

These two lines create the sequence associated with your serial field,
and set it's start value to the maximum value already in use.

CREATE TABLE "mynames" (
"nameid" int4 DEFAULT nextval('mynames_nameid_seq') NOT NULL,
"name" text);

This creates the table.

COPY "mynames" FROM stdin;
1 Fred
2 Angela
\.

Note that the COPY reads in the serial values as well.

CREATE UNIQUE INDEX "mynames_nameid_key" on "mynames" using btree (
"nameid" "int4_ops" );

And this index makes sure you don't reuse a serial value.

When you _do_ insert and set a serial field to a particular value, it's good
to reset the sequence, to make sure you don't get errors, like so:

select setval('mynames_nameid_seq',max(nameid)) from mynames;

I need to do this sort of thing when I recreate a sequence, or sometimes
after deleting a lot of test records, I'll do it to not have big gaps
in my serials.

Hope this helps,
Ross
>
> Regards from a sunny and almost boiling Alicante , Spain

Ah, got you beat there, Houston _is_ boiling!

--
Ross J. Reedstrom, Ph.D., <reedstrm(at)rice(dot)edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005