Postgres: Problems using insert with select

Question

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

foibs · Accepted Answer · 2013-12-08 01:36:37Z

1

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)

answered Dec 8, 2013 at 1:36

foibs

3,4141 gold badge21 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Russell Fulton Over a year ago

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 Over a year ago

I did try the setval as well but the problems still remains. select max(gid) from base.wp_master ; returns 113

Russell Fulton Over a year ago

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.

Collectives™ on Stack Overflow

Postgres: Problems using insert with select

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related