PostgreSQL与Oracle的语法差异总结

发表于 分类于 阅读次数： Valine：

为了降低成本、顺应开源化潮流，公司从 Oracle 转向了 PostgreSQL。由于是连平台一块换了，不存在迁移的问题，所以不需要使用 orafce、Ora2pg 之类的转换迁移工具，只需要了解语法差异，转换比较平稳。

PG 目前是数据库榜单上的探花，坐观榜眼 MySQL 和状元 Oracle，学习一下还是很有价值的！

JDBC 连接

  • Oracle 的 JDBC 连接字符串：

db.url=jdbc:oracle:thin:@192.168.1.1:1521:ORCL

  • PostgreSQL 的 JDBC 连接字符串：

db.url=jdbc:postgresql:@192.168.1.1:5432/database

数据类型

Oracle PostgreSQL
varchar2 varchar
number numeric
date timestamp/date/time
不支持 boolean 支持 boolean
null null

数据类型转换

Oracle 和 PostgreSQL 都支持 to_char()、to_number()、to_date()的函数进行字符类型、数值类型和日期类型的转换。

Oracle 在数据类型转换方面比较智能，能够在 SQL 中自动将数据转换为字段对应的类型，例如：

1
2
3
create tableA (name varchar2(36), score integer);
--'13'被自动转换为13
update tableA set score='13' where name='张三';

PostgreSQL 在类似场景下就比较严格：

1
2
3
create tableA (name varchar(36), score int);
--'13'被转换为13，::是PG特有的简化语法，等价于标准SQL的CAST('13' AS INT)
update tableA set score='13'::int where name='张三';

基本语法

函数 Oracle PostgreSQL
字符串连接 || concat()
‘a’||null ‘a’ null
系统当前时间 sysdate now(),current_timestamp,current_date,current_time
对时间截取 trunc() date_trunc()
判空操作 nvl() coalesce()
条件判断 decode() case…when…then…else…end
dual 伪表 支持 select now();
外联 (+) left join,right join
GOTO 支持 不支持
procedure 支持 改为 function
package 支持 改为 function

MERGE INTO 和 on conflict() do

Oracle 和 PostgreSQL 增删改查语法基本一致，有一点不同：

Oracle 有强大的 MERGE INTO：

1
2
3
4
5
6
7
8
9
MERGE INTO tableA AT a
USING tableB AS b
ON a.id=b.id
WHEN MATCHED--匹配则更新
THEN UPDATE SET a.field=b.field
WHEN NOT MATCHED--无匹配则插入
THEN INSERT VALUES (b.id,b.field)
WHEN NOT MATCHED BY SOURCE--源表不存在则删除目标表
THEN DELETE;

PostgreSQL 可以使用 on conflict() do：

1
2
3
4
insert into tableA
	select id,field from tableB
	on conflict (id)
  do update set field = exclude.field

查询语句

  1. 查询表中最新 n 条数据

Oracle 用 rownum：

写法一：

1
select t.* from (select * from tableA order by create_time desc) t where rownum <= n;

写法二：

1
select * from(select t.*, row_number() over(order by create_time desc) rn from tableA t) where rn <=n;

上述写法一为通用常规写法；写法二可以对分组后数据排序，分组语句写在 over()中

postgreSQL 用 limit：

1
select * from tableA order by create_time desc limit n;

注意：limit 必须用于 order by 之后

  1. 子查询

Oracle 子查询用括号包起来即可，无需加别名（Alias）:

1
select name from (select id,name from tableA) ;

postgreSQL 子查询要求比较严格，必须具有别名：

1
select a.name from (select id,name from tableA) a;

事务

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

1
2
3
4
5
6
7
8
9
begin
    select ...
    update ...
    delete ...
    commit;
exception
    when others then
    rollback;
end;

PostgreSQL 实际上把每个 SQL 语句当做在一个事务中执行来看待。

如果你没有发出 BEGIN 命令，那么每个独立的语句都被一个隐含的 BEGIN 和(如果成功的话)COMMIT 包围。

一组包围在 BEGIN 和 COMMIT 之间的语句有时候被称做事务块。例如：

1
2
3
4
5
6
7
8
9
10
11
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 之间迁移变得极为便捷。