Re: [GENERAL] RV: Serial fields

On Fri, Aug 20, 1999 at 12:52:20PM +0200, Pablo Sentis wrote:
>
> -----Mensaje original-----
> De: Pablo Sentis <grupoitem@ctv.es>
> Para: PostgreSQL mailing list <pgsql-general@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
Postgresmachine with SQL table creation statements and this works properly . But the problem comes when I try to
transferthe data via a flat file : If I try to do (from a Windows program) the data transfer when the table has a
serialfield , even though the original data is written in the postgres table all subsequent INSERTS from psql get a
'Duplicateindex' 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@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005