PostgreSQL은 일반적으로 고유 열에 여러 SQL NULL 값을 허용합니다. 예상치 못한 중복 키 오류를 진단하려면 정확한 인덱스를 식별하고, 실제로 전송된 값을 확인한 다음, 인덱스 정의가 null 비교 방식을 바꾸는지 살펴보세요. 고유 인덱스

이 안내는 Mac에서 psql 또는 node-postgres를 사용해 아티클을 삽입하는 과정에서 문제를 디버깅하는 사람을 위한 것입니다. 가상의 아티클 행을 사용하며, 기본 동작을 재현하고 실패한 규칙을 추적한 다음 적절한 해결 방법을 선택하는 진단 순서를 제공합니다.

null 허용 고유 열은 실제로 무엇을 허용하나요?

null 허용 열에는 NULL이 들어갈 수 있으며, 고유 제약 조건은 어떤 조합이 반복될 수 있는지 결정합니다. 일반적인 UNIQUE (slug)에서는 중복된 비-null 슬러그가 거부되지만, null 값의 반복은 허용됩니다. 일반적인 UNIQUE (section, slug)에서는 제약 조건이 적용되는 열 중 하나라도 null이면 해당 제약 조건 아래에서 다른 점이 같은 조합을 반복할 수 있습니다. 테이블에 설정된 다른 고유 규칙은 계속 적용됩니다. 제약 조건

PostgreSQL 15에서는 고유성 검사 시 null을 서로 같은 값으로 취급하는 NULLS NOT DISTINCT가 도입되었습니다. 복합 키에서는 전체 조합이 일치해야 합니다. ('notes', NULL)는 다른 ('notes', NULL)와 충돌하지만, ('guides', NULL)과는 충돌하지 않습니다. PostgreSQL 14는 이 절을 지원하지 않습니다. 릴리스 15, 고유 인덱스

실제 규칙 또는 입력 반복된 값으로 중복 키 오류가 발생할 수 있나요?
SQL NULL이 있는 일반 UNIQUE (slug) 이 규칙만으로는 불가능
UNIQUE NULLS NOT DISTINCT (slug) PostgreSQL 15 이상에서는 가능
''이 있는 일반 UNIQUE (slug) 가능. 빈 문자열입니다
'NULL'이 있는 일반 UNIQUE (slug) 가능. 텍스트입니다
같은 행의 다른 고유 키 null 허용 슬러그와 관계없이 가능

SQL NULL, 빈 문자열, 텍스트 'NULL'은 서로 다른 입력입니다. PostgreSQL의 비교 문서는 null과 일반 텍스트 값을 명확히 구분합니다. null 여부를 검사할 때는 IS NULL을 사용하세요. 비교 함수와 연산자

Mac Terminal에서 기본 동작 재현하기

기존 PostgreSQL 연결과 Terminal에서 사용할 수 있는 psql 명령이 필요합니다. 평소 사용하는 연결 설정으로 세션을 여세요. 예를 들어 로컬 데이터베이스 이름이 postgres라면 다음을 실행합니다. -d 옵션은 데이터베이스를 지정합니다. psql

psql -d postgres

버전별 구문을 선택하기 전에 연결된 서버의 버전을 확인하세요. server_version는 서버 버전을 표시하며, NULLS NOT DISTINCT 지원 여부를 판단하는 기준입니다. 사전 설정 옵션

SHOW server_version;

새 세션에서 이 전체 기준 예제를 한 번 실행하세요. 일반 고유 제약 조건은 PostgreSQL 14 이상에서 동작하며, 임시 데모 테이블만 생성합니다.

CREATE TEMP TABLE article_probe (
    article_id integer PRIMARY KEY,
    title text NOT NULL,
    slug text,
    CONSTRAINT article_probe_slug_key UNIQUE (slug)
);

INSERT INTO article_probe (article_id, title, slug)
VALUES
    (1, 'Paper shapes', NULL),
    (2, 'Color arrangements', NULL),
    (3, 'Desk sketches', ''),
    (4, 'Pattern notes', 'NULL');

