PostgreSQL에서는 ON CONFLICT DO UPDATE를 일반적인 “고유 키 중 하나와 일치” 작업으로 사용할 수 없습니다. 기준 키 하나를 선택하거나, 트랜잭션 안에서 두 키를 모두 명시적으로 확인하고 서로 다른 두 행을 가리키는 입력은 거부하세요.

이 튜토리얼은 Mac의 Terminal에서 작업하는 개발자가 node-postgres를 사용해 두 번째 정책을 구현하도록 돕습니다. 어느 키도 일치하지 않으면 삽입하고, 정확히 하나의 글이 일치하면 업데이트하며, 키가 서로 다른 글을 가리키면 명시적 충돌을 반환합니다. 모든 행은 합성 데이터입니다.

SQL 예시는 가져온 최신 문서에서 제공하는 버전인 PostgreSQL 18을 대상으로 합니다. 구현은 즉시 적용되는 고유 제약 조건 두 개가 있는 일반 비파티션 테이블을 사용합니다. 다른 버전의 설명서 예시를 적용하기 전에 서버의 메이저 버전을 확인하세요. PostgreSQL 18 INSERT 문서

두 개의 고유 제약 조건을 하나의 충돌 대상으로 만들 수 없는 이유

글에는 외부 참조와 슬러그가 있다고 가정해 봅시다. 두 값은 각각 고유해야 합니다. 빈 데모 데이터베이스에서 다음 전체 설정을 시작점으로 삼으세요. setup.sql로 저장합니다.

CREATE TABLE articles (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    external_key text NOT NULL,
    slug text NOT NULL,
    title text NOT NULL,
    CONSTRAINT articles_external_key_key UNIQUE (external_key),
    CONSTRAINT articles_slug_key UNIQUE (slug)
);

INSERT INTO articles (external_key, slug, title)
VALUES
    ('feed-101', 'blue-notes', 'Blue notes'),
    ('feed-202', 'green-notes', 'Green notes');

이 선언은 서로 별개인 두 규칙을 적용합니다. 이를 UNIQUE (external_key, slug)으로 바꾸면 데이터 모델이 달라집니다. 두 값의 조합만 고유하면 되므로, 서로 다른 조합에서 외부 키와 슬러그가 각각 반복될 수 있습니다. 두 열을 모두 NOT NULL로 유지하면 이 예시에 nullable 키 동작이 끼어드는 것도 방지할 수 있습니다. PostgreSQL 제약 조건

DO UPDATE를 사용하려면 PostgreSQL에 충돌 대상이 필요합니다. 열 목록은 일치하는 고유 인덱스의 열을 나타내며 “두 열 중 하나”를 뜻하지 않습니다. 따라서 ON CONFLICT (external_key, slug)는 위의 서로 별개인 두 제약 조건을 중재자로 추론할 수 없습니다. 제약 조건의 이름을 지정하면 해당 제약 조건이 선택됩니다. 또한 구문에는 대체 처리기를 연이어 지정하는 방식이 아니라 ON CONFLICT 절 하나만 있습니다. PostgreSQL INSERT: ON CONFLICT 절

더 근본적인 문제는 기존 글 가운데 어느 것이 입력 행을 나타내는지 결정하는 것입니다. 입력 값이 feed-101와 green-notes이라고 해 봅시다. 외부 키는 파란색 글에 속하고, 슬러그는 초록색 글에 속합니다. 두 입력 키를 사용해 어느 행이든 업데이트하면 다른 행과 충돌합니다. 첫 번째 일치 항목을 선택하면 해결되지 않은 입력 결정을 감추게 됩니다.

모든 일치 조합에 대한 동작을 결정하세요

쿼리를 작성하기 전에 다음 결정 표를 사용하세요. 이 표는 스키마에서 도출한 제안된 애플리케이션 정책이며, PostgreSQL이 자동으로 제공하는 동작은 아닙니다.

