Oracle database comes with some sample schemas, some can be setup automatically and some has to be installed manually. You can get the detail information on Oracle sample schemas here.
For the sake of explaining the migration of Oracle database features to PostgreSQL, we are considering following sample schemas.
- HR
- OE
- SCOTT
You can download the executable scripts here or the sample schema scripts are available on GitHub at oracle-github.
Once the scripts are downloaded, Unzip the file and follow the steps to install the sample schemas.
- CREATE_SCOTT.sql
- CREATE_HR.sql
- CREATE_OE.sql
- SCOTT_30.sql
- HR_30.sql
- OE.sql
You can copy the content of each file and execute or can run the file as a script through SQL* PLUS client. The CREATE files must be run first in any order.
Understand HR and OE schema relations
Showing below the ER diagram of all relations within HR and OE schemas.
Migrating Oracle SCOTT user tables
Let’s start migrating SCOTT user tables to PostgreSQL and understand the mapping of tables column data types.
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 “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);