oracle向PostgreSQL迁移

1. postgreSQL 没有dual
    例子:
    oracle    : select sysdate from dual;
    postgreSQL: select current_timestamp;

    为保证程序兼容性,可创建伪视图(view )替代:

    CREATE OR REPLACE VIEW dual AS
    SELECT NULL::"unknown"
    WHERE 1 = 1;
    ALTER TABLE dual OWNER TO postgres;
    GRANT ALL ON TABLE dual TO postgres;
    GRANT SELECT ON TABLE dual TO public;
    必须授权public 以select 权限

2. sequence
创建和oracle一样,不需要修改创建语句。
例子:create sequence seq_test_1 INCREMENT by 1 MINVALUE 1   NO MAXVALUE start with 1 ; --NO MAXVALUE在oracle不支持
使用有区别:
  1)查看序列 next 值
      select nextval('seq_test_1');
  2)查看序列最近使用值
      select currval('seq_test_1');
  3)序列重置
      方式一
      select setval('seq_test_1',100);

      select currval('seq_test_1');
        currval
       ---------
            100
       (1 row)
       select nextval('seq_test_1');
        nextval
       ---------
            101
       (1 row)
      方式二
      alter sequence seq_test_1 restart with 200;  --注意这个不会修改 currval 的值
      select nextval('seq_test_1');
       nextval
      ---------
           200
       (1 row)
   另:postgreSQL支持使用以下语句查看序列属性
       select * from seq_test_1;

3. postgreSQL 中的||用法与其他数据库不同:
  select a||b from table1;
  当a或b其中一个为null时,该查询返回null。
    如果不希望要这个结果,可以使用COALESCE函数:
    select COALESCE(a,'')||COALESCE(b,'') from table1;

4. postgreSQL 一个类型转换声明一个从一种数据类型到另外一种数据类型的转换。

    CAST ( expression AS type )
    expression::type
    CAST语法遵循 SQL 标准;::语法是PostgreSQL历史用法。

5. COALESCE (expression_1, expression_2, ...,expression_n)
   依次参考各参数表达式,遇到非null值即停止并返回该值。如果所有的表达式都是空值,最终将返回一个空值。
   可以替换oracle中的nvl函数。

6. oracle中的MINUS 用 EXCEPT来替代

7. postgreSQL 在FROM子条件中字段须有列名,解决方法用AS 别名

8. 关于''
    oracle                                                postgreSQL
SELECT LENGTH('') AS VALUE1 FROM DUAL                    SELECT LENGTH('') AS VALUE1
[Result]VALUE1=NULL                                      [Result]VALUE1=0

SELECT TO_DATE('','YYYYMMDD') AS VALUE2                  SELECT TO_DATE('','YYYYMMDD') AS VALUE2
FROM DUAL
[Result]VALUE2=NULL                                      [Result]VALUE2=0001-01-01 BC

SELECT TO_NUMBER('',1) AS VALUE3 FROM DUAL               SELECT TO_NUMBER('',1) AS VALUE3
[Result]VALUE3=NULL                                      [Result]不能执行

INSERT INTO TEST(VALUE4)VALUES('')                       INSERT INTO TEST(VALUE4)VALUES('')
[Result]VALUE4=NULL (注:VALUE4字段为数值类型)           [Result]VALUE4=0 (注:VALUE4字段为数值类型)

INSERT INTO TEST(VALUE5)VALUES('')                       INSERT INTO TEST(VALUE5)VALUES('')
[Result]VALUE5=NULL (注:VALUE5字段为字符类型)           [Result]VALUE5=''  (注:VALUE5字段为字符类型,结果为长度为零的字符串)

INSERT INTO TEST(VALUE6)VALUES(TO_DATE('','YYYYMMDD'))   INSERT INTO TEST(VALUE6)VALUES(TO_DATE('','YYYYMMDD'))
[Result]VALUE6=NULL (注:VALUE6字段为时间类型)           [Result]VALUE6=0001-01-01 BC (注:VALUE7字段为时间类型)

