Migrating tables from Oracle to PostgreSQL requires you to know corresponding data type of Oracle table column type.
OraclePostgreSQL
We have considered “EMP”, “DEPT” and “SALGRADE” Oracle SCOTT user tables.
create table dept(
deptno number(2,0),
dname varchar2(14),
loc varchar2(13),
constraint dept_pk primary key (deptno)
);
create table emp(
empno number(4,0),
ename varchar2(10),
job varchar2(9),
mgr number(4,0),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2,0),
constraint emp_pk primary key (empno),
constraint deptno_fk foreign key (deptno) references dept (deptno)
);
create table salgrade(
grade number,
losal number,
hisal number
);
/* Insert into dept table */
insert into dept
values(10, 'ACCOUNTING', 'NEW YORK');
insert into dept
values(20, 'RESEARCH', 'DALLAS');
insert into dept
values(30, 'SALES', 'CHICAGO');
insert into dept
values(40, 'OPERATIONS', 'BOSTON');
/* Insert into emp table */
insert into emp
values(
7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981','dd-mm-yyyy'), 5000, null, 10
);
insert into emp
values(
7698, 'BLAKE', 'MANAGER', 7839, to_date('1-5-1981','dd-mm-yyyy'), 2850, null, 30
);
insert into emp
values(
7782, 'CLARK', 'MANAGER', 7839, to_date('9-6-1981','dd-mm-yyyy'), 2450, null, 10
);
insert into emp
values(
7566, 'JONES', 'MANAGER', 7839, to_date('2-4-1981','dd-mm-yyyy'), 2975, null, 20
);
insert into emp
values(
7788, 'SCOTT', 'ANALYST', 7566, to_date('13-JUL-87','dd-mm-rr') - 85, 3000, null, 20
);
insert into emp
values(
7902, 'FORD', 'ANALYST', 7566, to_date('3-12-1981','dd-mm-yyyy'), 3000, null, 20
);
insert into emp
values(
7369, 'SMITH', 'CLERK', 7902, to_date('17-12-1980','dd-mm-yyyy'), 800, null, 20
);
insert into emp
values(
7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-2-1981','dd-mm-yyyy'), 1600, 300, 30
);
insert into emp
values(
7521, 'WARD', 'SALESMAN', 7698, to_date('22-2-1981','dd-mm-yyyy'), 1250, 500, 30
);
insert into emp
values(
7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-9-1981','dd-mm-yyyy'), 1250, 1400, 30
);
insert into emp
values(
7844, 'TURNER', 'SALESMAN', 7698, to_date('8-9-1981','dd-mm-yyyy'), 1500, 0, 30
);
insert into emp
values(
7876, 'ADAMS', 'CLERK', 7788, to_date('13-JUL-87', 'dd-mm-rr') - 51, 1100, null, 20
);
insert into emp
values(
7900, 'JAMES', 'CLERK', 7698, to_date('3-12-1981','dd-mm-yyyy'), 950, null, 30
);
insert into emp
values(
7934, 'MILLER', 'CLERK', 7782, to_date('23-1-1982','dd-mm-yyyy'), 1300, null, 10
);
/* Insert into salgrade table */
insert into salgrade
values (1, 700, 1200);
insert into salgrade
values (2, 1201, 1400);
insert into salgrade
values (3, 1401, 2000);
insert into salgrade
values (4, 2001, 3000);
insert into salgrade
values (5, 3001, 9999);
commit;
Converting Oracle “EMP”, “DEPT” and “SALGRADE” tables to PostgreSQL
create table dept(
deptno integer,
dname character varying(14),
loc character varying(13),
constraint dept_pk primary key (deptno)
);
create table emp(
empno integer,
ename character varying(10),
job character varying(9),
mgr integer,
hiredate date,
sal numeric(7,2),
comm numeric(7,2),
deptno integer,
constraint emp_pk primary key (empno),
constraint deptno_fk foreign key (deptno) references dept (deptno)
);
create table salgrade(
grade integer,
losal numeric(7,2),
hisal numeric(7,2)
);
/* Insert into dept table */
insert into dept
values(10, 'ACCOUNTING', 'NEW YORK');
insert into dept
values(20, 'RESEARCH', 'DALLAS');
insert into dept
values(30, 'SALES', 'CHICAGO');
insert into dept
values(40, 'OPERATIONS', 'BOSTON');
/* Insert into emp table */
insert into emp
values(
7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981','dd-mm-yyyy'), 5000, null, 10
);
insert into emp
values(
7698, 'BLAKE', 'MANAGER', 7839, to_date('1-5-1981','dd-mm-yyyy'), 2850, null, 30
);
insert into emp
values(
7782, 'CLARK', 'MANAGER', 7839, to_date('9-6-1981','dd-mm-yyyy'), 2450, null, 10
);
insert into emp
values(
7566, 'JONES', 'MANAGER', 7839, to_date('2-4-1981','dd-mm-yyyy'), 2975, null, 20
);
insert into emp
values(
7788, 'SCOTT', 'ANALYST', 7566, to_date('13-07-87','dd-mm-rr') - 85, 3000, null, 20
);
insert into emp
values(
7902, 'FORD', 'ANALYST', 7566, to_date('3-12-1981','dd-mm-yyyy'), 3000, null, 20
);
insert into emp
values(
7369, 'SMITH', 'CLERK', 7902, to_date('17-12-1980','dd-mm-yyyy'), 800, null, 20
);
insert into emp
values(
7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-2-1981','dd-mm-yyyy'), 1600, 300, 30
);
insert into emp
values(
7521, 'WARD', 'SALESMAN', 7698, to_date('22-2-1981','dd-mm-yyyy'), 1250, 500, 30
);
insert into emp
values(
7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-9-1981','dd-mm-yyyy'), 1250, 1400, 30
);
insert into emp
values(
7844, 'TURNER', 'SALESMAN', 7698, to_date('8-9-1981','dd-mm-yyyy'), 1500, 0, 30
);
insert into emp
values(
7876, 'ADAMS', 'CLERK', 7788, to_date('13-07-87', 'dd-mm-rr') - 51, 1100, null, 20
);
insert into emp
values(
7900, 'JAMES', 'CLERK', 7698, to_date('3-12-1981','dd-mm-yyyy'), 950, null, 30
);
insert into emp
values(
7934, 'MILLER', 'CLERK', 7782, to_date('23-1-1982','dd-mm-yyyy'), 1300, null, 10
);
/* Insert into salgrade table */
insert into salgrade
values (1, 700, 1200);
insert into salgrade
values (2, 1201, 1400);
insert into salgrade
values (3, 1401, 2000);
insert into salgrade
values (4, 2001, 3000);
insert into salgrade
values (5, 3001, 9999);
commit;