[大数据]oracle数据库切换为postgresql数据库sql修改

1. pg对大小写不敏感，查询大写的要加双引号，迁移数据是建议表名和字段名小写；
2. 返回第一个非空的：NVL()改为COALESCE()；
3. pg中要加‘表别名’：FROM(...) A；
4. 行号：ROWNUM 改为ROW_NUMBER()OVER()；
5. 随机数：dbms_random.value可用random()来替换，取介于两个数b, a之间的随机数random()*(b-a)+a；
6. 字段别名为name时，要加as
7. 数据类型：number(10,1) 改为?numeric(10,1)
8. 返回要截取的字符串在源字符串中的位置：instr('str1','str2')改为strpos('str1','str2')；
9. select 1 from dual改为select 1；
10. 当前时间：sysdate改为now()或current_timestamp
11. decode 函数改为case when
12. 日期加减：now() + interval '2 years'；
13. in和not in在数据量效率很慢，要改成 exists not exists；
14. pg中“/”“%”,/在pg中只保留整数，且只舍不入；%取模，四舍五入；要想/保留小数，类型转换为numeric。1>字段A::numeric/字段B::numeric；2>cast(字段A as numeric)/cast(字段B as numeric)；
15. oracle中int（）为向下取整函数，pg中floor（）向下取整，round（）四舍五入；
16. 序列：迁移序列时oracle序列可直接把sql放到pg中执行，使用时序列名.nextval 改为 nextval('序列名')；
17. limit m offset n从第n行开始取m行数据，一般和order by一起使用，因为它是无序的会有重复数据；

18. pg中日期加减：例：SELECT DATE_TRUNC('MONTH', TO_DATE('201907' ,'YYYYMM'))+INTERVAL'-1 month' --上月1号；SELECT TO_CHAR(TO_DATE('201907' ,'YYYYMM')+INTERVAL'-1 month','YYYYMM') --上月

19. pg查询表名、字段名、类型、注释、是否为空等：

    （1）查询某模式下所有表名：

    select tablename from pg_tables where schemaname='模式名'

    （2）获取表名及注释：

    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

    （3）获取某个表的字段名、类型、注释、是否为空:

    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 = '表名' and a.attrelid = c.oid and a.attnum>0

    （4）获取某模式下某个表的字段名、类型、注释、是否为空:

    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 in(select tablename from pg_tables where schemaname='模式名') and c.relname = '表名' and a.attrelid = c.oid and a.attnum>0