PostgreSQL Serial

概念描述

PostgreSQL中的SERIAL是一种特殊的类型，用于创建自增长的整数列，通常用作表的主键或其他需要唯一标识的列。SERIAL实际上不是真正的类型，而是一种便捷的写法，它会自动创建一个SEQUENCE对象，并将该SEQUENCE的下一个值作为该列的默认值。

• PostgreSQL SERIAL是一种特殊的用于生产整数序列数据库对象
• serial通常用于主键列，与mysql的AUTO_INCREMENT 概念类似。创建表时使用serial伪类型定义序列
• 当serial绑定的字段与表被删除时，serial自动被删除

测试验证

语法：

CREATE TABLE table_name(
   id SERIAL
   );

#创建一个表，将serial赋值伪类型给id列，PostgreSQL将执行下列步骤
1.创建serial对象并设置下一个生成值作为列的缺省值
2.给对应列增加NOT NULL约束，因为serial总是生成一个整数值，不能为null值
3.赋值serial的拥有者给id列，因此当id列或表被删除时，serial对象自动被删除

PostgreSQL 提供三种serial伪类型，分别为SMALLSERIAL, SERIAL, BIGSERIAL，对应范围如下

PostgresQL SERIAL示例:

#特别需要注意的是，serial列不会隐式在列上创建索引或是该列为主键列。当然可以很容易使用PRIMARY KEY关键字增加相应约束。
#下面语句创建fruits表，其中id类是serial类型

例：
CREATE TABLE fruits(
   id SERIAL PRIMARY KEY,
   name VARCHAR NOT NULL
);


#创建serial后会单独存在
course=# \d fruits
                                 Table "public.fruits"
 Column |       Type        | Collation | Nullable |              Default
--------+-------------------+-----------+----------+------------------------------------
 id     | integer           |           | not null | nextval('fruits_id_seq'::regclass)
 name   | character varying |           | not null |
Indexes:
    "fruits_pkey" PRIMARY KEY, btree (id)

#fruits表创建序列的时候会自动创建fruits_id_seq这个序列,fruits表的id字段引用这个serial
#当插入数据时会自动调用 nextval('fruits_id_seq'::regclass) 这个函数来递增id字段

查看serial信息:

course=# \d+ fruits_id_seq
                   Sequence "public.fruits_id_seq"
  Type   | Start | Minimum |  Maximum   | Increment | Cycles? | Cache
---------+-------+---------+------------+-----------+---------+-------
 integer |     1 |       1 | 2147483647 |         1 | no      |     1
Owned by: public.fruits.id

为serial赋值的方式:

#在insert语句中，可以忽略对应列或使用default关键字都可以给serial赋值

例：
1.无需给serial赋值,自动赋值
INSERT INTO fruits(name) VALUES('orange');

2.使用default自动赋值
INSERT INTO fruits(id,name) VALUES(DEFAULT,'apple');

查询结果：
course=# INSERT INTO fruits(name) VALUES('orange');
INSERT 0 1
course=# INSERT INTO fruits(id,name) VALUES(DEFAULT,'apple');
INSERT 0 1
course=# select * from fruits;
 id |  name
----+--------
  1 | orange
  2 | apple

#在插入数据时无需指定，会自动递增

注意:如果指定了值插入id字段则不会调用nextval函数,在之后使用序列的方式插入数据则会按照serial的上一个值进行递增,不会按照指定插入的值进行递增

course=# INSERT INTO fruits(id,name) VALUES(5,'apple');
INSERT 0 1
course=# select * from fruits;
 id |  name  
----+--------
  1 | orange
  2 | apple
  5 | apple
(3 rows)

course=# INSERT INTO fruits(id,name) VALUES(DEFAULT,'apple');
INSERT 0 1
course=# select * from fruits;
 id |  name  
----+--------
  1 | orange
  2 | apple
  5 | apple
  3 | apple
(4 rows)

查询serial的当前值/下一个值:

#查询当前serial的值
course=# select currval('fruits_id_seq');
 currval 
---------
       3
(1 row)

#查询下一个值
course=# select nextval('fruits_id_seq');
 nextval 
---------
       4
(1 row)

更新serial的起始值:

语法:
    alter sequence serial名称 restart with 7
#更新序列的起始值从7号开始    

例:
course=# alter sequence fruits_id_seq restart with 4;
course=# INSERT INTO fruits(id,name) VALUES(DEFAULT,'apple');
INSERT 0 1
course=# select * from fruits;
 id |  name  
----+--------
  1 | orange
  2 | apple
  5 | apple
  4 | apple
(4 rows)

#第二种方式(设置serial的起始值为8,从8开始)
course=# select setval('fruits_id_seq',8,false);
 setval 
--------
      8
(1 row)

#更新serial的起始值为5,从6开始
course=# select setval('fruits_id_seq',5);
 setval 
--------
      5
(1 row)

获取serial的名称(pg_get_serial_sequence):

#通过表名和绑定serial的字段来获取序列的名称
语法:
    pg_get_serial_sequence('table_name','column_name')

获取serial最近生成的值:

语法:
    SELECT currval(pg_get_serial_sequence('table_name', 'column_name'));

course=# SELECT currval(pg_get_serial_sequence('fruits', 'id'));
 currval 
---------
       3
       
#指定serial名称查询
course=# select currval('fruits_id_seq');
 currval 
---------
       3

参考文档

http://postgres.cn/v2/release/v/59

https://www.cnblogs.com/alianbog/p/5654604.html