본문 바로가기
빌드노트 검색

빌드노트

CREATE TABLE AS로 복사한 테이블에서 중복 ID가 들어간다

CTAS 복사본의 첫 행이 원본과 같아도 PK·NOT NULL·기본값은 같지 않을 수 있다. PostgreSQL 18.6에서 카탈로그와 중복·NULL·기본값 생략 INSERT를 비교하고, 명시한 스키마로 옮기거나 기존 데이터 확인 후 필요한 DDL을 보충하는 판단을 설명한다.

PostgreSQL에서 CREATE TABLE ... AS SELECT ...로 만든 복사본에 같은 ID가 다시 들어간다면, 원본의 기본 키가 복사됐는지부터 확인해야 한다. 원본과 열 이름·자료형·행 내용이 같아도 복사본에 같은 제약이 있다는 뜻은 아니다. 아래의 작은 예제에서는 원본의 PK, NOT NULL, 기본값이 모두 복사본에서 빠졌다.

CREATE TABLE AS를 줄여 CTAS라고 부른다. PostgreSQL 18의 CTAS 문서 (새 탭에서 열림)는 SELECT 출력의 열 이름과 자료형으로 테이블을 만들고 조회 결과를 채운다고 설명한다. 조회 결과를 보관하는 일과, 이후 쓰기에도 원본의 규칙을 적용하는 일은 따로 결정해야 한다.

이미 만든 복사본에서는 PK와 열 속성을 따로 본다

다음 두 조회는 각각 기본 키의 정의와 열의 NOT NULL·기본값을 확인한다. pg_temp.items_copy는 뒤에서 만들 임시 복사본이다. 기존 테이블을 진단할 때는 두 곳의 문자열을 실제 스키마명.복사본명으로 바꿔 실행한다. 조회에는 그 테이블의 행을 읽는 작업이 없다.

SELECT conname, pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'pg_temp.items_copy'::regclass
  AND contype = 'p';

SELECT a.attname, a.attnotnull AS not_null,
       pg_get_expr(d.adbin, d.adrelid) AS default_expr
FROM pg_attribute AS a
LEFT JOIN pg_attrdef AS d
  ON d.adrelid = a.attrelid AND d.adnum = a.attnum
WHERE a.attrelid = 'pg_temp.items_copy'::regclass
  AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;

첫 조회가 0행이면 지정한 테이블에 PK가 없다. 테이블 이름 자체가 잘못됐으면 regclass 변환에서 오류가 나므로, 0행과 이름 오류를 구분할 수 있다. PK가 조회된다면 그 정의에 어떤 열들이 들어 있는지 확인한다. 복합 PK에서는 ID 한 열만 같은 행이 반드시 위반인 것은 아니다.

두 번째 조회의 not_null은 열 속성이고 default_expr는 기본값 식이다. pg_constraint (새 탭에서 열림)의 contype='p'로 PK를 좁히고, pg_attribute (새 탭에서 열림)와 pg_attrdef (새 탭에서 열림)로 열 속성을 함께 읽는다. PK 조회만으로 NOT NULL이나 기본값까지 확인한 것으로 처리하지 않는다.

복사한 첫 행은 같지만 다음 INSERT는 달랐다

다음은 격리된 PostgreSQL 18.6에서 직접 실행한 실험이다. 재현하려면 임시 테이블을 만들 권한이 있는 실습용 DB 연결을 준비하고, 새 SQL 세션에서 실행한다. 아래 테이블들은 같은 세션에서만 보이며 연결을 닫으면 사라진다. 예제 이름의 기존 임시 테이블이 없는 세션을 사용한다.

원본에는 ID 기본 키와 필수 label, label을 생략할 때의 기본값을 선언하고 한 행만 복사한다.

CREATE TEMP TABLE items_source (
    id integer PRIMARY KEY,
    label text NOT NULL DEFAULT 'new'
);
INSERT INTO pg_temp.items_source (id) VALUES (1);

CREATE TEMP TABLE items_copy AS
SELECT id, label FROM pg_temp.items_source;

두 테이블의 첫 행은 모두 (1, 'new')였다. 하지만 앞의 카탈로그 조회를 복사본에 실행하니 PK는 0행, 열 속성은 다음과 같았다. 여기서 <NULL>은 SQL NULL을 표시한 것이며, 기본값 식이 없다는 뜻이다.

attname|not_null|default_expr
id|f|<NULL>
label|f|<NULL>

같은 조회 대상을 pg_temp.items_source로 바꾸면 PRIMARY KEY (id)가 나오고 두 열의 not_null은 t다. 원본 label의 기본값 식은 'new'::text였다. 즉 'new'라는 첫 행의 값은 복사됐지만, 다음 행에도 그 값을 채우라는 정의는 복사되지 않았다.

이 차이가 새 입력에 어떻게 나타나는지, 복사본에 중복 ID와 NULL을 넣고 label도 한 번 생략해 본다.

INSERT INTO pg_temp.items_copy (id, label) VALUES (1, 'duplicate');
INSERT INTO pg_temp.items_copy (id, label) VALUES (NULL, NULL);
INSERT INTO pg_temp.items_copy (id) VALUES (2);

