-- Oracle
CREATE TABLE EMP (
EMPNO NUMBER(4) NOT NULL Primary key,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4) ,
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) ,
);
-- PostgreSQL
CREATE TABLE emp (
empno decimal(4) NOT NULL PRIMARY KEY,
ename varchar(10),
job varchar(9),
mgr int ,
hiredate date,
sal decimal(7,2),
comm decimal(7,2),
deptno decimal(2) );
create index emp_deptno on emp(deptno) ;
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,'1981-05-09',2450,NULL,10);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,'1981-04-01',2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,'1981-09-10',1250,1400,30);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,'1981-02-11',1600,300,30);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,'1981-08-21',1500,0,30);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,'1981-12-11',950,NULL,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,'1981-02-23',1250,500,30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,'1981-12-11',3000,NULL,20);
INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,'1980-12-09',800,NULL,20);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,'1982-12-22',3000,NULL,20);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,'1983-01-15',1100,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,'1982-01-11',1300,NULL,10);
\d+ emp
Table "public.emp"
+----------+-----------------------+-----------+----------+---------+----------+-------------+--------------+-------------+
| Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description |
+----------+-----------------------+-----------+----------+---------+----------+-------------+--------------+-------------+
| empno | numeric(4,0) | | not null | | main | | | |
| ename | character varying(10) | | | | extended | | | |
| job | character varying(9) | | | | extended | | | |
| mgr | integer | | | | plain | | | |
| hiredate | date | | | | plain | | | |
| sal | numeric(7,2) | | | | main | | | |
| comm | numeric(7,2) | | | | main | | | |
| deptno | numeric(2,0) | | | | main | | | |
+----------+-----------------------+-----------+----------+---------+----------+-------------+--------------+-------------+
Indexes:
"emp_pkey" PRIMARY KEY, btree (empno)
"emp_deptno" btree (deptno)
Access method: heap
CTAS #1
create table emp1 as select * from emp ;
-- Not Null, Primary key, Foreign Key, Index 정보 같이 생성 안됨
\d+ emp1
Table "public.emp1"
+----------+-----------------------+-----------+----------+---------+----------+-------------+--------------+-------------+
| Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description |
+----------+-----------------------+-----------+----------+---------+----------+-------------+--------------+-------------+
| empno | numeric(4,0) | | | | main | | | |
| ename | character varying(10) | | | | extended | | | |
| job | character varying(9) | | | | extended | | | |
| mgr | integer | | | | plain | | | |
| hiredate | date | | | | plain | | | |
| sal | numeric(7,2) | | | | main | | | |
| comm | numeric(7,2) | | | | main | | | |
| deptno | numeric(2,0) | | | | main | | | |
+----------+-----------------------+-----------+----------+---------+----------+-------------+--------------+-------------+
Access method: heap
CTAS #2
create table emp2 (like emp including all) ;
-- Not null, Primary Key, Foreign Key, Index 모두 동일한 구조로 만듦
insert into emp2 select * from emp ;
Table "public.emp2"
+----------+-----------------------+-----------+----------+---------+----------+-------------+--------------+-------------+
| Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description |
+----------+-----------------------+-----------+----------+---------+----------+-------------+--------------+-------------+
| empno | numeric(4,0) | | not null | | main | | | |
| ename | character varying(10) | | | | extended | | | |
| job | character varying(9) | | | | extended | | | |
| mgr | integer | | | | plain | | | |
| hiredate | date | | | | plain | | | |
| sal | numeric(7,2) | | | | main | | | |
| comm | numeric(7,2) | | | | main | | | |
| deptno | numeric(2,0) | | | | main | | | |
+----------+-----------------------+-----------+----------+---------+----------+-------------+--------------+-------------+
Indexes:
"emp2_pkey" PRIMARY KEY, btree (empno)
"emp2_deptno_idx" btree (deptno)
Access method: heap