PostgreSQL与Oracle的差异对比_sql

概述Postgresql 与 Oracle 相关差异对比通过查阅资料及实验，以下对比了 Postgresql 9.3 版本与 Oracle 11g版本的相关差异。注意：相关细节仍待考证和完善。 1. 基本语法差异 1.1. 基本数据类型差异 Oracle Postgresql Varchar2 varchar DATE date/time/timestamp null null clob text Postgresql 与 Oracle 相关差异对比

通过查阅资料及实验，以下对比了 Postgresql 9.3 版本与 Oracle 11g版本的相关差异。注意：相关细节仍待考证和完善。

1. 基本语法差异 1.1. 基本数据类型差异 Oracle Postgresql

Varchar2	varchar
DATE	date/time/timestamp
null	null
clob	text
blob	bytea
number	smallint/integer/bigint/numeric/real/double precision
不支持boolean，可通过0/1代替	支持 boolean

1.2. 基本函数差异 Item Oracle Postgresql

字符串连接符	\|\|	concat()
‘a’ \|\| null =	‘a’	null
trunc(时间)	trunc(date)	date_trunc()
取当前系统时间	SYSDATE	localtimestamp,Now()
to_char,to_number, to_date	自动格式转换	需指定格式
decode	√	×
outer join	(+)	left(right) join
GOTO	√	×
procedure	√	× 需改写成function
package	√	× 需改写成function

@H_404_148@ 1.2.1. 游标属性 Oracle Postgresql

%FOUND	found
%NOTFOUND	not found
%ISOPEN	×
%rOWCOUNT	×

@H_404_148@ 1.2.2. 系统内置函数包 Oracle Postgresql

DBMS_OUTPUT	×
DBMS_sql	×
UTIL_file	×
UTIL_MAIL	×

1.3. DDL 差异 @H_404_148@ 1.3.1. Sequence语法及使用差异 Item Oracle Postgresql

create	create sequence SEQ_TAB_name minvalue 1 maxvalue 9999999999999999 start with 1 increment by 1 cache 20;	create sequence seq_tab_name minvalue 1 maxvalue 9223372036854775807 start 1 increment 1 cache 20;
query	select seq_tab_name.nextval from dual;	select next_val(seq_tab_name) from dual;

注意：pgsql中的 dual，需自主实现。详见兼容性设置->虚表dual问题章节。

@H_404_148@ 1.3.2. constraint 差异 Item Oracle Postgresql

constraint

alter table tab_name add constraint pk_tab_name primary key(column_ID) using index;

alter table tab_name add constraint pk_tab_name primary key(column_ID);

2. 高级语法差异 2.1. 事务差异

Oracle 中，通过 commit/rollback来实现事务提交或回滚。结构类似于：

begin select ... update ... delete ... commit;exception    when others then    rollback;end;

1 2 3 4 5 6 7 8 9 10 11 1 2 3 4 5 6 7 8 9 10 11

Postgresql 实际上把每个 sql 语句当做在一个事务中执行来看待。如果你没有发出BEGIN命令，那么每个独立的语句都被一个隐含的BEGIN 和(如果成功的话)COMMIT包围。一组包围在BEGIN和COMMIT 之间的语句有时候被称做事务块。

例如：

BEGIN;UPDATE accounts SET balance = balance - 100.00 WHERE name = 'Alice';SAVEPOINT my_savepoint;UPDATE accounts SET balance = balance + 100.00 WHERE name = 'Bob';-- 呀！加错钱了，应该用 Wally 的账号RolLBACK TO my_savepoint;UPDATE accounts SET balance = balance + 100.00 WHERE name = 'Wally';COMMIT;

1 2 3 4 5 6 7 8 9 10 11 12 1 2 3 4 5 6 7 8 9 10 11 12

在 PL/pgsql中，也提供了类似于Oracle 的 Begin、End及ExceptionCode处理机制。他们之间的差异不明显。事实上，PL/sql（Oracle数据库 *** 作语言）与PL/pgsql是高度相似的，这让procedure在Oracle与 Postgresql之间迁移变得极为便捷。

2.2. 函数继承与重载

Oracle不支持继承和重载特性，pgsql支持继承和函数重载；
（待完善）

2.3. 类型转换

pgsql 中的类型转换 “::” 符，Oracle 不支持。

2.4. 子查询

子查询，pgsql要求更严格，必须具有别名才可以；

3. 其他差异 3.1. jdbc差异

Oracle的jdbc连接字符串：db.url=jdbc:oracle:thin:@192.168.1.1:1521:ORCL

Postgresql的连接字符串：db.url=jdbc:postgresql:@192.168.1.1:5432/database

4. 兼容性设置 4.1. 字符串连接兼容性解决方案

Postgresql中没有concat函数，且由于 ||无法使用，需要通过在public schema中创建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;

1 2 3 4 5 6 1 2 3 4 5 6 4.2. 虚表 dual 问题

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;

1 2 3 4 5 6 7 8 1 2 3 4 5 6 7 8 4.3. 数据分页问题

Oracle中没有 limit，postgresql中没有rownum。需重写相关语句。

-- Oracleselect * from ( select * from ( select * from t1 order by col1,col2 ) where rownum <=50 order by col3,col4 ) where rowmun <=20 order by col5,col6;-- postgresqlselect * from (select * from (select * from t1 order by col1,col2) ta order by col3,col4 limit 50) tb order by col5,col6 limit 20;