Oracle数据库与PostgreSQL数据库对比，以及PostgreSQL遇到的各种坑

一.PostgreSQL数据库简介：

首先先百度一轮简介，PostgreSQL 是最先进的开放源码的数据库系统， 它提供了多版本并行控制，支持几乎所有 SQL 构件(包括子查询，事务和用户定 义类型和函数)， 并且可以获得非常广阔范围的(开发)语言绑定 (包括 C，C++，Java，perl，tcl，和 python)。由于PostgreSQL的开源免费，经历了从Oracle迁移PG数据库，遇到了各种坑，闲下来总结下，可能不是很完善，以后工作中遇到问题再来补充。

二.Oracle与PostgreSQL语法对比：

Oracle与PG对比

序号	类型	Oracle	PostgreSQL
1	当前时间	SYSDATE	可全部使用current_timestamp替换
2	序列	SEQNAME.NEXTVAL	NEXTVAL('SEQNAME')
3	固定值列	SELECT '1' AS COL1	SELECT CAST('1' AS TEXT) AS COL1
4	NVL	NVL函数	NVL可以用COALESCE函数替换
5	类型自动转换	Oracle某些情况下支持类型自动转换	会出现类型不匹配等错误，需要在Java或者sql中进行类型转换，使类型匹配
6	INSTR函数	instr('str1','str2')	strpos('str1','str2')
7	外连接	Oracle可简写为(+)	用LEFT JOIN等语句替换
8	层次查询	START WITH语句； CONNECT BY语句；	用WITH RECURSIVE语句；
9	数据库对象大小写	不区分大小写	创建数据库对象时要小写，这样才不区分SQL的大小写
10	DECODE等判断函数	DECODE()	用标准的CASE WHEN THEN ELSE END语句替换
11	同义词	Oracle支持同义词	用视图代替
12	DUAL	SELECT 1+1 FROM DUAL	SELECT 1+1或者CREATE VIEW dual AS SELECT current_timestamp
13	ROWNUM	ROWNUM关键字	两种情况： 1.限制结果集数量，用于翻页等：SELECT * FROM T LIMIT 5 OFFSET 0； 2.生成行号：使用分析函数ROW_NUMBER() OVER()；
14	TO_CHAR	TO_CHAR(COL,FMT)，格式化字符串可以为空，用于类型转换	TO_CHAR(COL1,FMT)，不支持FMT为空，使用类型转换使用cast()或者string::text的方式，详细定义见：http://www.postgresql.org/docs/9.4/static/functions-formatting.html
15	TO_NUMBER	TO_NUMBER(COL,FMT)，格式化字符串可以为空	TO_NUMBER(COL1,FMT)，不支持FMT为空，使用类型转换使用cast()或者string::text的方式，详细定义见：http://www.postgresql.org/docs/9.4/static/functions-formatting.html
16	NULL和''	ORACLE认为''等同于NULL	NULL和''不同
17	NULL和''	LENGTH('')为NULL	LENGTH('')为0
18	NULL和''	TO_DATE('','YYYYMMDD')为空	TO_DATE('','YYYYMMDD')为0001-01-01 BC
19	NULL和''	TO_NUMBER('',1)为NULL	TO_NUMBER('',1)，报错
20	NULL和''	INSERT INTO TEST(VALUE4)VALUES('')； [Result]VALUE4=NULL (注：VALUE4字段为数值类型)	INSERT INTO TEST(VALUE4)VALUES('')； VALUE4=NULL
21	NULL和''	INSERT INTO TEST(VALUE4)VALUES('')； [Result]VALUE4=NULL (注：VALUE4字段为字符类型)	INSERT INTO TEST(VALUE4)VALUES('')； VALUE4=''
22	NULL和''	INSERT INTO TEST(VALUE4)VALUES(TO_DATE('','YYYYMMDD'))； [Result]VALUE4=NULL (注：VALUE4字段为时间类型)	INSERT INTO TEST(VALUE4)VALUES(TO_DATE('','YYYYMMDD'))； [Result]VALUE4=0001-01-01 BC
23	SUBSTR函数	如果从第一个开始取子串，可以从0开始，也可以从1开始，如果不是第一个开始，则从1开始计数，可以为负值，从字符串结尾计数，用于取最后几位。	从1开始计数。如果要取最后几位，可以用RIGHT函数解决。
24	子查询别名	如果FROM后只有一个子查询，该子查询可以没有别名	必须有别名
25	列(别)名为关键字	Oracle中比如name，type这样的关键字可以直接作为列的别名，比如：select xx name from t	需要加as，比如select xx as name from t
26	当前登录用户	SELECT USER FROM DUAL	select current_user
27	修改表字段类型	1.如果字段无数据，可直接修改； 2.如果有数据且新类型和原类型兼容，也可以直接修改； 3.如果不兼容，可通过对原字段改名，然后增加新字段，再通过UPDATE语句对数据进行处理；	1.如果新类型和原类型兼容，可直接修改； 2.如果不兼容，需要使用USING关键字然后提供一个类型转换的表达式；
28	表名	默认为大写	默认为小写
29	事务处理机制差异	oracle后续语句可以正常操作。	Pg事务中任何操作失败都会导致后续语句的失败（包括查询，dml，lock[ nowait]等），可以开启ON_ERROR_ROLLBACK临时解决（影响性能）
30	DDL	Oracle执行 DDL语句如CREATE, DROP, RENAME, or ALTER时，会隐式提交事务；	PG在执行这类语句时，不会提交事务，需显式提交。
31	字符类型	GBK:汉字字符2个字节,ASCII码1个字节	UTF8字符集,汉字3个字节,ASCII吗1个字节
32	查看数据库中某个表的索引	select * from user_indexes where table_name=upper('表名')	select * from pg_statio_all_indexes where relname='表名'
33	查看表所占磁盘空间大小	select * from (select owner TABLESPACE_NAME,SUM(BYTES/1024/1024/1024),SEGMENT_NAME FROM dba_segments where segment_type like 'TABLE%' GROUP BY SEGMENT_NAME,TABLESPACE_NAME,owner order by 3 desc) where rownum<=10;	1.PG数据库查看所有表所占磁盘空间大小 SELECT SUM(t.SIZE/1024/1024/1024) from ( select table_schema || '.' || table_name as table_full_name,pg_total_relation_size('"'|| table_schema || '"."'||table_name|| '"') as size         from information_schema.tables order by pg_total_relation_size('"'||table_schema||'"."'||table_name||'"') DESC )t 2.PG数据库查看每个表所占磁盘空间大小 select table_schema || '.' || table_name as table_full_name,pg_total_relation_size('"'|| table_schema || '"."'||table_name|| '"') as size         from information_schema.tables order by pg_total_relation_size('"'||table_schema||'"."'||table_name||'"') DESC
34	查询创建索引的语句	select dbms_metadata.get_ddl ('INDEX','PK_TBL_ACC_INFO') from dual;	select * from pg_indexes where tablename='表名'

