Postgresql与Oracle相关差异对比

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

1、基本语法差异

1.1 基本数据类型差异

1.2 基本函数差异

1.2.1 游标属性

1.2.2 系统内置函数包

1.3 DDL差异

1.3.1 Sequence语法及使用差异

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

1.3.2 constraint差异

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 之后！

Python学习网，大量的免费PostgreSQL入门教程，欢迎在线学习！