On Fri, Aug 20, 1999 at 12:52:20PM +0200, Pablo Sentis wrote:
>
> -----Mensaje original-----
> De: Pablo Sentis <[EMAIL PROTECTED]>
> 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 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., <[EMAIL PROTECTED]>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005
************
