PostgreSQL COPY FROM 레코드 충돌 해결법: TEMPORARY TABLE과 ON CONFLICT
COPY FROM은 외부 파일을 읽어 PostgreSQL 테이블에 옮기는 SQL 명령어입니다.
COPY table FROM file;
COPY FROM은 INSERT에 비해 빠르게 동작하기 때문에 대용량의 파일을 읽어와야 할 때 주로 사용합니다.
하지만, COPY FROM는 파일에 중복된 레코드(Record) 등 테이블 제약조건(Constraint)에 맞지 않는 레코드가 있으면 오류를 생성하며 종료됩니다.
사전에 파일 내 데이터를 잘 확인하면 문제가 발생하지 않지만, 불가피하게 대량의 불완전한 데이터를 입력해야 할 경우에는 상당히 곤란한 상황이 발생합니다.
COPY FROM과 달리, INSERT 명령에는 ON CONFLICT를 통해 Unique Violation 등 충돌 발생 시 대응 방법을 지정할 수 있기에, 이것을 잘 조합하면 충돌을 무시하는 COPY FROM를 구현할 수 있습니다.
CREATE TEMPORARY TABLE tmp_table (LIKE table) ON COMMIT DROP;
COPY tmp_table FROM file;
INSERT INTO table SELECT * FROM tmp_table ON CONFLICT DO NOTHING;
먼저 CREATE TEMPORARY TABLE을 이용해 임시 테이블을 만듭니다.
그리고 COPY FROM으로 임시 테이블에 데이터를 전달합니다.
임시 테이블은 비어있는 상태로 생성되기 때문에, 기존의 데이터와 충돌하는 상황이 생기지 않습니다.
기존 테이블의 제약조건도 복사해야 할 때는
INCLUDING기능을 이용해 임시 테이블을 생성할 수 있습니다.CREATE TEMPORARY TABLE tmp_table (LIKE table INCLUDING ALL) ON COMMIT DROP;
INSERT 명령을 이용해 임시 테이블의 데이터를 원래 테이블로 이동시킵니다.
이때, ON CONFLICT를 이용해 충돌 시 대응 방법을 지정합니다.
DO NOTHING은 문제가 발생한 레코드를 무시하는 옵션입니다.
Upsert 기능이 필요한 경우, DO UPDATE SET을 이용하면 됩니다.
INSERT INTO table SELECT * FROM tmp_table ON CONFLICT DO UPDATE SET field = EXCLUDED.field;
EXCLUDED는 문제의 레코드를 의미합니다.
참고: How Postgresql COPY TO STDIN With CSV do on conflic do update?