本文共 7168 字，大约阅读时间需要 23 分钟。

 

作者：瀚高PG实验室 （Highgo PG Lab）- 瀚高大李

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 的值数必须大于 1SQL> 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          1TEST2_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 SEQUENCEpostgres=# create sequence test2_seqpostgres-# nocache;错误:  语法错误 在 "nocache" 或附近的第2行nocache;     ^postgres=# create sequence test2_seqpostgres-# cache 1;CREATE SEQUENCEpostgres=# create  sequence test3_seqpostgres-# start with 1postgres-# minvalue 1postgres-# increment by 1postgres-# maxvalue 100postgres-# cache 1postgres-# cycle;CREATE SEQUENCEpostgres=# \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----------         1SQL> select test1_seq.nextval from dual;   NEXTVAL----------         2SQL> select test1_seq.currval from dual;   CURRVAL----------         2

PostgreSQL 操作序列函数

postgres=# create sequence test1_seq maxvalue 9223372036854775807;CREATE SEQUENCEpostgres=# select currval('test1_seq');错误:  在此会话中序列 "test1_seq" 的 currval 仍没被定义postgres=# select lastval();错误:  在这个会话中还没有定义lastvalpostgres=# 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 TABLEpostgres=# create sequence  test_id_seq;CREATE SEQUENCEpostgres=#  insert into p_test values(nextval('test_id_seq'),'张三');INSERT 0 1postgres=# insert into p_test values(nextval('test_id_seq'),'李四');INSERT 0 1postgres=# select * from p_test; id | name----+------  1 | 张三  2 | 李四(2 行记录)postgres=# drop sequence test_id_seq;DROP SEQUENCEpostgres=# create table p_test1(id smallserial, name varchar(10));CREATE TABLEpostgres=# insert into p_test1(name) values('赵大');INSERT 0 1postgres=# insert into p_test1(name) values('钱二');INSERT 0 1postgres=# 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 TABLEpostgres=# insert into p_test2(name) values('孙三');INSERT 0 1postgres=# insert into p_test2(name) values('李四');INSERT 0 1postgres=# 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 TABLEpostgres=# insert into p_test3(name) values('周五');INSERT 0 1postgres=# insert into p_test3(name) values('吴六');INSERT 0 1postgres=# 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 中文手册：字符类型，二进制类型，对象标识符类型

 （序列号类型） （序列操作函数） （CREATE SEQUENCE）

Database SQL Language Reference：

CREATE SEQUENCE  Sequence Pseudocolumns