I am converting an application from another database to postgresql. I am
creating my new table using the 'Serial' type for the primary key. When I
use the copy command to import all the old records from the old database,
the sequence never gets incremeneted, even though I am populating the id
field that has a sequence tied to it.

When I go to insert another record trying to take advantage of the sequence
once all the records have been imported, postgresql still thinks the next
value for the sequence is #1, where it would normally start, even though
there may be 5000 records in the table already. The insert will fail because
it is assigning the new record a number that already exists from the import.

I cannot set the sequence using setval(), beforehand because the number of
records I am importing is not always known at the time and I have this
problem across my conversion application with dozens of tables.

To solve my problem, is there a way to ask postgresql for the name of the
sequence of a given table? I was thinking that after my initial import of
data using the copy command, I could use the setval command on the sequence,
to reset the sequence so it will work for inserts of future records.

Thanks in advance,
Alan



---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

create the sequence first. The sequence might be like

create sequence Category_category_id_seq ;

CREATE TABLE Category (
category_id int4 unique default nextval('Category_category_id_seq') not null,
"name" varchar(100) NOT NULL default '',
PRIMARY KEY (category_id));

but I think the sequence needs be created first and if not inserts wont' increment the counter.

On Fri, Jan 16, 2004 at 05:48:04PM -0700, Alan T. Miller wrote:

It is if I'm not mistaken TABLENAME_fieldname_SEQ


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend