[大数据]Pgsql与Oracle语法差异（SQL迁移记录）

oracle 数据库中没有limit关键字

LIMIT 1 替换为 rownum=1

select * from table where rownum=1;     // 输出1条

oracle 自增序列使用 sequence

PGSQL 自增序列可用 serial 自增序列 数据类型

--使用   
test_table_id_seq.NEXTVAL

create sequence test_table_id_seq
 increment by 1
 start with 1
 minvalue 1
 maxvalue 999999999999999999;

PGSQL

create table t_test1
(
  id      serial not null,
  name    varchar(32),
  p_order serial not null,
  data    varchar(32)
);```

oracle 条件语句on/where 中使用 case when

        FROM tableA  a
        LEFT JOIN tableB  b ON a.id = b.id AND
        --Oracle
        (CASE WHEN a.flag= '0' THEN a.code ELSE a.temp_code END)= b.code
        
        --PG
        (CASE WHEN d.stkex = '0' THEN a.code = b.bond_code ELSE a.stk_undl_code= b.bond_code END)

类型

registry.regkey_val::integer
 CAST(registry.regkey_val AS INTEGER)

函数替换 字符位置

pgsql “position”
oracle instr

"position"((( SELECT registry.regkey_val
 FROM registry
 WHERE registry.regkey_id = 'COLLATERALIZABLE_BOND_PARAM')),
 replace(mb.int_org , ' ', '')) = 0
                                        
                                                 
instr((( SELECT registry.regkey_val
FROM registry
WHERE registry.regkey_id = 'COLLATERALIZABLE_BOND_PARAM')),
replace(mb.int_org, ' ', '')) = 0                                                  

日期操作

        AND stk_calendar.physical_date >= (( SELECT to_char(date_trunc('month'::text, 'now'::text::date::timestamp with time zone) - '1 mon'::interval month, 'YYYYMMDD'::text)::integer AS to_char))
        AND stk_calendar.physical_date <= (( SELECT to_char(date_trunc('month'::text, 'now'::text::date::timestamp with time zone) - '1 day'::interval, 'YYYYMMDD'::text)::integer AS to_char))

时间函数

pgsql date_trunc

oracle TRUNC

pgsql   上月第一天、上月最后一天

AND stk_calendar.physical_date >= (( SELECT to_char(date_trunc('month'::text, 'now'::text::date::timestamp with time zone) - '1 mon'::interval month, 'YYYYMMDD'::text)::integer AS to_char)) 
AND stk_calendar.physical_date <= (( SELECT to_char(date_trunc('month'::text, 'now'::text::date::timestamp with time zone) - '1 day'::interval, 'YYYYMMDD'::text)::integer AS to_char))
        
        
oracle   上月第一天、上月最后一天
select CAST(to_char(trunc(add_months(sysdate,-1),'month'),'YYYYMMDD') AS INTEGER)  AS pre_month_first_date from dual
SELECT CAST(to_char(last_day(add_months(sysdate,-1)),'YYYYMMDD') AS INTEGER)AS pre_month_last_date FROM dual

上月第一天oracle

1.oracle 上月今日 月份函数add_months

SELECT add_months(sysdate,-1) FROM dual

2.本月第一天 日期函数trunc

select trunc(sysdate,'month') from dual;   //返回本月的第一天  oracle

3.上月第一天 （结合1、2即可）

select trunc(add_months(sysdate,-1),'month') from dual

--格式化，转出整形
select CAST(to_char(trunc(add_months(sysdate,-1),'month'),'YYYYMMDD') AS INTEGER)  AS pre_month_first_date from dual

上月最后一天Oracle

1.oracle 上月 同理

SELECT add_months(sysdate,-1) FROM dual

2.最后一天 last_day函数

SELECT last_day(sysdate) FROM dual

3.上月最后一天 结合1、2即可

SELECT last_day(add_months(sysdate,-1))FROM dual

--格式化，转出整形

SELECT CAST(to_char(last_day(add_months(sysdate,-1)),'YYYYMMDD') AS INTEGER)AS pre_month_last_date FROM dual

其他

select to_char(trunc(add_months(last_day(sysdate), -1) + 1), 'yyyy-mm-dd') from dual  // 本月第一天
select to_char(last_day(sysdate), 'yyyy-mm-dd') AS ENDDATE from dual    //  本月最后一天

上月第一天

--oracle
select CAST(to_char(trunc(add_months(sysdate,-1),'month'),'YYYYMMDD') AS INTEGER)  AS last_month_first_date from dual

select add_months(sysdate,-1) from dual;

--pgsql
SELECT to_char(date_trunc('month'::text, 'now'::text::date::timestamp with time zone) - '1 mon'::interval month, 'YYYYMMDD'::text)::integer AS to_char)

上月最后一天

--oracle



--pgsql
SELECT to_char(date_trunc('month'::text, 'now'::text::date::timestamp with time zone) - '1 day'::interval, 'YYYYMMDD'::text)::integer AS to_char

函数date_part，获取日期相差天数

ORACLE 日期相差天数（转日期类型相减 然后转整形）

select TO_NUMBER( TO_DATE(TO_CHAR(20180605),'YYYYMMDD') - TO_DATE(TO_CHAR(20180601),'YYYYMMDD')) from dual

select  abs(TO_NUMBER( TO_DATE(TO_CHAR( hbbc.bb_date),'YYYYMMDD') - TO_DATE(TO_CHAR(hbbc.trd_date),'YYYYMMDD'))) from dual

PGSQL

SELECT abs(date_part('day', hbbc.bb_date ::character varying::timestamp without time zone - hbbc.trd_date ::character varying::timestamp without time zone)) AS abs

(( SELECT abs(date_part('day'::text, bbc.bb_date ::character varying::timestamp without time zone - bbc.trd_date ::character varying::timestamp without time zone)) AS abs))

参考

--Oracle中两个日期相差天数--
select TO_NUMBER(TO_DATE('2018-6-5','yyyy-mm-dd hh24:mi:ss')- TO_DATE('2018-5-31','yyyy-mm-dd hh24:mi:ss'))
AS 相差天数 from dual;


select TO_NUMBER(TO_DATE('2018-6-5','yyyy-mm-dd hh24:mi:ss')- TO_DATE('2018-5-31','yyyy-mm-dd hh24:mi:ss'))

AS 相差天数 from dual;

2
(select TRUNC(t.finish_time) - TRUNC(t.start_time)) as 相隔天数 from table t

字符串分隔函数

pgsql regexp_split_to_table

Oracle实现split函数

oracle分割以逗号间隔得字符串

SELECT
	REGEXP_SUBSTR ('1,2,3',
	'[^,]+',
	1,
	rownum)
FROM
	dual
CONNECT BY
	rownum <= LENGTH ('1,2,3') - LENGTH (regexp_replace('1,2,3', ',', ''))+ 1;


第二种
 
SELECT DISTINCT REGEXP_SUBSTR ('1,2,3','[^,]+',1,LEVEL) as "test"
FROM  DUAL
CONNECT BY REGEXP_SUBSTR ('1,2,3','[^,]+',1,LEVEL) IS NOT NULL
order by 1