PostgreSQL和Oracle的常规使用对比
2023-07-30 19:59:00 
阿炯
PostgreSQL和Oracle的常规使用对比系列之
一：外连接
二：序列
三：rownum和聚合函数
四：特殊字符和符号
本文总结息《卸甲笔记》，感谢原作者。
--------------------------------------------------------------
一：外连接
PostgreSQL是世界上功能最强大的开源数据库，在国内得到了越来越多机构和开发者的青睐和应用。随着PostgreSQL的应用越来越广泛，Oracle向PostgreSQL数据库的数据迁移需求也越来越多。数据库之间数据迁移的时候，首先是迁移数据，然后就是SQL、存储过程、序列等程序中不同的数据库中数据的使用方式的转换。下面根据自己的理解和测试，写了一些SQL以及数据库对象转换方面的文章，不足之处，尚请多多指教。
外连接
Oracle数据库中，数据的外连接有两种写法。一种是标准SQL的写法。【left (outer) join XXX on】。这种标准写法Oracle和PostgreSQL都是支持的。但Oracle还有自己的一种写法，就是(+)。这种写法PostgreSQL不支持。所以数据迁移过来以后，需要将Oracle的这种特有的SQL写法转换成PostgreSQL支持的写法，程序才能够在PostgreSQL数据库中正常的运行。下面利用下面的表数据分析一下Oracle的这种外连接的写法和转换的方式。
SQL> select * from employ;
ID NAME AGE ADDR
---------- ---------- ---------- --------------------
1 赵大 20 山东济南
2 钱二 20 山东青岛
4 李四 20 山东济南
5 周五 20 山东日照
SQL> select * from salary;
EMPID YYMM SALARY
---------- ------ ----------
1 200010 6000
2 200010 5000
3 200010 7000
SQL> select * from achieve;
EMPID YYMM ACHIEVE
---------- ------ ----------
1 200010 150000
2 200010 100000
5 200011 50000
1、左外连接
Oracle的(+)写在等号右侧的时候，表示是以等号左边的表为主表，以右侧的表为连接表的左外连接。以左侧的表为主表，右侧的表的数据如果有，则把数据取出来显示。如果没有，则 显示空(NULL)。PostgreSQL中可以把它转化成对应的【left (outer) join on】语句就可以了。注意两个相同的表有多个关联条件的情况（比如下面的第二个SQL）。
Oracle 左外连接
SQL> select emp.id, emp.Name, sal.yymm, sal.salary
2 from employ emp, salary sal
3 where emp.id = sal.empid(+);
ID NAME YYMM SALARY
---------- ---------- ------ ----------
1 赵大 200010 6000
2 钱二 200010 5000
5 周五
4 李四
SQL> select ach.empid, ach.yymm, ach.achieve, sal.salary
2 from achieve ach, salary sal
3 where ach.empid=sal.empid(+)
4 and ach.yymm=sal.yymm(+);
EMPID YYMM ACHIEVE SALARY
---------- ------ ---------- ----------
1 200010 150000 6000
2 200010 100000 5000
5 200011 50000
对应的PostgreSQL的SQL
postgres=# select emp.id, emp.Name, sal.yymm, sal.salary
postgres-# from employ emp
postgres-# left outer join salary sal on emp.id=sal.empid;
id | name | yymm | salary
----+------+--------+--------
1 | 赵大 | 200010 | 6000
2 | 钱二 | 200010 | 5000
5 | 周五 | |
4 | 李四 | |
postgres=# select ach.empid, ach.yymm, ach.achieve, sal.salary
postgres-# from achieve ach
postgres-# left join salary sal
postgres-# on ach.empid=sal.empid
postgres-# and ach.yymm=sal.yymm;
empid | yymm | achieve | salary
-------+--------+---------+--------
1 | 200010 | 150000 | 6000
2 | 200010 | 100000 | 5000
5 | 200011 | 50000 |
2、右外连接
Oracle的(+)写在等号左侧的时候，表示是以等号右边的表为主表，以左侧的表为连接表的右外连接。以右侧的表为主表，左侧的表的数据如果有，则把数据取出来显示。如果没有，则 显示空(NULL)。PostgreSQL中可以把它转化成对应的【right (outer) join on】语句就可以了。
Oracle 右外连接
SQL> select emp.id, emp.Name, sal.yymm, sal.salary
2 from employ emp, salary sal
3 where emp.id(+) = sal.empid;
ID NAME YYMM SALARY
---------- ---------- ------ ----------
1 赵大 200010 6000
2 钱二 200010 5000
200010 7000
SQL> select ach.empid, ach.yymm, ach.achieve, sal.salary
2 from achieve ach, salary sal
3 where ach.empid(+)=sal.empid
4 and ach.yymm(+)=sal.yymm;
EMPID YYMM ACHIEVE SALARY
---------- ------ ---------- ----------
1 200010 150000 6000
2 200010 100000 5000
7000
对应的PostgreSQL的SQL
postgres=# select emp.id, emp.Name, sal.yymm, sal.salary
postgres-# from employ emp
postgres-# right outer join salary sal on emp.id=sal.empid;
id | name | yymm | salary
----+------+--------+--------
1 | 赵大 | 200010 | 6000
2 | 钱二 | 200010 | 5000
| | 200010 | 7000
postgres=# select ach.empid, ach.yymm, ach.achieve, sal.salary
postgres-# from achieve ach
postgres-# right join salary sal
postgres-# on ach.empid=sal.empid
postgres-# and ach.yymm=sal.yymm;
empid | yymm | achieve | salary
-------+--------+---------+--------
1 | 200010 | 150000 | 6000
2 | 200010 | 100000 | 5000
| | | 7000
3、多个连接条件的注意事项
Oracle中，两个表通过多个关连条件外连接的时候，如果多个条件中有没有写(+)的条件，则连接会自动变成内连接，而不再是外连接。这种情况应该是属于写SQL的失误。遇到这种情况的时候一定要注意。
Oracle
SQL> select ach.empid, ach.yymm, ach.achieve, sal.salary
2 from achieve ach, salary sal
3 where ach.empid=sal.empid
4 and ach.yymm=sal.yymm;
EMPID YYMM ACHIEVE SALARY
---------- ------ ---------- ----------
1 200010 150000 6000
2 200010 100000 5000
SQL> select ach.empid, ach.yymm, ach.achieve, sal.salary
2 from achieve ach, salary sal
3 where ach.empid=sal.empid(+)
4 and ach.yymm=sal.yymm(+);
EMPID YYMM ACHIEVE SALARY
---------- ------ ---------- ----------
1 200010 150000 6000
2 200010 100000 5000
5 200011 50000
SQL> select ach.empid, ach.yymm, ach.achieve, sal.salary
2 from achieve ach, salary sal
3 where ach.empid=sal.empid(+)
4 and ach.yymm=sal.yymm;
EMPID YYMM ACHIEVE SALARY
---------- ------ ---------- ----------
1 200010 150000 6000
2 200010 100000 5000
对应的PostgreSQL的SQL
postgres=# select ach.empid, ach.yymm, ach.achieve, sal.salary
postgres-# from achieve ach
postgres-# left join salary sal
postgres-# on ach.empid=sal.empid
postgres-# and ach.yymm=sal.yymm;
empid | yymm | achieve | salary
-------+--------+---------+--------
1 | 200010 | 150000 | 6000
2 | 200010 | 100000 | 5000
5 | 200011 | 50000 |
postgres=# select ach.empid, ach.yymm, ach.achieve, sal.salary
postgres-# from achieve ach, salary sal
postgres-# where ach.empid=sal.empid
postgres-# and ach.yymm=sal.yymm;
empid | yymm | achieve | salary
-------+--------+---------+--------
1 | 200010 | 150000 | 6000
2 | 200010 | 100000 | 5000
--------------------------------------------------------------
二：序列
序列是一种数据库对象。可以供多个用户同时使用，得到不重复的、递增的数字值。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
postgres=# select nextval('test1_seq');
nextval
---------
2
postgres=# select currval('test1_seq');
currval
---------
2
postgres=# select lastval();
lastval
---------
2
postgres=# select setval('test1_seq', 1);
setval
--------
1
postgres=# select currval('test1_seq');
currval
---------
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 | 李四
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 | 钱二
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 | 李四
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 | 吴六
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
参考文档：
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）
--------------------------------------------------------------
三：rownum和聚合函数
rownum
rownum是Oracle内部的一个伪列，用来表示数据在结果集中的行数。它是在同一层SQL中结果集查询出来以后，排序以前进行的操作。Oracle的rownum必须从1开始。如果使用>1这样的条件，将一条数据都取不到。Oracle中把检索结果取出来后，在画面上分页显示的功能，都是使用rownum，并且使用多层子查询来实现的。PostgreSQL中，没有rownum这样的伪列，但是提供了 limit 和 offset 两个关键字来实现类似的功能。limit表示取得记录的条数，而 offset 是指偏移位置，也就是表示从第几条开始取数据（offset从0开始）。
Oracle rownum
SQL> select rownum, id, name from student;
ROWNUM ID NAME
---------- ---------- ----------
1 1 张一
2 2 王二
3 3 李三
4 4 赵四
SQL> select rownum ,id,name from student order by name;
ROWNUM ID NAME
---------- ---------- ----------
3 3 李三
2 2 王二
1 1 张一
4 4 赵四
SQL> select rownum ,id,name from (select * from student order by name);
ROWNUM ID NAME
---------- ---------- ----------
1 3 李三
2 2 王二
3 1 张一
4 4 赵四
SQL> select rownum, id, name from student where rownum >1 order by name;
未选定行
SQL> select * from
2 (
3 select a.*, rownum rn
4 from (select * from student)a
5 where rownum <=3
6 )
7 where rn >2;
ID NAME RN
---------- ---------- ----------
3 李三 3
PostgreSQL limit 和 offset
postgres=# select * from student;
id | name
----+------
1 | 张一
2 | 王二
3 | 李三
4 | 赵四
postgres=# select * from student order by name;
id | name
----+------
3 | 李三
2 | 王二
1 | 张一
4 | 赵四
postgres=# select * from student limit 2;
id | name
----+------
1 | 张一
2 | 王二
postgres=# select * from student offset 2;
id | name
----+------
3 | 李三
4 | 赵四
postgres=# select * from student limit 1 offset 2;
id | name
----+------
3 | 李三
聚合函数
聚合函数是标准SQL语言中的函数，主要包括求计数(count)，求最大值(max)，求最小值(min)，求和(sum)和求平均数(avg)等几个。Oracle和PostgreSQL都支持聚合函数。但是对求和(sum)和求平均数(avg)而言，Oracle和PostgreSQL有一些不同。
对求和(sum)和求平均数(avg)而言，Oracle由于内部有自动类型转换，所以对于字符型数字（数据类型是字符，但是里面的数据都是数字）可以直接使用。而PostgreSQL内部没有自动类型转换，所以对于字符型数字而言，必须先使用类型转换，转换成数字后再使用函数进行计算。
Oracle 聚合函数
SQL> desc achieve;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(38)
NAME VARCHAR2(10)
ACHIEVE VARCHAR2(10)
SQL> select * from achieve;
ID NAME ACHIEVE
---------- ---------- ----------
1 赵大 5200
2 钱二 4500
3 孙三 6000
4 李四 8000
5 周五
SQL> select min(achieve) from achieve;
MIN(ACHIEV
----------
4500
SQL> select max(achieve) from achieve;
MAX(ACHIEV
----------
8000
SQL> select count(achieve) from achieve;
COUNT(ACHIEVE)
--------------
4
SQL> select sum(achieve) from achieve;
SUM(ACHIEVE)
------------
23700
SQL> select avg(achieve) from achieve;
AVG(ACHIEVE)
------------
5925
PostgreSQL 聚合函数
postgres=# \d achieve;
数据表 "public.achieve"
栏位 | 类型 | 修饰词
---------+-----------------------+--------
id | integer | 非空
name | character varying(10) |
achieve | character varying(10) |
索引：
"achieve_pkey" PRIMARY KEY, btree (id)
postgres=# select * from achieve;
id | name | achieve
----+------+---------
1 | 赵大 | 5200
2 | 钱二 | 4500
3 | 孙三 | 6000
4 | 李四 | 8000
5 | 周五 |
postgres=# select min(achieve) from achieve;
min
------
4500
postgres=# select max(achieve) from achieve;
max
------
8000
postgres=# select count(achieve) from achieve;
count
-------
4
postgres=# select sum(achieve) from achieve;
错误: 函数 sum(character varying) 不存在
第1行select sum(achieve) from achieve;
^
提示: 没有匹配指定名称和参数类型的函数. 您也许需要增加明确的类型转换.
postgres=# select avg(achieve) from achieve;
错误: 函数 avg(character varying) 不存在
第1行select avg(achieve) from achieve;
^
提示: 没有匹配指定名称和参数类型的函数. 您也许需要增加明确的类型转换.
postgres=# select sum(achieve::int) from achieve;
sum
-------
23700
postgres=# select avg(cast(achieve as int)) from achieve;
avg
-----------------------
5925.0000000000000000
--------------------------------------------------------------
四：特殊字符和符号
空字符串( '' )
Oracle中，空字符串('')很多时候是和null同样处理的。给varchar2和char类型赋值的时候按照null处理。在给日期类型或者数字类型赋值的时候，也是按照null处理。但是在where条件部分，=‘’和 is null 是不同的。PostgreSQL里面，空字符串('')和 null是不同的。完全是不同的处理。转换SQL的时候一定要注意。
Oracle 空字符串
SQL> create table o_test(value1 number, value2 varchar2(10), value3 date);
表已创建。
SQL> insert into o_test values('', '11111', to_date( '2010-01-01','YYYY-MM-DD'));
已创建 1 行。
SQL> insert into o_test values(1, '', to_date( '2010-01-01','YYYY-MM-DD'));
已创建 1 行。
SQL> insert into o_test values(2, '22222', to_date( '','YYYY-MM-DD'));
已创建 1 行。
SQL> select * from o_test;
VALUE1 VALUE2 VALUE3
---------- ---------- --------------
11111 01-1月 -10
1 01-1月 -10
2 22222
SQL> select * from o_test where value1 = '';
未选定行
SQL> select * from o_test where value1 is null;
VALUE1 VALUE2 VALUE3
---------- ---------- --------------
11111 01-1月 -10
SQL> select * from o_test where value2 = '';
未选定行
SQL> select * from o_test where value2 is null;
VALUE1 VALUE2 VALUE3
---------- ---------- --------------
1 01-1月 -10
SQL> select * from o_test where value3 = '';
未选定行
SQL> select * from o_test where value3 is null;
VALUE1 VALUE2 VALUE3
---------- ---------- --------------
2 22222
PostgreSQL 空字符串
postgres=# create table p_test(value1 integer, value2 varchar(10), value3 timestamp(0) without time zone);
CREATE TABLE
postgres=# insert into p_test values('', '11111', to_timestamp('2010-01-01', 'YYYY-MM-DD'));
错误: 无效的整数类型输入语法: ""
第1行insert into p_test values('', '11111', to_timestamp('2010-01...
postgres=# insert into p_test values(null, '11111', to_timestamp('2010-01-01', 'YYYY-MM-DD'));
INSERT 0 1
postgres=# insert into p_test values(1, '', to_timestamp('2010-01-01', 'YYYY-MM-DD'));
INSERT 0 1
postgres=# insert into p_test values(2, '22222', to_timestamp('', 'YYYY-MM-DD'));
INSERT 0 1
postgres=# select * from p_test;
value1 | value2 | value3
--------+--------+------------------------
| 11111 | 2010-01-01 00:00:00
1 | | 2010-01-01 00:00:00
2 | 22222 | 0001-01-01 00:00:00 BC
(3 行记录)
postgres=# select * from p_test where value1 = '';
错误: 无效的整数类型输入语法: ""
第1行select * from p_test where value1 = '';
postgres=# select * from p_test where value1 is null;
value1 | value2 | value3
--------+--------+---------------------
| 11111 | 2010-01-01 00:00:00
postgres=# select * from p_test where value2 = '';
value1 | value2 | value3
--------+--------+---------------------
1 | | 2010-01-01 00:00:00
postgres=# select * from p_test where value2 is null;
value1 | value2 | value3
--------+--------+--------
postgres=# select * from p_test where value3 is null;
value1 | value2 | value3
--------+--------+--------
postgres=# select * from p_test where to_char(value3, 'YYYY-MM-DD') ='0001-01-01';
value1 | value2 | value3
--------+--------+------------------------
2 | 22222 | 0001-01-01 00:00:00 BC
比较运算符
Oracle中比较运算符之间是可以有空格的。比如【> = 】这样的写法是允许的；而PostgreSQL中，运算符之间不能有空格。
Oracle 比较运算符
SQL> select * from o_test where value1 >= 2;
VALUE1 VALUE2 VALUE3
---------- ---------- --------------
2 22222
SQL> select * from o_test where value1 <= 3;
VALUE1 VALUE2 VALUE3
---------- ---------- --------------
1 01-1月 -10
2 22222
PostgreSQL 比较运算符
postgres=# select * from p_test where value1 > = 2;
错误: 语法错误 在 "=" 或附近的
第1行select * from p_test where value1 > = 2;
postgres=# select * from p_test where value1 >= 2;
value1 | value2 | value3
--------+--------+------------------------
2 | 22222 | 0001-01-01 00:00:00 BC
(1 行记录)
postgres=# select * from p_test where value1 < = 3;
错误: 语法错误 在 "=" 或附近的
第1行select * from p_test where value1 < = 3;
postgres=# select * from p_test where value1 <= 3;
value1 | value2 | value3
--------+--------+------------------------
1 | | 2010-01-01 00:00:00
2 | 22222 | 0001-01-01 00:00:00 BC
逃逸字符
逃逸的意思是有特殊意义的字符，前面如果加上逃逸字符的话，就不代表它的特殊含义，而代表它的字符本意。
Oracle的SQL中，使用( ' )来逃逸它本身。Oracle没有其它的标准逃逸字符。比如"n"并不代表回车，而是代表它的本意的"n"两个字符。如果输入回车的话，使用chr(10)来表示。在正则表达式等需要使用特殊字符本意的时候，使用关键字(escape)后面定义的字符进行逃逸。
PostgreSQL的SQL，也支持使用( ' )来逃逸它本身。老版的还支持使用反斜杠( )， 但新版已经不使用。Oracle定义了标准的逃逸字串（E'XXX'） 格式。在正则表达式等需要使用特殊字符本意的时候，默认使用""做逃逸字串。也可以使用关键字(escape)后面定义的字符进行逃逸。
数据迁移的时候，老版的PostgreSQL中，需要对逃逸字串进行特殊处理。比如把"“替换成"\\"。新版已经不再需要。可以直接按照Oracle的方式直接转换。
Oracle 逃逸字符
SQL> insert into o_test values(1, '12e34'6r8', null);
ERROR:
ORA-01756: 引号内的字符串没有正确结束
SQL> insert into o_test values(1, '12e34''6r8', null);
已创建 1 行。
SQL> insert into o_test values(1, '12e34\n6r8', null);
已创建 1 行。
SQL> insert into o_test values(1, '12e34' || chr(10) || '6r8', null);
已创建 1 行。
SQL> select * from o_test;
VALUE1 VALUE2 VALUE3
---------- ---------- --------------
1 1234_678
1 12e34%6r8
1 12e34'6r8
1 12e34\n6r8
1 12e34
6r8
SQL> select * from o_test where value2 like '%_%';
VALUE1 VALUE2 VALUE3
---------- ---------- --------------
1 1234_678
1 12e34%6r8
1 12e34'6r8
1 12e34\n6r8
1 12e34
6r8
SQL> select * from o_test where value2 like '%\_%';
VALUE1 VALUE2 VALUE3
---------- ---------- --------------
1 12e34\n6r8
SQL> select * from o_test where value2 like '%\_%' escape '\' ;
VALUE1 VALUE2 VALUE3
---------- ---------- --------------
1 1234_678
SQL> select * from o_test where value2 like '%r_%' escape 'r' ;
VALUE1 VALUE2 VALUE3
---------- ---------- --------------
1 1234_678
PostgreSQL 逃逸字符
postgres=# insert into p_test values(1, '12e34'6r8', null);
postgres'# ');
错误: 语法错误 在 "6" 或附近的
第1行insert into p_test values(1, '12e34'6r8', null);
postgres=# insert into p_test values(1, '12e34''6r8', null);
INSERT 0 1
postgres=# insert into p_test values(1, '\n\r', null);
INSERT 0 1
postgres=# insert into p_test values(1, E'ab\n\rc', null);
INSERT 0 1
postgres=# insert into p_test values(1, 'ab'|| chr(10) || 'c', null);
INSERT 0 1
postgres=# select * from p_test;
value1 | value2 | value3
--------+-----------+--------
1 | 1234_678 |
1 | 12e34%6r8 |
1 | 12e34'6r8 |
1 | \n\r |
1 | ab +|
| \rc |
1 | ab +|
| c |
postgres=# select * from p_test where value2 like '%_%';
value1 | value2 | value3
--------+-----------+--------
1 | 1234_678 |
1 | 12e34%6r8 |
1 | 12e34'6r8 |
1 | \n\r |
1 | ab +|
| \rc |
1 | ab +|
| c |
postgres=# select * from p_test where value2 like '%\_%';
value1 | value2 | value3
--------+----------+--------
1 | 1234_678 |
postgres=# select * from p_test where value2 like '%r_%' escape 'r' ;
value1 | value2 | value3
--------+----------+--------
1 | 1234_678 |
postgres=# select * from p_test where value2 like '%\\%';
value1 | value2 | value3
--------+--------+--------
1 | \n\r |
一：外连接
二：序列
三：rownum和聚合函数
四：特殊字符和符号
本文总结息《卸甲笔记》，感谢原作者。
--------------------------------------------------------------
一：外连接
PostgreSQL是世界上功能最强大的开源数据库，在国内得到了越来越多机构和开发者的青睐和应用。随着PostgreSQL的应用越来越广泛，Oracle向PostgreSQL数据库的数据迁移需求也越来越多。数据库之间数据迁移的时候，首先是迁移数据，然后就是SQL、存储过程、序列等程序中不同的数据库中数据的使用方式的转换。下面根据自己的理解和测试，写了一些SQL以及数据库对象转换方面的文章，不足之处，尚请多多指教。
外连接
Oracle数据库中，数据的外连接有两种写法。一种是标准SQL的写法。【left (outer) join XXX on】。这种标准写法Oracle和PostgreSQL都是支持的。但Oracle还有自己的一种写法，就是(+)。这种写法PostgreSQL不支持。所以数据迁移过来以后，需要将Oracle的这种特有的SQL写法转换成PostgreSQL支持的写法，程序才能够在PostgreSQL数据库中正常的运行。下面利用下面的表数据分析一下Oracle的这种外连接的写法和转换的方式。
SQL> select * from employ;
ID NAME AGE ADDR
---------- ---------- ---------- --------------------
1 赵大 20 山东济南
2 钱二 20 山东青岛
4 李四 20 山东济南
5 周五 20 山东日照
SQL> select * from salary;
EMPID YYMM SALARY
---------- ------ ----------
1 200010 6000
2 200010 5000
3 200010 7000
SQL> select * from achieve;
EMPID YYMM ACHIEVE
---------- ------ ----------
1 200010 150000
2 200010 100000
5 200011 50000
1、左外连接
Oracle的(+)写在等号右侧的时候，表示是以等号左边的表为主表，以右侧的表为连接表的左外连接。以左侧的表为主表，右侧的表的数据如果有，则把数据取出来显示。如果没有，则 显示空(NULL)。PostgreSQL中可以把它转化成对应的【left (outer) join on】语句就可以了。注意两个相同的表有多个关联条件的情况（比如下面的第二个SQL）。
Oracle 左外连接
SQL> select emp.id, emp.Name, sal.yymm, sal.salary
2 from employ emp, salary sal
3 where emp.id = sal.empid(+);
ID NAME YYMM SALARY
---------- ---------- ------ ----------
1 赵大 200010 6000
2 钱二 200010 5000
5 周五
4 李四
SQL> select ach.empid, ach.yymm, ach.achieve, sal.salary
2 from achieve ach, salary sal
3 where ach.empid=sal.empid(+)
4 and ach.yymm=sal.yymm(+);
EMPID YYMM ACHIEVE SALARY
---------- ------ ---------- ----------
1 200010 150000 6000
2 200010 100000 5000
5 200011 50000
对应的PostgreSQL的SQL
postgres=# select emp.id, emp.Name, sal.yymm, sal.salary
postgres-# from employ emp
postgres-# left outer join salary sal on emp.id=sal.empid;
id | name | yymm | salary
----+------+--------+--------
1 | 赵大 | 200010 | 6000
2 | 钱二 | 200010 | 5000
5 | 周五 | |
4 | 李四 | |
postgres=# select ach.empid, ach.yymm, ach.achieve, sal.salary
postgres-# from achieve ach
postgres-# left join salary sal
postgres-# on ach.empid=sal.empid
postgres-# and ach.yymm=sal.yymm;
empid | yymm | achieve | salary
-------+--------+---------+--------
1 | 200010 | 150000 | 6000
2 | 200010 | 100000 | 5000
5 | 200011 | 50000 |
2、右外连接
Oracle的(+)写在等号左侧的时候，表示是以等号右边的表为主表，以左侧的表为连接表的右外连接。以右侧的表为主表，左侧的表的数据如果有，则把数据取出来显示。如果没有，则 显示空(NULL)。PostgreSQL中可以把它转化成对应的【right (outer) join on】语句就可以了。
Oracle 右外连接
SQL> select emp.id, emp.Name, sal.yymm, sal.salary
2 from employ emp, salary sal
3 where emp.id(+) = sal.empid;
ID NAME YYMM SALARY
---------- ---------- ------ ----------
1 赵大 200010 6000
2 钱二 200010 5000
200010 7000
SQL> select ach.empid, ach.yymm, ach.achieve, sal.salary
2 from achieve ach, salary sal
3 where ach.empid(+)=sal.empid
4 and ach.yymm(+)=sal.yymm;
EMPID YYMM ACHIEVE SALARY
---------- ------ ---------- ----------
1 200010 150000 6000
2 200010 100000 5000
7000
对应的PostgreSQL的SQL
postgres=# select emp.id, emp.Name, sal.yymm, sal.salary
postgres-# from employ emp
postgres-# right outer join salary sal on emp.id=sal.empid;
id | name | yymm | salary
----+------+--------+--------
1 | 赵大 | 200010 | 6000
2 | 钱二 | 200010 | 5000
| | 200010 | 7000
postgres=# select ach.empid, ach.yymm, ach.achieve, sal.salary
postgres-# from achieve ach
postgres-# right join salary sal
postgres-# on ach.empid=sal.empid
postgres-# and ach.yymm=sal.yymm;
empid | yymm | achieve | salary
-------+--------+---------+--------
1 | 200010 | 150000 | 6000
2 | 200010 | 100000 | 5000
| | | 7000
3、多个连接条件的注意事项
Oracle中，两个表通过多个关连条件外连接的时候，如果多个条件中有没有写(+)的条件，则连接会自动变成内连接，而不再是外连接。这种情况应该是属于写SQL的失误。遇到这种情况的时候一定要注意。
Oracle
SQL> select ach.empid, ach.yymm, ach.achieve, sal.salary
2 from achieve ach, salary sal
3 where ach.empid=sal.empid
4 and ach.yymm=sal.yymm;
EMPID YYMM ACHIEVE SALARY
---------- ------ ---------- ----------
1 200010 150000 6000
2 200010 100000 5000
SQL> select ach.empid, ach.yymm, ach.achieve, sal.salary
2 from achieve ach, salary sal
3 where ach.empid=sal.empid(+)
4 and ach.yymm=sal.yymm(+);
EMPID YYMM ACHIEVE SALARY
---------- ------ ---------- ----------
1 200010 150000 6000
2 200010 100000 5000
5 200011 50000
SQL> select ach.empid, ach.yymm, ach.achieve, sal.salary
2 from achieve ach, salary sal
3 where ach.empid=sal.empid(+)
4 and ach.yymm=sal.yymm;
EMPID YYMM ACHIEVE SALARY
---------- ------ ---------- ----------
1 200010 150000 6000
2 200010 100000 5000
对应的PostgreSQL的SQL
postgres=# select ach.empid, ach.yymm, ach.achieve, sal.salary
postgres-# from achieve ach
postgres-# left join salary sal
postgres-# on ach.empid=sal.empid
postgres-# and ach.yymm=sal.yymm;
empid | yymm | achieve | salary
-------+--------+---------+--------
1 | 200010 | 150000 | 6000
2 | 200010 | 100000 | 5000
5 | 200011 | 50000 |
postgres=# select ach.empid, ach.yymm, ach.achieve, sal.salary
postgres-# from achieve ach, salary sal
postgres-# where ach.empid=sal.empid
postgres-# and ach.yymm=sal.yymm;
empid | yymm | achieve | salary
-------+--------+---------+--------
1 | 200010 | 150000 | 6000
2 | 200010 | 100000 | 5000
--------------------------------------------------------------
二：序列
序列是一种数据库对象。可以供多个用户同时使用，得到不重复的、递增的数字值。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
postgres=# select nextval('test1_seq');
nextval
---------
2
postgres=# select currval('test1_seq');
currval
---------
2
postgres=# select lastval();
lastval
---------
2
postgres=# select setval('test1_seq', 1);
setval
--------
1
postgres=# select currval('test1_seq');
currval
---------
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 | 李四
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 | 钱二
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 | 李四
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 | 吴六
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
参考文档：
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）
--------------------------------------------------------------
三：rownum和聚合函数
rownum
rownum是Oracle内部的一个伪列，用来表示数据在结果集中的行数。它是在同一层SQL中结果集查询出来以后，排序以前进行的操作。Oracle的rownum必须从1开始。如果使用>1这样的条件，将一条数据都取不到。Oracle中把检索结果取出来后，在画面上分页显示的功能，都是使用rownum，并且使用多层子查询来实现的。PostgreSQL中，没有rownum这样的伪列，但是提供了 limit 和 offset 两个关键字来实现类似的功能。limit表示取得记录的条数，而 offset 是指偏移位置，也就是表示从第几条开始取数据（offset从0开始）。
Oracle rownum
SQL> select rownum, id, name from student;
ROWNUM ID NAME
---------- ---------- ----------
1 1 张一
2 2 王二
3 3 李三
4 4 赵四
SQL> select rownum ,id,name from student order by name;
ROWNUM ID NAME
---------- ---------- ----------
3 3 李三
2 2 王二
1 1 张一
4 4 赵四
SQL> select rownum ,id,name from (select * from student order by name);
ROWNUM ID NAME
---------- ---------- ----------
1 3 李三
2 2 王二
3 1 张一
4 4 赵四
SQL> select rownum, id, name from student where rownum >1 order by name;
未选定行
SQL> select * from
2 (
3 select a.*, rownum rn
4 from (select * from student)a
5 where rownum <=3
6 )
7 where rn >2;
ID NAME RN
---------- ---------- ----------
3 李三 3
PostgreSQL limit 和 offset
postgres=# select * from student;
id | name
----+------
1 | 张一
2 | 王二
3 | 李三
4 | 赵四
postgres=# select * from student order by name;
id | name
----+------
3 | 李三
2 | 王二
1 | 张一
4 | 赵四
postgres=# select * from student limit 2;
id | name
----+------
1 | 张一
2 | 王二
postgres=# select * from student offset 2;
id | name
----+------
3 | 李三
4 | 赵四
postgres=# select * from student limit 1 offset 2;
id | name
----+------
3 | 李三
聚合函数
聚合函数是标准SQL语言中的函数，主要包括求计数(count)，求最大值(max)，求最小值(min)，求和(sum)和求平均数(avg)等几个。Oracle和PostgreSQL都支持聚合函数。但是对求和(sum)和求平均数(avg)而言，Oracle和PostgreSQL有一些不同。
对求和(sum)和求平均数(avg)而言，Oracle由于内部有自动类型转换，所以对于字符型数字（数据类型是字符，但是里面的数据都是数字）可以直接使用。而PostgreSQL内部没有自动类型转换，所以对于字符型数字而言，必须先使用类型转换，转换成数字后再使用函数进行计算。
Oracle 聚合函数
SQL> desc achieve;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(38)
NAME VARCHAR2(10)
ACHIEVE VARCHAR2(10)
SQL> select * from achieve;
ID NAME ACHIEVE
---------- ---------- ----------
1 赵大 5200
2 钱二 4500
3 孙三 6000
4 李四 8000
5 周五
SQL> select min(achieve) from achieve;
MIN(ACHIEV
----------
4500
SQL> select max(achieve) from achieve;
MAX(ACHIEV
----------
8000
SQL> select count(achieve) from achieve;
COUNT(ACHIEVE)
--------------
4
SQL> select sum(achieve) from achieve;
SUM(ACHIEVE)
------------
23700
SQL> select avg(achieve) from achieve;
AVG(ACHIEVE)
------------
5925
PostgreSQL 聚合函数
postgres=# \d achieve;
数据表 "public.achieve"
栏位 | 类型 | 修饰词
---------+-----------------------+--------
id | integer | 非空
name | character varying(10) |
achieve | character varying(10) |
索引：
"achieve_pkey" PRIMARY KEY, btree (id)
postgres=# select * from achieve;
id | name | achieve
----+------+---------
1 | 赵大 | 5200
2 | 钱二 | 4500
3 | 孙三 | 6000
4 | 李四 | 8000
5 | 周五 |
postgres=# select min(achieve) from achieve;
min
------
4500
postgres=# select max(achieve) from achieve;
max
------
8000
postgres=# select count(achieve) from achieve;
count
-------
4
postgres=# select sum(achieve) from achieve;
错误: 函数 sum(character varying) 不存在
第1行select sum(achieve) from achieve;
^
提示: 没有匹配指定名称和参数类型的函数. 您也许需要增加明确的类型转换.
postgres=# select avg(achieve) from achieve;
错误: 函数 avg(character varying) 不存在
第1行select avg(achieve) from achieve;
^
提示: 没有匹配指定名称和参数类型的函数. 您也许需要增加明确的类型转换.
postgres=# select sum(achieve::int) from achieve;
sum
-------
23700
postgres=# select avg(cast(achieve as int)) from achieve;
avg
-----------------------
5925.0000000000000000
--------------------------------------------------------------
四：特殊字符和符号
空字符串( '' )
Oracle中，空字符串('')很多时候是和null同样处理的。给varchar2和char类型赋值的时候按照null处理。在给日期类型或者数字类型赋值的时候，也是按照null处理。但是在where条件部分，=‘’和 is null 是不同的。PostgreSQL里面，空字符串('')和 null是不同的。完全是不同的处理。转换SQL的时候一定要注意。
Oracle 空字符串
SQL> create table o_test(value1 number, value2 varchar2(10), value3 date);
表已创建。
SQL> insert into o_test values('', '11111', to_date( '2010-01-01','YYYY-MM-DD'));
已创建 1 行。
SQL> insert into o_test values(1, '', to_date( '2010-01-01','YYYY-MM-DD'));
已创建 1 行。
SQL> insert into o_test values(2, '22222', to_date( '','YYYY-MM-DD'));
已创建 1 行。
SQL> select * from o_test;
VALUE1 VALUE2 VALUE3
---------- ---------- --------------
11111 01-1月 -10
1 01-1月 -10
2 22222
SQL> select * from o_test where value1 = '';
未选定行
SQL> select * from o_test where value1 is null;
VALUE1 VALUE2 VALUE3
---------- ---------- --------------
11111 01-1月 -10
SQL> select * from o_test where value2 = '';
未选定行
SQL> select * from o_test where value2 is null;
VALUE1 VALUE2 VALUE3
---------- ---------- --------------
1 01-1月 -10
SQL> select * from o_test where value3 = '';
未选定行
SQL> select * from o_test where value3 is null;
VALUE1 VALUE2 VALUE3
---------- ---------- --------------
2 22222
PostgreSQL 空字符串
postgres=# create table p_test(value1 integer, value2 varchar(10), value3 timestamp(0) without time zone);
CREATE TABLE
postgres=# insert into p_test values('', '11111', to_timestamp('2010-01-01', 'YYYY-MM-DD'));
错误: 无效的整数类型输入语法: ""
第1行insert into p_test values('', '11111', to_timestamp('2010-01...
postgres=# insert into p_test values(null, '11111', to_timestamp('2010-01-01', 'YYYY-MM-DD'));
INSERT 0 1
postgres=# insert into p_test values(1, '', to_timestamp('2010-01-01', 'YYYY-MM-DD'));
INSERT 0 1
postgres=# insert into p_test values(2, '22222', to_timestamp('', 'YYYY-MM-DD'));
INSERT 0 1
postgres=# select * from p_test;
value1 | value2 | value3
--------+--------+------------------------
| 11111 | 2010-01-01 00:00:00
1 | | 2010-01-01 00:00:00
2 | 22222 | 0001-01-01 00:00:00 BC
(3 行记录)
postgres=# select * from p_test where value1 = '';
错误: 无效的整数类型输入语法: ""
第1行select * from p_test where value1 = '';
postgres=# select * from p_test where value1 is null;
value1 | value2 | value3
--------+--------+---------------------
| 11111 | 2010-01-01 00:00:00
postgres=# select * from p_test where value2 = '';
value1 | value2 | value3
--------+--------+---------------------
1 | | 2010-01-01 00:00:00
postgres=# select * from p_test where value2 is null;
value1 | value2 | value3
--------+--------+--------
postgres=# select * from p_test where value3 is null;
value1 | value2 | value3
--------+--------+--------
postgres=# select * from p_test where to_char(value3, 'YYYY-MM-DD') ='0001-01-01';
value1 | value2 | value3
--------+--------+------------------------
2 | 22222 | 0001-01-01 00:00:00 BC
比较运算符
Oracle中比较运算符之间是可以有空格的。比如【> = 】这样的写法是允许的；而PostgreSQL中，运算符之间不能有空格。
Oracle 比较运算符
SQL> select * from o_test where value1 >= 2;
VALUE1 VALUE2 VALUE3
---------- ---------- --------------
2 22222
SQL> select * from o_test where value1 <= 3;
VALUE1 VALUE2 VALUE3
---------- ---------- --------------
1 01-1月 -10
2 22222
PostgreSQL 比较运算符
postgres=# select * from p_test where value1 > = 2;
错误: 语法错误 在 "=" 或附近的
第1行select * from p_test where value1 > = 2;
postgres=# select * from p_test where value1 >= 2;
value1 | value2 | value3
--------+--------+------------------------
2 | 22222 | 0001-01-01 00:00:00 BC
(1 行记录)
postgres=# select * from p_test where value1 < = 3;
错误: 语法错误 在 "=" 或附近的
第1行select * from p_test where value1 < = 3;
postgres=# select * from p_test where value1 <= 3;
value1 | value2 | value3
--------+--------+------------------------
1 | | 2010-01-01 00:00:00
2 | 22222 | 0001-01-01 00:00:00 BC
逃逸字符
逃逸的意思是有特殊意义的字符，前面如果加上逃逸字符的话，就不代表它的特殊含义，而代表它的字符本意。
Oracle的SQL中，使用( ' )来逃逸它本身。Oracle没有其它的标准逃逸字符。比如"n"并不代表回车，而是代表它的本意的"n"两个字符。如果输入回车的话，使用chr(10)来表示。在正则表达式等需要使用特殊字符本意的时候，使用关键字(escape)后面定义的字符进行逃逸。
PostgreSQL的SQL，也支持使用( ' )来逃逸它本身。老版的还支持使用反斜杠( )， 但新版已经不使用。Oracle定义了标准的逃逸字串（E'XXX'） 格式。在正则表达式等需要使用特殊字符本意的时候，默认使用""做逃逸字串。也可以使用关键字(escape)后面定义的字符进行逃逸。
数据迁移的时候，老版的PostgreSQL中，需要对逃逸字串进行特殊处理。比如把"“替换成"\\"。新版已经不再需要。可以直接按照Oracle的方式直接转换。
Oracle 逃逸字符
SQL> insert into o_test values(1, '12e34'6r8', null);
ERROR:
ORA-01756: 引号内的字符串没有正确结束
SQL> insert into o_test values(1, '12e34''6r8', null);
已创建 1 行。
SQL> insert into o_test values(1, '12e34\n6r8', null);
已创建 1 行。
SQL> insert into o_test values(1, '12e34' || chr(10) || '6r8', null);
已创建 1 行。
SQL> select * from o_test;
VALUE1 VALUE2 VALUE3
---------- ---------- --------------
1 1234_678
1 12e34%6r8
1 12e34'6r8
1 12e34\n6r8
1 12e34
6r8
SQL> select * from o_test where value2 like '%_%';
VALUE1 VALUE2 VALUE3
---------- ---------- --------------
1 1234_678
1 12e34%6r8
1 12e34'6r8
1 12e34\n6r8
1 12e34
6r8
SQL> select * from o_test where value2 like '%\_%';
VALUE1 VALUE2 VALUE3
---------- ---------- --------------
1 12e34\n6r8
SQL> select * from o_test where value2 like '%\_%' escape '\' ;
VALUE1 VALUE2 VALUE3
---------- ---------- --------------
1 1234_678
SQL> select * from o_test where value2 like '%r_%' escape 'r' ;
VALUE1 VALUE2 VALUE3
---------- ---------- --------------
1 1234_678
PostgreSQL 逃逸字符
postgres=# insert into p_test values(1, '12e34'6r8', null);
postgres'# ');
错误: 语法错误 在 "6" 或附近的
第1行insert into p_test values(1, '12e34'6r8', null);
postgres=# insert into p_test values(1, '12e34''6r8', null);
INSERT 0 1
postgres=# insert into p_test values(1, '\n\r', null);
INSERT 0 1
postgres=# insert into p_test values(1, E'ab\n\rc', null);
INSERT 0 1
postgres=# insert into p_test values(1, 'ab'|| chr(10) || 'c', null);
INSERT 0 1
postgres=# select * from p_test;
value1 | value2 | value3
--------+-----------+--------
1 | 1234_678 |
1 | 12e34%6r8 |
1 | 12e34'6r8 |
1 | \n\r |
1 | ab +|
| \rc |
1 | ab +|
| c |
postgres=# select * from p_test where value2 like '%_%';
value1 | value2 | value3
--------+-----------+--------
1 | 1234_678 |
1 | 12e34%6r8 |
1 | 12e34'6r8 |
1 | \n\r |
1 | ab +|
| \rc |
1 | ab +|
| c |
postgres=# select * from p_test where value2 like '%\_%';
value1 | value2 | value3
--------+----------+--------
1 | 1234_678 |
postgres=# select * from p_test where value2 like '%r_%' escape 'r' ;
value1 | value2 | value3
--------+----------+--------
1 | 1234_678 |
postgres=# select * from p_test where value2 like '%\\%';
value1 | value2 | value3
--------+--------+--------
1 | \n\r |