PostgreSQL在插入时使现有的主键自动递增 [英] postgresql make existing primary key auto incrementing when inserting

问题描述

具有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;

这篇关于PostgreSQL在插入时使现有的主键自动递增的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！