三.Oracle与PostgreSQL差异导致的一些坑：

1.性能测试，通过性能测试发现慢SQL瓶颈，由于oracle和pg有着不同的SQL优化器，所以同一个SQL两边可能走不同的执行计划，进而产生性能差异。

2.postgreSQL 中的||用法与其他数据库不同：
   select a||b from table1;当a或b其中一个为null时，该查询返回null。如果不希望要这个结果，可以使用COALESCE函数：
   select COALESCE(a,'')||COALESCE(b,'') from table1;

3.postgreSQL 一个类型转换声明一个从一种数据类型到另外一种数据类型的转换。
    CAST ( expression AS type )    expression::type
    CAST语法遵循 SQL 标准；::语法是PostgreSQL历史用法。

4.rownum
PostgresQL 中没有rownum ，无法 使用where rownum < = X 的方法进行分页，取而代之的是limit X ，offset Y 方法, 而ORACLE 中不允许使用LIMIT X 的 方法
ORACLE:
SELECT * FROM ( SELECT * FROM (SELECT * FROM SCHEMA.PREFIX_TABLE1 ORDER BY COL1 DESC,COL2 ASC) where ROWNUM <= 50 ORDER BY COL3 ASC,COL4 DESC)
WHERE ROWNUM <= 20 ORDER BY COL5 DESC,COL6 ASC;

