current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I have a table defined like this:

CREATE TABLE wp_master (
  gid integer NOT NULL DEFAULT nextval('wp_master_gid_seq'::regclass),
  name character varying(80),
  ....
  type integer DEFAULT 4,
  CONSTRAINT p_key PRIMARY KEY (gid),
);

I want to insert data into the table from another table so I 

insert into wp_master ( name, .... type) select "NAME", ...., 1 from ."Tiri2011";

but I get the error:

ERROR:  duplicate key value violates unique constraint "p_key"
DETAIL:  Key (gid)=(2) already exists.

Why is postgres trying to put anything into the gid field when I have explicitly not included it in the list of columns? I assumed that gid pick up its value from the sequence.

Russell

share|improve this question

1 Answer 1

up vote 1 down vote accepted

Is is trying to insert the next value of the wp_master_gid_seq sequence. Declaring an id column as serial (auto-increment) will create a sequence which has a stored value of the last inserted id which was auto-incremented. If at anytime you inserted a gid value manually, it bypassed the sequence and the autoincrement function may become broken, because the sequence value did not get updated accordingly.

The easiest way to fix it is to change the value of the sequence to the (max gid value of your table) + 1. Just execute this once and you should be ok

select setval('wp_master_gid_seq', coalesce((select max(id)+1 from wp_master), 1), false)
share|improve this answer
    
I thought I had done just that: CREATE SEQUENCE base.wp_master_gid_seq INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 114 CACHE 1; –  Russell Fulton Dec 8 '13 at 2:41
    
I did try the setval as well but the problems still remains. select max(gid) from base.wp_master ; returns 113 –  Russell Fulton Dec 8 '13 at 3:02
    
I've figured it out. The issue was that the sequence was in another schema so no matter what I did to the sequence in the same schema as the table it had no effect. I've accepted Foibs' answer because although it did not actually answer the question it did give me vital information that I used to solve it. –  Russell Fulton Dec 8 '13 at 3:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.