From: | "Ross J(dot) Reedstrom" <reedstrm(at)wallace(dot)ece(dot)rice(dot)edu> |
---|---|
To: | Pablo Sentis <grupoitem(at)ctv(dot)es> |
Cc: | PostgreSQL mailing list <pgsql-general(at)postgreSQL(dot)org> |
Subject: | Re: [GENERAL] RV: Serial fields |
Date: | 1999-08-21 17:33:58 |
Message-ID: | 19990821123358.A11089@wallace.ece.rice.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | marten | 1999-08-22 16:23:56 | Re: PostgreSQL order of evaluation |
Previous Message | Henrique Pantarotto | 1999-08-20 23:55:50 | Re: [GENERAL] Trigger documentation? Need more examples.. pleeeze.. ; -) |