转载

每日一博 | Oracle 迁移 PostgreSQL 经验总结

原荐顶 Oracle迁移PostgreSQL经验总结（SQL部分，未完待续）

发表于18小时前(2015-12-04 14:19) 阅读（ 337 ） | 评论（ 0 ） 0 人收藏此文章,

12月12日北京OSC源创会 —— 开源技术的年终盛典 » 每日一博 | Oracle 迁移 PostgreSQL 经验总结

摘要

Oracle迁移PostgreSQL数据库经验总结（SQL部分，未完待续），本文只包括了我工作中接触到或者用到的技术点，其实两个数据库设计上的差异是很大的，但是Oracle数据库有的功能，PostgreSQL大体上也都能实现

Oracle PostgreSQL 迁移

序号	项目	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	同义词	Oracle支持同义词	用视图代替
11	DUAL	SELECT 1+1 FROM DUAL	SELECT 1+1 或者 CREATE VIEW dual AS SELECT current_timestamp
12	ROWNUM	ROWNUM关键字	两种情况： 1.限制结果集数量，用于翻页等： SELECT * FROM T LIMIT 5 OFFSET 0 2.生成行号： ROW_NUMBER() OVER()
13	DECODE等判断函数	DECODE()	用标准的CASE WHEN THEN ELSE END语句替换
14	TO_CHAR	TO_CHAR(COL,FMT)，格式化字符串可以为空	TO_CHAR(COL1,'FM999999')，9的个数为字段长度，详细定义见：http://www.postgresql.org/docs/9.4/static/functions-formatting.html
15	TO_NUMBER	TO_NUMBER(COL,FMT)，格式化字符串可以为空	TO_NUMBER(COL1,'999999')，9的个数为字段长度，详细定义见：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 (注：VALUE3字段为数值类型)	INSERT INTO TEST(VALUE4)VALUES('')VALUE4=NULL
21	NULL和''	INSERT INTO TEST(VALUE4)VALUES('')[Result]VALUE4=NULL (注：VALUE3字段为字符类型)	INSERT INTO TEST(VALUE4)VALUES('')VALUE4=''
22	NULL和''	INSERT INTO TEST(VALUE4)VALUES(TO_DATE('','YYYYMMDD'))[Result]VALUE4=NULL (注：VALUE3字段为时间类型)	INSERT INTO TEST(VALUE6)VALUES(TO_DATE('','YYYYMMDD'))[Result]VALUE6=0001-01-01 BC
23	ADD_MONTHS	ADD_MONTHS(DATE,INT)	CREATE FUNCTION add_months(date, int) RETURNS date AS 'SELECT ($1 +($2::text\|\|'' month'')::interval)::date' LANGUAGE 'sql' 或SQL: SELECT ($1 +($2::text\|\|' month')::interval)
24	LAST_DAY	LAST_DAY(DATE)	创建函数来解决 CREATE OR REPLACE FUNCTION last_day(date) RETURNS date AS $$ SELECT (date_trunc('MONTH', $1) + INTERVAL '1 MONTH - 1 day')::date; $$ LANGUAGE 'sql'; 或SQL: SELECT (date_trunc('MONTH', $1) + interval '1 month - 1 day')::date;
25	MONTHS_BETWEEN	MONTHS_BETWEEN(DATE,DATE)	创建函数来解决 CREATE FUNCTION MONTH_BETWEEN(d1 timestamp,d2 timestamp) RETURNS NUMERIC AS 'SELECT (extract(year from age(d1,d2))*12 + extract(month from age(d1,d2)))::integer' LANGUAGE 'sql';
26	BITAND	BITAND(A,B)	A & B
27	MINUS	MINUS语句	以EXCEPT语句来替代
28	BIN_	SELECT BIN_TO_NUM(1,0,1,0) AS VALUE1 FROM DUAL	SELECT CAST(B'1010' AS INTEGER) AS VALUE1
29	UPDATE语句列列表	UPDATE accounts SET (contact_last_name, contact_first_name) = (SELECT last_name, first_name FROM salesmen WHERE salesmen.id = accounts.sales_id);	不支持该语法，需要拆分为多个单独的列
30	SUBSTR函数	如果从第一个开始取子串，可以从0开始，也可以从1开始，如果不是第一个开始，则从1开始计数，可以为负值，从字符串结尾计数，用于取最后几位。	从1开始计数。如果要取最后几位，可以用RIGHT函数解决。
31	子查询别名	如果FROM后只有一个子查询，该子查询可以没有别名	必须有别名
32	列(别)名为关键字	Oracle中比如name，type这样的关键字可以直接作为列的别名，比如：select xx name from t	需要加as，比如select xx as name from t
33	当前登录用户	SELECT USER FROM DUAL	select current_user
34	ALL_COL_COMMENTS	通过SELECT * FROM ALL_COL_COMMENTS可以获得列注释信息	select s.column_name as COLUMN_NAME,coalesce(col_description(c.oid,ordinal_position) ,s.column_name) as COMMENTS from information_schema.columns s,pg_class c where s.table_name = 'ac01_si' and s.table_name = c.relname and s.table_schema = current_schema() PG需要通过col_description获得列注释信息
35	修改表字段类型	1.如果字段无数据，可直接修改 2.如果有数据且新类型和原类型兼容，也可以直接修改 3.如果不兼容，可通过对原字段改名，然后增加新字段，再通过UPDATE语句对数据进行处理	1.如果新类型和原类型兼容，可直接修改2.如果不兼容，需要使用USING关键字然后提供一个类型转换的表达式