postgresql----serial类型和序列
postgresql----serial类型和序列
postgresql序列号（SERIAL）类型包括smallserial（smallint,short）,serial(int)和bigserial(bigint,long long int)，不管是smallserial,serial还是bigserial，其范围都是(1,9223372036854775807)，但是序列号类型其实不是真正的类型，当声明一个字段为序列号类型时其实是创建了一个序列，INSERT时如果没有给该字段赋值会默认获取对应序列的下一个值。
测试表1：
test=# create table tbl_serial(a serial,b varchar(2));
CREATE TABLE
test=# \d tbl_serial
Table "public.tbl_serial"
Column | Type | Modifiers
--------+----------------------+--------------------------------------------------------
a | integer | not null default nextval('tbl_serial_a_seq'::regclass)
b | character varying(2) |
从结果中看，声明字段a为serial类型，会自动创建一个名为tbl_serial_a_seq的序列，INSERT时缺省为该序列的下一个序列值nextval。
自动创建的序列如下定义：
test=# \d tbl_serial_a_seq
Sequence "public.tbl_serial_a_seq"
Column | Type | Value
---------------+---------+---------------------
sequence_name | name | tbl_serial_a_seq
last_value | bigint | 1
start_value | bigint | 1
increment_by | bigint | 1
max_value | bigint | 9223372036854775807
min_value | bigint | 1
cache_value | bigint | 1
log_cnt | bigint | 0
is_cycled | boolean | f
is_called | boolean | f
Owned by: public.tbl_serial.a
其实也可以先创建一个序列，然后将表的某字段默认值设为该序列的下一个序列值。
测试表2：
test=# create sequence sql_tbl_serial2_a increment by 1 minvalue 1 no maxvalue start with 1;
CREATE SEQUENCE
test=# create table tbl_serial2(a int not null default nextval('sql_tbl_serial2_a'),b varchar(2));
CREATE TABLE
test=# \d tbl_serial2
Table "public.tbl_serial2"
Column | Type | Modifiers
--------+----------------------+---------------------------------------------------------
a | integer | not null default nextval('sql_tbl_serial2_a'::regclass)
b | character varying(2) |
test=# \d sql_tbl_serial2_a
Sequence "public.sql_tbl_serial2_a"
Column | Type | Value
---------------+---------+---------------------
sequence_name | name | sql_tbl_serial2_a
last_value | bigint | 1
start_value | bigint | 1
increment_by | bigint | 1
max_value | bigint | 9223372036854775807
min_value | bigint | 1
cache_value | bigint | 1
log_cnt | bigint | 0
is_cycled | boolean | f
is_called | boolean | f
此方法和第一种直接使用serial类型效果完全相同，但是这里可以自己定义序列名称。
创建序列的语法如下：
test=# \h create sequence
Command: CREATE SEQUENCE
Description: define a new sequence generator
Syntax:
CREATE [ TEMPORARY | TEMP ] SEQUENCE [ IF NOT EXISTS ] name [ INCREMENT [ BY ] increment ]
[ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
[ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
[ OWNED BY { table_name.column_name | NONE } ]
其实和上面使用\d一个序列时对应的，
INCREMENT BY ： 每次序列增加（或减少）的步长
MINVALUE ： 序列最小值，NO MINVALUE表示没有最小值
MAXVALUE ： 序列最大值，NO MAXVALUE表示没有最大值
START WITH ：以什么序列值开始
CYCLE ： 序列是否循环使用
OWNED BY ： 可以直接指定一个表的字段，也可以不指定。
序列涉及的函数有：
| 函 数 | 返 回 类 型 | 描 述 |
| currval( regclass ) | bigint | 获取指定序列最近一次使用netxval后的数值，如果没有使用nextval而直接使用currval会出错。 |
| lastval() | bigint | 返回最近一次用 nextval 获取的任意序列的数值 |
| nextval( regclass ) | bigint | 递增序列并返回新值 |
| setval( regclass,bigint ) | bigint | 设置序列的当前数值 |
| setval( regclass,bigint ,boolean ) | bigint | 设置序列的当前数值以及 is_called 标志，如果为true则立即生效，如果为false，则调用一次nextval后才会生效。 |
示例1：获取序列tbl_serial_a_seq当前序列值
test=# select currval('tbl_serial_a_seq');
ERROR: currval of sequence "tbl_serial_a_seq" is not yet defined in this session
test=#
test=# select nextval('tbl_serial_a_seq');
nextval
---------
1
(1 row)
test=# select currval('tbl_serial_a_seq');
currval
---------
1
(1 row)
示例2：获取最近一次使用nextval的返回值
test=# select nextval('sql_tbl_serial2_a');
nextval
---------
5
(1 row)
test=# select lastval();
lastval
---------
5
(1 row)
test=# select nextval('tbl_serial_a_seq');
nextval
---------
3
(1 row)
test=# select lastval();
lastval
---------
3
(1 row)
示例3：将序列tbl_serial_a_seq当前值设为100
test=# select setval('tbl_serial_a_seq',100);
setval
--------
100
(1 row)
test=# select currval('tbl_serial_a_seq');
currval
---------
100
(1 row)
示例4：使用is_called标志设置序列值是马上生效(true)还是下次生效(false)
test=# select setval('tbl_serial_a_seq',200,true);
setval
--------
200
(1 row)
test=# select currval('tbl_serial_a_seq');
currval
---------
200
(1 row)
test=# select setval('tbl_serial_a_seq',300,false);
setval
--------
300
(1 row)
test=# select currval('tbl_serial_a_seq');
currval
---------
200
(1 row)
test=# select nextval('tbl_serial_a_seq');
nextval
---------
300
(1 row)
test=# select currval('tbl_serial_a_seq');
currval
---------
300
(1 row)
修改序列和创建序列的语法基本相同，只是用ALTER替换了CREATE，请参考
test=# \h alter sequence
Command: ALTER SEQUENCE
Description: change the definition of a sequence generator
Syntax:
ALTER SEQUENCE [ IF EXISTS ] name [ INCREMENT [ BY ] increment ]
[ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
[ START [ WITH ] start ]
[ RESTART [ [ WITH ] restart ] ]
[ CACHE cache ] [ [ NO ] CYCLE ]
[ OWNED BY { table_name.column_name | NONE } ]
如果一个序列是NO CYCLE，当序列值全部使用完会怎样呢？
test=# create sequence seq_test increment by 2 MINVALUE 1 MAXVALUE 5 START WITH 2 NO CYCLE;
CREATE SEQUENCE
test=# \d seq_test
Sequence "public.seq_test"
Column | Type | Value
---------------+---------+----------
sequence_name | name | seq_test
last_value | bigint | 2
start_value | bigint | 2
increment_by | bigint | 2
max_value | bigint | 5
min_value | bigint | 1
cache_value | bigint | 1
log_cnt | bigint | 0
is_cycled | boolean | f
is_called | boolean | f
test=# select nextval('seq_test');
nextval
---------
2
(1 row)
test=# select nextval('seq_test');
nextval
---------
4
(1 row)
test=# select nextval('seq_test');
ERROR: nextval: reached maximum value of sequence "seq_test" (5)
如果序列值用完了当然是错误了！！！如果是CYCLE则会重新从START处开始再次循环。
漫思
