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

23505 unique violation 는?

PostgreSQL 에러 코드 23505 unique violation은 테이블에 정의된 유니크 제약 조건(UNIQUE Constraint) 또는 기본 키(PRIMARY KEY)를 위반하는 데이터를 삽입하거나 업데이트하려 할 때 발생합니다. 즉, 이미 존재하는 값과 동일한 값을 유니크 컬럼에 저장하려는 시도가 감지되면 PostgreSQL은 해당 트랜잭션을 즉시 중단시키고 이 에러를 반환합니다. 실무에서는 회원가입 시 중복 이메일 입력, 주문 번호 중복 생성, 배치 작업 중 데이터 재삽입 등 다양한 상황에서 빈번하게 발생하는 에러입니다.

---

주요 발생 원인

1. 중복 데이터 삽입 (INSERT 충돌)

가장 흔한 원인으로, 애플리케이션 레벨에서 중복 체크 없이 동일한 값을 가진 레코드를 INSERT하려 할 때 발생합니다. 특히 멀티 스레드 환경이나 동시 요청이 많은 웹 서비스에서, 두 개 이상의 트랜잭션이 동시에 같은 값을 삽입하려 하면 한쪽은 성공하고 다른 한쪽은 이 에러를 반환받게 됩니다.

2. 시퀀스(Sequence) 또는 AUTO INCREMENT 충돌

PostgreSQL의 SERIAL, BIGSERIAL, 또는 SEQUENCE를 사용하는 기본 키 컬럼에서 시퀀스 값이 실제 테이블 데이터와 동기화되지 않을 때 발생합니다. 예를 들어 외부 도구로 데이터를 대량 이관하면서 기본 키 값을 직접 지정한 경우, 이후 새로운 레코드를 삽입할 때 시퀀스가 이미 존재하는 ID 값을 생성하여 충돌이 일어날 수 있습니다.

3. UPSERT 미사용 또는 잘못된 배치 처리

배치 작업이나 데이터 마이그레이션 과정에서 동일한 데이터를 여러 번 처리하거나, 이미 존재하는 데이터를 다시 INSERT하는 로직이 포함될 때 발생합니다. INSERT ... ON CONFLICT 구문을 사용하지 않고 단순 INSERT만 반복 실행하는 배치 스크립트가 대표적인 사례이며, 이는 대량 데이터 처리 환경에서 특히 치명적입니다.

---

해결 방법

원인 1: 중복 데이터 삽입 해결 — ON CONFLICT DO NOTHING 또는 ON CONFLICT DO UPDATE

삽입 전 중복 여부를 애플리케이션에서 체크하는 것보다 PostgreSQL의 UPSERT 기능을 활용하는 것이 훨씬 안전하고 효율적입니다.

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

-- 방법 1: 충돌 시 무시 (중복이면 아무 작업도 하지 않음)
INSERT INTO users (email, name)
VALUES ('hong@example.com', '홍길동')
ON CONFLICT (email) DO NOTHING;

-- 방법 2: 충돌 시 업데이트 (UPSERT)
INSERT INTO users (email, name)
VALUES ('hong@example.com', '홍길동 수정')
ON CONFLICT (email)
DO UPDATE SET
    name = EXCLUDED.name,
    created_at = NOW();

-- 방법 3: 삽입 전 존재 여부 확인 후 처리 (트랜잭션 내에서)
BEGIN;
  SELECT id FROM users WHERE email = 'hong@example.com' FOR UPDATE;
  -- 결과가 없을 때만 INSERT 수행
  INSERT INTO users (email, name)
  SELECT 'hong@example.com', '홍길동'
  WHERE NOT EXISTS (
      SELECT 1 FROM users WHERE email = 'hong@example.com'
  );
COMMIT;

---

원인 2: 시퀀스 동기화 문제 해결

외부 데이터 이관 후 시퀀스가 맞지 않는 경우, 시퀀스를 현재 테이블의 최대 ID 값으로 재설정해야 합니다.

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

-- 시퀀스 현재 값 확인
SELECT last_value FROM users_id_seq;

-- 방법 1: setval로 시퀀스를 최대 ID + 1로 재설정
SELECT setval('users_id_seq', (SELECT MAX(id) FROM users));

-- 방법 2: 시퀀스를 테이블 최대값에 맞게 자동 재설정
-- (PostgreSQL 10 이상 IDENTITY 컬럼 사용 시)
ALTER SEQUENCE users_id_seq RESTART WITH 1001;

