[PostgreSQL 시리즈 2편] 자료형과 DDL 완벽 비교 - Oracle 타입을 PostgreSQL로 옮기기

[PostgreSQL 시리즈 2편] 자료형과 DDL 완벽 비교 - Oracle 타입을 PostgreSQL로 옮기기

- 테스트 환경: PostgreSQL 17, Rocky Linux 9 / Oracle 19c

PostgreSQL로 넘어온 Oracle DBA가 실무에서 가장 먼저 부딪히는 것이 자료형입니다. NUMBER, VARCHAR2, DATE... 익숙한 타입들이 PostgreSQL에선 다른 이름이거나, 아예 개념이 다릅니다. 여기서 잘못 매핑하면 데이터 정합성 문제나 성능 저하로 이어집니다.

이번 편에서는 1편(첫걸음)에 이어 실제 테이블을 만들며 다음을 다룹니다.

• Oracle vs PostgreSQL 자료형 완벽 매핑표
• 숫자·문자·날짜 타입의 핵심 차이
• ★ SERIAL vs IDENTITY vs 시퀀스 (자동 증가 컬럼)
• CREATE TABLE과 제약조건 문법 비교
• PostgreSQL 고유 타입 맛보기

모든 예제는 직접 실행 가능하니, psql을 열고 따라 해보세요.

 

★ 자료형 완벽 매핑표

Oracle 타입을 PostgreSQL로 옮길 때 참조하는 표입니다. 실무에서 가장 자주 쓰는 것 위주로 정리했습니다.

숫자 타입

Oracle PostgreSQL 비고

NUMBER(p,s)	NUMERIC(p,s)	정확한 소수
NUMBER (정수)	INTEGER, BIGINT	크기에 맞게 선택
NUMBER(10)	INTEGER 또는 BIGINT	자릿수 고려
BINARY_FLOAT	REAL	4바이트 부동소수
BINARY_DOUBLE	DOUBLE PRECISION	8바이트 부동소수

핵심 차이: Oracle은 NUMBER 하나로 대부분 처리하지만, PostgreSQL은 용도에 맞는 정수 타입을 선택하는 게 성능에 좋습니다.

-- Oracle 스타일 (모두 NUMBER)
-- emp_id NUMBER, salary NUMBER(10,2)

-- PostgreSQL 권장 (용도별)
emp_id   INTEGER,        -- 또는 BIGINT
salary   NUMERIC(10,2)   -- 정확한 금액

정수 타입 선택 기준:

SMALLINT: -32,768 ~ 32,767 (2바이트)
INTEGER:  약 -21억 ~ 21억 (4바이트) ← 대부분 이걸로
BIGINT:   매우 큰 수 (8바이트)

NUMERIC은 정확하지만 느립니다. 금액처럼 정확성이 중요할 때만 쓰고, 일반 정수는 INTEGER를 쓰세요.

문자 타입

Oracle PostgreSQL 비고

VARCHAR2(n)	VARCHAR(n)	가변 길이
CHAR(n)	CHAR(n)	고정 길이
CLOB	TEXT	대용량 텍스트
NVARCHAR2	VARCHAR	PostgreSQL은 기본 유니코드

핵심 차이:

• PostgreSQL의 TEXT는 길이 제한이 없고 성능도 좋습니다. Oracle의 CLOB처럼 특별 취급하지 않습니다.
• 사실 PostgreSQL에서는 VARCHAR(n)과 TEXT의 성능 차이가 거의 없습니다. 길이 제한이 꼭 필요한 게 아니면 TEXT를 써도 됩니다.

-- Oracle
-- memo CLOB, name VARCHAR2(100)

-- PostgreSQL
memo  TEXT,          -- 길이 제한 없음, 성능 좋음
name  VARCHAR(100)   -- 제한 필요 시

날짜·시간 타입

Oracle PostgreSQL 비고

DATE	TIMESTAMP	Oracle DATE는 시분초 포함
DATE (날짜만)	DATE	PostgreSQL DATE는 날짜만
TIMESTAMP	TIMESTAMP	유사
TIMESTAMP WITH TIME ZONE	TIMESTAMPTZ	타임존 포함

★ 가장 주의할 함정: Oracle의 DATE는 시분초를 포함하지만, PostgreSQL의 DATE는 날짜만 담습니다.

-- Oracle DATE = 날짜 + 시분초
-- → PostgreSQL에서는 TIMESTAMP로 매핑해야 함!

-- 잘못된 매핑 (시분초 사라짐)
hire_date DATE          -- PostgreSQL: 날짜만!

-- 올바른 매핑
hire_date TIMESTAMP     -- Oracle DATE의 시분초까지 보존

이걸 놓치면 마이그레이션 시 시간 정보가 통째로 사라집니다. Oracle DBA가 가장 많이 하는 실수 중 하나입니다.

기타 타입

Oracle PostgreSQL 비고

