[GENERAL] What's the best way to deal with the pk_seq sequence value after a restore (bulk loading)?

[Wang, Mary Y]

Hi All,

After a restore, I got a lot errors like this one : "duplicate key value 
violates unique constraint "bug_pkey"".  After looking at the dump file, it has

CREATE SEQUENCE bug_pk_seq
    INCREMENT BY 1
    MAXVALUE 2147483647
    NO MINVALUE
    CACHE 1;

ALTER TABLE
SELECT pg_catalog.setval('bug_pk_seq', 6818, true);
 setval
--------
   6818
(1 row)

Here is the bug table
CREATE TABLE bug (
    bug_id integer DEFAULT nextval('bug_pk_seq'::text) NOT NULL,
    group_id integer DEFAULT '0' NOT NULL,
    status_id integer DEFAULT '0' NOT NULL,
    priority integer DEFAULT '0' NOT NULL,
    category_id integer DEFAULT '0' NOT NULL,
    submitted_by integer DEFAULT '0' NOT NULL,
    assigned_to integer DEFAULT '0' NOT NULL,
    date integer DEFAULT '0' NOT NULL,
    summary text,
    details text,
    close_date integer,
    bug_group_id integer DEFAULT '0' NOT NULL,
    resolution_id integer DEFAULT '0' NOT NULL
);

Because the current value is 6818, during the restore process, it complained 
about "duplicate key value violates unique constraint "bug_pkey, because the 
value of  bug_pk_seq for a insert has been already been used.  So what is the 
best way to resolve this?  Should I set the value for bug_pk_seq to be 1 in the 
beginning of the dump file?

Any suggestions?

Mary



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general