Oracle → PostgreSQL 타입 매핑
오라클의 모든 타입이 PostgreSQL 에서 무엇이 되는지, 그리고 매핑은 맞는데 의미가 달라지는 네 자리.
대응표
마이그레이션의 대부분은 기계적입니다. 아래는 더 생각하지 않아도 되는 대응이고, 그다음 절들이 그렇지 않은 넷을 다룹니다.
| Oracle | PostgreSQL | 비고 |
|---|---|---|
| NUMBER(p,s) | numeric(p,s) | 의미까지 같음 |
| NUMBER(p), p ≤ 4 | smallint | 소수부 없는 정수 |
| NUMBER(p), p ≤ 9 | integer | |
| NUMBER(p), p ≤ 18 | bigint | |
| NUMBER (정밀도 없음) | numeric | 아래 참조 — 대개 틀린 선택 |
| BINARY_FLOAT | real | |
| BINARY_DOUBLE | double precision | |
| VARCHAR2(n) | varchar(n) | 오라클의 n 은 바이트, PostgreSQL 은 글자 |
| NVARCHAR2(n) | varchar(n) | PostgreSQL 은 대개 전체가 UTF-8 |
| CHAR(n) | char(n) | 둘 다 공백으로 채움 |
| CLOB / NCLOB | text | PostgreSQL 은 길이 제한 없음 |
| BLOB | bytea | |
| RAW(n) | bytea | |
| LONG / LONG RAW | text / bytea | 오라클에서도 폐기 예정 |
| DATE | timestamp(0) | date 가 아님 — 아래 참조 |
| TIMESTAMP(n) | timestamp(n) | |
| TIMESTAMP WITH TIME ZONE | timestamptz | |
| TIMESTAMP WITH LOCAL TIME ZONE | timestamptz | 오라클은 읽을 때, PostgreSQL 은 쓸 때 변환 |
| INTERVAL YEAR TO MONTH | interval | |
| INTERVAL DAY TO SECOND | interval | |
| XMLTYPE | xml | |
| ROWID / UROWID | text | 대응 없음. 보통 버림 |
| 플래그로 쓴 NUMBER(1) | boolean | 오라클은 23c 전까지 불리언이 없었음 |
정밀도 없는 NUMBER 가 가장 비싸다
오라클의 맨 NUMBER 는 유효숫자 38자리까지, 소수부도 자유롭게 담습니다. 곧이곧대로 옮기면 numeric 이 되는데, PostgreSQL 의 numeric 은 임의 정밀도 소프트웨어 연산입니다 — 정확하고, 기계 정수보다 몇 배 느립니다.
문제는 NUMBER 로 선언된 컬럼 대부분이 전혀 임의 정밀도가 아니라는 점입니다. 그냥 정밀도를 안 준 식별자, 개수, 외래키입니다. 이것들을 numeric 으로 옮기면 모든 조인과 인덱스가 느린 경로로 가고, 그 저하는 아무도 짚어내지 못하는 전반적인 굼뜸으로 나타납니다.
옮기기 전에 실제로 무엇이 들어 있는지 보세요. 실데이터에 max(abs(col)) 과 소수 자릿수를 재 보면 그 NUMBER 가 bigint 인지 몇 초 만에 알 수 있습니다. 해당하는 것은 바꾸고, 소수부가 진짜 의미를 갖는 것 — 금액, 비율, 측정값 — 만 numeric 으로 남기세요.
| 데이터가 말하는 것 | 쓸 타입 | 이유 |
|---|---|---|
| 정수, 21억 미만 | integer | 기계 연산, 저장 공간 절반 |
| 정수, 더 큼 | bigint | |
| 소수 자릿수 고정 — 금액, 비율 | numeric(p,s) | 오라클이 안 준 정밀도를 여기서 준다 |
| 정말로 한계가 없음 | numeric | 과학 데이터 말고는 드묾 |
오라클의 DATE 는 날짜가 아니다
오라클의 DATE 는 연·월·일에 시·분·초까지 담습니다. PostgreSQL 의 date 는 날짜만 담습니다. 이 둘을 이어 두면 모든 행에서 시각이 조용히 사라지는 마이그레이션이 됩니다. 아무 오류도 나지 않고, 데이터가 전부 자정으로 도착합니다.
맞는 대상은 timestamp(0) 이고, 그 컬럼이 벽시계 의도가 아니라 일어난 사건을 기록한다면 timestamptz 입니다. date 는 시각이 늘 0 인 것을 확인한 경우에만 쓰세요 — birth_date 같은 이름이면 대개 그렇습니다.
반대 방향 함정도 있습니다. DATE 를 TRUNC(SYSDATE) 와 비교하던 오라클 코드는 타임스탬프를 자정과 비교하는 것인데, PostgreSQL 의 timestamp 에 같은 비교를 하면 등호가 아니라 캐스팅이나 구간이 필요합니다.
오라클에서는 빈 문자열이 NULL 이다
오라클은 빈 문자열을 NULL 로 저장합니다. PostgreSQL 은 둘을 다른 값으로 봅니다. 타입 매핑 문제가 아니고 — 타입은 잘 맞습니다 — 마이그레이션 뒤 애플리케이션 동작을 가장 많이 바꿀 차이입니다.
오라클에서 NULL 로 넘어온 행이, 사실은 빈 문자열로 써 놓고 NVL 로 읽던 코드의 결과일 수 있습니다. PostgreSQL 에서는 같은 코드가 두 경우를 다 다뤄야 합니다 — 같은 애플리케이션이 새로 쓰는 행에는 '' 가 들어가고, 옮겨 온 행에는 NULL 이 있기 때문입니다.
- 새 스키마에서 "값 없음" 을 어느 쪽으로 할지 정하고, 옮긴 데이터를 그쪽으로 통일하세요.
- 기본값이 있는 NOT NULL 을 걸거나 '' 를 거부하는 체크 제약을 두어, 그 결정이 다시 흔들리지 않게 하세요.
- 텍스트 컬럼 주변의 NVL·COALESCE·IS NULL 을 찾아보세요. 오라클의 규칙을 전제한 자리들입니다.
식별자 대소문자가 반대로 접힌다
따옴표 없는 식별자는 오라클에서 대문자로, PostgreSQL 에서 소문자로 접힙니다. Orders 로 만든 테이블은 오라클에서 ORDERS, PostgreSQL 에서 orders 이고 둘 다 따옴표 없이 닿습니다 — 여기까지는 괜찮습니다.
깨지는 것은 오라클 스키마가 따옴표를 써서 만들어진 경우입니다. 그때는 이름이 진짜로 ORDERS 이고, 그것을 보존하는 마이그레이션 도구는 "ORDERS" 로만 닿을 수 있는 PostgreSQL 테이블을 만듭니다. 그 뒤로 애플리케이션의 모든 질의에 따옴표가 영원히 붙습니다.
보존하지 말고 마이그레이션 중에 소문자로 접으세요. 한 번의 이름 변경과, 스키마가 사는 내내 모든 식별자에 따옴표를 붙이는 것 중의 선택입니다.
시퀀스와 기본키
오라클의 seq.NEXTVAL 은 nextval('seq') 로 그대로 옮겨집니다. 다만 한 걸음 더 가서 identity 컬럼을 쓰는 편이 대개 낫습니다. 시퀀스가 테이블에 붙고, 그것을 깜빡한 INSERT 가 나올 여지가 사라집니다.
| Oracle | PostgreSQL | 비고 |
|---|---|---|
| CREATE SEQUENCE s | CREATE SEQUENCE s | 같음 |
| s.NEXTVAL | nextval('s') | |
| s.CURRVAL | currval('s') | 둘 다 세션 범위 |
| 시퀀스로 id 를 넣는 트리거 | GENERATED BY DEFAULT AS IDENTITY | 트리거 자체가 사라짐 |
| ROWNUM <= n | LIMIT n | ROWNUM 은 ORDER BY 전에, LIMIT 은 후에 적용 |
| SELECT ... FROM DUAL | SELECT ... | DUAL 불필요 |
| NVL(a, b) | COALESCE(a, b) | COALESCE 는 인자 개수 제한 없음 |
| SYSDATE | CURRENT_TIMESTAMP | 또는 now() |
id 를 명시해서 데이터를 적재하면 시퀀스는 여전히 1 에 있고 다음 INSERT 가 충돌합니다. 적재한 최대 id 로 setval 을 돌려 맞추세요 — 마이그레이션 중에는 아무것도 실패하지 않아서 잊기 쉬운 단계입니다.
자주 묻는 것
- 오라클 NUMBER 는 PostgreSQL 에서 무엇이 되나요?
- 곧이곧대로는 numeric 입니다 — 다만 대개 틀린 답입니다. 정밀도 없는 NUMBER 는 실제로는 거의 언제나 정수이고, numeric 으로 두면 모든 조인과 인덱스가 임의 정밀도 연산을 타게 됩니다. 데이터를 확인하고 맞으면 integer 나 bigint 를 쓰세요.
- 오라클 DATE 를 PostgreSQL date 로 옮겨도 되나요?
- 시각이 늘 자정일 때만요. 오라클의 DATE 는 시·분·초를 포함하므로 보통은 timestamp(0) 이고, 그 컬럼이 사건을 기록한다면 timestamptz 입니다.
- 마이그레이션 후 빈 문자열이 왜 다르게 동작하나요?
- 오라클은 빈 문자열을 NULL 로 저장하고 PostgreSQL 은 둘을 구분합니다. 옮긴 행은 NULL 로 오는데 같은 애플리케이션이 새로 쓰는 행에는 '' 가 들어가서, 텍스트에 NVL 이나 IS NULL 을 쓰던 코드가 두 경우를 다 다뤄야 합니다.
- 대문자 테이블 이름을 유지해야 하나요?
- 아니요. 오라클은 따옴표 없는 이름을 대문자로, PostgreSQL 은 소문자로 접습니다. 대문자를 보존하면 스키마가 사는 내내 모든 질의의 모든 식별자에 따옴표를 붙여야 합니다. 마이그레이션 때 소문자로 접으세요.
- ROWNUM 은 무엇으로 바꾸나요?
- LIMIT 입니다. 중요한 차이가 하나 있는데, ROWNUM 은 ORDER BY 전에 매겨져서 둘을 같이 쓴 질의는 보이는 대로 동작하지 않습니다. LIMIT 은 정렬 후에 적용되는데, 대개 그 오라클 질의가 표현하려던 것이 그쪽입니다.
- 데이터 적재 후 시퀀스에 뭘 해야 하나요?
- 네 — 적재한 최대 id 로 setval 을 돌려 맞추세요. 마이그레이션 중에는 아무것도 실패하지 않고, 그 뒤 첫 INSERT 가 기본키에서 충돌합니다.