- pg对大小写不敏感,查询大写的要加双引号,迁移数据是建议表名和字段名小写;
- 返回第一个非空的:NVL()改为COALESCE();
- pg中要加‘表别名’:FROM(...) A;
- 行号:ROWNUM 改为ROW_NUMBER()OVER();
- 随机数:dbms_random.value可用random()来替换,取介于两个数b, a之间的随机数random()*(b-a)+a;
- 字段别名为name时,要加as
- 数据类型:number(10,1) 改为 numeric(10,1)
- 返回要截取的字符串在源字符串中的位置:instr('str1','str2')改为strpos('str1','str2');
- select 1 from dual改为select 1;
- 当前时间:sysdate改为now()或current_timestamp
- decode 函数改为case when
- 日期加减:now() + interval '2 years';
- in和not in在数据量效率很慢,要改成 exists not exists;
- pg中的“/”“%”,/在pg中只保留整数,且只舍不入;%取模,四舍五入;要想/保留小数,类型转换为numeric。转换方法:(1)字段A::numeric/字段B::numeric;(2)cast(字段A as numeric)/cast(字段B as numeric);
- oracle中int()为向下取整函数,pg中floor()向下取整,round()四舍五入;
- 序列:迁移序列时oracle序列可直接把sql放到pg中执行,使用时序列名.nextval 改为 nextval('序列名');
- limit m offset n从第n行开始取m行数据,一般和order by一起使用,因为它是无序的会有重复数据;
-
pg中日期加减:例:
SELECT DATE_TRUNC('MONTH', TO_DATE('201907' ,'YYYYMM'))+INTERVAL'-1 month' --上月1号; SELECT TO_CHAR(TO_DATE('201907' ,'YYYYMM')+INTERVAL'-1 month','YYYYMM') --上月
-
pg查询表名、字段名、类型、注释、是否为空等:
(1)查询某模式下所有表名:
select tablename from pg_tables where schemaname='模式名'
(2)获取某个表的字段名、类型、注释、是否为空:
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
(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 in(select tablename from pg_tables where schemaname='模式名') and c.relname = '表名' and a.attrelid = c.oid and a.attnum>0
oracle切换为postgresql-sql语法修改
最新推荐文章于 2024-07-17 13:58:11 发布