Postgre与Oracle编写SQL的区别与转换方法

1.instr函数可以用like替换

也可以用 POSITION ('试油' in T1.WELLTESTMETHOD)>0替换

2.decode可以用case when 2 then 2 when 1 then -1替换

SELECT
M.DESIGN_ID,
MAX( DECODE( M.AUDIT_CODE, 2, 2, 1,- 1, 0, 0 ) ) AUDIT_CODE
FROM
PC_BUILD_DAILY_REPORT_DAY M
WHERE
to_date ( m.control_date, 'yyyy-mm-dd' ) = to_date ( '2019-05-21', 'yyyy-mm-dd' )
GROUP BY
M.DESIGN_ID

 

SELECT
A.design_id,
MAX(
CASE A.AUDIT_CODE
WHEN '2' THEN '2'
WHEN '1' THEN '-1'
WHEN '0' THEN '0'
END) AS AUDIT_CODE
FROM PC_BUILD_DAILY_REPORT_DAY A
WHERE
to_date ( A.control_date, 'yyyy-mm-dd' ) = to_date ( '2019-05-21', 'yyyy-mm-dd' )
GROUP BY
A.DESIGN_ID

3.SUBSTR 截取字符串的开始位置必须为1 例如截取aaaaa的四位 SUBSTR ('aaaaa',1,4) 若为SUBSTR ('aaaaa',0,4) 则无效

4.PostgreSQL中不需要dual虚拟表 select语句可以没有from

5.类似于select *from(select...) from里面的子查询必须要起别名,多层嵌套一样都要起别名

如select *from(select *from(select...) as A)as B

6.postgre中没有trunc、addmonths、lastday函数可以用date_trunc、interval替代(已经编写了f_add_months f_last_day函数可用)

例子：

求日期该年第一天
trunc(TO_DATE('20190714', 'yyyymmdd'),'yyyy')
date_trunc('year',TO_DATE('20190714', 'yyyymmdd'))

求日期该年最后一天 两种方式替换（第二种比较好）
last_day(add_months(trunc(TO_DATE('20190714', 'yyyymmdd'),'y'),11))
date_trunc('day',(date_trunc('year',TO_DATE('20190714', 'yyyymmdd'))+ interval '11 month')+interval '30 day')
(date_trunc('MONTH',date_trunc('year',TO_DATE('2019-07-14', 'yyyy-mm-dd'))+ interval '11 month') + INTERVAL '1 MONTH - 1 day')
求日期该月第一天
TRUNC(TO_DATE('20190414', 'yyyymmdd'),'mm')
TRUNC(ADD_MONTHS(LAST_DAY(TO_DATE('20190414', 'yyyymmdd')),-1)) + 1
date_trunc('MONTH',(date_trunc('MONTH',to_date('2019-07-09','yyyy-mm-dd')) - INTERVAL '1 MONTH')+ INTERVAL '1 MONTH')

详细介绍见https://blog.csdn.net/liguangxianbin/article/details/80166574

7.postgre中没有nvl函数，可以用coalesce函数替代

nvl(collect_result,0) as collect_result
coalesce(collect_result,0) as collect_result

8.字符串去掉空格

oracle
Select LTRIM(' sql_in_a_nutshell'),
Select RTRIM('sql_in_a_nutshell '),
TRIM(' sql_in_a_nutshell ')
FROM dual;

PostgreSQL
Select TRIM(LEADING FROM ' sql_in_a_nutshell'),
TRIM(TRAILING FROM 'sql_in_a_nutshell '),
TRIM(BOTH FROM ' sql_in_a_nutshell ');

9.上面清除空格相反的操作，添加空格

