文档章节

Oracle迁移PostgreSQL经验总结(SQL部分,未完待续)

李玉珏
 李玉珏
发布于 2015/12/04 14:19
字数 1099
阅读 7578
收藏 149
点赞 19
评论 14
序号 项目 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关键字然后提供一个类型转换的表达式
       

© 著作权归作者所有

共有 人打赏支持
李玉珏

李玉珏

粉丝 268
博文 57
码字总数 92115
作品 0
沈阳
技术主管
加载中

评论(14)

李玉珏
李玉珏

引用来自“巴拉提”的评论

额,搞了半天,原来博主是手动迁移的,我还以为有说明工具或者脚本呢。

工具的话,有官方推荐的ora2pg,还有些商业的工具,但是只有表等少数对象能做到自动转换,其他的大都需要手工处理
m
microsoft_fly
其实可以尝试用下orafce,Oracle兼容性函数,可惜有时候不稳定,导致数据库挂掉
巴拉迪维
巴拉迪维
额,搞了半天,原来博主是手动迁移的,我还以为有说明工具或者脚本呢。
李玉珏
李玉珏

引用来自“运维技术”的评论

@红薯 很多时候客户端不能自适应博客?可以优化么?比如以上博客

电脑版表格效果也不好
运维技术
运维技术
@红薯 很多时候客户端不能自适应博客?可以优化么?比如以上博客
首席程序猿_默
首席程序猿_默
公司的产品从DB2到Oracle、从Oracle到PG都迁移过,那酸爽。5
李玉珏
李玉珏

引用来自“久永”的评论

这样搞累死,不如用 EntityFramwork ,貌似现在 PG 的 EF 支持很好。如果能再加上 json、jsonb 类型在 EF 里面的支持,那就完美了!

这个只能用在一些简单的场景里
久永
久永
这样搞累死,不如用 EntityFramwork ,貌似现在 PG 的 EF 支持很好。如果能再加上 json、jsonb 类型在 EF 里面的支持,那就完美了!
李玉珏
李玉珏

引用来自“钛元素”的评论

大赞!不过这迁移成本也是蛮高的,尤其是技术不达标绝对不敢动!还是花钱买平安 T_T

主要是PG资料少,要想顺利迁移,尤其是大型应用,必须熟悉PG的文档,其实PG的功能性比很多人想象的要强很多
李玉珏
李玉珏

引用来自“魔力猫”的评论

任何数据库,特别是使用了很多数据库独有特性的迁移都是需要很高的代价。建议如果代价太大可以考虑PostgreSQL的那个收费版产品,那家公司做了大量兼容工作。

向PG的迁移有两个原因,一个是他的企业级功能在开源库里是最强的,另一个是阿里云的RDS除了MySQL,还支持PG,其实去O的选择空间并不大
数据库案例集锦 - 开发者的《如来神掌》

标签 PostgreSQL , PG DBA cookbook , PG Oracle兼容性 , PG 架构师 cookbook , PG 开发者 cookbook , PG 应用案例 背景 「剑魔独孤求败,纵横江湖三十馀载,杀尽仇寇,败尽英雄,天下更无抗...

德哥
2017/06/09
0
0
Oracle DBA 增值 PostgreSQL,Greenplum 学习计划

标签 PostgreSQL , Oracle , Greenplum 背景 去O很大程度上是国家层面的战略考虑,比如斯诺登事件,最近贸易战的“中兴”事件,使得去O成为一个不可不做的事情。 但是去O喊了若干年,并没有真...

德哥
05/06
0
0
Oracle migration to Greenplum - (含 Ora2pg)

标签 PostgreSQL , Oracle , Greenplum , PL/SQL , Ora2pg 背景 Oracle在OLTP领域毫无疑问是非常不错的数据库,但是OLAP领域,可以有更好的选择,特别是在数据量大到一定程度的时候,Oracle用...

德哥
05/06
0
0
Oracle数据库迁移到PostgreSQL/EDB初探

由于某些非技术方面的原因,我们也在搞一些开源数据库引入,替换商业数据库,于是瞄上了PostgreSQL。 PostgreSQL本身的技术不在这里做介绍,虽然国内PostgreSQL没有Mysql那么流行,但是搜索一...

Rocky-Wang
2015/04/17
0
1
阿里云如何打破Oracle 迁移上云的壁垒

  【IT168 评论】摘要:2018第九届中国数据库技术大会,阿里云数据库产品专家萧少聪带来以阿里云如何打破Oracle迁移上云的壁垒为题的演讲。Oracle是指“数据库管理系统”,面对Oracle迁移上...

it168网站
05/23
0
0
阿里云如何打破Oracle迁移上云的壁垒

