与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 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 <= 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