PostgreSql 设置自增字段

一、概述

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

二、使用示例

postgres=# create table test(id serial primary key,name varchar);
CREATE TABLE
postgres=# \d test
                                 Table "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 | zhao
  2 | qian
  3 | 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 test
                                 Table "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 | zhao
  2 | qian
  3 | sun
(3 rows)