2018第九届中国数据库技术大会,阿里云数据库产品专家萧少聪带来以阿里云如何打破Oracle迁移上云的壁垒为题的演讲。Oracle是指“数据库管理系统”,面对Oracle迁移上云的壁垒,阿里云如何能够...

云迹九州
05/22
0
0
DevOps 资讯 | PostgreSQL 的时代到来了吗 ?

PostgreSQL是对象-关系型数据库,BSD 许可证。拼读为"post-gress-Q-L"。 作者: Tony Baer 原文: Has the time finally come for PostgreSQL?(有删节) 近30年来 PostgreSQL 无疑是您从未听...

RiboseYim
06/23
0
0
Migration Oracle to PostgreSQL "百家"文档集

标签 PostgreSQL , Oracle 背景 2002 Porting from Oracle to PostgreSQL 《PDF Download》 Agenda SQL Syntax, Functions, Sequences, Etc. Database Server General Characteristics Data......

德哥
05/06
0
0
EDB PPAS(Oracle 兼容版) Oracle与PostgreSQL 兼容模式的参数配置切换

标签 PostgreSQL , EDB , PPAS , 参数 , Oracle模式 , PostgreSQL模式 背景 EDB PPAS是EDB推出的一款同时兼容Oracle和PostgreSQL协议的数据库,在去O的场景中,使用非常广泛,价格便宜,同时...

德哥
05/06
0
0
ETL for Oracle to PostgreSQL 1 - Oracle Data Integrator (ODI)

标签 PostgreSQL , Oracle , ETL , Oracle Data Integrator , ODI 背景 原文 https://www.cdata.com/kb/tech/postgresql-jdbc-odi.rst 正文 ETL PostgreSQL in Oracle Data Integrator This ......

德哥
05/06
0
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

Mybatis收集配置

一、Mybatis取Clob数据 1、Mapper.xml配置 <resultMap type="com.test.User" id="user"> <result column="id" property="id"/> <result column="json_data" property="jsonData" ......

星痕2018
28分钟前
0
0
centos7设置以多用户模式启动

1、旧版本linux系统修改inittab文件,在新版本执行vi /etc/inittab 会有以下提示 # inittab is no longer used when using systemd. # # ADDING CONFIGURATION HERE WILL HAVE NO EFFECT ON......

haha360
今天
0
0
OSChina 周日乱弹 —— 局长:怕你不爱我

Osc乱弹歌单(2018)请戳(这里) 【今日歌曲】 @ andonny :分享周二珂的单曲《孤独她呀》 《孤独她呀》- 周二珂 手机党少年们想听歌,请使劲儿戳(这里) @孤星闵月 :没事干,看一遍红楼梦...

小小编辑
今天
158
9
Java架构师知识体认识

源码分析 常用设计模式 Proxy代理模式 Factory工厂模式 Singleton单例模式 Delegate委派模式 Strategy策略模式 Prototype原型模式 Template模板模式 Spring5 beans 接口实例化 代理Bean操作 ...

小致dad
今天
0
0
SpringBoot | 第十章:Swagger2的集成和使用

前言 前一章节介绍了mybatisPlus的集成和简单使用,本章节开始接着上一章节的用户表,进行Swagger2的集成。现在都奉行前后端分离开发和微服务大行其道,分微服务及前后端分离后,前后端开发的...

oKong
今天
10
0
Python 最小二乘法 拟合 二次曲线

Python 二次拟合 随机生成数据,并且加上噪声干扰 构造需要拟合的函数形式,使用最小二乘法进行拟合 输出拟合后的参数 将拟合后的函数与原始数据绘图后进行对比 import numpy as npimport...

阿豪boy
今天
17
0
云拿 无人便利店

附近(上海市-航南路)开了家无人便利店.特意进去体验了一下.下面把自己看到的跟大家分享下. 经得现场工作人员同意后拍了几张照片.从外面看是这样.店门口的指导里强调:不要一次扫码多个人进入....

周翔
昨天
1
0
Java设计模式学习之工厂模式

在Java(或者叫做面向对象语言)的世界中,工厂模式被广泛应用于项目中,也许你并没有听说过,不过也许你已经在使用了。 简单来说,工厂模式的出现源于增加程序序的可扩展性,降低耦合度。之...

路小磊
昨天
248
1
npm profile 新功能介绍

转载地址 npm profile 新功能介绍 npm新版本新推来一个功能,npm profile,这个可以更改自己简介信息的命令,以后可以不用去登录网站来修改自己的简介了 具体的这个功能的支持大概是在6这个版...

durban
昨天
1
0
Serial2Ethernet Bi-redirection

Serial Tool Serial Tool is a utility for developing serial communications, custom protocols or device testing. You can set up bytes to send accordingly to your protocol and save......

zungyiu
昨天
1
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部