前言：近日，公司的一套使用 postgresql 数据库的应用软件要兼容oracle。本文系统性地整理了PostgreSQL 和 Oracle的一些差异点，和应用程序中的改动点。
4 应用程序的改造
4.1 JDBC 配置
下面是PostgreSQL和Oracle的JDBC配置差异：
|
|
PostgreSQL 11 |
Oracle 19c |
|
driver_class |
org.postgresql.Driver |
oracle.jdbc.driver.OracleDriver |
|
url |
jdbc:postgresql://127.0.0.1:5432/postgres |
jdbc:oracle:thin:@127.0.0.1:1521:ORCL |
4.2 常用函数和运算符
下面是PostgreSQL和Oracle中，对应的常用的数据库函数和运算符：
|
|
PostgreSQL 11 |
Oracle 19c |
|
类型转换 |
value :: type 或 CAST(value AS type) |
CAST(value AS type) |
|
序列取值 |
nextval('sequence_name'） |
sequence_name.nextval |
|
获取uuid |
gen_random_uuid() 或者uuid_generate_v1mc() |
sys_guid() |
|
模糊匹配且不区分大小写 |
select * from tb_usergroup where group_name ilike 'group%' |
select * from tb_usergroup where upper(group_name) like upper('group%'); |
|
时间加减 |
select now() - INTERVAL '3 months'; 或 select now() - INTERVAL '3' month |
select now() - INTERVAL '3' month from dual; |
|
获取当前时间 |
获取事务开始时间戳： select now(); select current_timestamp;
获取当前命令执行的时间戳： select clock_timestamp(); |
获取事务开始时间戳： 无
获取当前命令执行的时间戳： select current_timestamp from dual; |
|
获取会话当前时刻UTC毫秒数 |
select extract(epoch from now()) * 1000; |
select (CAST(SYS_EXTRACT_UTC(current_timestamp) AS date) - to_date('1970-01-01', 'YYYY-MM-DD')) * 86400*1000 + mod(extract(second from current_timestamp), 1) * 1000 from dual; |
|
获取会话当前时区的名称 |
select current_setting('timezone'); |
select SESSIONTIMEZONE FROM DUAL; |
|
获取会话当前时区的偏移量（格式：+08:00） |
select left(to_char(now(),'OF') || ':00', 6); |
SELECT TZ_OFFSET(SESSIONTIMEZONE) FROM DUAL; |
|
字符串聚集 |
select string_agg(name, ',') from man; |
select listagg(name, ',') from man; |
|
计算一个子串在字符串中出现的第一个位置 |
select strpos('abcd','c'); |
select instr('abcd','c') from dual; |
|
正则表达式匹配 |
select 1 where 'abcd' ~ '^[a-z]*$'; |
select 1 from dual where regexp_like('abcd', '^[a-z]*$'); |
4.3 一些 sql 语法
下面是一些应用程序中需要改造的语法，这里列举的都是DML语句。
|
|
PostgreSQL 11 |
Oracle 19c |
|
查询表之外的数据 |
select 1; |
select 1 from dual;
注意：from dual 是固定的语法。 |
|
分页查询 |
select * FROM tablename offset m limit n 或 select * FROM tablename limit n offset m |
select * from (SELECT rownum rn, tablename.* FROM tablename where ROWNUM <= m+n) where rn > m; |
|
NULL和'' |
NULL和''不同 |
ORACLE认为''等同于NULL |
|
批量插入 |
insert into tb01 (id) values(1),(2),(3); |
insert into tb01 (id) values(1); insert into tb01 (id) values(2); insert into tb01 (id) values(3); |
|
集合的减 |
except |
minus |
注意：分页查询中，m 表示起始位置，最小值为0，n 代表所取的行数。
4.4 JSON 功能
目前 iSecure Center （以下简称ISC）平台的基线版本使用了一些PostgreSQL （PostgreSQL 11）的NO-SQL 特性，包括数组和json。Oracle同样提供了JSON和数组的处理功能。
下面是一些json功能的改造。
4.4.1 JSON 字段的类型
PostgreSQL 有原生类型json和jsonb。它们接受相同的输入格式。它们实际的主要差别是效率。json 数据类型存储输入的文本内容，处理函数在处理时必须将它解析为JSON；而jsonb数据以分解的二进制格式存储，这使得它由于添加了转换机制而在输入上稍微慢些，但是在处理上明显更快，因为不需要重新解析。jsonb支持索引，这是一个明显的优势。 实际应用中，我们使用jsonb类型。
Oracle中，JSON字段用varchar或clob类型表示。在创建了这样的字段后，需要用一个检查约束确保数据是真正的json格式的。
下面是在 PostgreSQL 中创建一个包含jsonb类型的表的示例：
create table tb_test
(
id int,
json_column jsonb,
);
insert into tb_test (id, json_column) values (1, '{"name":"zhangsan","gender":"male","age":22}');
下面是在 Oracle 中创建一个包含json字段的表的示例：
create table tb_test
(
id int,
json_column clob,
CHECK (json_column IS JSON)
);
insert into tb_test (id, json_column) values (1, '{"name":"zhangsan","gender":"male","age":22}');
4.4.2 JSON 的查询
如果你要查询JSON 中的值，则需要给出JSON 键的名称或键的完整路径。注意，对于JSON对象{"a":{"b":{"c": "foo"}}}，在PostgreSQL 中JSON键路径的表示为 {a,b,c}；而在Oracle中，表示为 $.a.b.c
下面是两种数据库中，常用的json查询功能的对比：
|
|
PostgreSQL 11 |
Oracle 19c |
|
判断JSON的最外层是否包含某个键 |
{"a":1, "b":2}'::jsonb ? 'a' |
json_exists('{"a":1, "b":2}', '$.a') |
|
给定一个键，它的值是普通文本 |
{"a":"1", "b":{"c": "foo"}}' ->> 'a'
结果： 1 |
json_value('{"a":"1", "b":{"c": "foo"}}' , '$.a')
结果： 1 |
|
给定一个键，它的值是一个json对象或json数组 |
’{"a":"1", "b":{"c": "foo"}}‘ -> 'b' （返回值的类型是jsonb/json）
结果：{"c": "foo"}
或
'{"a":"1", "b":{"c": "foo"}}' ->> 'b' （返回值的类型是text）
结果：{"c": "foo"} |
json_query('{"a":"1", "b":{"c": "foo"}}' , '$.b')
结果：{"c": "foo"} |
|
给定一个键的路径，它的值是普通文本 |
{"a":"1", "b":{"c": "foo"}}' #>> ‘{b, c}’
结果：foo |
json_value('{"a":"1", "b":{"c": "foo"}}' , '$.b.c') |
|
给定一个键的路径，它的值是 json 对象或json数组格式 |
{"a":"1", "b":{"c": "foo"}, "d":{"e":{"f":"1"}}}' #> ‘{d, e}’ （返回值的类型是jsonb/json）
结果：{"f":"1"}
或
{"a":"1", "b":{"c": "foo"}, "d":{"e":{"f":"1"}}}' #>> ‘{d, e}’ （返回值的类型是text）
结果：{"f":"1"} |
json_query('{"a":"1", "b":{"c": "foo"}, "d":{"e":{"f":"1"}}}', '$.d.e')
结果：{"f":"1"} |
|
取 json 数组中的第一个元素， 注意数组的下标从0开始 |
'{"a":[1,2,3,4]}'::jsonb #>> '{a,0}' |
json_value('{"a":[1,2,3,4]}','$.a[0]') |
4.4.3 JSON 的修改
下面是两种数据库中，添加，修改和删除JSON 键值对的方法：
|
|
PostgreSQL 11 |
Oracle 19c |
|
合并或插入json 键值对 |
{"a":"1"}'::jsonb || '{"b":"1"}'::jsonb 结果：{"a":"1", "b" :"1"} |
JSON_MERGEPATCH('{"a":"1"}', '{"b":"1"}') 结果：{"a":"1", "b" :"1"} |
|
删除json 最外层的键值对 |
{"a":1, "b":2}'::jsonb - 'b' 结果：{"a":"1"} |
JSON_MERGEPATCH('{"a":"1", "b" :"1"}', '{"b":null}') 结果：{"a":"1"} |
|
修改json 最外层键值对 |
{"a":"1", "b":"2"}'::jsonb || '{"b":"1"}'::jsonb 结果：{"a":"1", "b" :"1"} |
JSON_MERGEPATCH('{"a":"1", "b":"2"}', '{"b":"1"}') 结果：{"a":"1", "b" :"1"} |
|
根据键的路径在JSON中插入或修改值 |
jsonb_set('{"a":1,"b":{"c":"1"}}', '{b,c}','"2"', false) 结果： {"a": 1, "b": {"c": "2"}} |
JSON_MERGEPATCH('{"a":1,"b":{"c":"1"}}', '{"b":{"c":"2"}}') 结果： {"a": 1, "b": {"c": "2"}} |
4.4.4 JSON的比较
下面的是两种数据库中比较json的方法，返回结果是布尔类型：
|
|
PostgreSQL 11 |
Oracle 19c |
|
比较json是否相等 |
'{"a":"1"}' :: jsonb = '{"a":"1"}'::jsonb |
json_equal('{"a":"1"}', '{"a":"1"}') |
|
左边的json 是否包含右边的json |
{"a":1, "b":2}'::jsonb @> '{"a":1}'::jsonb |
json_equal(JSON_MERGEPATCH('{"a":"1", "b" :"1"}', '{"a":"1"}'), '{"a":"1", "b" :"1"}') |
4.4.5 其他JSON 功能
下面是一些常用其他的JSON函数：
|
|
PostgreSQL 11 |
Oracle 19c |
备注 |
|
创建一个json对象 |
jsonb_build_object('a',‘1’,'b',‘2’)
结果： {"a": "1", "b": "2"} |
json_object(key 'a' value '1', key 'b' value '2')
结果： {"a": "1", "b": "2"} |
|
|
把一个json中的键变成列 |
select * from jsonb_populate_record(null::myrowtype, '{"a":1,"b":2}')
结果： a | b ---+--- 1 | 2 |
SELECT * FROM json_table('{"a":1, b:"2"}', '$' columns (a PATH '$.a', b PATH '$.b'))
结果： a | b ---+--- 1 | 2 |
可以看出，Oracle中把json键转成列，需要指定列名和json的键。 |
|
把一个含有json数组中的json对象的键变成列 |
select * from jsonb_populate_recordset(null::myrowtype, '[{"a":1,"b":2},{"a":3,"b":4}]')
结果： a | b ---+--- 1 | 2 3 | 4 |
SELECT * FROM json_table('[{"a":1,"b":2},{"a":3,"b":4}]', '$[*]' columns (a PATH '$.a', b PATH '$.b'))
结果： a | b ---+--- 1 | 2 3 | 4 |
可以看出，Oracle中把json键转成列，需要指定列名和json的键。 |
|
将两列多行数据聚集为一个json对象 |
select jsonb_object_agg(name, id) from tb_man;
结果： {"Tom":1,"Bob":2} |
select json_objectagg(key name value id) from tb_man;
结果： {"Tom":1,"Bob":2} |
|
4.5 数组功能
PostgreSQL 中提供了两种的数组可作为字段类型，一种是数组类型，一种是类型是jsonb的json数组。而Oracle则只支持JSON数组。
现在我们介绍这两种数据库中数组的知识和对应的函数和运算符。
4.5.1 数组的类型
PostgreSQL 支持的数组有两种，一种是数组类型，一种是json数组。
下面的案例是创建一张表，它的字段类型是整型数组类型：
create table tb_test
(
id int,
codes integer [],
);
insert into tb_test (id, codes) values (1, '{1, 2, 3, 4}');
insert into tb_test (id, codes) values (2, array[1, 2, 3, 4]);
下面的案例是创建一张表，它的字段类型是jsonb，格式是json数组：
create table tb_test
(
id int,
codes jsonb
);
insert into tb_test (id, codes) values (1,'[1, 2, 3, 4]');
而Oracle中，json数组的类型是varchar。创建包含数组字段的表的示例如下：
create table tb_test
(
id int,
codes varchar(512),
CHECK (attribute IS JSON)
);
insert into tb_test (id, json_column) values (1, '[1, 2, 3, 4]');
4.5.2 数组的查询
下面是两种数据库中，常见的涉及数组的查询功能：
|
|
PostgreSQL 11 |
Oracle 19c |
|
|
数组的类型 |
array 类型的数组 |
json 数组，类型为jsonb |
json 数组，类型为varchar |
|
判断数组是否包含某个元素 |
array[1,2,3,4] @> array[1] |
cast('[1, 2, 3, 4]' as jsonb) @> cast('[1]' as jsonb) |
json_exists('[1, 2, 3]','$?(@ == "1")') |
|
取数组中的第一个元素 |
select (array[1,2,3,4])[1];
注意，数组下标从1开始，[1] 表示第1个元素 |
select '[1,2,3,4]'::jsonb #>> '{0}'
注意，数组下标从0开始 |
SELECT json_value('[1,2,3,4]','$[0]') from dual
注意，数组下标从0开始 |
4.5.3 数组的修改
下面是两种数据库中，常见的修改数组的方法：
|
|
PostgreSQL 11 |
Oracle 19c |
|
|
数组的类型 |
array 类型的数组 |
json 数组，类型为jsonb |
json 数组，类型为varchar |
|
向数组末端追加一个元素 |
array_append(array[1,2], 3) 或 array[1,2] || array[3] |
cast( '[1, 2]' as jsonb) || cast( '[3]' as jsonb) |
substr('[1, 2]', 1, length('[1, 2]') - 1) || '3]' |
|
合并两个数组 |
array[1,2] || array[3, 4] 或 array_cat(array[1,2], array[3,4]) |
cast( '[1, 2]' as jsonb) || cast( '[3, 4]' as jsonb) |
substr('[1, 2]', 1, length('[1, 2]') - 1) || '3, 4]' |
|
从数组中删除值为2的数字元素 |
array_remove(ARRAY[1,2,3,2], 2)
结果： {1,3} |
不支持，实现复杂 |
不支持，实现复杂 |
|
从数组中删除值为“b”的字符串元素 |
array_remove(ARRAY['a ', 'b', 'c'], 'b')
结果： ['a', 'c '] |
Cast('["a", "b", "c"]' as jsonb) -'b'
结果： ['a', 'c '] |
不支持，实现复杂 |
4.5.4 数组的比较
下面是两种数据库中，常见的数组的比较方法：
|
|
PostgreSQL 11 |
Oracle 19c |
|
|
数组的类型 |
array 类型的数组 |
json 数组，类型为jsonb |
json 数组，类型为varchar |
|
比较数组是否相等 |
array[1,2,3,4] = array[1,2,3] |
cast('[1, 2, 3, 4]' as jsonb) = cast('[1,2,3]' as jsonb) |
json_equal('[1, 2, 3, 4]', '[1,2,3]') |
|
判断左边的数组是否包含右边的数组 |
array[1,2,3,4] @> array[1,2] |
cast('[1, 2, 3, 4]' as jsonb) @> cast('[1,2]' as jsonb) |
不支持直接比较数组，需要把右边的数组拆分： json_exists('[1, 2, 3]','$?(@ == "1")') and json_exists('[1, 2, 3]','$?(@ == "2")') |
4.5.5 其他数组功能
下面是一些常用其他的数组功能：
|
|
PostgreSQL 11 |
Oracle 19c |
|
|
数组的类型 |
array 类型的数组 |
json 数组，类型为jsonb |
json 数组，类型为varchar |
|
计算数组的元素个数 |
select array_length(array[1,2,3], 1) |
select jsonb_array_length('[1, 2, 3, 4]') |
SELECT count(*) FROM json_table('[1, 2, 3]', '$[*]' COLUMNS (value PATH '$')) |
|
将不同的行聚合成一个数组 |
select array_agg(code) from tb_device where device_type = 'super'; |
select jsonb_agg(code) from tb_device where device_type = 'super'; |
select json_arrayagg(code) from tb_device where device_type = 'super'; |
|
将数组元素转成多个行 |
select unnest(array[1,2,3]); |
select cast(jsonb_array_elements('[1,2,3]' as text); |
SELECT value FROM json_table( '[1, 2, 3]' , '$[*]' COLUMNS (value PATH '$') ) |
从上面的例子可以看出，PostgreSQL 11 对 数组的支持要比Oracle多。