《卸甲笔记》-PostgreSQL和Oracle的SQL差异分析之二：序列的使用

PostgreSQL是世界上功能最强大的开源数据库，在国内得到了越来越多机构和开发者的青睐和应用。随着PostgreSQL的应用越来越广泛，Oracle向PostgreSQL数据库的数据迁移需求也越来越多。数据库之间数据迁移的时候，首先是迁移数据，然后就是SQL、存储过程、序列等程序中不同的数据库中数据的使用方式的转换。下面根据自己的理解和测试，写了一些SQL以及数据库对象转换方面的文章，不足之处，尚请多多指教。

序列

序列是一种数据库对象。可以供多个用户同时使用，得到不重复的、递增的数字值。Oracle和PostgreSQL都支持这种数据库对象。但是在使用过程中，有一些不太一样的地方。下面分析一下它在Oracle数据库和PostgreSQL数据库中的一些异同。

1、序列的一些属性

Oracle的序列创建的时候，可以指定起始值 (start with)，最大值 (maxvalue，默认是10^28)，最小值 (minvalue，默认1)，每次自增值 (increment by)、是否使用Cache (cache | nocache)、是否保证按请求顺序获得序列值 (order) 以及是否循环 (cycle | nocycle) 等属性。

PostgreSQL的序列除了以下三点，别的属性和Oracle都是相同的。
**①order 属性：Oracle有order属性，PostgreSQL则没有order属性。
②cache属性：Oracle的cache的最小值是2。小于最小值就是nocache。而PostgreSQL没有nocache值，cache的最小值和默认值都等于1，就是不使用缓存。
③maxvalue属性：Oracle序列的 maxvalue 的最大值和默认值都是10^28，并且当指定值超过10^28，且没超过number类型最大值 (10^126) 的时候，都自动采用10^28。而PostgreSQL的序列的 maxvalue 的最大值和默认值都是BIGINT类型的最大值( 即9223372036854775807)。虽然没有Oracle的大，但在实际使用中已经足够了。**

Oracle 序列

SQL> create sequence test1_seq maxvalue 1E126;
create sequence test1_seq maxvalue 1E126
                                   *
第 1 行出现错误:
ORA-01426: 数字溢出

SQL> create sequence test1_seq maxvalue 1E125;

序列已创建。

SQL> create sequence test2_seq
  2  cache  1;
create sequence test2_seq
*
第 1 行出现错误:
ORA-04010: CACHE 的值数必须大于 1

SQL> create sequence test2_seq
  2  start with 1
  3  minvalue 1
  4  increment by 1
  5  maxvalue 100
  6  nocache
  7  noorder
  8  cycle;

序列已创建。

SQL> select * from user_sequences;

SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE
------------------------------ ---------- ---------- ------------ - - ----------
LAST_NUMBER
-----------
TEST1_SEQ                               1 1.0000E+28            1 N N         20
          1

TEST2_SEQ                               1        100            1 Y N          0
          1

PostgreSQL 序列

postgres=# create sequence test1_seq maxvalue 9223372036854775808;
错误:  值 "9223372036854775808" 超出 bigint 类型范围
postgres=# create sequence test1_seq maxvalue 9223372036854775807;
CREATE SEQUENCE
postgres=# create sequence test2_seq
postgres-# nocache;
错误:  语法错误 在 "nocache" 或附近的
第2行nocache;
     ^
postgres=# create sequence test2_seq
postgres-# cache 1;
CREATE SEQUENCE
postgres=# create  sequence test3_seq
postgres-# start with 1
postgres-# minvalue 1
postgres-# increment by 1
postgres-# maxvalue 100
postgres-# cache 1
postgres-# cycle;
CREATE SEQUENCE
postgres=# \ds
                 关联列表
 架构模式 |   名称    |  类型  |  拥有者
----------+-----------+--------+----------
 public   | test1_seq | 序列数 | postgres
 public   | test2_seq | 序列数 | postgres
 public   | test3_seq | 序列数 | postgres
(3 行记录)

2、操作序列的函数

Oracle操作序列的函数，主要是nextval， currval。用于取得下一个值和当前值。Oracle中，没有重置序列值的方法。
PostgreSQL中操作序列的函数，除了nextval, currval，还提供了重置序列值的方法setval，以及一个lastval（返回会话内上一次调用序列的序列值）。

Oracle 操作序列函数

SQL> create sequence test1_seq maxvalue 1E125;

序列已创建。

SQL> select test1_seq.currval from dual;
select test1_seq.currval from dual
       *
第 1 行出现错误:
ORA-08002: 序列 TEST1_SEQ.CURRVAL 尚未在此会话中定义

SQL> select test1_seq.nextval from dual;

   NEXTVAL
----------
         1

SQL> select test1_seq.nextval from dual;

   NEXTVAL
----------
         2

SQL> select test1_seq.currval from dual;

   CURRVAL
----------
         2

PostgreSQL 操作序列函数

postgres=# create sequence test1_seq maxvalue 9223372036854775807;
CREATE SEQUENCE
postgres=# select currval('test1_seq');
错误:  在此会话中序列 "test1_seq" 的 currval 仍没被定义
postgres=# select lastval();
错误:  在这个会话中还没有定义lastval
postgres=# select nextval('test1_seq');
 nextval
