oracle转pg日志

peterPan1993

已于 2023-12-21 16:01:46 修改

阅读量1k

点赞数 1

分类专栏： postgresql pgsql 文章标签：数据仓库

于 2022-08-02 10:32:07 首次发布

本文链接：https://blog.youkuaiyun.com/peterPan1993/article/details/126117084

版权

pgsql 同时被 2 个专栏收录

2 篇文章

订阅专栏

postgresql

1 篇文章

订阅专栏

本文详细介绍了从Oracle数据库迁移至PostgreSQL过程中常见的数据类型转换，如时间戳处理和字符串操作，并展示了存储过程和异常处理的差异。还提到了时间计算、字符串拼接、子查询别名、窗口函数等语法的不同。对于开发者进行数据库迁移或跨平台开发具有指导意义。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、时间数据类型

转为时间：

to_timestamp('2022-08-01 11:46:50', 'yyyy-mm-dd hh24:mi:ss')
timestamp '2022-08-01 11:46:50'
'2022-08-01 11:46:50'::timestamp
cast('2022-08-01 11:46:50' as timestamp)
-- postgresql 时间相减计算后，转化为分钟
select round(date_part('epoch', (now()-to_timestamp('2023-11-14 8:00:00','yyyy-mm-dd hh24:mi:ss')))::NUMERIC / 60, 2) || '分钟'

pg不支持oracle中的，date差相除，pg时间间隔仅可除double

二、字符串

substr() – 该方法第二个参数不支持负数
substr(str, -length('1')) => sbustr(str, length(str)+1-length('1'))
'' is null 判断；oracle返回true，pgsql与mysql返回false
字符串拼接：
oracle：string || string 或 concat(concat(str1, str2), str3)
pgsql：string || string
mysql：concat(str1, str2[, str3…])

三、存储过程

Oracle:
declare
 id varchar2(50); -- 定义变量接收参数
begin
 proc_test(id); -- id为out输出参数
 dbms_output.put_line(id); -- 输出
end;
---------------------------------------------------
postgresql:
call proc_test(''); -- 实际两个均为输出
----
create or replace procedure proc_test(out id varchar)
as $body$
declare
 name varchar(20);
begin
	-- Routine body goes here...
 id := Nextval('seq_id'); -- create sequence if not exists seq_id;
end;
$body$
 language plpgsql

四、异常

HINT:  没有匹配指定名称和参数类型的过程. 您也许需要增加明确的类型转换.
create or replace procedure proc_test(id int2); -- 创建存储过程
call proc_test(1); -- 调用存储过程，这里将会报错
**原因：数字默认为 integer 数据类型，要强转**
应该改为： call proc_test(1::smallint); -- 以此调用存储过程

1.oracle 的SYSDATE对应Pgsql的CURRENT_TIME 或 now()，CURRENT_DATE只保留日期
2.oarcle的start with connect by prior 对应pgsql的WITH RECURSIVE a AS
3.oracle的子查询，pgsql 要增加别名，列如 as ***
4.oracle的rownum对应pgsql 的ROW_NUMBER() OVER()
5.pgsql 没有虚拟表dual 可以直接不加from dual
6.oracle中nvl对应pgsql的 coalesce
7.oracle中listagg 对应pgsql的STRING_AGG
8.获取时间差round(extract(epoch FROM (now() - cast(‘2022-07-29 17:33:00’ as TIMESTAMP) ))::numeric,0) 获取秒数，除于60，获取分钟数
9.oracle connect by 转化 pgsql 用 recursive 例：
　SELECT A.BSID FROM WP_BASE_STRUCT A WHERE A.ENABLED = 0 START WITH A.BSFID = 1 CONNECT BY PRIOR A.BSID = BSFID;
=> with recursive r as (select * from WP_BASE_STRUCT where BSFID = 1 union all select k.* from WP_BASE_STRUCT k inner join r on r.BSID = k.BSFID) select BSID from r where r.ENABLED = 0;
oracle 的 connect by 配合 level 用法：SELECT LEVEL FROM DUAL CONNECT BY LEVEL <=7;
10.oracle 没有 split，用（SELECT REGEXP_SUBSTR(string, ‘[^特定字符]+’, 1, LEVEL, ‘i’) as 分割后结果 FROM DUAL CONNECT BY LEVEL <= 特定字符在字符串当中出现的次数 + 1;）代替
对应 pgsql ：regexp_split_to_table()；拓展：split_part()、string_to_array()、regexp_split_to_array()
11.oracle 中的 regexp_instr 对应 pgsql 正则匹配操作符 ~
12.ctid是postgresql中的索引字段，不能使用之命名，要改为大写：CTID