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)