Oracle PostgreSQL 변환 시 자료형 대체 및 DDL 수정 방법

Oracle에서 작성한 Create Table문을 수정없이 PostgreSQL에서 사용할 수 없는 경우가 있습니다. Oracle의 자료형 중에 일부 VARCHAR2 , NUMBER, BLOB 등은 PostgreSQL에 존재하지 않으며 또한 DATE 자료형은 PostgreSQL에서는 조금 다른 형태로 데이터를 저장하기 때문에 그대로 사용할 수 없습니다. 이 글에서는 CREATE TABLE에 대한 Oracle과 PostgreSQL 정의의 차이점을 비교하고 대체 가능한 자료형이 있는 지를 소개합니다.

Oracle DDL문
Oracle DDL문을 PostgreSQL에서 실행 했을 때
PostgreSQL형식으로 수정한 DDL문
DDL문 Oracle vs PostgreSQL

Oracle DDL문

다음은 일반적인 Oracle에서 사용하는 DROP TABLE문과 CREATE TABLE문, CREATE INDEX문 입니다.

DROP TABLE SAMPLETBL;
CREATE TABLE SAMPLETBL (
  ID_ITEM1 VARCHAR2(4) NOT NULL,
  ID_ITEM2 NUMBER NOT NULL,
  DT_LOGIN DATE,
  UPLOAD BLOB,
  CONSTRAINT PK_MST PRIMARY KEY ( ID_ITEM1)
  USING INDEX TABLESPACE USERS
  LOGGING
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE ( 
      INITIAL 64K
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS UNLIMITED
      BUFFER_POOL DEFAULT )
) TABLESPACE USERS
  LOGGING
  PCTFREE 10
  INITRANS 1
  MAXTRANS 255
  STORAGE ( 
      INITIAL 64K
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS UNLIMITED
      BUFFER_POOL DEFAULT )
MONITORING;                   

CREATE INDEX IDX_ITEM2
      ON  SAMPLETBL(ID_ITEM2)
TABLESPACE USERS
  LOGGING
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE ( 
      INITIAL 64K
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS UNLIMITED
      BUFFER_POOL DEFAULT );

Oracle DDL문을 PostgreSQL에서 실행 했을 때

Oracle 옵션 지정어 삭제하기

위의 Oracle SQL문(DROP TABLE문과 CREATE TABLE문, CREATE INDEX문)을 PostgreSQL에서 실행 했을때 결과는 다음과 같습니다.

ERROR: 오류: 구문 오류, "LOGGING" 부근
LINE 9: LOGGING
^
SQL state: 42601
Character: 219

LOGGING, PCTFREE, INITRANS, MAXTRANS, STORAGE(…), MONITORING 옵션 문은 PostgreSQL에서는 사용하지 않습니다.

Oracle 옵션 문 LOGGING, PCTFREE, INITRANS, MAXTRANS, STORAGE(…), MONITORING 를 지우고 PostgreSQL에서 다시 실행합니다.

VARCHAR2 자료형 대체하기

실행한 결과는 다음과 같습니다.

ERROR: 오류:  "varchar2" 자료형 없음
LINE 3:   ID_ITEM1 VARCHAR2(4) NOT NULL,
                   ^
SQL state: 42704
Character: 38

VARCHAR2는 Oracle에서만 사용하는 자료형입니다. PostgreSQL에서는 VARCHAR 또는 Character Varying를 대체 자료형으로 사용합니다. 참고로 VARCHAR를 지정해도 내부적으로는 Character Varying으로 인식합니다.

VARCHAR2를 VARCHAR로 변경후 PostgreSQL에서 다시 실행합니다.

NUMBER 자료형 대체하기

실행한 결과는 다음과 같습니다.

ERROR: 오류:  "number" 자료형 없음
LINE 3:   ID_ITEM2 NUMBER NOT NULL,
                   ^
SQL state: 42704
Character: 69

NUMBER는 Oracle에서만 사용하는 자료형입니다. PostgreSQL에서는 DECIMAL또는 NUMERIC을 대체 자료형으로 사용합니다. 참고로 DECIMAL을 지정해도 내부적으로는 NUMERIC으로 인식합니다.

NUMBER를 NUMERIC로 변경후 PostgreSQL에서 다시 실행합니다.

BLOB 자료형 대체하기

실행한 결과는 다음과 같습니다.

ERROR: 오류:  "blob" 자료형 없음
LINE 5:   UPLOAD BLOB,
                 ^
SQL state: 42704
Character: 113

BLOB는 Oracle에서만 사용하는 자료형입니다. PostgresSQL에서는 BYTEA가 대신합니다. BYTEA자료형은 최대 1Gbyte까지라는 저장 용량에 제약이 있습니다.

BLOB을 BYTEA로 변경후 PostgreSQL에서 다시 실행합니다.

USERS 테이블스페이스 대체하기

실행한 결과는 다음과 같습니다.

