postgresql make existing primary key auto incrementing when inserting

Question

Existing table with 5 columns.

qid which is the PK, question geo_type user_input active

I need to be able to insert into the table with each new insert getting a new primary key id (which would be the max existing id +1).

So i need to be able to do this

insert into sip_questions (question,geo_type,user_input,active) values('noury','octagon',TRUE,TRUE)

but this give me this error

ERROR:  duplicate key value violates unique constraint "s_questions_pkey"
DETAIL:  Key (qid)=(1) already exists.

********** Error **********

ERROR: duplicate key value violates unique constraint "s_questions_pkey"
SQL state: 23505
Detail: Key (qid)=(1) already exists.

this is the table

CREATE TABLE public.sip_questions
(
  qid integer NOT NULL DEFAULT nextval('s_questions_qid_seq'::regclass),
  question character varying(200),
  geo_type character varying(10),
  user_input boolean,
  active boolean,
  CONSTRAINT s_questions_pkey PRIMARY KEY (qid)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.sip_questions
  OWNER TO postgres;

i know how to do this from a fresh table like this

ALTER TABLE table ADD COLUMN id SERIAL PRIMARY KEY;

and every insert will increment the PK without me having to specify the id column

JGH · Accepted Answer · 2019-03-19 18:43:41Z

2

The new sequence must be bumped to the current max value.

You can reset it using

SELECT setval('s_questions_qid_seq', max(id)) FROM sip_questions;

answered Mar 19, 2019 at 18:43

JGH

18.2k5 gold badges40 silver badges57 bronze badges

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

Collectives™ on Stack Overflow

postgresql make existing primary key auto incrementing when inserting

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related