Oracle迁移到PostgreSQL的一些经验

来源：互联网发布：java web开发书籍推荐编辑：程序博客网时间：2024/05/16 19:49

原文地址：

http://blog.sina.com.cn/s/blog_8742444201012y03.html

http://blog.sina.com.cn/s/blog_8742444201012y0h.html

Oracle迁移到PostgreSQL的一些经验(一）

(2012-04-09 16:36:34)

最近遇到数据库由Oracle迁移到PostgreSQL的一些问题，结合网上搜索到信息，将我的解决办法记录如下：

1 安装及配置

1.1 安装

在windowXP下安装 PostgreSQL 9.1:

Ø Installation Directory àC:\Program Files\PostgreSQL\9.1

Ø Data Directory à D:\PostgreSQL\9.1\data

Ø Password à postgres

Ø Port à5432

Ø Locale à [Default locale]

1.2 配置

1.1.1远程连接:

修改文件：D:\PostgreSQL\9.1\data\pg_hba.conf, 增加:

**********************************

# IPv4 local connections:

host all all 127.0.0.1/32 md5

host all all 192.168.0.0/24 md5

**********************************

使得ip在 192.168.0.0 到 192.168.0.255 之间的客户端可以通过密码访问数据库.

修改文件：D:\PostgreSQL\9.1\data\postgresql.conf，确保:

**********************************

listen_address = '*'

**********************************

1.1.2 表空间访问权限

创建表空间文件夹: E:\PostgreSQL\mytablespace，修改文件夹权限，使postgres用户有读写权限。

2 创建数据库脚本

2.1 创建用户及表空间脚本

CreateTableSpace.sql

**********************************

CREATE ROLE myuser LOGIN

PASSWORD 'mypassword'

SUPERUSER INHERIT NOCREATEDB NOCREATEROLE REPLICATION;

CREATE TABLESPACE mytablespace

OWNER myuser

LOCATION 'E:/PostgreSQL/mytablespace';

**********************************

2.2 创建数据库脚本

CreateTableSpace.sql

**********************************

CREATE DATABASE mydb

WITH OWNER = myuser

ENCODING = 'UTF8'

TABLESPACE = mytablespace

LC_COLLATE = 'Chinese_People''s Republic of China.936'

LC_CTYPE = 'Chinese_People''s Republic of China.936'

CONNECTION LIMIT = -1;

**********************************

2.3 建表脚本

**********************************

CREATE TABLE mytable

(

id integer unique not null,

value varchar(50)

);

ALTER TABLE mytable OWNER TO myuser;

**********************************

2.4 导入缺省数据

Insert.sql

**********************************

insert into mytable (id, value) values (1, 'V1');

insert into mytable (id, value) values (2, 'V2');

**********************************

2.5 批处理文件

**********************************

set PGUSER=postgres

set PGPASSWORD=postgres

c:\Progra~1\PostgreSQL\9.1\bin\psql.exe -f CreateTableSpace.sql

c:\Progra~1\PostgreSQL\9.1\bin\psql.exe -d mydb -f CreateTable.sql

c:\Progra~1\PostgreSQL\9.1\bin\psql.exe -d mydb -f Insert.sql

**********************************

Oracle迁移到PostgreSQL的一些经验(二）

(2012-04-09 16:43:29)

3 sql语句与Oracle的区别

3.1 数据类型

Oracle

PostgreSQL

VARCHAR2

VARCHAR （character varying）

Long、 CLOB

TEXT

DATE

DATE/TIME/TIMESTAMP

NUMBER

SMALLINT/INTEGER/BIGINT/NUMERIC/REAL/DOUBLE PRECISION

BLOB

SMALLINT/INTEGER/BIGINT/NUMERIC/REAL/DOUBLE PRECISION

sysdate

now()、O_TIMESTAMP(CURRENT_TIMESTAMP,'YYYY-MM-DD HH24:MI:SS')、CURRENT_TIMESTAMP

Tips: PostgreSQL 中字段名区分大小写，为保证兼容行，强烈建议脚本中的字符均用小写，这样在Sql语句中将忽略大小写。

3.2 方法

Function

Returns

Example

to_char(timestamp, text)

text

to_char(timestamp 'now','HH12:MI:SS')

to_char(interval, text)

text

to_char(interval '15h 2m 12s','HH24:MI:SS')

to_char(int, text)

text

to_char(125, '999')

to_char(double precision, text)

text

to_char(125.8, '999D9')

to_char(numeric, text)

text

to_char(numeric '-125.8', '999D99S')

to_date(text, text)

date

to_date('05 Dec 2000', 'DD Mon YYYY')

to_timestamp(text, text)

timestamp

to_timestamp('05 Dec 2000', 'DD Mon YYYY')

to_number(text, text)

numeric

to_number('12,454.8-', '99G999D9S')

3.3 sql

Oracle

PostgreSQL

Constraint

alter table schema.prefix_info add (
constraint pk_prefix_info primary key (info_id));

alter table schema.prefix_info add constraint prefix_info_pkey primary key(info_id);

Default Maximun in sequence

create sequence prefix_info_sequence
increment by 1
start with 582
minvalue 1
maxvalue 9999999999999999999999999999
nocycle
cache 20
noorder;

create sequence schema.prefix_info_sequence
increment 1
minvalue 1
maxvalue 9223372036854775807
start 582
cache 20;

select a||b from table1;

returns null when there is one null value in a and b.

Dual table

select sysdate from dual;

select now();

Associated query

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 left outer join schema.prefix_table2 b on (a.col2 = b.col2)
where 1 = 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'

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'

Subquery

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

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

Rownum

limit

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;

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 must be used after order by

sequence

select schema.prefix_table1_sequence.nextval as ncode from dual

select nextval('schema.prefix_table1_sequence') as ncode

select a.col1 a_col1，a.col2 a_col2 from a_table a

select a.col1 as a_col1，a.col2 as a_col2 from a_table a

NVL

select nvl(sum(value11),0) fs_value1, nvl(sum(value21),0) fs_value2 from field_sum

select coalesce(sum(value11),0) as fs_value1,coalesce(sum(value21),0) as fs_value2
from field_sum

Decode

select decode(endflag,'1','a','b') endflagfrom test

select (caseendflag when '1' then 'a'else 'b' end) as endflag from test

参考：

http://ken-81710.iteye.com/blog/346639