PostgreSQL与Oracle的差异对比

时间:2021-11-04 14:53:55

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

1.2.1. 游标属性

Oracle Postgresql
%FOUND found
%NOTFOUND not found
%ISOPEN ×
%ROWCOUNT ×

1.2.2. 系统内置函数包

Oracle Postgresql
DBMS_OUTPUT ×
DBMS_SQL ×
UTIL_FILE ×
UTIL_MAIL ×

1.3. DDL 差异

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问题 章节。

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;

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;

在 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;

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;

4.3. 数据分页问题

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

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


-- postgresql
select * from (select * from (select * from t1 order by col1, col2) ta order by col3, col4 limit 50) tb
order by col5, col6 limit 20;

注意:limit必须用于 order by 之后!

扩展阅读