-- 방법 3: 시퀀스 이름을 동적으로 찾아서 재설정
SELECT setval(
    pg_get_serial_sequence('users', 'id'),
    COALESCE((SELECT MAX(id) FROM users), 0) + 1,
    false
);

---

원인 3: 배치 처리 중 중복 발생 해결

배치 작업 시 임시 테이블을 활용하거나 UPSERT 패턴을 적용하여 안전하게 처리합니다.

-- 임시 스테이징 테이블을 활용한 배치 UPSERT 패턴
CREATE TEMP TABLE staging_users (
    email   VARCHAR(255),
    name    VARCHAR(100)
);

-- 스테이징 테이블에 배치 데이터 로드
COPY staging_users (email, name)
FROM '/tmp/users_batch.csv'
DELIMITER ',' CSV HEADER;

-- 중복 없이 안전하게 UPSERT
INSERT INTO users (email, name)
SELECT email, name FROM staging_users
ON CONFLICT (email)
DO UPDATE SET
    name = EXCLUDED.name;

-- 처리 완료 후 스테이징 테이블 삭제
DROP TABLE staging_users;

-- 특정 컬럼 조합(복합 유니크)에서의 충돌 처리
CREATE TABLE order_items (
    order_id   INT,
    product_id INT,
    quantity   INT,
    UNIQUE (order_id, product_id)
);

INSERT INTO order_items (order_id, product_id, quantity)
VALUES (101, 55, 3)
ON CONFLICT (order_id, product_id)
DO UPDATE SET quantity = order_items.quantity + EXCLUDED.quantity;

---

예방 방법

1. 유니크 제약 조건을 명시적으로 정의하고 UPSERT 패턴을 표준화하라

테이블 설계 단계부터 유니크 제약 조건을 명확히 정의하고, 팀 전체의 INSERT 로직에서 ON CONFLICT 절을 표준 패턴으로 사용하도록 코딩 가이드라인을 수립해야 합니다. 특히 고트래픽 환경에서는 애플리케이션 레벨의 중복 체크보다 데이터베이스 레벨의 충돌 처리가 훨씬 신뢰성이 높으므로, ORM을 사용하더라도 반드시 UPSERT 옵션을 활성화하는 습관을 들여야 합니다.

-- 복합 유니크 인덱스를 명시적으로 생성하여 의도를 명확히
CREATE UNIQUE INDEX idx_users_email_unique
ON users (LOWER(email));  -- 대소문자 무관 유니크 처리

-- 부분 유니크 인덱스로 특정 조건에서만 유니크 보장
CREATE UNIQUE INDEX idx_active_users_email
ON users (email)
WHERE is_deleted = FALSE;

2. 데이터 이관 및 배치 작업 시 반드시 시퀀스 검증 절차를 포함하라

대규모 데이터 이관 작업 후에는 모든 시퀀스의 동기화 상태를 검증하는 스크립트를 배포 프로세스에 포함시켜야 합니다. 아래 쿼리를 이관 완료 체크리스트에 등록하면 시퀀스 충돌로 인한 장애를 사전에 방지할 수 있습니다.

-- 모든 시퀀스와 실제 데이터의 최대값 비교 쿼리
SELECT
    t.table_name,
    c.column_name,
    pg_get_serial_sequence(t.table_name, c.column_name) AS seq_name,
    (SELECT last_value FROM pg_sequences
     WHERE sequencename = REPLACE(
         pg_get_serial_sequence(t.table_name, c.column_name), 'public.', ''
     )) AS seq_last_value,
    (SELECT MAX(id) FROM users) AS table_max_id
FROM information_schema.tables t
JOIN information_schema.columns c ON t.table_name = c.table_name
WHERE c.column_default LIKE 'nextval%'
  AND t.table_schema = 'public';

---

관련 에러

• 23000 integrity_constraint_violation: 23505를 포함하는 상위 에러 클래스로, 무결성 제약 조건 위반 전체를 포괄합니다.
• 23502 not_null_violation: NOT NULL 제약 조건 위반으로, NULL 값을 NOT NULL 컬럼에 삽입할 때 발생합니다.
• 23503 foreign_key_violation: 외래 키 제약 조건 위반으로, 참조 대상이 없는 값을 삽입하거나 참조되는 레코드를 삭제할 때 발생합니다.
• 23514 check_violation: CHECK 제약 조건 위반으로, 컬럼에 정의된 유효성 조건을 통과하지 못할 때 발생합니다.
• 40001 serialization_failure: 동시성 환경에서 트랜잭션 직렬화 실패 시 발생하며, 유니크 위반과 함께 발생하는 경우가 많습니다.

---

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