pg与oracle区别,PostgresQL 学习记录之与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 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