serial 是 postgres 中很特殊的一个类型，与 mysql 中的 auto_increase 有些类似，

Usage

首先创建一个包含 Serial 的表

  1. CREATE TABLE basic.tbl_serial (
  2.     id serial PRIMARY KEY,
  3.       ival int
  4. )

从 pg_class 可以获得表的 oid

  1. SELECT * FROM pg_class WHERE relname = 'tbl_serial';
  2. --   oid  |  relname   | relnamespace | reltype
  3. -- -------+------------+--------------+---------
  4. --  16673 | tbl_serial |        16670 |   16675
  5. -- (1 row)

根据 oid，从下面的 SQL 语句获得表的详细信息，从这里可以看到 serial 实际上就是设置了一下该字段的默认值，就是一个 sequence 的 nextval。

  1. SELECT a.attname,
  2.   pg_catalog.format_type(a.atttypid, a.atttypmod),
  3.   (SELECT pg_catalog.pg_get_expr(d.adbin, d.adrelid, true)
  4.    FROM pg_catalog.pg_attrdef d
  5.    WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
  6.   a.attnotnull
  7. FROM pg_catalog.pg_attribute a
  8. WHERE a.attrelid = '16673' AND a.attnum > 0 AND NOT a.attisdropped
  9. ORDER BY a.attnum;
  10. --  attname | format_type |                 pg_get_expr                  | attnotnull
  11. -- ---------+-------------+----------------------------------------------+------------
  12. --  id      | integer     | nextval('basic.tbl_serial_id_seq'::regclass) | t
  13. --  ival    | integer     |                                              | f
  14. -- (2 rows)

Sequence

sequence 就是一个生成器，用于生成整数，这个数一般会作为表的主键，来保证主键不会重复。 postgres 为 seqence 提供了一些函数。

currval(regclass) 返回最近一次用 nextval 获取的指定序列的值
lastval() 返回最近一次用 nextval 获取的任何序列的值
nextval(regclass) 递增序列并返回新值
setval(regclass, bigint) 设置序列的当前值
setval(regclass, bigint, boolean) 设置序列的当前值以及is_called标志

postgres 中所有 sequence 都存储在 pg_catalog.pg_sequence 中

  1. SELECT * FROM pg_catalog.pg_sequence WHERE seqrelid = '16671';
  2. --  seqrelid | seqtypid | seqstart | seqincrement |   seqmax   | seqmin | seqcache | seqcycle
  3. -- ----------+----------+----------+--------------+------------+--------+----------+----------
  4. --     16671 |       23 |        1 |            1 | 2147483647 |      1 |        1 | f
  5. -- (1 row)

可以看到对于默认创建的 sequence，它的起始值是 1，每次会增加 1，最大值是 2147483647，也就是 31 个 1，32 位有符号整数能够存储的最大值。

也可以在创建表的时候指定 serial 的类型

smallserial 2 字节 自动增加
serial 4 字节 自动增加
bigserial 8 字节 自动增加

Tips

如果在 INSERT 中添加条目的时候手动指定值，sequence 的值是不会改变的，再次添加还是按照原来的值，这可能会产生一些错误，比如下面这种情况，手动指定 id 添加下一个值之后，再使用默认值插入，就会插入失败，因为唯一索引值已经被占用了。

  1. INSERT INTO basic.tbl_serial(id, ival) VALUES (2, 2);
  2. INSERT INTO basic.tbl_serial(ival) VALUES (3);
  3. -- ERROR:  duplicate key value violates unique constraint "tbl_serial_pkey"
  4. -- DETAIL:  Key (id)=(2) already exists.

这个时候可以使用 sequence 的 setval 重新指定下一个值，或者手动再调用一次 nextval，需要注意的是，在调用这些函数的时候需要参数需要使用 regclass。

  1. SELECT setval('basic.tbl_serial_id_seq'::regclass, 2);
  2. SELECT * FROM nextval('basic.tbl_serial_id_seq'::regclass);