입력 외부 키 입력 슬러그 서로 다른 일치 행 수 이 튜토리얼의 동작
사용되지 않음 사용되지 않음 0 글 삽입
글과 일치 사용되지 않음 1 해당 글 업데이트 및 슬러그 변경
사용되지 않음 글과 일치 1 해당 글 업데이트 및 외부 키 변경
글 A와 일치 글 A와 일치 1 글 A 업데이트
글 A와 일치 글 B와 일치 2 입력 거부, 두 글 모두 보존

한 개만 일치하는 경우에는 두 키 중 어느 쪽이든 글을 식별할 수 있고, 다른 키는 변경할 수 있도록 의도적으로 설정했습니다. 가져오기 작업에 필요한 동작인지 확인하세요. 외부 키가 절대 바뀌면 안 된다면 슬러그만 일치하는 경우는 애플리케이션 충돌로 처리해야 합니다.

실제 규칙이 더 간단해서 “외부 키가 항상 글을 결정”한다면 일반적인 대상 지정 업서트를 사용하세요. 다음 전체 문은 위 설정을 실행한 뒤 작동합니다.

INSERT INTO articles (external_key, slug, title)
VALUES ('feed-101', 'blue-notes', 'Revised blue notes')
ON CONFLICT (external_key) DO UPDATE
SET slug = EXCLUDED.slug,
    title = EXCLUDED.title
RETURNING id, external_key, slug, title;

여기서 EXCLUDED는 제안된 값을 제공합니다. 다른 고유 제약 조건도 여전히 적용됩니다. 제안된 슬러그를 green-notes로 바꾸면 다른 글을 선택하는 대신 별도의 고유성 충돌이 발생합니다. 대상 지정 업서트의 원자적 삽입 또는 업데이트 보장은 별도의 오류가 없을 때만 성립합니다. PostgreSQL INSERT 문서

동시 쓰기 중에도 어느 키를 기준으로 할지 안정적으로 결정하기

조회한 뒤 삽입하면 결정을 내리는 시점과 실제로 쓰는 시점 사이에 간격이 생깁니다. 그 사이에 다른 트랜잭션이 일치하는 키를 삽입할 수 있습니다. 조회에 FOR UPDATE를 추가하면 가져온 행은 잠기지만, 일치하는 행이 없을 때는 잠글 행도 없습니다. 따라서 이 특정한 무일치 경로에서는 행 잠금만으로 충분하지 않습니다. PostgreSQL 명시적 잠금

소규모 가져오기나 가끔 실행하는 데스크톱 작업에는 두 키를 확인하기 전에 SHARE ROW EXCLUSIVE 테이블 잠금을 획득하는 간단한 방법이 있습니다. 이 모드는 일반적인 삽입, 업데이트, 삭제가 획득하는 ROW EXCLUSIVE 잠금과 충돌하며, 같은 모드의 다른 잠금과도 충돌합니다. 일반 읽기는 계속 호환됩니다. PostgreSQL 테이블 수준 잠금 모드

그러면 트랜잭션을 시작하고, 테이블 잠금을 획득하고, 서로 다른 일치 행을 찾고, 결정 표에 따라 처리한 뒤, 커밋하는 순서가 됩니다. 명시적 잠금은 호환되지 않는 잠금이 풀릴 때까지 기다리며 트랜잭션이 끝날 때까지 유지됩니다. PostgreSQL LOCK

여기서는 READ COMMITTED를 명시적으로 사용하세요. 조회가 시작되기 전에 커밋된 데이터는 잠금 획득을 기다리는 동안 완료된 쓰기를 포함해 조회에 표시됩니다. 이 스냅샷 동작과 획득한 테이블 잠금을 함께 사용하면 결정을 내리고 쓸 때 안정적인 기준을 마련할 수 있습니다. PostgreSQL 트랜잭션 격리