SELECT article_id, title, slug,
       slug IS NULL AS is_sql_null,
       slug = '' AS is_empty_string
FROM article_probe
ORDER BY article_id;

표시된 정의에서는 네 번의 삽입이 모두 성공해야 합니다. 처음 두 행에는 is_sql_null = true이 있고, 세 번째에는 is_empty_string = true가 있으며, 네 번째에는 리터럴 텍스트가 들어 있습니다. null과 관련된 비교 결과 자체가 null일 수 있으므로, 결과를 해석할 때는 명시적인 null 여부 열을 사용하세요. 제약 조건, 비교 함수와 연산자

이 기준 예제는 유용한 판단 기준이 됩니다. 이 예제는 성공하는데 애플리케이션에서 실패한다면 애플리케이션의 실제 테이블 정의와 제출한 값을 기준 예제와 비교하세요. 열이 null을 허용한다는 사실만으로는 실패한 규칙을 특정할 수 없습니다.

오류를 일으킨 정확한 규칙 찾기

먼저 PostgreSQL에서 unique_violation을 나타내는 코드인 SQLSTATE 23505을 확인하세요. 오류 메시지의 문구를 일치시키기보다는 코드를 확인합니다. 그런 다음 오류에 스키마, 테이블, 제약 조건, 상세 정보가 보고되었다면 이를 기록하세요. PostgreSQL은 인덱스를 CREATE UNIQUE INDEX로 직접 생성했더라도 오류의 제약 조건 이름 필드에 인덱스를 제약 조건으로 취급합니다. PostgreSQL 오류 코드, 오류 및 알림 메시지 필드

기준 예제를 실행한 것과 같은 psql 세션에서 이 진단 쿼리로 인덱스를 살펴보세요. pg_indexes 뷰는 각 인덱스의 스키마, 이름, 재구성된 정의를 제공합니다. 쿼리를 수정할 때는 오류에 보고된 테이블과 스키마를 모두 지정하세요. pg_indexes

SELECT schemaname, tablename, indexname, indexdef
FROM pg_indexes
WHERE tablename = 'article_probe'
ORDER BY schemaname, indexname;

오류에 나온 이름을 시작으로 모든 고유 정의를 확인하세요. 중복된 article_id는 slug이 null이어도 기본 키 위반입니다. 기본 키 값은 고유해야 하며 null일 수 없습니다. 행의 다른 열이 null을 허용하더라도 이 요구 사항은 달라지지 않습니다. 제약 조건

기준 예제와 다른 다음 세 가지를 찾아보세요.

  • null 동일성: NULLS NOT DISTINCT는 반복되는 null 키를 의도적으로 충돌시킵니다.
  • 표현식: 고유성은 저장된 열이 아니라 계산된 값에 적용될 수 있습니다.
  • 조건식: 부분 인덱스는 WHERE 조건을 충족하는 행에만 규칙을 적용합니다.

표현식 인덱스라면 충돌을 조사할 때 인덱싱된 표현식을 재현하세요. 예를 들어 lower(slug)에 대한 인덱스는 대소문자만 다른 두 비-null 슬러그를 거부할 수 있습니다. COALESCE(slug, '')에 대한 인덱스는 null과 빈 문자열을 모두 동일한 인덱싱 값으로 변환하므로 두 입력이 충돌할 수 있습니다. 이는 표현식 인덱스의 고유성과 COALESCE의 문서화된 동작에 따른 것입니다. 표현식 인덱스, 조건식

부분 고유 인덱스도 주의 깊게 살펴봐야 합니다. WHERE slug IS NULL 조건과 함께 section에 생성된 인덱스는 슬러그가 null인 행 중 섹션별로 일치하는 행 하나만 허용합니다. 이 인덱스는 slug 자체의 고유성을 보장하지 않습니다. PostgreSQL 문서에 따르면 부분 고유 인덱스는 조건식이 선택한 하위 집합 안에서 고유성을 보장합니다. 부분 인덱스

node-postgres가 실제로 전송하는 값 확인하기