9. 常用字符串函数
函数                                                        返回类型    描述                                                                                示例                                结果
substring(string [from int] [for int])                         text    截取子字符串                                                                             substring('Thomas' from 2 for 3)    hom
substr(string, from [, count])                                 text    抽取子字符串。和substring(string from from for count))一样                                 substr('alphabet', 3, 2)            ph
trim([leading | trailing | both] [characters] from string)     text    从字符串string的开头/结尾/两边删除只包含 characters中字符 (缺省是空白)的最长的字符串     trim(both 'x' from 'xTomxx')        Tom

10. 聚集函数
avg、count、max、min、sum用法一样。
to_number to_char具体转换模式可能会有区别。

11. 关联查询

ORACLE:

简单外连接:
SELECT COUNT(DISTINCT(A.COL1)) AS RCOUNT FROM
SCHEMA.PREFIX_TABLE1 A,SCHEMA.PREFIX_TABLE2 B
WHERE 1 = 1
AND A.COL2 = B.COL2(+)
AND A.COL3 > 0
AND A.COL4 = '1'

复杂外连接:
SELECT COUNT(DISTINCT(A.COL1)) AS RCOUNT FROM
SCHEMA.PREFIX_TABLE1 A,SCHEMA.PREFIX_TABLE2 B,SCHEMA.PREFIX_TABLE3 C,SCHEMA.PREFIX_TABLE4 D
WHERE 1 = 1
AND A.COL2 = B.COL2
AND A.COL3 = C.COL3(+)
AND A.COL4 = D.COL4(+)
AND A.COL5 > 0
AND A.COL6 = '1'


postgreSQL:

简单外连接:
select count(distinct(a.col1)) as rcount from
schema.prefix_table1 a left outer join schema.prefix_table2 b on (a.col2 = b.col2)
where 1 = 1
and a.col3 > 0
and a.col4 = '1'

复杂外连接:
select count(distinct(a.col1)) as rcount from
schema.prefix_table1 a inner join schema.prefix_table2 b on (a.col2 = b.col2)
left outer join schema.prefix_table3 c on (a.col3 = c.col3)
left outer join schema.prefix_table4 d on (a.col4 = d.col4)
where 1 = 1
and a.col5 > 0
and a.col6 = '1'

12. PostgresQL 中子查询较为规范,子查询结果集必须拥有alias

ORACLE:
SELECT * FROM (
SELECT * FROM (
SELECT * FROM SCHEMA.PREFIX_TABLE ORDER BY COL1
) WHERE X=1 ORDER BY COL2
) WHERE Y=2 ORDER BY COL3

postgreSQL:

SELECT * FROM (
SELECT * FROM (
SELECT * FROM SCHEMA.PREFIX_TABLE ORDER BY COL1 ALIAS1
) WHERE X=1 ORDER BY COL2 ALIAS2
) WHERE Y=2 ORDER BY COL3

13. rownum
PostgresQL 中没有rownum ,无法 使用where rownum < = X 的方法进行分页,取而代之的是limit X ,offset Y 方法, 而ORACLE 中不允许使用LIMIT X 的 方法
ORACLE:

SELECT * FROM ( SELECT * FROM (SELECT * FROM SCHEMA.PREFIX_TABLE1 ORDER BY COL1 DESC,COL2 ASC) where ROWNUM <= 50 ORDER BY COL3 ASC,COL4 DESC)
WHERE ROWNUM <= 20 ORDER BY COL5 DESC,COL6 ASC;

postgreSQL:

select * from ( select * from (SELECT * FROM SCHEMA.PREFIX_TABLE1 ORDER BY COL1 DESC,COL2 ASC) selb order by col3 asc,col4 desc limit 50 ) sela
order by col5 desc,col6 asc limit 20;

-- 注意!!limit 必须用于order by 之后

-- 例:取1 到50 条数据

select * from VOIP_FEE_RATE temp offset 0 limit 50

附加上 LIMIT和OFFSET 子句之后,你就可以检索原来查询语句查询出来的结果中的一部分数据行:

SELECT select_list
    FROM table_expression
    [LIMIT { number | ALL }] [OFFSET number]

