Oracle迁移PostgreSQL经验总结
来源:互联网 发布:淘宝怎么举报假冒商品 编辑:程序博客网 时间:2024/05/22 15:28
本文完全转载自:http://my.oschina.net/liyuj/blog/539303?fromerr=L3ZJLcuW&utm_source=tuicool&utm_medium=referral
摘要
Oracle迁移PostgreSQL数据库经验总结(SQL部分,未完待续),本文只包括了我工作中接触到或者用到的技术点,其实两个数据库设计上的差异是很大的,但是Oracle数据库有的功能,PostgreSQL大体上也都能实现
序号项目OraclePostgreSQL1当前时间SYSDATE可全部使用current_timestamp替换2序列SEQNAME.NEXTVALNEXTVAL('SEQNAME')3固定值列SELECT '1' AS COL1SELECT CAST('1' AS TEXT) AS COL14NVLNVL函数NVL可以用COALESCE函数替换5类型自动转换Oracle某些情况下支持类型自动转换会出现类型不匹配等错误,需要在Java或者sql中进行类型转换,使类型匹配6INSTR函数instr('str1','str2')strpos('str1','str2')7外连接Oracle可简写为(+)用LEFT JOIN等语句替换8层次查询START WITH语句
CONNECT BY语句用WITH RECURSIVE语句9数据库对象大小写不区分大小写创建数据库对象时要小写,这样才不区分SQL的大小写10同义词Oracle支持同义词用视图代替11DUALSELECT 1+1 FROM DUALSELECT 1+1
或者
CREATE VIEW dual AS
SELECT current_timestamp12ROWNUMROWNUM关键字两种情况:
1.限制结果集数量,用于翻页等:
SELECT * FROM T LIMIT 5 OFFSET 0
2.生成行号:
ROW_NUMBER() OVER()13DECODE等判断函数DECODE()用标准的CASE WHEN THEN ELSE END语句替换14TO_CHARTO_CHAR(COL,FMT),格式化字符串可以为空TO_CHAR(COL1,'FM999999'),9的个数为字段长度,详细定义见:
http://www.postgresql.org/docs/9.4/static/functions-formatting.html15TO_NUMBERTO_NUMBER(COL,FMT),格式化字符串可以为空TO_NUMBER(COL1,'999999'),9的个数为字段长度,详细定义见:
http://www.postgresql.org/docs/9.4/static/functions-formatting.html16NULL和''ORACLE认为''等同于NULLNULL和''不同17NULL和''LENGTH('')为NULLLENGTH('')为018NULL和''TO_DATE('','YYYYMMDD')为空TO_DATE('','YYYYMMDD')为0001-01-01 BC19NULL和''TO_NUMBER('',1)为NULLTO_NUMBER('',1),报错20NULL和''INSERT INTO TEST(VALUE4)VALUES('')
[Result]VALUE4=NULL (注:VALUE3字段为数值类型)INSERT INTO TEST(VALUE4)VALUES('')
VALUE4=NULL21NULL和''INSERT INTO TEST(VALUE4)VALUES('')
[Result]VALUE4=NULL (注:VALUE3字段为字符类型)INSERT INTO TEST(VALUE4)VALUES('')
VALUE4=''22NULL和''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 BC23ADD_MONTHSADD_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)24LAST_DAYLAST_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;25MONTHS_BETWEENMONTHS_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';26BITANDBITAND(A,B)A & B27MINUSMINUS语句以EXCEPT语句来替代28BIN_SELECT BIN_TO_NUM(1,0,1,0) AS VALUE1 FROM DUALSELECT CAST(B'1010' AS INTEGER) AS VALUE129UPDATE语句列列表UPDATE accounts SET (contact_last_name, contact_first_name) =
(SELECT last_name, first_name FROM salesmen
WHERE salesmen.id = accounts.sales_id);不支持该语法,需要拆分为多个单独的列30SUBSTR函数如果从第一个开始取子串,可以从0开始,也可以从1开始,如果不是第一个开始,则从1开始计数,可以为负值,从字符串结尾计数,用于取最后几位。从1开始计数。如果要取最后几位,可以用RIGHT函数解决。31子查询别名如果FROM后只有一个子查询,该子查询可以没有别名必须有别名32列(别)名为关键字Oracle中比如name,type这样的关键字可以直接作为列的别名,比如:select xx name from t需要加as,比如select xx as name from t33当前登录用户SELECT USER FROM DUALselect current_user34ALL_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关键字然后提供一个类型转换的表达式
CONNECT BY语句用WITH RECURSIVE语句9数据库对象大小写不区分大小写创建数据库对象时要小写,这样才不区分SQL的大小写10同义词Oracle支持同义词用视图代替11DUALSELECT 1+1 FROM DUALSELECT 1+1
或者
CREATE VIEW dual AS
SELECT current_timestamp12ROWNUMROWNUM关键字两种情况:
1.限制结果集数量,用于翻页等:
SELECT * FROM T LIMIT 5 OFFSET 0
2.生成行号:
ROW_NUMBER() OVER()13DECODE等判断函数DECODE()用标准的CASE WHEN THEN ELSE END语句替换14TO_CHARTO_CHAR(COL,FMT),格式化字符串可以为空TO_CHAR(COL1,'FM999999'),9的个数为字段长度,详细定义见:
http://www.postgresql.org/docs/9.4/static/functions-formatting.html15TO_NUMBERTO_NUMBER(COL,FMT),格式化字符串可以为空TO_NUMBER(COL1,'999999'),9的个数为字段长度,详细定义见:
http://www.postgresql.org/docs/9.4/static/functions-formatting.html16NULL和''ORACLE认为''等同于NULLNULL和''不同17NULL和''LENGTH('')为NULLLENGTH('')为018NULL和''TO_DATE('','YYYYMMDD')为空TO_DATE('','YYYYMMDD')为0001-01-01 BC19NULL和''TO_NUMBER('',1)为NULLTO_NUMBER('',1),报错20NULL和''INSERT INTO TEST(VALUE4)VALUES('')
[Result]VALUE4=NULL (注:VALUE3字段为数值类型)INSERT INTO TEST(VALUE4)VALUES('')
VALUE4=NULL21NULL和''INSERT INTO TEST(VALUE4)VALUES('')
[Result]VALUE4=NULL (注:VALUE3字段为字符类型)INSERT INTO TEST(VALUE4)VALUES('')
VALUE4=''22NULL和''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 BC23ADD_MONTHSADD_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)24LAST_DAYLAST_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;25MONTHS_BETWEENMONTHS_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';26BITANDBITAND(A,B)A & B27MINUSMINUS语句以EXCEPT语句来替代28BIN_SELECT BIN_TO_NUM(1,0,1,0) AS VALUE1 FROM DUALSELECT CAST(B'1010' AS INTEGER) AS VALUE129UPDATE语句列列表UPDATE accounts SET (contact_last_name, contact_first_name) =
(SELECT last_name, first_name FROM salesmen
WHERE salesmen.id = accounts.sales_id);不支持该语法,需要拆分为多个单独的列30SUBSTR函数如果从第一个开始取子串,可以从0开始,也可以从1开始,如果不是第一个开始,则从1开始计数,可以为负值,从字符串结尾计数,用于取最后几位。从1开始计数。如果要取最后几位,可以用RIGHT函数解决。31子查询别名如果FROM后只有一个子查询,该子查询可以没有别名必须有别名32列(别)名为关键字Oracle中比如name,type这样的关键字可以直接作为列的别名,比如:select xx name from t需要加as,比如select xx as name from t33当前登录用户SELECT USER FROM DUALselect current_user34ALL_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关键字然后提供一个类型转换的表达式
0 0
- Oracle迁移PostgreSQL经验总结
- oracle向PostgreSQL迁移
- postgresql数据库迁移到oracle
- Oracle迁移到PostgreSQL问题
- [数据库迁移] ORACLE到MYSQL经验总结
- Oracle迁移到PostgreSQL的一些经验
- Oracle迁移Postgresql 遇到的一些问题记录
- 使用ora2pg完成从oracle到postgresql的迁移工作
- Mac上使用Ora2Pg迁移Oracle到PostgreSQL
- svn迁移git经验总结
- mysql 迁移到postgresql
- postgresql 数据库迁移时间
- postgresql数据库路径迁移
- Postgresql的数据迁移
- mysql 迁移至 postgresql
- 经验总结12--EF6数据迁移
- Core Data 版本迁移经验总结
- Core Data 版本迁移经验总结
- Android 系统信息的获取
- Expandablelistview 带有二级菜单的listview
- 第二周项目3-体验复杂度(1)两种排序算法的运行时间
- [绍棠] iOS 10 开发适配系列 之 权限Crash问题
- Java程序设计教程笔记
- Oracle迁移PostgreSQL经验总结
- 第三方登陆代码实现
- Spring 的常用注释
- 初试android,写了个2个服务端springmvc基于htpp协议的json互传和传统soap协议的webservice
- 在android中,编译的项目使用到第三方jar的导入方法 终极版!
- java版文件重命名
- 383C - Propagating tree 线段树加时间戳
- HDU:不可模数
- Java 汉字转拼音工具类