node-postgres는 JavaScript null와 undefined 쿼리 매개변수를 모두 SQL null로 변환합니다. 빈 문자열과 문자열 'NULL'은 일반 텍스트 입력으로 유지됩니다. null 매개변수를 제공한 경우와 SQL 열을 생략한 경우도 구분하세요. 생략된 열에는 선언된 기본값이 들어가며, 기본값이 없으면 null이 들어갑니다. 쿼리, INSERT

다음 전체 프로그램은 이러한 차이를 보여줍니다. Node.js, npm, 기존 PostgreSQL 연결 설정이 필요합니다. 작업 폴더에서 의존성을 설치하고 프로그램을 diagnose-null.mjs이라는 이름으로 저장한 다음 실행하세요. 클라이언트는 표준 PostgreSQL 연결 환경 변수를 사용합니다. 연결

npm install pg

import pg from 'pg';

const { Client } = pg;
const client = new Client();

async function main() {
  await client.connect();
  try {
    await client.query(`
      CREATE TEMP TABLE article_node_probe (
        title text NOT NULL,
        slug text DEFAULT 'untitled',
        CONSTRAINT article_node_probe_slug_key UNIQUE (slug)
      )
    `);

    const samples = [
      { title: 'Paper shapes', slug: null },
      { title: 'Color arrangements', slug: undefined },
      { title: 'Desk sketches', slug: '' },
      { title: 'More sketches', slug: '' },
      { title: 'Pattern notes', slug: 'NULL' },
      { title: 'More patterns', slug: 'NULL' },
    ];

    for (const sample of samples) {
      console.log('Input:', {
        title: sample.title,
        type: typeof sample.slug,
        value: sample.slug,
      });

      try {
        const result = await client.query(
          `INSERT INTO article_node_probe (title, slug)
           VALUES ($1, $2)
           RETURNING title, slug, slug IS NULL AS is_sql_null`,
          [sample.title, sample.slug],
        );
        console.log('Inserted:', result.rows[0]);
      } catch (error) {
        if (error.code !== '23505') throw error;
        console.log('Duplicate:', {
          code: error.code,
          schema: error.schema,
          table: error.table,
          constraint: error.constraint,
          detail: error.detail,
        });
      }
    }
  } finally {
    await client.end();
  }
}

main().catch((error) => {
  console.error(error);
  process.exitCode = 1;
});

PGDATABASE=postgres node diagnose-null.mjs

이 프로그램의 스키마에서는 기본값이 선언되어 있어도 처음 두 행에 SQL null이 저장되어야 합니다. 처음 삽입하는 빈 문자열과 'NULL' 문자열은 성공하고, 같은 값을 다시 삽입하면 23505이 발생해야 합니다. 프로그램은 예상된 충돌을 보고하고 그 밖의 오류는 다시 발생시킵니다. 진단 속성 이름은 node-postgres의 DatabaseError 필드와 일치합니다. 쿼리, DatabaseError 소스

데이터베이스 예제는 검색한 문서를 바탕으로 검토했지만, 작성 과정에서 PostgreSQL에 실행하지는 않았습니다.

일치하는 행을 빠뜨리지 않고 null 허용 키 검색하기

slug = NULL을 사용하는 진단 조회로는 null 슬러그를 찾을 수 없습니다. null만 검색할 때는 IS NULL을 사용하고, 값이 null일 수도 있고 아닐 수도 있을 때는 IS NOT DISTINCT FROM을 사용하세요. 후자는 비교 시 두 null을 같은 값으로 취급합니다. 비교 함수와 연산자

이 진단 쿼리는 같은 세션에서 앞서 만든 article_probe 테이블을 대상으로 실행됩니다. null 슬러그가 있는 두 행이 반환되어야 합니다.

SELECT article_id, title, slug
FROM article_probe
WHERE slug IS NOT DISTINCT FROM NULL::text
ORDER BY article_id;

매개변수화된 조회에 해당하는 조건식은 WHERE slug IS NOT DISTINCT FROM $1::text입니다. 복합 규칙에서는 모든 키 구성 요소를 일치시키고, 인덱싱된 표현식이나 부분 인덱스 조건식도 재현하세요. 일치하는 null 행을 찾으면 저장된 값을 확인할 수 있지만, 그 값이 고유성 위반인지 여부는 여전히 인덱스 정의에 달려 있습니다.

