Oracle迁移PostgreSQL经验总结(SQL部分)
jopen
9年前
Oracle迁移PostgreSQL数据库经验总结(SQL部分,未完待续),本文只包括了我工作中接触到或者用到的技术点,其实两个数据库设计上的差异是很大的,但是Oracle数据库有的功能,PostgreSQL大体上也都能实现。
序号 | 项目 | Oracle | PostgreSQL |
1 | 当前时间 | SYSDATE | 可全部使用current_timestamp替换 |
2 | 序列 | SEQNAME.NEXTVAL | NEXTVAL('SEQNAME') |
3 | 固定值列 | SELECT '1' AS COL1 | SELECT CAST('1' AS TEXT) AS COL1 |
4 | NVL | NVL函数 | NVL可以用COALESCE函数替换 |
5 | 类型自动转换 | Oracle某些情况下支持类型自动转换 | 会出现类型不匹配等错误,需要在Java或者sql中进行类型转换,使类型匹配 |
6 | INSTR函数 | instr('str1','str2') | strpos('str1','str2') |
7 | 外连接 | Oracle可简写为(+) | 用LEFT JOIN等语句替换 |
8 | 层次查询 | START WITH语句 CONNECT BY语句 | 用WITH RECURSIVE语句 |
9 | 数据库对象大小写 | 不区分大小写 | 创建数据库对象时要小写,这样才不区分SQL的大小写 |
10 | 同义词 | Oracle支持同义词 | 用视图代替 |
11 | DUAL | SELECT 1+1 FROM DUAL | SELECT 1+1 或者 CREATE VIEW dual AS SELECT current_timestamp |
12 | ROWNUM | ROWNUM关键字 | 两种情况: 1.限制结果集数量,用于翻页等: SELECT * FROM T LIMIT 5 OFFSET 0 2.生成行号: ROW_NUMBER() OVER() |
13 | DECODE等判断函数 | DECODE() | 用标准的CASE WHEN THEN ELSE END语句替换 |
14 | TO_CHAR | TO_CHAR(COL,FMT),格式化字符串可以为空 | TO_CHAR(COL1,'FM999999'),9的个数为字段长度,详细定义见: http://www.postgresql.org/docs/9.4/static/functions-formatting.html |
15 | TO_NUMBER | TO_NUMBER(COL,FMT),格式化字符串可以为空 | TO_NUMBER(COL1,'999999'),9的个数为字段长度,详细定义见: http://www.postgresql.org/docs/9.4/static/functions-formatting.html |
16 | NULL和'' | ORACLE认为''等同于NULL | NULL和''不同 |
17 | NULL和'' | LENGTH('')为NULL | LENGTH('')为0 |
18 | NULL和'' | TO_DATE('','YYYYMMDD')为空 | TO_DATE('','YYYYMMDD')为0001-01-01 BC |
19 | NULL和'' | TO_NUMBER('',1)为NULL | TO_NUMBER('',1),报错 |
20 | NULL和'' | INSERT INTO TEST(VALUE4)VALUES('') [Result]VALUE4=NULL (注:VALUE3字段为数值类型) | INSERT INTO TEST(VALUE4)VALUES('') VALUE4=NULL |
21 | NULL和'' | INSERT INTO TEST(VALUE4)VALUES('') [Result]VALUE4=NULL (注:VALUE3字段为字符类型) | INSERT INTO TEST(VALUE4)VALUES('') VALUE4='' |
22 | NULL和'' | INSERT INTO TEST(VALUE4)VALUES(TO_DATE('','YYYYMMDD')) [Result]VALUE4=NULL (注:VALUE3字段为时间类型) | INSERT INTO TEST(VALUE6)VALUES(TO_DATE('','YYYYMMDD')) [Result]VALUE6=0001-01-01 BC |
23 | ADD_MONTHS | ADD_MONTHS(DATE,INT) | CREATE FUNCTION add_months(date, int) RETURNS date AS 'SELECT ($1 +($2::text||'' month'')::interval)::date' LANGUAGE 'sql' 或SQL: SELECT ($1 +($2::text||' month')::interval) |
24 | LAST_DAY | LAST_DAY(DATE) | 创建函数来解决 CREATE OR REPLACE FUNCTION last_day(date) RETURNS date AS $$ SELECT (date_trunc('MONTH', $1) + INTERVAL '1 MONTH - 1 day')::date; $$ LANGUAGE 'sql'; 或SQL: SELECT (date_trunc('MONTH', $1) + interval '1 month - 1 day')::date; |
25 | MONTHS_BETWEEN | MONTHS_BETWEEN(DATE,DATE) | 创建函数来解决 CREATE FUNCTION MONTH_BETWEEN(d1 timestamp,d2 timestamp) RETURNS NUMERIC AS 'SELECT (extract(year from age(d1,d2))*12 + extract(month from age(d1,d2)))::integer' LANGUAGE 'sql'; |
26 | BITAND | BITAND(A,B) | A & B |
27 | MINUS | MINUS语句 | 以EXCEPT语句来替代 |
28 | BIN_ | SELECT BIN_TO_NUM(1,0,1,0) AS VALUE1 FROM DUAL | SELECT CAST(B'1010' AS INTEGER) AS VALUE1 |
29 | UPDATE语句列列表 | UPDATE accounts SET (contact_last_name, contact_first_name) = (SELECT last_name, first_name FROM salesmen WHERE salesmen.id = accounts.sales_id); | 不支持该语法,需要拆分为多个单独的列 |
30 | SUBSTR函数 | 如果从第一个开始取子串,可以从0开始,也可以从1开始,如果不是第一个开始,则从1开始计数,可以为负值,从字符串结尾计数,用于取最后几位。 | 从1开始计数。如果要取最后几位,可以用RIGHT函数解决。 |
31 | 子查询别名 | 如果FROM后只有一个子查询,该子查询可以没有别名 | 必须有别名 |
32 | 列(别)名为关键字 | Oracle中比如name,type这样的关键字可以直接作为列的别名,比如:select xx name from t | 需要加as,比如select xx as name from t |
33 | 当前登录用户 | SELECT USER FROM DUAL | select current_user |
34 | ALL_COL_COMMENTS | 通过SELECT * FROM ALL_COL_COMMENTS可以获得列注释信息 | select s.column_name as COLUMN_NAME,coalesce(col_description(c.oid,ordinal_position) ,s.column_name) as COMMENTS from information_schema.columns s,pg_class c where s.table_name = 'ac01_si' and s.table_name = c.relname and s.table_schema = current_schema() PG需要通过col_description获得列注释信息 |
35 | 修改表字段类型 | 1.如果字段无数据,可直接修改 2.如果有数据且新类型和原类型兼容,也可以直接修改 3.如果不兼容,可通过对原字段改名,然后增加新字段,再通过UPDATE语句对数据进行处理 | 1.如果新类型和原类型兼容,可直接修改 2.如果不兼容,需要使用USING关键字然后提供一个类型转换的表达式 |
来自:http://my.oschina.net/liyuj/blog/539303