BLOB	BYTEA	바이너리
RAW	BYTEA	바이너리
ROWID	ctid	물리 위치 (개념 다름)
XMLTYPE	XML	XML
(없음)	JSONB	PostgreSQL 강점!
(없음)	배열 타입	PostgreSQL 강점!
(없음)	UUID	기본 지원

마지막 3개(JSONB, 배열, UUID)는 Oracle에 없거나 약한 PostgreSQL의 강점입니다. 8편에서 집중 조명합니다.

 

★ 자동 증가 컬럼 - SERIAL vs IDENTITY vs 시퀀스

Oracle DBA가 가장 반가워하는 부분입니다. Oracle에서 자동 증가 컬럼을 만들려면 시퀀스 + 트리거를 따로 만들어야 했지만, PostgreSQL은 훨씬 간단합니다.

방법 1: SERIAL (가장 간단)

CREATE TABLE employees (
    emp_id SERIAL PRIMARY KEY,   -- 자동 증가!
    emp_name VARCHAR(100)
);

-- INSERT 시 emp_id 자동 생성
INSERT INTO employees (emp_name) VALUES ('홍길동');

SERIAL은 내부적으로 시퀀스를 자동 생성하고 연결합니다. Oracle의 시퀀스+트리거를 한 줄로 대체합니다.

SERIAL 종류:
SMALLSERIAL: 2바이트
SERIAL:      4바이트 (INTEGER)
BIGSERIAL:   8바이트 (BIGINT) ← 대용량 테이블 권장

방법 2: IDENTITY (SQL 표준, 권장)

PostgreSQL 10부터 SQL 표준인 IDENTITY를 지원합니다. Oracle 12c의 IDENTITY와 동일한 문법입니다.

CREATE TABLE employees (
    emp_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    emp_name VARCHAR(100)
);

SERIAL vs IDENTITY 선택:

SERIAL:   전통적, 간단, 널리 쓰임
IDENTITY: SQL 표준, 더 엄격한 제어 (권장)

GENERATED ALWAYS: 사용자가 값 직접 입력 불가 (안전)
GENERATED BY DEFAULT: 직접 입력도 허용 (유연)

최신 프로젝트에서는 IDENTITY를 권장합니다. Oracle 12c 이상을 써온 분이라면 익숙한 문법일 것입니다.

방법 3: 시퀀스 직접 사용 (Oracle 스타일)

Oracle처럼 시퀀스를 직접 만들 수도 있습니다.

-- 시퀀스 생성 (Oracle과 유사)
CREATE SEQUENCE emp_seq START 1 INCREMENT 1;

-- 사용 (Oracle: emp_seq.NEXTVAL)
SELECT nextval('emp_seq');   -- 다음 값
SELECT currval('emp_seq');   -- 현재 값

-- 테이블에서 사용
CREATE TABLE employees (
    emp_id INTEGER DEFAULT nextval('emp_seq') PRIMARY KEY,
    emp_name VARCHAR(100)
);

Oracle과 문법 차이:

Oracle:     emp_seq.NEXTVAL, emp_seq.CURRVAL
PostgreSQL: nextval('emp_seq'), currval('emp_seq')

 

CREATE TABLE 완벽 비교 (실습)

이제 실제 테이블을 만들며 제약조건까지 비교합니다.

Oracle 스타일 테이블

-- Oracle
CREATE TABLE departments (
    dept_id    NUMBER(10)    PRIMARY KEY,
    dept_name  VARCHAR2(100) NOT NULL,
    location   VARCHAR2(50)  DEFAULT 'Seoul',
    created_at DATE          DEFAULT SYSDATE
);

PostgreSQL로 변환

-- PostgreSQL
CREATE TABLE departments (
    dept_id    INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    dept_name  VARCHAR(100) NOT NULL,
    location   VARCHAR(50)  DEFAULT 'Seoul',
    created_at TIMESTAMP    DEFAULT CURRENT_TIMESTAMP
);

바뀐 점:

NUMBER(10)  → INTEGER (+ IDENTITY로 자동증가)
VARCHAR2    → VARCHAR
DATE        → TIMESTAMP (시분초 보존)
SYSDATE     → CURRENT_TIMESTAMP

제약조건 비교

제약조건 문법은 대부분 동일합니다.

CREATE TABLE employees (
    emp_id     INTEGER GENERATED ALWAYS AS IDENTITY,
    emp_name   VARCHAR(100) NOT NULL,
    email      VARCHAR(100) UNIQUE,
    dept_id    INTEGER,
    salary     NUMERIC(10,2) CHECK (salary > 0),
    hire_date  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- 기본키
    CONSTRAINT pk_emp PRIMARY KEY (emp_id),
    -- 외래키
    CONSTRAINT fk_dept FOREIGN KEY (dept_id) 
        REFERENCES departments(dept_id)
);

Oracle과 거의 동일합니다. PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL 모두 같은 문법입니다. 이 부분은 Oracle DBA에게 부담이 없습니다.

함정: 외래키 인덱스

Oracle: 외래키 컬럼에 인덱스 자동 생성 안 됨 (수동 필요)
PostgreSQL: 마찬가지로 자동 생성 안 됨