의도한 규칙에 맞는 해결 방법 선택하기

아티클에 누락된 슬러그를 여러 개 허용해도 된다면 일반 UNIQUE (slug)만으로 이미 그 요구 사항을 표현할 수 있습니다. 잘못 들어간 텍스트 값, 의도하지 않은 표현식, 또는 오류에서 확인한 다른 중복 키를 바로잡으세요. null 처리 방식을 바꿔도 다른 키에서 발생한 충돌은 해결되지 않습니다.

요구 사항이 섹션마다 null 슬러그를 하나만 허용하는 것이라면 PostgreSQL 15 이상에서 이를 직접 표현할 수 있습니다. 다음 독립 예제의 마지막 삽입은 23505을 발생시키도록 의도되어 있습니다. 첫 번째 행이 이미 해당 섹션과 null 조합을 차지하고 있기 때문입니다. 릴리스 15, 고유 인덱스

CREATE TEMP TABLE article_one_missing (
    section text NOT NULL,
    slug text,
    title text NOT NULL,
    CONSTRAINT article_one_missing_key
        UNIQUE NULLS NOT DISTINCT (section, slug)
);

INSERT INTO article_one_missing (section, slug, title)
VALUES ('notes', NULL, 'Paper shapes');

-- Expected: 23505 on article_one_missing_key.
INSERT INTO article_one_missing (section, slug, title)
VALUES ('notes', NULL, 'Color arrangements');

PostgreSQL 14에서는 이 2열 설계에 부분 고유 인덱스를 사용하세요. 다음 독립 예제는 section이 null이 아니고 slug이 유일하게 null을 허용하는 키 구성 요소이므로 같은 규칙을 구현합니다. 이 방법은 PostgreSQL의 부분 인덱스 동작에 관한 문서를 바탕으로 구성한 것입니다. 부분 인덱스

CREATE TEMP TABLE article_legacy (
    section text NOT NULL,
    slug text,
    title text NOT NULL
);

CREATE UNIQUE INDEX article_legacy_present_key
ON article_legacy (section, slug)
WHERE slug IS NOT NULL;

CREATE UNIQUE INDEX article_legacy_missing_key
ON article_legacy (section)
WHERE slug IS NULL;

INSERT INTO article_legacy (section, slug, title)
VALUES ('notes', NULL, 'Paper shapes');

-- Expected: 23505 on article_legacy_missing_key.
INSERT INTO article_legacy (section, slug, title)
VALUES ('notes', NULL, 'Color arrangements');

첫 번째 인덱스는 섹션 내에서 값이 채워진 슬러그의 반복을 막습니다. 두 번째 인덱스는 섹션별로 누락된 슬러그 하나를 허용합니다. null을 허용하는 키 구성 요소가 더 많다면 가능한 조합을 추가로 고려해야 합니다. 이 두 인덱스 구성은 명시된 스키마에만 해당합니다.

마지막으로 충돌 처리 방식을 명시적으로 선택하세요. ON CONFLICT DO NOTHING은 충돌하는 삽입을 건너뛰며, 근본적인 값 문제를 수정하지는 않습니다. ON CONFLICT DO UPDATE에는 충돌 대상이 필요하며, 해당 대상의 열, 표현식, 적용 가능한 조건식이 의도한 규칙을 식별해야 합니다. 어느 동작도 일반 고유성이 반복되는 null을 허용하는 경우에 null 충돌을 만들어 내지 않습니다. INSERT

오류에 보고된 인덱스 이름을 정의와 연결하고, 제출된 키 값을 재현하며, 해당 규칙에서 왜 충돌하는지 설명할 수 있으면 진단이 완료된 것입니다. 수정한 뒤에는 동일한 입력을 다시 시도하고 반환된 결과와 저장된 행을 모두 확인하여 의도한 아티클이 처리되었는지 확인하세요.

출처