PostgreSQL在插入时使现有的主键自动递增
问题描述:
具有5列的现有表。
qid
这是PK,问题
geo_type
用户输入
活动
我需要能够插入到表中,每次新插入都会获得一个新的主键ID(这将是现有的最大ID +1)。
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)
但这会给我这个错误
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.
这是表格
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;
每次插入都会增加PK,而无需我指定id列
and every insert will increment the PK without me having to specify the id column
答
新序列必须达到当前最大值。
The new sequence must be bumped to the current max value.
您可以重置它使用
SELECT setval('s_questions_qid_seq', max(id)) FROM sip_questions;