이 잠금은 범위가 넓어 관련 없는 글 쓰기도 대기하게 됩니다. 트랜잭션을 짧게 유지하고, 잠금 획득부터 커밋까지 사용자 입력을 기다리거나 외부 작업을 하지 마세요. 동시 가져오기가 빈번하다면 이 설계를 채택하기 전에 기준 키 하나로 식별자를 정할 수 있는지 검토하세요.

node-postgres로 트랜잭션 구현하기

실행 중인 PostgreSQL 18 데모 데이터베이스, psql 명령, 그리고 설치한 node-postgres 패키지가 지원하는 Node.js 버전이 필요합니다. Terminal에서 패키지를 설치하고 설정 파일을 불러오세요. 첫 번째 명령은 데모 디렉터리에 패키지 메타데이터가 아직 없는 경우 이를 생성합니다. node-postgres 설치 및 호환성

npm init -y
npm install pg
psql -v ON_ERROR_STOP=1 -f setup.sql

이 명령들은 일반 로컬 연결 설정이 이미 데모 데이터베이스를 선택한다고 가정합니다. 프로그램은 new Pool()를 사용합니다. 이 명령은 psql과 동일한 PostgreSQL 연결 환경 변수를 읽습니다. 필요한 경우 PGDATABASE을 데모 데이터베이스 이름으로 설정하세요. node-postgres 연결

다음 전체 프로그램을 upsert-articles.mjs으로 저장하세요. 설정 테이블에 일치 또는 업데이트 동작을 바꾸는 추가 트리거나 규칙이 없다고 가정합니다. 값은 SQL 텍스트와 별도로 제공되는 위치 매개변수를 사용합니다. node-postgres 쿼리

import pg from 'pg';
const { Pool } = pg;
const pool = new Pool();

async function upsertArticle(input) {
  const client = await pool.connect();
  let discardClient = false;

  try {
    await client.query('BEGIN ISOLATION LEVEL READ COMMITTED');
    await client.query(
      'LOCK TABLE articles IN SHARE ROW EXCLUSIVE MODE'
    );

    const matches = await client.query(
      `SELECT id FROM articles
       WHERE external_key = $1 OR slug = $2
       ORDER BY id`,
      [input.externalKey, input.slug]
    );

    if (matches.rows.length > 1) {
      const error = new Error(
        'Incoming keys identify two different articles'
      );
      error.code = 'ARTICLE_KEY_CONFLICT';
      throw error;
    }

    const values = [input.externalKey, input.slug, input.title];
    const result = matches.rows.length === 0
      ? await client.query(
          `INSERT INTO articles (external_key, slug, title)
           VALUES ($1, $2, $3)
           RETURNING id, external_key, slug, title`,
          values
        )
      : await client.query(
          `UPDATE articles
           SET external_key = $1, slug = $2, title = $3
           WHERE id = $4
           RETURNING id, external_key, slug, title`,
          [...values, matches.rows[0].id]
        );

    if (result.rows.length !== 1) {
      throw new Error('Expected exactly one article');
    }

    await client.query('COMMIT');
    return result.rows[0];
  } catch (error) {
    try {
      await client.query('ROLLBACK');
    } catch {
      discardClient = true;
    }
    throw error;
  } finally {
    client.release(discardClient);
  }
}

try {
  console.log(await upsertArticle({
    externalKey: 'feed-101',
    slug: 'blue-notes',
    title: 'Revised blue notes'
  }));
} finally {
  await pool.end();
}

node-postgres의 요구 사항에 따라 모든 트랜잭션 문은 동일하게 체크아웃한 클라이언트를 사용합니다. 이 문들을 별도의 pool.query() 호출로 보내면 해당 연결 요구 사항을 지킬 수 없습니다. node-postgres 트랜잭션

finally 블록은 획득한 클라이언트를 항상 해제합니다. 롤백 자체가 실패하면 release(true)가 연결을 폐기합니다. 바깥쪽 pool.end()는 데스크톱 스크립트가 끝날 때 풀을 닫습니다. node-postgres Pool API

