数据库改造(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
);
2. pg库空间索引创建
create index a103851_shape_idx on gis.a103851 using gist(shape);
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
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
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;
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;
10 start with connect by函数的问题:
oracle的这个函数是用来查树形结构的,即同一张的表的记录有父子级关系的那种。oracle中用法如下:
select module_id
from sys_modules
start with module_id = ?
connect by prior module_id = module_fid;
这就表示查询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;
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;
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)
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)
17 dual的select语句
Pg中的 select xxx from dual 可以直接用select xxx
18 空间字段构造函数
Oracle的用法:
SDE.ST_POINT(V_X, V_Y, V_SRID)
PG中的用法:
st_geomfromtext('POINT('||V_X||' '||V_Y||')',V_SRID::integer));
19 游标的定义与使用
定义游标
Oracle的用法:
TYPE C_CURSOR IS REF CURSOR;
V_CURSOR C_CURSOR;
PG中的用法:
V_CURSOR REFCURSOR;
打开游标
Oracle的用法:
OPEN V_CURSOR FOR V_SQL;
PG中的用法:
OPEN V_CURSOR FOR EXECUTE format(V_SQL);
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中能用小写的就小写。
更多推荐
所有评论(0)