→ 양쪽 모두 외래키 컬럼에 인덱스를 수동으로 만들어야
  성능 문제(락 등)를 피할 수 있음

-- 외래키 컬럼에 인덱스 (권장)
CREATE INDEX idx_emp_dept ON employees(dept_id);

 

테이블 수정 (ALTER TABLE)

컬럼 추가·수정도 비교해봅니다.

-- 컬럼 추가 (거의 동일)
ALTER TABLE employees ADD COLUMN phone VARCHAR(20);

-- 컬럼 타입 변경 (문법 차이)
-- Oracle: ALTER TABLE emp MODIFY (phone VARCHAR2(30));
-- PostgreSQL:
ALTER TABLE employees ALTER COLUMN phone TYPE VARCHAR(30);

-- 컬럼 삭제 (동일)
ALTER TABLE employees DROP COLUMN phone;

-- 컬럼명 변경 (동일)
ALTER TABLE employees RENAME COLUMN phone TO mobile;

-- 기본값 설정 (문법 차이)
-- Oracle: ALTER TABLE emp MODIFY (location DEFAULT 'Busan');
-- PostgreSQL:
ALTER TABLE employees ALTER COLUMN location SET DEFAULT 'Busan';

핵심 차이: Oracle의 MODIFY가 PostgreSQL에서는 ALTER COLUMN ... TYPE, SET DEFAULT 등으로 세분화됩니다.

 

PostgreSQL 고유 타입 맛보기

Oracle에 없는 PostgreSQL만의 타입을 미리 살짝 보겠습니다. (자세한 건 8편에서)

배열 타입

-- 한 컬럼에 여러 값을 배열로!
CREATE TABLE products (
    product_id INTEGER GENERATED ALWAYS AS IDENTITY,
    name       VARCHAR(100),
    tags       TEXT[]        -- 문자열 배열
);

INSERT INTO products (name, tags) 
VALUES ('노트북', ARRAY['전자', '컴퓨터', '휴대용']);

-- 배열 검색
SELECT * FROM products WHERE '전자' = ANY(tags);

Oracle이라면 별도 테이블로 정규화해야 할 것을 배열로 간단히 처리합니다.

JSONB 타입

-- JSON을 네이티브로 저장하고 인덱싱까지!
CREATE TABLE events (
    event_id INTEGER GENERATED ALWAYS AS IDENTITY,
    data     JSONB
);

INSERT INTO events (data) 
VALUES ('{"user": "kim", "action": "login", "ip": "1.2.3.4"}');

-- JSON 필드 조회
SELECT data->>'user' AS username FROM events;

JSONB는 PostgreSQL의 킬러 기능 중 하나입니다. Oracle도 JSON을 지원하지만 PostgreSQL의 JSONB가 더 강력하고 널리 쓰입니다.

 

★ AI 활용 - 타입 매핑 자동화

Oracle DDL을 PostgreSQL로 변환할 때 Claude를 활용하면 빠릅니다.

프롬프트 예시:
"다음 Oracle CREATE TABLE 문을 PostgreSQL 17로 
변환해줘. 다음을 지켜서:
- DATE는 시분초 필요 여부를 물어보고 매핑
- NUMBER는 용도에 맞는 정수/NUMERIC로
- 시퀀스+트리거는 IDENTITY로
- 바뀐 부분마다 이유 주석

[Oracle DDL 붙여넣기]"

주의: AI 변환 결과는 반드시 검증하세요. 특히 DATE → TIMESTAMP 매핑은 데이터 손실과 직결되므로 꼭 확인해야 합니다.

 

다음 글 예고

다음 글은 [PostgreSQL 시리즈 3편] DML과 트랜잭션 - MVCC의 차이 입니다. INSERT/UPDATE/DELETE 문법 비교와 함께, Oracle의 UNDO와 PostgreSQL의 MVCC가 어떻게 다른지 — 이 시리즈에서 가장 중요한 개념을 다룹니다.

 

마무리

Oracle 자료형을 PostgreSQL로 옮길 때 핵심입니다.

1. NUMBER는 용도별로 — INTEGER/BIGINT/NUMERIC 구분
2. DATE 함정 주의 — Oracle DATE → PostgreSQL TIMESTAMP (시분초!)
3. 자동 증가는 IDENTITY 권장 — 시퀀스+트리거 불필요
4. 제약조건은 거의 동일 — 부담 없음
5. JSONB·배열 — Oracle에 없는 강점 (8편에서)

자료형 매핑은 마이그레이션의 첫 관문입니다. 특히 DATE 함정과 NUMBER 선택만 정확히 해도 많은 문제를 예방할 수 있습니다. 다음 편에서는 이 시리즈의 하이라이트인 MVCC를 다룹니다. Oracle DBA에게 가장 낯선, 그러나 가장 중요한 개념입니다.

타입 매핑에서 겪은 경험이나 까다로웠던 케이스가 있다면 댓글로 공유해주세요.