数据库改造（Oracle-＞PostgreSQL）

    最近一祥在公司有了新任务，就是资源上云，首先要做的就是去oracle，将其改造成pg数据库，期间一祥也碰到了各种各样的问题，这里进行一一罗列。

1. PG空间表建表示例

create table gis.gr_area_yc_city_county
(
  objectid        INTEGER not null,
  code            varchar(255),
  type            varchar(7),
  name_chn        varchar(100),
  name_eng        varchar(254),
  name_py         varchar(254),
  shape           geometry(Geometry,4326),   --空间字段
  area_id_2       INTEGER,
  area_id_3       INTEGER,
  area_id_4       INTEGER,
  gdb_from_date   TIMESTAMP(6) ,
  gdb_to_date  TIMESTAMP(6),
  gdb_archive_oid INTEGER not null
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

2. pg库空间索引创建

create index a103851_shape_idx on gis.a103851 using gist(shape);
1

3.序列问题

oracle获取序列：my_sequence.nextval postgre获取序列：nextval(‘my_sequence’)

4.sysdate替换方案：

				Oracle				postgresql
当前日期 时间 	sysdate 			now()
当前日期 		trunc(sysdate) 		now() :: date
?分钟前 			sysdate - ?/24/60 	now() - (? || 'min')::interval
?天前 			sysdate - ? 		now() - (? || 'day')::interval
1
2
3
4
5

5、NVL函数替换方案：

oracle中的NVL(arg1, arg2)用来设置默认值，arg1为空就设置为arg2。postgre中可以用coalesce(arg1, arg2)实现相同效果。

6.decode函数问题：

oracle中的decode(arg1, arg2, arg3, arg4)函数， 表示：当 arg1 等于 arg2 时，取 arg3
，否则取 arg4。 postgre中没有类似的函数，可以用如下方式实现

CASE
     WHEN condition_1  THEN result_1
     WHEN condition_2  THEN result_2
     ...
     ELSE  result_n
END
1
2
3
4
5
6

7. to_date函数注意点

postgre中to_date函数转出来的是不带时分秒的时间，如果想要带时分秒的，需要用to_timestamp。

8.substr(arg, begin, num)函数

表示对arg进行截取，从第begin位开始，截取num个。oracle中，arg可以是字符串也可以数字类型。
但是postgre中只支持对字符串的截取，如果要对数字进行截取，得写成：SUBSTR(1.23 :: TEXT,1,3)。
还有一点，oracle中substr(1.23, -2)表示截取最后两位，结果就是23，
postgre中不支持这种用法，要实现相同功能，可以用right函数：right(1.23::text, 2)。

9.listagg(column1, ‘,’) within group(column2)函数

这个函数的意思是将column2相同的多行记录的column1的值合并成一行，例如：

SELECT tr.tr_gw_no,
        listagg(tr.tr_status, ',') WITHIN GROUP(ORDER BY tr.tr_status) status
FROM trade tr
where tr_gw_no = 12198006
     or tr_gw_no = 12167001
GROUP BY tr.tr_gw_no;
1
2
3
4
5
6

postgre中可以用string_agg函数实现相同的功能，具体用法如下：

SELECT
    tr_gw_no,
    string_agg ( tr_status :: TEXT, ',' ) status
FROM
    trade 
WHERE
    tr_gw_no = 12198006 
    OR tr_gw_no = 12167001 
GROUP BY
    tr_gw_no;
1
2
3
4
5
6
7
8
9
10

10 start with connect by函数的问题：

oracle的这个函数是用来查树形结构的，即同一张的表的记录有父子级关系的那种。oracle中用法如下：

select module_id
    from sys_modules
start with module_id = ?
connect by prior module_id = module_fid;
1
2
3
4

这就表示查询module_id为?的所有子module，即父模块idmodule_fid为?的所有的记录。postgre可以用WITH
RECURSIVE实现相同的效果，如下：

WITH RECURSIVE subtabela AS (
    SELECT
        module_id 
    FROM
        sys_modules 
    WHERE
        module_id = ? UNION ALL
    SELECT
        tt.module_id 
    FROM
        sys_modules tt
        INNER JOIN subtabela st ON tt.module_fid = st.module_id 
    ) SELECT
    * 
FROM
    subtabela;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

11 表连接问题

oracle中外连接可以这样写： select * from tableA a, tableB b where a.id =b.id(+);
有(+)的一方是副表，另一方是主表，即上面那种写法表示左外连接。postgre不支持这种写法，可以用left join代替。

12 instr 函数的问题：

instr函数表示包含，postgre中可以用strpos函数替代。

13 insert/update 表别名问题

PG insert/update 语句中 表名后面不能再加别名

14 rownum 函数

PG的用法:

select row_number() OVER (ORDER BY id) as rownum ,* from score;
1

15 list_agg、wm_concat函数

改为string_agg(,‘,’)

16 merge into 函数

Oracle的用法： 判断源表 t1 和目标表 stu 是否满足 ON 中的条件，如果满足则用 t1 表去更新 stu 表，如果不满足，则将
t1 表数据插入 stu 表中

merge into stu 
using(id,name,age) t1
on(stu.id = t1.id)
when matched then
update set
name = t1.name,
age = t1.age
when not matched then
insert (id,name,age)
values(t1.id,t1.name,t1.age)
1
2
3
4
5
6
7
8
9
10

PG中可用with upsert as 改写：

with upsert as(
update stu t1
set name = t2.name,
age = t2.age
from (id,name,age) t2
where t1.id = t2.id
returning t1.id,t1.name,t1.age
)
insert into stu (id,name,age)
values(id,name,age)
where not exists(
SELECT 1
FROM upsert) 
1
2
3
4
5
6
7
8
9
10
11
12
13

17 dual的select语句

Pg中的 select xxx from dual 可以直接用select xxx

18 空间字段构造函数

Oracle的用法：

SDE.ST_POINT(V_X, V_Y, V_SRID)
1

PG中的用法：

st_geomfromtext('POINT('||V_X||' '||V_Y||')',V_SRID::integer));
1

19 游标的定义与使用

定义游标
Oracle的用法：

TYPE C_CURSOR IS REF CURSOR;
V_CURSOR C_CURSOR;
1
2

PG中的用法：

V_CURSOR REFCURSOR;
1

打开游标
Oracle的用法：

OPEN V_CURSOR FOR V_SQL;
1

PG中的用法：

OPEN V_CURSOR FOR EXECUTE format(V_SQL);
1

20 类型自动转换问题

Oracle中有些类型之间会自动转换，比如数字类型和字符类型之间。PostgreSQL则不支持，需要通过会出现类型不匹配等错误。需要使用cast函数进行转换CAST(aa
as NUMERIC)，CAST( bb as VARCHAR)。

21 PostgreSQL中的分页

PostgreSQL使用limit代替Oracle中的rownum分页。语法： select * from table limit A
offset B; 解释：A就是你需要的记录行数；B就是查询的起始行。

22 PostgreSQL中 的大小写

PostgreSQL对表名、字段名都是区分大小写的。但是，PostgreSQL在SQL语句中对大小写是不敏感的，在不加双引号的情况下，创建时无论使用大写还是小写，表中都会统一转为小写显示的，因此查询时也会将语句中的字段名统一改成小写，因此，此时使用大小写查询均可。最后的建议就是PostgreSQL中能用小写的就小写。