Shulou(Shulou.com)05/31 Report--

This article mainly shows you "how to transplant Oracle to PostgresQL", the content is easy to understand, clear, hope to help you solve doubts, the following let the editor lead you to study and learn "how to transplant Oracle to PostgresQL" this article.

Migration instance from Oracle to PostgresQL

1. Data migration

1.1. Database initialization

Take WINDOWS platform as an example

Standard version PostgresQL 8.0.3

LOCALE must choose C when installing, otherwise it will cause a lot of coding problems!

SQL_ASCII encoding is selected for initializing the database because UNICODE is not allowed to initialize the database directly during 8.0.3 installation

The administrative tool uses pgAdmin III because it automatically detects database encoding, defaults to UNICODE, and uses SQL_ASCII encoding only when the database uses SQL_ASCII

1.2. Build a database

1.2.1. UNICODE coding is used when establishing the production database, the tablespace is empty when the database is built (different from the concept of ORACLE tablespace), and the owner chooses Postgres.

1.2.2. After building the library, create schema. This usage is the same as ORACLE.

1.2.3. The permission management of PostgresQL is relatively strict. All objects created after the establishment of the library, if not specially declared, will be established under public schema, please note!

1.2.4. All object owners created in the library are the owners of the library (default is Postgres). If you need to change them, please handle them manually.

1.2.5. Views, tables and functions created under public schema are not allowed to be accessed by other users by default. If necessary, add the permission of public group in the authorization wizard.

1.3. Create a table

1.3.1, data type

Oracle data type | PostgresQL data type

VARCHAR2 | VARCHAR

CLOB | TEXT

DATE | DATE/TIME/TIMESTAMP (DATE contains date only, TIME contains time only, TIMESTAMP contains only, usually DATE is used)

NUMBER | SMALLINT/INTEGER/BIGINT/NUMERIC/REAL/DOUBLE PRECISION (usually available NUMERIC)

BLOB | BYTEA

The above table includes only the different parts of the common data types.

1.3.2. Create a table script

ORACLE script

CREATE TABLE SCHEMA.PREFIX_INFO

(

INFO_ID VARCHAR2 (25 BYTE)

INFO_TITLE VARCHAR2 (500 BYTE)

INFO_CONTENT CLOB

INFO_DATE DATE DEFAULT sysdate

INFO_STATUS VARCHAR2 (1 BYTE) DEFAULT'1'

LANG_ID NUMBER DEFAULT 1

INFO_CLICKNUM NUMBER DEFAULT 0

IS_POP VARCHAR2 (1 BYTE) DEFAULT'0'

INFO_VALIDDAYS NUMBER DEFAULT 1

)

LOGGING

NOCACHE

NOPARALLEL

ALTER TABLE SCHEMA.PREFIX_INFO ADD (

CONSTRAINT PK_PREFIX_INFO PRIMARY KEY (INFO_ID))

CREATE SEQUENCE PREFIX_INFO_SEQUENCE

INCREMENT BY 1

START WITH 582

MINVALUE 1

MAXVALUE 9999999999999999999999999999

NOCYCLE

CACHE 20

NOORDER

PostgresQL script

Create table schema.prefix_info

(

Info_id varchar (25)

Info_title varchar (500)

Info_content text

Info_date date default now ()

Info_status varchar (1) default'1'

Lang_id numeric default 1

Info_clicknum numeric default 0

Is_pop varchar2 (1) default'0'

Info_validdays numeric default 1

);

Field names in PostgresQL are case sensitive. To ensure compatibility, it is strongly recommended that characters in scripts be lowercase, so that case will be ignored in SQL statements.

-- only numbers can appear in parentheses of field types in PostgresQL

Sysdate in PostgresQL should be written as now (), which is used to fetch the current time of the system.

Alter table schema.prefix_info add constraint prefix_info_pkey primary

Key (info_id)

-- pay attention to the way you write when adding constraints, which is slightly different from ORACLE.

CREATE SEQUENCE schema.prefix_info_sequence

Increment 1

Minvalue 1

Maxvalue 9223372036854775807

Start 582

Cache 20

-- the default maximum of the system is different from ORACLE

1.3.3. Data migration

It is strongly recommended to use the SAVE AS tool in TOAD, not the export table tool (there is a problem with the time format). After building the table, it can be migrated seamlessly.

2. Program transplant

2.1. Environmental treatment

2.1.1 points for attention

The usage of | | in PostgresQL is different from that of other databases:

Select a | | b from table1

When one of an or b is null, the query returns null, remember!

2.2, compatibility settings

2.2.1. There is no concat function in PostgresQL, and it cannot be used because of | | usage problem | | replace. The solution is

Create the function concat in public schema

Create or replace function concat (text, text)

Returns text as

$body$select coalesce ($1 recording') | | coalesce ($2 recording') $body$

Language 'sql' volatile

Alter function concat (text, text) owner to postgres

Pay attention to the role of coalesce ()

-- can be used in other schema without special authorization

2.2.2. There is no dual virtual table in PostgresQL. To ensure program compatibility, you can create pseudo views (view) instead:

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 must be granted select permission

2.3. Program porting

2.3.1. Concat function: see 2.1.2.1. No modification is needed in the program.

2.3.2, external connection

Migration method:

ORACLE:

Simple external connection:

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'

Super abnormal external connection:

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:

Simple external connection:

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'

Super abnormal external connection:

Select count (distinct (a.col1)) as rcount from

Schema.prefix_table1 an 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'

2.3.3. Subquery:

PostgresQL subquery is more standardized, and the subquery result set must have alias.

Migration method:

ORACLE:

SELECT * FROM (

SELECT * FROM (

SELECT * FROM SCHEMA.PREFIX_TABLE ORDER BY COL1

) WHERE Xero1 ORDER BY COL2

) WHERE Yellow2 ORDER BY COL3

POSTGRESQL:

SELECT * FROM (

SELECT * FROM (

SELECT * FROM SCHEMA.PREFIX_TABLE ORDER BY COL1 ALIAS1

) WHERE Xero1 ORDER BY COL2 ALIAS2

) WHERE Yellow2 ORDER BY COL3

2.3.4, data paging

There is no rownum in PostgresQL, so you cannot use the method of where rownum < = X for paging. Instead, it is replaced by the limit X _ offset Y method.

The LIMIT X method is not allowed in ORACLE.

Migration method:

* this migration cannot make the two databases compatible.

ORACLE:

SELECT * FROM (SELECT * FROM (SELECT * FROM SCHEMA.PREFIX_TABLE1 ORDER BY COL1 DESC,COL2 ASC) where ROWNUM

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.