PostgreSql 设置自增字段

一、概述

  序列类型是 PostgreSQL 特有的创建一个自增列的方法。包含 smallserial、serial和 bigserial 类型，它们不是真正的类型，只是为了创建唯一标识符列而存在的方便符号。其本质也是调用的序列，序列详情可参考：《PostgreSql 序列》

二、使用示例

postgres=# create table test(id serial primary key,name varchar);
CREATE TABLE
postgres=# \d testTable "public.test"Column |       Type        | Collation | Nullable |             Default
--------+-------------------+-----------+----------+----------------------------------id     | integer           |           | not null | nextval('test_id_seq'::regclass)name   | character varying |           |          |
Indexes:"test_pkey" PRIMARY KEY, btree (id)
postgres=# insert into test(name) values('zhao'),('qian'),('sun');
INSERT 0 3
postgres=# select * from test;id | name
----+------1 | zhao2 | qian3 | sun
(3 rows)--上述等价于
postgres=# create table test(id int primary key,name varchar);
CREATE TABLE
postgres=# create sequence test_id_seq start with 1 increment by 1 no minvalue no maxvalue cache 1;
CREATE SEQUENCE
postgres=# alter table test alter column id set default nextval('test_id_seq');
ALTER TABLE
postgres=# \d testTable "public.test"Column |       Type        | Collation | Nullable |             Default
--------+-------------------+-----------+----------+----------------------------------id     | integer           |           | not null | nextval('test_id_seq'::regclass)name   | character varying |           |          |
Indexes:"test_pkey" PRIMARY KEY, btree (id)postgres=# insert into test(name) values('zhao'),('qian'),('sun');
INSERT 0 3
postgres=# select * from test;id | name
----+------1 | zhao2 | qian3 | sun
(3 rows)