Oracle迁移PostgreSQL经验总结

本文完全转载自：http://my.oschina.net/liyuj/blog/539303?fromerr=L3ZJLcuW&utm_source=tuicool&utm_medium=referral

摘要 Oracle迁移PostgreSQL数据库经验总结（SQL部分，未完待续），本文只包括了我工作中接触到或者用到的技术点，其实两个数据库设计上的差异是很大的，但是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关键字然后提供一个类型转换的表达式