---------
       1
(1 行记录)

postgres=# select nextval('test1_seq');
 nextval
---------
       2
(1 行记录)

postgres=# select currval('test1_seq');
 currval
---------
       2
(1 行记录)

postgres=# select lastval();
 lastval
---------
       2
(1 行记录)

postgres=# select setval('test1_seq', 1);
 setval
--------
      1
(1 行记录)

postgres=# select currval('test1_seq');
 currval
---------
       1
(1 行记录)

3、表中自增字段的实现方法

Oracle数据库中，表字段不能直接使用序列实现自增，但可以创建一个序列，写库的时候，先调用【序列.nextval】方法取得自增的值，再将该值赋给需要自增的字段，然后写库，从而实现自增字段。
PostgreSQL除了采用上面的赋值的方式外，还 提供了三种序列的数据类型，分别叫smallserial、serial、bigserial。它们三个实际上不是真正意义的数据类型，而是为该数字型字段生成一个序列，每当该字段数据增加的时候，都自动调用该序列的 nextval方法获得一个自动增大的、不重复的数字值。这三种数据类型，相当于执行以下的三个SQL：
*CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (

colname smallint(integer，bigint)  NOT NULL DEFAULT nextval('tablename_colname_seq')

);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;*
这三种序列类型对应的实际字段类型分别为smallint，integer，bigint。

Oracle 自增字段的实现

SQL> create sequence test1_seq;

序列已创建。

SQL> insert into o_test1 values(test1_seq.nextval, '赵大');

已创建 1 行。

SQL> insert into o_test1 values(test1_seq.nextval, '钱二');

已创建 1 行。

SQL> select * from o_test1;

        ID NAME
---------- ----------
         1 赵大
         2 钱二

PostgreSQL 自增字段的实现

postgres=# create table p_test(id bigint, name varchar(10));
CREATE TABLE
postgres=# create sequence  test_id_seq;
CREATE SEQUENCE
postgres=#  insert into p_test values(nextval('test_id_seq'),'张三');
INSERT 0 1
postgres=# insert into p_test values(nextval('test_id_seq'),'李四');
INSERT 0 1
postgres=# select * from p_test;
 id | name
----+------
  1 | 张三
  2 | 李四
(2 行记录)

postgres=# drop sequence test_id_seq;
DROP SEQUENCE

postgres=# create table p_test1(id smallserial, name varchar(10));
CREATE TABLE
postgres=# insert into p_test1(name) values('赵大');
INSERT 0 1
postgres=# insert into p_test1(name) values('钱二');
INSERT 0 1
postgres=# select * from p_test1;
 id | name
----+------
  1 | 赵大
  2 | 钱二
(2 行记录)

postgres=# \d p_test1;
                           数据表 "public.p_test1"
 栏位 |         类型          |                    修饰词
------+-----------------------+-----------------------------------------------
 id   | smallint              | 非空 默认 nextval('p_test1_id_seq'::regclass)
 name | character varying(10) |

postgres=# create table p_test2(id serial, name varchar(10));
CREATE TABLE
postgres=# insert into p_test2(name) values('孙三');
INSERT 0 1
postgres=# insert into p_test2(name) values('李四');
INSERT 0 1
postgres=# select * from p_test2;
 id | name
----+------
  1 | 孙三
  2 | 李四
(2 行记录)

postgres=# \d p_test2;
                           数据表 "public.p_test2"
 栏位 |         类型          |                    修饰词
------+-----------------------+-----------------------------------------------
 id   | integer               | 非空 默认 nextval('p_test2_id_seq'::regclass)
 name | character varying(10) |

postgres=# create table p_test3(id bigserial, name varchar(10));
CREATE TABLE
postgres=# insert into p_test3(name) values('周五');
INSERT 0 1
postgres=# insert into p_test3(name) values('吴六');
INSERT 0 1
postgres=# select * from p_test3;
 id | name
----+------
  1 | 周五
  2 | 吴六
(2 行记录)

postgres=# \d p_test3;
                           数据表 "public.p_test3"
 栏位 |         类型          |                    修饰词
------+-----------------------+-----------------------------------------------
 id   | bigint                | 非空 默认 nextval('p_test3_id_seq'::regclass)
 name | character varying(10) |

postgres=# \ds
                   关联列表
 架构模式 |      名称      |  类型  |  拥有者
----------+----------------+--------+----------
 public   | p_test1_id_seq | 序列数 | postgres
 public   | p_test2_id_seq | 序列数 | postgres
 public   | p_test3_id_seq | 序列数 | postgres
(3 行记录)

参考文档：

PostgreSQL 9.4.4 中文手册：字符类型，二进制类型，对象标识符类型
http://www.postgres.cn/docs/9.4/datatype-numeric.html （序列号类型）
http://www.postgres.cn/docs/9.4/functions-sequence.html （序列操作函数）
http://www.postgres.cn/docs/9.4/sql-createsequence.html （CREATE SEQUENCE）

Database SQL Language Reference：
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_6015.htm#SQLRF01314
CREATE SEQUENCE
http://docs.oracle.com/cd/E11882_01/server.112/e41084/pseudocolumns002.htm#SQLRF50944
Sequence Pseudocolumns