SELECT id, label FROM pg_temp.items_copy
ORDER BY id NULLS LAST, label NULLS LAST;

세 INSERT가 모두 성공했고, 조회한 네 행은 다음과 같았다. 출력의 NULL 표기와 구분자만 읽기 쉽게 통일했다.

id|label
1|duplicate
1|new
2|<NULL>
<NULL>|<NULL>

ID 1이 두 번 저장됐고 NULL ID도 들어갔다. label을 생략한 ID 2의 값 역시 NULL이었다. 원본에 같은 종류의 입력을 각각 시도했을 때는 중복 ID가 23505, NULL ID와 명시적 NULL label이 각각 23502로 거절됐다. 원본에서 label을 생략한 새 ID는 'new'를 받았다. 이 결과는 실제 업무 테이블의 장애 기록이 아니라, 위 임시 테이블로 별도 재현한 관찰이다.

WITH NO DATA를 붙인 CTAS도 추가로 확인했다. 행만 0개였고 처음에는 PK·NOT NULL·기본값이 모두 없었다. 이 옵션을 붙인다고 원본의 무결성 규칙까지 복사되는 것은 아니었다.

같은 규칙이 필요하면 선언한 테이블로 데이터를 옮긴다

복사본에도 중복 ID와 NULL을 막아야 한다면, 필요한 정의를 먼저 쓰고 데이터를 넣는 방법이 분명하다. 아래 코드는 앞의 원본 한 행을 그대로 가져오되, 복사본이 지켜야 할 규칙을 DDL에 명시한다.

CREATE TEMP TABLE items_explicit (
    id integer PRIMARY KEY,
    label text NOT NULL DEFAULT 'new'
);
INSERT INTO pg_temp.items_explicit (id, label)
SELECT id, label FROM pg_temp.items_source;

INSERT INTO pg_temp.items_explicit (id) VALUES (2)
RETURNING id, label;

마지막 INSERT는 2|new를 반환했다. 이 테이블에서도 중복 ID, NULL ID, 명시적 NULL label을 각각 넣어 보니 원본처럼 23505, 23502, 23502로 실패했다. 카탈로그에서도 PK, 두 열의 NOT NULL, label 기본값을 확인했다. 이 예제의 PRIMARY KEY와 DEFAULT 정의 (새 탭에서 열림)는 각각 ID의 유일성·NULL 금지와, 생략한 label의 값을 담당한다. 기본값이 있어도 명시적으로 넣은 NULL을 'new'로 바꾸지는 않는다.

이미 쓰기 시작한 CTAS 복사본이라면 제약을 추가하기 전에 기존 데이터를 먼저 판단해야 한다. 다음 조회는 실습 복사본에서 중복 ID와 NULL이 얼마나 생겼는지 확인한다. 실제 테이블에 적용하면 데이터를 읽으므로 크기와 실행 여건을 보고 수행한다.

SELECT id, count(*)
FROM pg_temp.items_copy
GROUP BY id HAVING count(*) > 1;

SELECT count(*) FILTER (WHERE id IS NULL) AS null_ids,
       count(*) FILTER (WHERE label IS NULL) AS null_labels
FROM pg_temp.items_copy;

실험에서는 중복 ID 조회가 1|2, NULL 집계가 1|2였다. 이런 상태에서 PK를 추가하니 중복 때문에 23505로, label에 SET NOT NULL을 적용하니 기존 NULL 때문에 23502로 실패했다. 어떤 행을 남길지, NULL을 무엇으로 보완할지는 데이터의 의미를 보고 정해야 한다. PK나 NOT NULL을 추가하는 DDL이 그 결정을 대신하지 않는다.

반면 비어 있는 CTAS 테이블에 ADD PRIMARY KEY (id), ALTER COLUMN label SET NOT NULL, ALTER COLUMN label SET DEFAULT 'new'를 명시한 뒤에는 같은 거절·기본값 동작을 확인했다. 기존 테이블을 유지해야 한다면 이처럼 필요한 정의를 보충할 수 있다. 다만 ALTER TABLE 문서 (새 탭에서 열림)에 따라 기존 데이터 검사와 잠금이 필요할 수 있으므로, 큰 테이블의 변경 시간은 별도로 검토해야 한다. 이번 실험에서는 동시 쓰기나 대용량 변경 시간을 측정하지 않았다.

기본값 보충도 기존 행을 고치는 작업은 아니다. 실습의 잘못된 복사본에 SET DEFAULT 'new'만 적용했을 때, 이미 들어 있던 NULL label 두 개는 남았고 이후 label을 생략한 새 행만 'new'를 받았다.

CREATE TABLE ... (LIKE ... INCLUDING ALL)을 선택하더라도 완전한 백업이라고 해석하면 안 된다. LIKE 옵션 설명 (새 탭에서 열림)에 있는 복사 항목을 검토하고 데이터 이동도 별도로 정해야 한다. LIKE 조합은 이번에 실행 비교하지 않았다. 직접 확인한 범위는 일반 integer·text 열의 CTAS, 명시한 PK·NOT NULL·상수 기본값이다. 도메인, identity·시퀀스, 외래키·트리거의 이전까지 검증한 결과는 아니다.

← 전체 글 보기