oracle
Select LPAD(('sql', 5, ' '),
RPAD(('sql', 5, ' ')
FROM dual;

PostgreSQL
Select LPAD('sql', 5, ' '),
LPAD('sql', 2, ' '),
RPAD('sql', 5, ' '),
RPAD('sql', 2, ' '),;
//结果为 ' sql' 'sq' 'sql ' 'ql'

10.字符串替换

oracle
Select
REPLACE('wabbit_season','it_','it_hunting_')
FROM dual;

PostgreSQL
Select TRANSLATE('wabbit_season','it_','it_hunting_');
Select replace('wabbit_season','it_','it_hunting_');

 

 

11.NULLIF(expression1, expression2) 如果 expression1 等于 expression2则返回 NULL，如果expression1的值为null,也返回NULL

oracle
Select DECODE(foo,'Wabbits!',NULL)
FROM dual;

PostgreSQL
Select NULLIF(foo, 'Wabbits!');

12.postgre中没有nvl2函数，可以用case when is not null then 2 else 1替换

nvl2(name, '有人', '无人')

when name is not null then '有人' else '无人' end

13 REGEXP_SUBSTR可以替换为substring (匹配正则表达式)

REGEXP_SUBSTR(ST.STRAT_UNIT_NAME,'[^'|| UNISTR('4e00') ||'-'||UNISTR('9fa5') || ']',1,1)
SELECT substring(ST.STRAT_UNIT_NAME from '[^u4e00-u9fa5]')

 

 

14 postgre函数学习手册https://blog.csdn.net/zdq0394123/article/details/8227567

15 FNSPLIT 可以用regexpsplittotable替换(列转行)

SELECT split_part('哈-哈哈哈', '哈', 2) //结果 -
SELECT substring('你好-哈哈' from '([u3007u3400-u4DB5u4E00-u9FCBuF900-uFA2D]+-)')//结果 你好-
SELECT a.string_to_array[2] from (SELECT string_to_array ('a,b,b',',')) as a //结果b
SELECT split_part('accb','c',2) //无结果
SELECT regexp_split_to_table('a,b,b',',')//结果a b b集合

 

 

16(行转列)

PIVOT(MAX(BTM_DEPTH) BTM_DEPTH,(MAX(BTM_DEPTH - TOP_DEPTH)) THICKNESS, MAX(OIL_GAS_POSITION) OIL_GAS_POSITION, MAX(DIP_ANGLE) DIP_ANGLE
FOR PHASE IN('设计' DESIGN, '实际' ACTUAL))

max( CASE WHEN PHASE = '设计' THEN BTM_DEPTH ELSE 0 END ) AS DESIGN_BTM_DEPTH,
max( CASE WHEN PHASE = '实际' THEN BTM_DEPTH ELSE 0 END ) AS ACTUAL_BTM_DEPTH,
max( CASE WHEN PHASE = '设计' THEN BTM_DEPTH - TOP_DEPTH ELSE 0 END ) AS DESIGN_THICKNESS,
max( CASE WHEN PHASE = '实际' THEN BTM_DEPTH - TOP_DEPTH ELSE 0 END ) AS ACTUAL_THICKNESS,
max( CASE WHEN PHASE = '设计' THEN OIL_GAS_POSITION ELSE '' END ) AS DESIGN_OIL_GAS_POSITION,
max( CASE WHEN PHASE = '实际' THEN OIL_GAS_POSITION ELSE '' END ) AS ACTUAL_OIL_GAS_POSITION,
max( CASE WHEN PHASE = '设计' THEN DIP_ANGLE ELSE 0 END ) AS DESIGN_DIP_ANGLE,
max( CASE WHEN PHASE = '实际' THEN DIP_ANGLE ELSE 0 END ) AS ACTUAL_DIP_ANGLE

17 postgre对于数据类型的要求十分严格,并且没有自动转换格式,而oracle对于数据类型要求不是十分严格

例如用||拼接字符串的时候 'aaa'||123||'bbb' 这种就会报错(oracle不会报错,正常运行) 需要改为 'aaa'||to_char(123,'99999999999')||'bbb' 才能拼接 ,

需要编写者自行匹配正确的数据格式,如果格式不相符,则需要用to_char(字段,'999999999999')或to_number(字段,'9999999999999999') 或cast(字段 as varchar)  cast(字段 as integer)

PS:

对于to_char 9的数量没有限制,尽量多些,不然会丢失结果,

如to_char(123,'9') 结果为 ' #';

to_char(123,'99') 结果为 ' ##';

to_char(123,'999') 结果为 ' 123'

to_char(123,'9999') 结果为 ' 123'

随着9的增加 123前面的空格增多,但并不影响计算

对于to_number 9的数量没有限制,尽量多些,不然会丢失位数,

如to_number('123','9') 结果为 1;

to_number('123','99') 结果为 12;

to_number(123,'999') 结果为 123

to_char(123,'9999') 结果为 123 随着9的增加 结果始终是123

 

18 贼实用的generate_series函数

函数	参数类型	返回类型	描述
generate_series(start, stop)	int 或 bigint	setof int 或 setof bigint(与参数类型相同)	生成一个数值序列，从start 到 stop，步进为一
generate_series(start, stop, step)	int 或 bigint	setof int 或 setof bigint(与参数类型相同)	生成一个数值序列，从start 到 stop，步进为step
generate_series(start, stop, step_interval)	timestamp or timestamp with time zone	timestamp 或 timestamp with time zone(same as argument type)	生成一个数值序列，从start 到 stop，步进为step

SELECT generate_series(1,5,2)

结果

1

3

5

select date(zz) as t from generate_series(date_trunc('month',to_date('201505','yyyymm')), date_trunc('month',to_date('201507','yyyymm')),'1 month') as zz;

结果

2015-05-01

2015-06-01

2015-07-01

19 新增fn_get_month_days函数,获取当月天数

例子:

select fn_get_month_days('201912')

结果  31

20 替代Oracle中ALL_TAB_COLUMNS与ALL_TABLES

ALL_TAB_COLUMNS:

SELECT
col_description ( A.attrelid, A.attnum ) AS COMMENT,
format_type ( A.atttypid, A.atttypmod ) AS TYPE,
A.attname AS NAME,
A.attnotnull AS NOTNULL
FROM
pg_class AS C,
pg_attribute AS A
WHERE
C.relname = 'cd_well'
AND A.attrelid = C.oid
AND A.attnum > 0

ALL_TABLES:

SELECT
relname AS tabname,
CAST ( obj_description ( relfilenode, 'pg_class' ) AS VARCHAR ) AS COMMENT
FROM
pg_class C
WHERE
relkind = 'r'
AND relname NOT LIKE'pg_%'
AND relname NOT LIKE'sql_%'
ORDER BY
relname

21 ‘a’ || null=？

Oracle中 ‘a’ || null = ‘a’

Postgre中 ‘a’ || null = null