ORACLE 与 PostgreSQL 差别整理

目前正在做一个项目得数据库迁移(oracle->postgresql),查找了一些资料将2者得差异整理如下:

ORACLE 与 PostgreSQL差别
NO	问题点	Oracle	PostgreSQL
1	DUAL	SELECT 1+1 FROM DUAL	SELECT  1+1 或者 CREATE VIEW dual AS       SELECT 'X'::VARCHAR(1) AS DUMMY 再 SELECT 1+1 FROM DUAL
2	NEXTVAL	SELECT A_TABLE_SEQUENCE.NEXTVAL FROM   DUAL	SELECT NEXTVAL('A_TABLE_SEQUENCE') FROM   DUAL
3	ROWNUM	①SELECT * FROM  AGE_TYPE WHERE ROWNUM<=5	①SELECT * FROM AGE_TYPE LIMIT 5 OFFSET 0
②SELECT * FROM AGE_TYPE WHERE CODE IS NOT NULL AND ROWNUM<=5 ORDER BY CODE DESC	②SELECT  * FROM AGE_TYPE WHERE CODE IS NOT NULL ORDER BY CODE DESC LIMIT 5 OFFSET 0
4	(+)	①SELECT * FROM A_TABLE A , B_TABLE B WHERE A.ID(+)=B.ID	①SELECT * FROM A_TABLE A RIGHT OUTER JOIN B_TABLE B ON A.ID=B.ID
②SELECT * FROM A_TABLE A , B_TABLE B WHERE A.ID(+)=B.ID AND A.COL1='COL1_VALUE'	②SELECT * FROM A_TABLE A RIGHT OUTER JOIN B_TABLE B ON A.ID=B.ID AND A.COL1='COL1_VALUE'
③SELECT * FROM A_TABLE A, B_TABLE B,C_TABLE C,D_TABLE D WHERE A.ID=B.ID(+) AND A.ID=C.ID(+) AND A.COL1=D.COL1	③SELECT * FROM (A_TABLE A LEFT OUTER JOIN B_TABLE B ON A.ID=B.ID) LEFT OUTER JOIN C_TABLE C ON A.ID=C.ID,D_TABLE D WHERE A.COL1=D.COL1
④!!! SELECT * FROM A_TABLE A WHERE A.COL1(+)=0 AND   A.COL2(+) ='A_VALUE2'	④!!! SELECT * FROM A_TABLE A WHERE A.COL1=0 AND   A.COL2='A_VALUE2' WHERE (A.COL1=0 OR A.COL1 IS NULL) AND   (A.COL2='A_VALUE2' OR A.COL2 IS NULL)
5	AS	SELECT A.COL1  A_COL1,            A.COL2  A_COL2 FROM A_TABLE A	SELECT A.COL1 AS A_COL1,            A.COL2 AS A_COL2 FROM A_TABLE A
6	NVL	SELECT NVL(SUM(VALUE11),0) FS_VALUE1,             NVL(SUM(VALUE21),0) FS_VALUE2 FROM   FIELD_SUM	SELECT COALESCE(SUM(VALUE11),0) AS FS_VALUE1,            COALESCE(SUM(VALUE21),0) AS FS_VALUE2 FROM   FIELD_SUM
7	TO_ NUMBER	SELECT COL1 FROM A_TABLE ORDER BY TO_NUMBER(COL1)	SELECT COL1 FROM A_TABLE ORDER BY TO_NUMBER(COL1,999999) [注：'999999' ---- 6位数 为 COL1字段的 长 度]
8	DECODE	SELECT DECODE(ENDFLAG,'1','A','B') ENDFLAG FROM  TEST	SELECT (CASE ENDFLAG WHEN '1' THEN 'A' ELSE ' B ' END) AS ENDFLAG FROM TEST
9	时间 问题	UPDATE A_TABLE SET ENTREDATE=SYSDATE	UPDATE A_TABLE SET ENTREDATE=TO_TIMESTAMP(CURRENT_TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') 或者 UPDATE A_TABLE SET ENTREDATE=CURRENT_TIMESTAMP
SELECT TO_DATE('20010203','YYYY-MM-DD') AS DAY FROM DUAL	SELECT TO_DATE('20010203','YYYYMMDD') AS DAY FROM DUAL SELECT TO_DATE('20010203','YYYY-MM-DD') AS DAY FROM DUAL
SELECT TO_DATE(SYSDATE,'YYYY-MM-DD') AS DAY FROM DUAL	SELECT TO_DATE(CURRENT_DATE,'YYYY-MM-DD') AS DAY FROM DUAL
SELECT TO_DATE(SYSDATE,'YYYY/MM/DD') AS DAY FROM DUAL	SELECT TO_DATE(CURRENT_DATE,'YYYY/MM/DD') AS DAY FROM DUAL
10	||	SELECT NULL||'-'||NULL AS VALUES1 FROM DUAL	SELECT COALESCE(NULL,'')||'-'||COALESCE(NULL,'') AS VALUES1 FROM DUAL SELECT NULL||'-' ||NULL AS VALUES1 FROM DUAL
11	aggregate	SELECT ROUND(AVG(SUM(BASICCNT1))) BASICCNT FROM   ACCESS_INFO_SUM1_V WHERE YEARCODE BETWEEN '200305' AND '200505' GROUP BY SCCODE	SELECT  ROUND(AVG(AIV.BASICCNT)) AS BASICCNT FROM     (SELECT SUM(BASICCNT1)      AS BASICCNT     FROM   ACCESS_INFO_SUM1_V     WHERE YEARCODE BETWEEN '200305' AND '200505'     GROUP BY sccode      ) AIV
12	「"」	①SELECT LENGTH('') AS VALUE1 FROM DUAL [Result]VALUE1=NULL	①SELECT LENGTH('') AS VALUE1 FROM DUAL [Result]VALUE1=0
②SELECT TO_DATE('','YYYYMMDD') AS VALUE2 FROM DUAL [Result]VALUE2=NULL	②SELECT TO_DATE('','YYYYMMDD') AS VALUE2 FROM DUAL [Result]VALUE2=0001-01-01 BC
③SELECT TO_NUMBER('',1) AS VALUE3 FROM DUAL [Result]VALUE3=NULL	③SELECT TO_NUMBER('',1) AS VALUE3 FROM DUAL [Result]不能 执 行
④INSERT INTO TEST(VALUE4)VALUES('') [Result]VALUE4=NULL (注：VALUE3字段 为 数 值 类 型)	④INSERT INTO TEST(VALUE4)VALUES('') [Result]VALUE4=0   (注：VALUE4字段 为 数 值 类 型)
⑤INSERT INTO TEST(VALUE5)VALUES('') [Result]VALUE5=NULL (注：VALUE5字段 为 字符 类 型)	⑤INSERT INTO TEST(VALUE5)VALUES('') [Result]VALUE5=''   (注：VALUE5字段 为 字符 类 型, 结果为长度为零的字符串 )
⑥INSERT INTO TEST(VALUE6)VALUES(TO_DATE('','YYYYMMDD')) [Result]VALUE6=NULL (注：VALUE6字段 为时间类 型)	⑥INSERT INTO TEST(VALUE6)VALUES(TO_DATE('','YYYYMMDD')) [Result]VALUE6=0001-01-01 BC   (注：VALUE7字段 为时间类 型)
13	CEIL	SELECT CEIL(SYSDATE - TO_DATE('20051027 14:56:10','YYYYMMDD HH24:MI:SS')) AS DAYS FROM DUAL	SELECT EXTRACT(DAY FROM (TO_TIMESTAMP(CURRENT_TIMESTAMP,'YYYY-MM-DD-HH24-MI-SS') -TO_TIMESTAMP('2005-10-27 14:56:10','YYYY-MM-DD-HH24-MI-SS') ))+1 AS DAYS FROM DUAL
14	NULLIF	无NULLIF函数	SELECT NULLIF(VALUE1,VALUE2) AS COL1 FROM DUAL [注]当VALUE1=VALUE2 时，COL1=NULL
15	CONCAT	CONCAT(CHAR,CHAR)	创建函数来解决 CREATE FUNCTION CONCAT(CHAR,CHAR) RETURNS CHAR AS         'SELECT $1 || $2' LANGUAGE 'sql';
16	ADD_ MONTHS	add_months(date, int)	创 建函数来解决 CREATE FUNCTION add_months(date, int) RETURNS date AS 'SELECT ($1 + ( $2::text || ''months'')::interval)::date;' LANGUAGE 'sql'
17	LAST _DAY	LAST_DAY(DATE)	创 建函数来解决 CREATE FUNCTION LAST_DAY(DATE) RETURNS DATE AS         'SELECT date(substr(text($1 +                 interval(''1 month'')),1,7)||''-01'')-1'         LANGUAGE 'sql';
18	MONTHS _BETWEEN	MONTH_BETWEEN(DATA,DATA)	创 建函数来解决 CREATE FUNCTION MONTH_BETWEEN(DATA,DATA) RETURNS NUMERIC AS         'SELECT to_number((date($1)-                            date($2)),''999999999'')/31'         LANGUAGE 'sql';
19	GRE~ ATEST	GREATEST (LEAST)	创 建函数来解决 CREATE OR REPLACE FUNCTION    GREATEST(TEXT[]) RETURNS TEXT AS ' DECLARE    ARRY ALIAS FOR $1;    GREATEST TEXT; BEGIN    GREATEST := ARRY[1];    FOR I IN 1 .. ARRAY_UPPER(ARRY,1) LOOP       IF ARRY[I] > GREATEST THEN         GREATEST := ARRY[I];       END IF;    END LOOP;    RETURN GREATEST; END; ' LANGUAGE 'PLPGSQL'; SELECT GREATEST( ARRAY['HARRY','HARRIOT','HAROLD']) AS "Greatest";
20	BITAND	BITAND(int,int)	SELECT 値 & 値;
21	子条件		在FROM子条件中字段 须有列名， 处理方法用AS +别名
22	MINUS	MINUS	以EXCEPT来替代
23	BIN_ TO_ NUM	SELECT BIN_TO_NUM(1,0,1,0) AS VALUE1 FROM DUAL	SELECT CAST(B'1010' AS INTEGER) AS VALUE1