1. Home
  2. Docs
  3. Oracle to PostgreSQL Migration
  4. 2 Migrate Tables
  5. 2.1 Migrating Oracle SCOTT user tables

2.1 Migrating Oracle SCOTT user tables

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;
Was this article helpful to you? Yes No

How can we help?