Import using pgAdmin3 produces ERROR: duplicate key value violates unique constraint

Question

I am importing from the GUI a CSV file into a table. I have created the following table:

CREATE TABLE jira_tickets.daqa_rpt_tbl
(
 daqa_report_id bigint NOT NULL DEFAULT 'jira_tickets.daqa_id_seq'::regclass,
 project_name character varying(250) NOT NULL,
 jira_key character varying(250) NOT NULL,
 jira_summary text NOT NULL,
 issue_type character varying(100) NOT NULL,
 jira_status character varying(100) NOT NULL,
 jira_resolution character varying(250),
 jira_assignee character varying(250) NOT NULL,
 jira_reporter character varying(250) NOT NULL,
 jira_created text,
 jira_last_viewed text,
 jira_updated text,
 jira_resolved text,
 last_updated timestamp without time zone DEFAULT now(),
 CONSTRAINT "daqa_report_id_PK" PRIMARY KEY (daqa_report_id)
);

Error Message

When I import I get this error. Clearly the sequence is not incrementing when I move to the next row. Here is how I declare the sequence:

 CREATE SEQUENCE jira_tickets.daqa_id_seq
 INCREMENT 1
 MINVALUE 1
 MAXVALUE 1000000000000000000
 START 1
 CACHE 1;
ALTER TABLE jira_tickets.daqa_id_seq
 OWNER TO postgres;
GRANT ALL ON TABLE jira_tickets.daqa_id_seq TO public;
GRANT ALL ON TABLE jira_tickets.daqa_id_seq TO postgres;

Any suggestions or trouble shooting would be greatly appreciated.

• user1822: If your CSV import supplies a value for the daqa_report_id then that won't increment the sequence. You need to manually adjust the sequence after such an import. Search for "setting sequence value in postgres"

Answer 1

The column default for daqa_report_id would have to be:

nextval('jira_tickets.daqa_id_seq'::regclass)

Not:

(削除) 'jira_tickets.daqa_id_seq'::regclass (削除ここまで)

That would just fetch the OID for the sequence object from pg_class, which is converted to a meaningless, static bigint number.

pgAdmin has nothing to do with this.

But just use the pseudo data type bigserial instead. It does everything automatically. Here's how you would add a bigserial to an existing table:
Most efficient way to add a serial column to a huge table

• rray: Thank you! Yep that did it. How I was importing the data was my only point for mentioning pgAdmin.