搜索泥称 发表于 2018-9-23 07:20:03

PostgreSQL和Oracle用法上的一些区别

  (1 ) 注意增加约束时的写法,和ORACLE 略有不同
Oracle :
ALTER TABLE SCHEMA.PREFIX_INFO ADD (
  CONSTRAINT PK_PREFIX_INFO PRIMARY KEY (INFO_ID));
PostgresQL :
alter table schema.prefix_info add constraint prefix_info_pkey primary key(info_id);
(2 )系统默认的最大值与ORACLE 不同
Oracle :
CREATE SEQUENCE PREFIX_INFO_SEQUENCE
  INCREMENT BY 1
  START WITH 582
  MINVALUE 1
  MAXVALUE 9999999999999999999999999999
  NOCYCLE
  CACHE 20
  NOORDER;
PostgresQL :
CREATE SEQUENCE schema.prefix_info_sequence
  increment 1
  minvalue 1
  maxvalue 9223372036854775807
  start 582
  cache 20;
(3 )PostgresQL 中的 || 用 法与其他数据库不同:
  select a|| b from table1;
  当a 或b 其中一个为null 时, 该查询返回null ,
(4 )PostgresQL 中没有concat 函数,且由于|| 用法的问题,无法使用|| 替换,解决 方法为在public schema 中创建函数concat
create or replace function concat(text, text)
  returns text as
  $body$select coalesce($1,'') || coalesce($2,'')$body$
  language 'sql' volatile;
  alter function concat(text, text) owner to postgres;
-- 无需特殊授权即可在其他schema 中使用
(4 )PostgresQL 中没有dual 虚拟表,为保 证程序兼容性,可创建伪视图(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 权 限
(5 )关联查询用法区别
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'
(6 )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
(7 ) 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
页: [1]
查看完整版本: PostgreSQL和Oracle用法上的一些区别