postgreSQL:
select * from ( select * from (SELECT * FROM SCHEMA.PREFIX_TABLE1 ORDER BY COL1 DESC,COL2 ASC) selb order by col3 asc,col4 desc limit 50 ) sela
order by col5 desc,col6 asc limit 20;
-- 注意！！limit 必须用于order by 之后
OFFSET说明在开始返回行之前忽略多少行。 OFFSET 0和省略OFFSET子句是一样的。 如果OFFSET和LIMIT都出现了， 那么在计算LIMIT个行之前忽略OFFSET行。

如果使用LIMIT，那么用ORDER BY 子句把结果行约束成一个唯一的顺序是一个好主意。 否则你就会拿到一个不可预料的该查询的行的子集。 你要的可能是第十到二十行，但以什么顺序的十到二十？ 除非你声明了ORDER BY，否则顺序是不知道的。

查询优化器在生成查询规划的时候会考虑LIMIT，因此如果你给 LIMIT和OFFSET不同的东西，那么你很可能收到不同的规划（产生不同的行顺序）。 因此，使用不同的LIMIT/OFFSET值选择不同的查询结果的子集将生成不一致的结果， 除非你用ORDER BY强制一个可预料的顺序。这可不是臭虫； 这是一个很自然的结果，因为 SQL 没有许诺把查询的结果按照任何特定的顺序发出，除非用了ORDER BY来约束顺序。

OFFSET 子句忽略的行仍然需要在服务器内部计算；因此，一个很大的 OFFSET 可能还是不够有效率的。 

5.空串‘’和null：

在Oracle数据库中空串和null是等同的，而在PG数据库中两者是不同的，这就会引发一个问题，以前Oracle程序中更新一个字段为空串，但实际查询时使用is null仍然是可以查到的；而在PG中是查不到的；解决方法有三种：

（1）修改更新代码，但是工作量很大；

（2）修改后续查询代码，在查询条件中加上 select * from A where （A.aa is null or A.aa =‘’）；或者select * from A where （A.aa is  not null and A.aa ！=‘’），这样工作量也不小；

（3）使用触发器，当更新为空串时，将其更新为null；但是使用触发器可能会有失效；

注意：如果表结构中存在not null 约束，代码中更新为空串时，依然可以生效，数据库中会保存空串，因为PG数据库认为空串和null是不同的，这是最坑的。

6.numeric问题：

在Oracle中，numeric类型如果小数点后有0，则会去掉小数点后的0；而PG数据库会补0；比如numeric（10,3），在Oracle中如果是2.5，则会保存2.5；在PG数据库中会保存为2.500；

四.其他常用：

1.查看所有连接的用户
select * from pg_stat_activity where state='idle';

2.结束连接的进程
--pg_cancel_backend只是取消当前某一个进程的查询操作,但不能释放数据库连接;
--pg_terminate_backend可以在PG的后台杀死这个进程;
select pg_terminate_backend(2501);--括号中pid

3.使用ASCII查看数据库中记录含有回车换行符的数据
 select req_remark from accept_due_pay where req_remark like '%' ||chr(13)||'%' or req_remark like '%' ||chr(10)||'%'
4.去掉数据库中回车换行符
select replace(replace(req_remark,chr(13),''),chr(10),'') from accept_due_pay where id in ('266363','266364','26365',)

5.PG数据库修改关键字的字段名称
alter table ent_bank_buss rename "CURRENT_DATE" to current_date1;
alter table ent_bank_buss rename "CURRENT_TIME" to current_time1;