PostgreSQL 23505 오류 원인과 해결 방법 완벽 가이드

23505 unique violation 는?

PostgreSQL 에러 코드 23505는 unique violation으로, 테이블에 정의된 UNIQUE 제약 조건(Unique Constraint) 또는 고유 인덱스(Unique Index)를 위반할 때 발생합니다. 즉, 이미 존재하는 값과 동일한 값을 INSERT하거나 UPDATE하려 할 때 PostgreSQL이 이 에러를 반환합니다. 실무에서는 회원 이메일 중복 등록, 주문 번호 중복 생성, 배치 작업 중 데이터 충돌 등 다양한 상황에서 빈번하게 마주치는 에러입니다.

---

주요 발생 원인

1. 애플리케이션 레벨에서 중복 체크 없이 직접 INSERT

가장 흔한 원인입니다. 애플리케이션 코드에서 “이미 존재하는지” 확인하는 로직 없이 곧바로 INSERT 구문을 실행할 때 발생합니다. 특히 다수의 사용자가 동시에 동일한 데이터를 등록하려 할 때 Race Condition이 발생하여, 애플리케이션 레벨의 중복 체크를 통과했더라도 DB 레벨에서 이 에러가 발생할 수 있습니다.

2. 배치(Batch) 작업 또는 데이터 마이그레이션 중 중복 데이터 유입

ETL 파이프라인이나 대용량 데이터 마이그레이션 작업 중 소스 데이터 자체에 중복이 포함되어 있거나, 작업이 중간에 실패 후 재실행될 때 이미 적재된 데이터가 다시 삽입되면서 발생합니다. 이 경우 수천~수만 건의 에러가 한꺼번에 발생할 수 있으므로 배치 설계 단계에서 반드시 고려해야 합니다.

3. 시퀀스(Sequence) 또는 Serial 컬럼의 동기화 불일치

SERIAL 또는 BIGSERIAL 컬럼을 사용하는 테이블에 외부에서 직접 ID 값을 지정하여 INSERT하면, 이후 시퀀스가 이미 사용된 값을 생성하면서 충돌이 발생합니다. 데이터베이스 복원(Restore) 이후 시퀀스 값이 실제 데이터의 최댓값보다 낮게 리셋되는 경우에도 동일한 문제가 반복적으로 발생합니다.

---

해결 방법

원인 1 해결: INSERT ... ON CONFLICT 구문 활용 (UPSERT)

PostgreSQL 9.5+부터 지원하는 ON CONFLICT 절을 사용하면 중복 발생 시 INSERT를 무시하거나, 기존 행을 업데이트하는 방식으로 우아하게 처리할 수 있습니다.

-- 예시 테이블
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    username VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 중복 시 무시 (DO NOTHING)
INSERT INTO users (email, username)
VALUES ('hong@example.com', '홍길동')
ON CONFLICT (email) DO NOTHING;

-- 중복 시 업데이트 (DO UPDATE)
INSERT INTO users (email, username)
VALUES ('hong@example.com', '홍길동_수정')
ON CONFLICT (email) 
DO UPDATE SET 
    username = EXCLUDED.username,
    created_at = NOW();

-- EXCLUDED 키워드는 삽입하려 했던 신규 행의 값을 참조합니다.

원인 2 해결: 배치 작업 전 중복 제거 및 INSERT ... ON CONFLICT 적용

-- 소스 데이터에서 중복 제거 후 삽입 (ROW_NUMBER 활용)
INSERT INTO orders (order_no, customer_id, amount)
SELECT order_no, customer_id, amount
FROM (
    SELECT order_no, customer_id, amount,
           ROW_NUMBER() OVER (PARTITION BY order_no ORDER BY created_at DESC) AS rn
    FROM staging_orders
) sub
WHERE rn = 1
ON CONFLICT (order_no) DO NOTHING;

-- 이미 존재하는 데이터 확인 후 삽입 대상만 필터링
INSERT INTO orders (order_no, customer_id, amount)
SELECT s.order_no, s.customer_id, s.amount
FROM staging_orders s
LEFT JOIN orders o ON s.order_no = o.order_no
WHERE o.order_no IS NULL;

