postgresql 有以下三种方法设置主键递增:
1.方法一
highgo=# create table test_a
highgo-# (
highgo(# id serial,
highgo(# name character varying(128),
highgo(# constraint pk_test_a_id primary key( id)
highgo(# );
CREATE TABLE
highgo=# \d test_a
Table "public.test_a"
Column | Type | Collation | Nullable | Default
--------+------------------------+-----------+----------+------------------------------------
id | integer | | not null | nextval('test_a_id_seq'::regclass)
name | character varying(128) | | |
Indexes:
"pk_test_a_id" PRIMARY KEY, btree (id)
highgo=# insert into test_a(name) values('a');
INSERT 0 1
highgo=# insert into test_a(name) values('b');
INSERT 0 1
highgo=# select * from test_a;
id | name
----+------
1 | a
2 | b
(2 rows)
highgo=# insert into test_a(id,name) values(1,'c');
2022-11-15 17:54:01.015 CST [11510] ERROR: duplicate key value violates unique constraint "pk_test_a_id"
2022-11-15 17:54:01.015 CST [11510] DETAIL: Key (id)=(1) already exists.
2022-11-15 17:54:01.015 CST [11510] STATEMENT: insert into test_a(id,name) values(1,'c');
ERROR: duplicate key value violates unique constraint "pk_test_a_id"
DETAIL: Key (id)=(1) already exists.
highgo=#
2.方法二
highgo=# create table test_b
highgo-# (
highgo(# id serial PRIMARY KEY,
highgo(# name character varying(128)
highgo(# );
CREATE TABLE
highgo=# \d test_b
Table "public.test_b"
Column | Type | Collation | Nullable | Default
--------+------------------------+-----------+----------+------------------------------------
id | integer | | not null | nextval('test_b_id_seq'::regclass)
name | character varying(128) | | |
Indexes:
"test_b_pkey" PRIMARY KEY, btree (id)
highgo=# insert into test_b(name) values('a');
INSERT 0 1
highgo=# insert into test_b(name) values('b');
INSERT 0 1
highgo=# select * from test_b;
id | name
----+------
1 | a
2 | b
(2 rows)
highgo=# insert into test_b(id,name) values(1,'c');
2022-11-15 17:56:25.780 CST [11510] ERROR: duplicate key value violates unique constraint "test_b_pkey"
2022-11-15 17:56:25.780 CST [11510] DETAIL: Key (id)=(1) already exists.
2022-11-15 17:56:25.780 CST [11510] STATEMENT: insert into test_b(id,name) values(1,'c');
ERROR: duplicate key value violates unique constraint "test_b_pkey"
DETAIL: Key (id)=(1) already exists.
highgo=#
3.方法三
highgo=# create table test_c
highgo-# (
highgo(# id integer PRIMARY KEY,
highgo(# name character varying(128)
highgo(# );
CREATE TABLE
highgo=# CREATE SEQUENCE test_c_id_seq
highgo-# START WITH 1
highgo-# INCREMENT BY 1
highgo-# NO MINVALUE
highgo-# NO MAXVALUE
highgo-# CACHE 1
highgo-# OWNED BY test_c.id;
CREATE SEQUENCE
highgo=# insert into test_c(id,name) values(nextval('test_c_id_seq'),'a');
INSERT 0 1
highgo=# insert into test_c(id,name) values(nextval('test_c_id_seq'),'b');
INSERT 0 1
highgo=# select * from test_c;
id | name
----+------
1 | a
2 | b
(2 rows)
highgo=# insert into test_c(id,name) values('1','c');
2022-11-15 18:02:57.230 CST [11510] ERROR: duplicate key value violates unique constraint "test_c_pkey"
2022-11-15 18:02:57.230 CST [11510] DETAIL: Key (id)=(1) already exists.
2022-11-15 18:02:57.230 CST [11510] STATEMENT: insert into test_c(id,name) values('1','c');
ERROR: duplicate key value violates unique constraint "test_c_pkey"
DETAIL: Key (id)=(1) already exists.
highgo=# \d test_c
Table "public.test_c"
Column | Type | Collation | Nullable | Default
--------+------------------------+-----------+----------+---------
id | integer | | not null |
name | character varying(128) | | |
Indexes:
"test_c_pkey" PRIMARY KEY, btree (id)
highgo=#
方法一和方法二只是写法不同,实质上主键都通过使用 serial 类型来实现的, 使用serial类型,PG会自动创建一个序列给主键用,当插入表数据时如果不指定ID,则ID会默认使用序列的NEXT值。
方法三是先创建一张表,再创建一个序列,并关联这个表的id列。这种写法似乎更符合人们的思维习惯,也便于管理,如果系统遇到sequence 性能问题时,便于调整 sequence 属性。