PostgreSQL和Oracle用法上的一些区别

* 博客文章部分截图及内容来自于学习的书本及相应培训课程以及网络其他博客，仅做学习讨论之用，不做商业用途。
* 如有侵权，马上联系我，我立马删除对应链接。
* @author Alan
* @Email no008@foxmail.com

PostgreSQL和Oracle用法上的一些区别

wh62592855 2010-04-22 13:43:00  5472  收藏

分类专栏： PostgreSQL 文章标签： postgresql oracle table date join function

PostgreSQL专栏收录该内容

55 篇文章1 订阅

订阅专栏

（1 ） 注意增加约束时的写法，和ORACLE 略有不同

Oracle ：

ALTER TABLE SCHEMA.PREFIX_INFO ADD (
CONSTRAINT PK_PREFIX_INFO PRIMARY KEY (INFO_ID));

PostgresQL ：

alter table schema.prefix_info add constraint prefix_info_pkey primary key(info_id);

（2 ）系统默认的最大值与ORACLE 不同

Oracle ：

CREATE SEQUENCE PREFIX_INFO_SEQUENCE
INCREMENT BY 1 
START WITH 582
MINVALUE 1 
MAXVALUE 9999999999999999999999999999 
NOCYCLE 
CACHE 20 
NOORDER;

PostgresQL ：

CREATE SEQUENCE schema.prefix_info_sequence
increment 1
minvalue 1
maxvalue 9223372036854775807
start 582
cache 20;

（3 ）PostgresQL 中的 || 用 法与其他数据库不同：
select a|| b from table1;
当a 或b 其中一个为null 时， 该查询返回null ，

（4 ）PostgresQL 中没有concat 函数，且由于|| 用法的问题，无法使用|| 替换，解决 方法为在public schema 中创建函数concat

create or replace function concat(text, text)
returns text as
$body$select coalesce($1,'') || coalesce($2,'')$body$
language 'sql' volatile;
alter function concat(text, text) owner to postgres;

-- 无需特殊授权即可在其他schema 中使用

（4 ）PostgresQL 中没有dual 虚拟表，为保 证程序兼容性，可创建伪视图（view ）替代：

CREATE OR REPLACE VIEW dual AS 
SELECT NULL::"unknown"
WHERE 1 = 1;

ALTER TABLE dual OWNER TO postgres;
GRANT ALL ON TABLE dual TO postgres;
GRANT SELECT ON TABLE dual TO public;

必须授权public 以select 权 限

（5 ）关联查询用法区别

ORACLE:

简单外连接：
SELECT COUNT(DISTINCT(A.COL1)) AS RCOUNT FROM 
SCHEMA.PREFIX_TABLE1 A,SCHEMA.PREFIX_TABLE2 B
WHERE 1 = 1 
AND A.COL2 = B.COL2(+)
AND A.COL3 > 0 
AND A.COL4 = '1'

超级变态外连接：
SELECT COUNT(DISTINCT(A.COL1)) AS RCOUNT FROM 
SCHEMA.PREFIX_TABLE1 A,SCHEMA.PREFIX_TABLE2 B,SCHEMA.PREFIX_TABLE3 C,SCHEMA.PREFIX_TABLE4 D 
WHERE 1 = 1 
AND A.COL2 = B.COL2 
AND A.COL3 = C.COL3(+) 
AND A.COL4 = D.COL4(+)
AND A.COL5 > 0 
AND A.COL6 = '1'

POSTGRESQL:

简单外连接：
select count(distinct(a.col1)) as rcount from 
schema.prefix_table1 a left outer join schema.prefix_table2 b on (a.col2 = b.col2)
where 1 = 1 
and a.col3 > 0 
and a.col4 = '1'

超级变态外连接：
select count(distinct(a.col1)) as rcount from 
schema.prefix_table1 a inner join schema.prefix_table2 b on (a.col2 = b.col2) 
left outer join schema.prefix_table3 c on (a.col3 = c.col3) 
left outer join schema.prefix_table4 d on (a.col4 = d.col4) 
where 1 = 1 
and a.col5 > 0 
and a.col6 = '1'

（6 ）PostgresQL 中子查询较为规范，子查询结果集必须拥有alias