如果给出了一个限制计数,那么返回不超过那么多的行。 (但可能更少些,因为查询本身可能生成的行数就比较少。) LIMIT ALL和省略 LIMIT子句一样。

OFFSET说明在开始返回行之前忽略多少行。 OFFSET 0和省略OFFSET子句是一样的。 如果OFFSET和LIMIT都出现了, 那么在计算LIMIT个行之前忽略OFFSET行。

如果使用LIMIT,那么用ORDER BY 子句把结果行约束成一个唯一的顺序是一个好主意。 否则你就会拿到一个不可预料的该查询的行的子集。 — 你要的可能是第十到二十行,但以什么顺序的十到二十? 除非你声明了ORDER BY,否则顺序是不知道的。

查询优化器在生成查询规划的时候会考虑LIMIT,因此如果你给 LIMIT和OFFSET不同的东西,那么你很可能收到不同的规划(产生不同的行顺序)。 因此,使用不同的LIMIT/OFFSET值选择不同的查询结果的子集将生成不一致的结果, 除非你用ORDER BY强制一个可预料的顺序。这可不是臭虫; 这是一个很自然的结果,因为 SQL 没有许诺把查询的结果按照任何特定的顺序发出,除非用了ORDER BY来约束顺序。

OFFSET 子句忽略的行仍然需要在服务器内部计算;因此,一个很大的 OFFSET 可能还是不够有效率的。

14. postgreSQL 没有DECODE函数
Oracle:
    SELECT DECODE(ENDFLAG,'1','A','B') ENDFLAGFROM TEST
postgreSQL:
    SELECT (CASE ENDFLAG WHEN '1' THEN 'A'ELSE 'B' END) AS ENDFLAG FROM TEST

15. 当前日期/时间
PostgreSQL提供许多返回当前日期和时间的函数。 这些符合 SQL 标准的函数全部都按照当前事务的开始时刻返回结果:

CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_TIME(precision)
CURRENT_TIMESTAMP(precision)
LOCALTIME
LOCALTIMESTAMP
LOCALTIME(precision)
LOCALTIMESTAMP(precision)

CURRENT_TIME和CURRENT_TIMESTAMP 返回带有时区的值;LOCALTIME和LOCALTIMESTAMP 返回不带时区的值。

CURRENT_TIME,CURRENT_TIMESTAMP, LOCALTIME,LOCALTIMESTAMP 可以有选择地获取一个精度参数,
该精度导致结果的秒数域园整到指定小数位。 如果没有精度参数,将给予所能得到的全部精度。

一些例子:

SELECT CURRENT_TIME;
Result: 14:39:53.662522-05

SELECT CURRENT_DATE;
Result: 2001-12-23

SELECT CURRENT_TIMESTAMP;
Result: 2001-12-23 14:39:53.662522-05

SELECT CURRENT_TIMESTAMP(2);
Result: 2001-12-23 14:39:53.66-05

SELECT LOCALTIMESTAMP;
Result: 2001-12-23 14:39:53.662522

16. 常见数据类型中两者不同的部分

Oracle数据类型    PostgresQL数据类型
VARCHAR2    VARCHAR
CLOB    TEXT
DATE    DATE/TIME/TIMESTAMP(DATE仅包含日期、TIME仅包含时间、TIMESTAMP均包含,通常使用DATE)
NUMBER    SMALLINT/INTEGER/BIGINT/NUMERIC/REAL/DOUBLE PRECISION(通常可用NUMERIC)
BLOB    BYTEA

17. sql语句执行结果,sqlcode判断PostgresQL和Oracle不完全一样

18. PostgresQL
当创建游标的事务用COMMIT或ROLLBACK终止之后,每个不可保持的已打开游标都隐含关闭。
当创建游标的事务通过ROLLBACK退出之后,每个可以保持的游标都将隐含关闭。
当创建游标的事务成功提交,那么可保持的游标保持打开,直到执行一个明确的CLOSE命令或者客户端断开。

展开阅读全文

没有更多推荐了,返回首页