|
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
|
本文分享了从Oracle数据库迁移到PostgreSQL过程中遇到的主要差异点，包括语法转换、函数使用等方面的对比，帮助开发者顺利完成数据库迁移。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