ERROR: 오류:  "users" 테이블스페이스 없음
SQL state: 42704

PostgreSQL에서 기본으로 사용되는 테이블스페이스에는 pg_default가 있습니다.

USERS를 pg_default로 변경합니다.

DATE 자료형 대체하기

Oracle의 DATE 자료형은 날짜 / 시간을 저장하지만 PostgreSQL의 DATE 자료형은 날짜만 저장합니다. PostgreSQL에서 날짜 / 시간을 저장하는 자료형에는 TIMESTAMP가 있습니다.

DATE를 TIMESTAMP로 변경후 PostgreSQL에서 다시 실행합니다.

PostgreSQL형식으로 수정한 DDL문

위와 같이 Oracle DDL문을 그대로 psql명령으로 PostgreSQL서버에서 실행했을때 발생한 오류의 원인을 삭제하거나 PostgreSQL문법으로 대체한 DDL은 다음과 같습니다.

DROP TABLE IF EXISTS SAMPLETBL;
 
CREATE TABLE SAMPLETBL (
  ID_ITEM1 VARCHAR(4) NOT NULL,
  ID_ITEM2 NUMERIC NOT NULL,
  DT_LOGIN TIMESTAMP,
  UPLOAD BYTEA,
CONSTRAINT PK_MST PRIMARY KEY ( ID_ITEM1)
USING INDEX TABLESPACE pg_default
) TABLESPACE pg_default;

CREATE INDEX IDX_ITEM2
ON SAMPLETBL(ID_ITEM2)
TABLESPACE pg_default;

PostgreSQL형식으로 수정한 최종 DDL문을 psql명령으로 PostgreSQL서버에서 실행한 결과는 다음과 같습니다.

알림:  "sampletbl" 테이블 없음, 무시함
DROP TABLE
CREATE TABLE
CREATE INDEX

DDL문 Oracle vs PostgreSQL

Oracle의 DDL문과 PostgreSQL의 DDL문을 비교하면 다음과 같습니다.

Oracle	PostgreSQL
DROP TABLE SAMPLETBL; CREATE TABLE SAMPLETBL ( ID_ITEM1 *VARCHAR2(4) NOT NULL, ID_ITEM2 NUMBER* NOT NULL, DT_LOGIN DATE, UPLOAD *BLOB, CONSTRAINT PK_MST PRIMARY KEY ( ID_ITEM1) USING INDEX TABLESPACE USERS* LOGGING PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED BUFFER_POOL DEFAULT ) ) TABLESPACE USERS LOGGING PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED BUFFER_POOL DEFAULT ) MONITORING;	DROP TABLE IF EXISTS SAMPLETBL; CREATE TABLE SAMPLETBL ( ID_ITEM1 VARCHAR(4) NOT NULL, ID_ITEM2 NUMERIC NOT NULL, DT_LOGIN TIMESTAMP, UPLOAD BYTEA, CONSTRAINT PK_MST PRIMARY KEY ( ID_ITEM1) USING INDEX TABLESPACE pg_default ) TABLESPACE pg_default;
CREATE INDEX IDX_ITEM2 ON SAMPLETBL(ID_ITEM2) TABLESPACE USERS LOGGING PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED BUFFER_POOL DEFAULT );	CREATE INDEX IDX_ITEM2 ON SAMPLETBL(ID_ITEM2) TABLESPACE pg_default;

Oracle

PostgreSQL

DROP TABLE SAMPLETBL;
CREATE TABLE SAMPLETBL (
ID_ITEM1 VARCHAR2(4) NOT NULL,
ID_ITEM2 NUMBER NOT NULL,
DT_LOGIN DATE,
UPLOAD BLOB,
CONSTRAINT PK_MST PRIMARY KEY ( ID_ITEM1)
USING INDEX TABLESPACE USERS
LOGGING
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT )
) TABLESPACE USERS
LOGGING
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT )
MONITORING;

DROP TABLE IF EXISTS SAMPLETBL;

CREATE TABLE SAMPLETBL (
ID_ITEM1 VARCHAR(4) NOT NULL,
ID_ITEM2 NUMERIC NOT NULL,
DT_LOGIN TIMESTAMP,
UPLOAD BYTEA,
CONSTRAINT PK_MST PRIMARY KEY ( ID_ITEM1)
USING INDEX TABLESPACE pg_default
) TABLESPACE pg_default;

CREATE INDEX IDX_ITEM2
ON SAMPLETBL(ID_ITEM2)
TABLESPACE USERS
LOGGING
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT );

CREATE INDEX IDX_ITEM2
ON SAMPLETBL(ID_ITEM2)
TABLESPACE pg_default;

Oracle데이테베이스를 사용하는 응용 프로그램을 PostgreSQL 데이테베이스로 변경 또는 그 반대로 변경하는 프로젝트를 진행하는 분들께 이 글이 도움이 되기를 바랍니다.