원인 3 해결: 시퀀스 값 재동기화

-- 현재 테이블의 최대 ID 값 확인
SELECT MAX(id) FROM users;

-- 시퀀스를 현재 최대값 이후로 재설정
-- setval의 세 번째 인수 'true'는 다음 호출 시 max_id + 1을 반환하게 함
SELECT setval('users_id_seq', (SELECT MAX(id) FROM users), true);

-- 시퀀스 현재 값 확인
SELECT currval('users_id_seq');
SELECT nextval('users_id_seq');

-- pg_sequences 뷰로 모든 시퀀스 상태 점검
SELECT schemaname, sequencename, last_value, max_value
FROM pg_sequences
WHERE schemaname = 'public';

에러 발생 위치 확인 방법

-- 에러 발생 시 제약 조건 이름 확인 (psql 에서)
-- 에러 메시지 예시:
-- ERROR: duplicate key value violates unique constraint "users_email_key"
-- DETAIL: Key (email)=(hong@example.com) already exists.

-- 특정 컬럼의 중복 여부 사전 점검
SELECT email, COUNT(*) AS cnt
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- 제약 조건 목록 확인
SELECT conname, contype, pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'users'::regclass
  AND contype = 'u';

---

예방 방법

1. 애플리케이션과 DB 양쪽에 방어 로직 구현 (Defense in Depth)

애플리케이션 레벨에서 중복 체크 로직을 구현하되, 이것만 믿지 말고 DB 레벨에서도 반드시 UNIQUE 제약 조건을 유지해야 합니다. Race Condition은 애플리케이션 레벨 체크만으로는 절대 100% 방어할 수 없으므로, 모든 INSERT/UPDATE 쿼리는 ON CONFLICT 절을 기본적으로 포함하는 코드 패턴을 팀 표준으로 정립하는 것이 좋습니다. 또한 트랜잭션 격리 수준(Isolation Level)을 적절히 설정하고, 필요 시 SELECT FOR UPDATE를 통해 명시적 잠금을 사용하는 것도 효과적입니다.

2. 데이터 파이프라인에 멱등성(Idempotency) 보장 설계

배치 작업, ETL, 마이그레이션 스크립트는 반드시 재실행해도 결과가 동일하도록 멱등성을 가져야 합니다. 모든 INSERT 문에 ON CONFLICT DO NOTHING 또는 ON CONFLICT DO UPDATE를 기본으로 적용하고, 작업 전 스테이징 테이블에서 ROW_NUMBER()나 DISTINCT ON을 이용해 중복을 제거하는 전처리 단계를 필수화하세요. 또한 CI/CD 파이프라인에 데이터 품질 검증 쿼리(중복 건수 체크 등)를 포함시켜 운영 DB 반영 전에 사전 탐지하는 체계를 갖추는 것을 강력히 권장합니다.

---

관련 에러

• 23000 integrity_constraint_violation: 무결성 제약 조건 위반의 상위 범주 에러로, 23505를 포함한 다양한 제약 위반의 부모 에러 코드입니다.
• 23502 not_null_violation: NOT NULL 제약 조건 위반으로, 필수 컬럼에 NULL 값을 삽입하려 할 때 발생합니다.
• 23503 foreign_key_violation: 외래 키 제약 조건 위반으로, 참조 대상 행이 존재하지 않을 때 발생합니다.
• 23514 check_violation: CHECK 제약 조건 위반으로, 컬럼에 정의된 조건식을 만족하지 못하는 값을 삽입할 때 발생합니다.
• 40001 serialization_failure: 직렬화 실패로, SERIALIZABLE 격리 수준에서 동시 트랜잭션 충돌 시 발생하며 23505와 함께 동시성 문제 디버깅 시 자주 함께 등장합니다.

본 포스트는 AI가 생성한 기술 가이드입니다. 운영 환경 적용 전 충분한 검토를 권장합니다.