ORACLE:
SELECT * FROM (
SELECT * FROM (
SELECT * FROM SCHEMA.PREFIX_TABLE ORDER BY COL1
) WHERE X=1 ORDER BY COL2
) WHERE Y=2 ORDER BY COL3

POSTGRESQL:

SELECT * FROM (
SELECT * FROM (
SELECT * FROM SCHEMA.PREFIX_TABLE ORDER BY COL1 ALIAS1
) WHERE X=1 ORDER BY COL2 ALIAS2
) WHERE Y=2 ORDER BY COL3

（7 ） PostgresQL 中没有rownum ，无法 使用where rownum < = X 的方法进行分页，取而代之的是limit X ，offset Y 方法, 而ORACLE 中不允许使用LIMIT X 的 方法
ORACLE:

SELECT * FROM ( SELECT * FROM (SELECT * FROM SCHEMA.PREFIX_TABLE1 ORDER BY COL1 DESC,COL2 ASC) where ROWNUM <= 50 ORDER BY COL3 ASC,COL4 DESC)
WHERE ROWNUM <= 20 ORDER BY COL5 DESC,COL6 ASC;

POSTGRES:

select * from ( select * from (SELECT * FROM SCHEMA.PREFIX_TABLE1 ORDER BY COL1 DESC,COL2 ASC) selb order by col3 asc,col4 desc limit 50 ) sela
order by col5 desc,col6 asc limit 20;

-- 注意！！limit 必须用于order by 之后

-- 例：取1 到50 条数据

select * from VOIP_FEE_RATE temp offset 0 limit 50

（8 ）序列使用的区别

ORACLE:
SELECT SCHEMA.PREFIX_TABLE1_SEQUENCE.NEXTVAL AS nCode FROM DUAL

POSTGRES:
SELECT NEXTVAL('SCHEMA.PREFIX_TABLE1_SEQUENCE') AS nCode FROM DUAL 
-- 注意，此方法前提是dual 视图已建立，如没有，可省略FROM DUAL

（9 ）字段取别名必须用as
Oracle -- SELECT A.COL1 A_COL1 ，A.COL2 A_COL2 FROM A_TABLE A
Postgresql---- SELECT A.COL1 AS A_COL1 ，A.COL2 AS A_COL2 FROM A_TABLE A

（10 ）NVL 用法
Oracle --SELECT NVL(SUM(VALUE11),0) FS_VALUE1, NVL(SUM(VALUE21),0) FS_VALUE2 FROM   FIELD_SUM 
Postgresql--SELECT COALESCE(SUM(VALUE11),0) AS FS_VALUE1,COALESCE(SUM(VALUE21),0) AS FS_VALUE2
FROM FIELD_SUM

（11 ）TO_NUMBER 用法
Oracle -- SELECT COL1 FROM A_TABLE ORDER BY TO_NUMBER(COL1)
Postgresql- select TO_NUMBER(COL1,'99G999D9S') from A_TABLE [ 注：'999999' ---- 6 位数为COL1 字段的长度]

（12 ）DECODE 用法
Oracle -- SELECT DECODE(ENDFLAG,'1','A','B') ENDFLAGFROM TEST
Postgresql- SELECT (CASEENDFLAG WHEN '1' THEN 'A'ELSE 'B' END) AS ENDFLAG FROM TEST

（13 ）统计 相关 用法
Oracle -- SELECT ROUND(AVG(SUM(BASICCNT1))) BASICCNT FROM   ACCESS_INFO_SUM1_V
WHERE YEARCODE BETWEEN '200305' AND '200505' GROUP BY SCCODE

Postgresql-- 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

（14 ）时间计算 用法
Oracle -- SELECT CEIL(SYSDATE - TO_DATE('20051027 14:56:10','YYYYMMDD HH24:MI:SS')) AS DAYS
FROM DUAL 
Postgresql-- 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
Oracle -- add_months(date, int)
Postgresql-- 创建函数来解决
CREATE FUNCTION add_months(date, int) 
RETURNS date AS 
'SELECT ($1 + ( $2::text || ''months'')::interval)::date;' 
LANGUAGE 'sql' 、

（15）关于事务

在PostgreSQL中，默认情况下，一条SQL语句是一个独立的事务。在一条SQL语句开始执行时，PostgreSQL自动为其创建一个事务。如果语句执行成功，PostgreSQL自动提交该语句的事务，否则回滚事务。