node upsert-articles.mjs로 프로그램을 실행하세요. 예상 결과는 제목이 수정되고 키는 그대로 유지된 파란색 글입니다. JavaScript 구문은 Node.js로 검사했으며, 데이터베이스 예시는 문서를 기준으로 검토했지만 여기서는 PostgreSQL 서버에서 실행하지 않았습니다.

각 분기를 확인하고 오류를 명시적으로 처리하기

설정된 행을 사용해 upsertArticle()에 전달하는 객체를 다음 사례에 맞게 바꾸세요. 각 사례는 원래 설정 상태에서 실행하거나, 앞선 사례로 인해 바뀐 내용을 고려하세요.

externalKey slug 예상 결과
feed-303 amber-notes 새 글 삽입
feed-101 blue-revised 파란색 글 업데이트 및 슬러그 교체
feed-303 blue-notes 파란색 글 업데이트 및 외부 키 교체
feed-101 blue-notes 파란색 글의 제목 업데이트
feed-101 green-notes ARTICLE_KEY_CONFLICT 발생, 어느 글도 변경하지 않음

조회 결과는 행이므로 두 조건에 모두 일치하는 글은 한 번만 집계됩니다. 서로 독립적인 이 두 개의 널이 아닌 고유 키에서는 서로 다른 행이 최대 두 개까지 일치할 수 있습니다. 코드는 LIMIT 1로 두 행 중 하나를 선택하거나, 글을 삭제하거나, 입력을 조용히 건너뛰지 않습니다.

ARTICLE_KEY_CONFLICT은 애플리케이션에서 정의한 코드입니다. PostgreSQL의 23505은 unique_violation를 뜻하며, 별개의 조건입니다. 예시를 적용한 뒤 후자가 발생하면 모든 중복을 같은 결과로 취급하지 말고 위반된 제약 조건과 추가된 테이블 동작을 확인하세요. PostgreSQL은 오류 메시지 문자열을 파싱하는 대신 오류 코드를 확인할 것을 권장합니다. PostgreSQL 오류 코드

잠금 획득은 대기할 수 있습니다. 이 데스크톱 작업에서 즉시 실패하게 하려면 잠금 문을 LOCK TABLE articles IN SHARE ROW EXCLUSIVE MODE NOWAIT로 바꾸세요. 그러면 PostgreSQL이 잠금을 바로 획득할 수 없을 때 오류를 발생시키고, 프로그램은 롤백 경로를 따릅니다. PostgreSQL LOCK

DO NOTHING이나 MERGE를 쓰면 작업이 간단해질까요?

대상 없는 ON CONFLICT DO NOTHING은 사용 가능한 고유 제약 조건 또는 인덱스 중 어느 하나와 충돌하는 삽입을 건너뛸 수 있습니다. 건너뛰기가 의도된 결과일 때 사용하세요. 여기서 설명한 업데이트 정책을 구현하지는 않습니다. PostgreSQL INSERT 문서

MERGE는 PostgreSQL 15에서 도입되었습니다. 키 중 어느 하나를 포함하는 조인 조건을 허용하지만, 그 조인에도 원본 행이 서로 다른 두 대상 글과 일치할 때의 정책이 필요합니다. 해당 작업도 일반 제약 조건의 적용을 받으며, 동시 실행 동작은 대상 지정 업서트와 동일하지 않습니다. PostgreSQL 15 릴리스 노트, PostgreSQL MERGE

어느 키든 일치할 수 있는 작업에서는 일치 개수를 명시적 결정 기준으로 삼으세요. 0개면 삽입하고, 1개면 업데이트하며, 2개면 유용한 충돌 정보를 반환하고 중단합니다. 고유 제약 조건은 유지하고 쓰기가 완료될 때까지 그 결정을 보존하는 동시성 전략을 선택하세요. 글 쓰기를 직렬화해도 괜찮다면 테이블 잠금 구현을 구체적인 시작점으로 삼을 수 있습니다.

출처