PostgreSQL
发布时间：2020-12-13 18:23:30 所属栏目：百科 来源：网络整理
导读：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 NEXTV
|
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(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) AS VALUE1 FROM DUAL SELECT CAST(B'1010' AS INTEGER) AS VALUE1 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |