PG数据库result refcursor pg数据库和oracle数据库区别

与oracle的区别

1、数据类型区别

Oracle	PostgresQL	注释
VARCHAR2	VARCHAR （character varying）
Long、 CLOB	TEXT
DATE	DATE/TIME/TIMESTAMP	DATE仅包含日期、TIME仅包含时间、TIMESTAMP均包含,通常使用DATE
NUMBER	SMALLINT/INTEGER/BIGINT/NUMERIC/REAL/DOUBLE PRECISION	通常可用NUMERIC
BLOB	BYTEA
sysdate	now()、O_TIMESTAMP(CURRENT_TIMESTAMP,'YYYY-MM-DD HH24:MI:SS')、CURRENT_TIMESTAMP	取系统当前时间

--PostgresQL中字段名称区分大小写，为保证兼容性，强烈建议脚本中的字符均用小写，这样在SQL语句中将忽略大小写
--PostgresQL中字段类型的括号中只能出现数字

2、语句与方法的区别

Function	Returns	Description	Example
to_char(timestamp, text)	text	convert time stamp to string	to_char(timestamp 'now','HH12:MI:SS')
to_char(interval, text)	text	convert interval to string	to_char(interval '15h 2m 12s','HH24:MI:SS')
to_char(int, text)	text	convert integer to string	to_char(125, '999')
to_char(double precision, text)	text	convert real/double precision to string	to_char(125.8, '999D9')
to_char(numeric, text)	text	convert numeric to string	to_char(numeric '-125.8', '999D99S')
to_date(text, text)	date	convert string to date	to_date('05 Dec 2000', 'DD Mon YYYY')
to_timestamp(text, text)	timestamp	convert string to time stamp	to_timestamp('05 Dec 2000', 'DD Mon YYYY')
to_number(text, text)	numeric	convert string to numeric	to_number('12,454.8-', '99G999D9S')

 

（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 COL3POSTGRESQL:
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 <= 50 ORDER BY COL3 ASC,COL4 DESC)
WHERE ROWNUM <= 20 ORDER BY COL5 DESC,COL6 ASC;POSTGRES:
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  
（8）序列使用的区别 
ORACLE:
 SELECT SCHEMA.PREFIX_TABLE1_SEQUENCE.NEXTVAL AS nCode FROM DUAL 
POSTGRES:
SELECT NEXTVAL('SCHEMA.PREFIX_TABLE1_SEQUENCE') AS nCode FROM DUAL 
--注意，此方法前提是dual视图已建立，如没有，可省略FROM DUAL