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处开始再次循环。

巴特西

postgresql----serial类型和序列

最新文章

热门文章