From: | joseph speigle <joe(dot)speigle(at)jklh(dot)us> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: changing start value of sequence after it was created... |
Date: | 2004-01-17 05:37:11 |
Message-ID: | 20040117053711.GC13730@www.sirfsup.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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:
> 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,
It is if I'm not mistaken TABLENAME_fieldname_SEQ
>
> Thanks in advance,
> Alan
>
From | Date | Subject | |
---|---|---|---|
Next Message | Ankur Arora | 2004-01-17 11:02:42 | query |
Previous Message | Alan T. Miller | 2004-01-17 00:48:04 | changing start value of sequence after it was created... |