Convert Oracle Database Tables to PostgreSQL
RazorSQL includes a database conversion tool that can convert one or many Oracle tables to PostgreSQL tables.
The conversion tool can be
accessed via the following menu option in RazorSQL:
DB Tools -> Database Conversion
Inside the Database Conversion menu there are options for converting one table or to
convert a selection of tables within
a specific schema or database.
Selecting the Single Table option brings up the following screen:
Information needed on the screen includes the existing Oracle table to convert, the name of the new PostgreSQL table to create, and whether to execute the conversion directly on a PostgreSQL database connection or to generate an SQL script file with the DDL and SQL insert statements for the conversion.
After clicking the convert button, RazorSQL will generate a PostgreSQL specific create table statement for the table. It will also generate an SQL insert statement for each row in the Oracle table that can be executed against the new PostgreSQL table. Listed below is an example SQL script that would be generated by the RazorSQL Oracle to PostgreSQL conversion tool.
CREATE TABLE DEPARTMENT (
DNUMBER numeric NOT NULL,
DNAME varchar(25) NOT NULL,
PRIMARY KEY (DNUMBER)
);
INSERT INTO DEPARTMENT(DNUMBER, DNAME) VALUES (5, 'Research');
INSERT INTO DEPARTMENT(DNUMBER, DNAME) VALUES (4, 'Administration');
INSERT INTO DEPARTMENT(DNUMBER, DNAME) VALUES (1, 'Headquarters');
When electing to convert multiple tables, in addition to the create table statement and SQL insert statements, RazorSQL can generate alter table statements to set up foreign key relationships. Listed below is an example Oracle to PostgreSQL conversion script that includes the alter table statement to create the foreign key on the Employee table that references the Department table.
CREATE TABLE DEPARTMENT (
DNUMBER numeric NOT NULL,
DNAME varchar(25) NOT NULL,
PRIMARY KEY (DNUMBER)
);
CREATE TABLE EMPLOYEE (
SSN varchar(25) NOT NULL,
FNAME varchar(25),
MINIT char(1),
LNAME varchar(50),
ADDRESS varchar(50),
SEX char(1) NOT NULL,
SALARY numeric,
SUPERSSN varchar(50),
DNO numeric,
PRIMARY KEY (SSN)
);
INSERT INTO DEPARTMENT(DNUMBER, DNAME) VALUES (5, 'Research');
INSERT INTO DEPARTMENT(DNUMBER, DNAME) VALUES (4, 'Administration');
INSERT INTO DEPARTMENT(DNUMBER, DNAME) VALUES (1, 'Headquarters');
INSERT INTO DEPARTMENT(DNUMBER, DNAME) VALUES (7, 'test2');
INSERT INTO DEPARTMENT(DNUMBER, DNAME) VALUES (8, 'test3');
INSERT INTO EMPLOYEE(SSN, FNAME, MINIT, LNAME, ADDRESS, SEX, SALARY, SUPERSSN, DNO) VALUES ('123456789', 'John', 'B', 'Smith', '731 Fondren, Houston TX', 'M', 30000, '333445555', 5);
INSERT INTO EMPLOYEE(SSN, FNAME, MINIT, LNAME, ADDRESS, SEX, SALARY, SUPERSSN, DNO) VALUES ('333445555', 'Franklin', 'T', 'Wong', '638 Voss, Houston TX', 'M', 40000, '888665555', 5);
INSERT INTO EMPLOYEE(SSN, FNAME, MINIT, LNAME, ADDRESS, SEX, SALARY, SUPERSSN, DNO) VALUES ('999887777', 'Alicia', 'J', 'Zelaya', '3321 Castle, Spring TX', 'F', 25000, '987654321', 4);
INSERT INTO EMPLOYEE(SSN, FNAME, MINIT, LNAME, ADDRESS, SEX, SALARY, SUPERSSN, DNO) VALUES ('987654321', 'Jennifer', 'S', 'Wallace', '291 Berry, Bellaire, TX', 'F', 43000, '888666555', 4);
INSERT INTO EMPLOYEE(SSN, FNAME, MINIT, LNAME, ADDRESS, SEX, SALARY, SUPERSSN, DNO) VALUES ('666884444', 'Ramesh', 'K', 'Narayan', '975 Fire Oak, Humble TX', 'M', 38000, '333445555', 5);
INSERT INTO EMPLOYEE(SSN, FNAME, MINIT, LNAME, ADDRESS, SEX, SALARY, SUPERSSN, DNO) VALUES ('453453453', 'Joyce', 'A', 'English', '5631 Rice, Houston TX', 'F', 25000, '333445555', 5);
INSERT INTO EMPLOYEE(SSN, FNAME, MINIT, LNAME, ADDRESS, SEX, SALARY, SUPERSSN, DNO) VALUES ('987987987', 'Ahmad', 'V', 'Jabbar', '980 Dallas, Houston TX', 'M', 25000, '987654321', 1);
ALTER TABLE EMPLOYEE
ADD FOREIGN KEY (DNO)
REFERENCES DEPARTMENT (DNUMBER);
