존재하는 경우 PostgreSQL 삽입 또는 업데이트

키가 새 값이면 행을 삽입하고 이미 있으면 업데이트하려면 PostgreSQL의 INSERT ... ON CONFLICT (UPSERT)를 사용합니다. 충돌 대상은 기본 키와 같은 고유 제약 조건이나 고유 인덱스와 일치해야 합니다. 예를 들어 id가 기본 키인 users 테이블에서 INSERT INTO users (id, name, gender, age) VALUES (12, 'John Kane', 'Male', 24) ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, gender = EXCLUDED.gender, age = EXCLUDED.age;를 실행하면 사용자 12가 새로 삽입되거나, 이미 있는 행의 이름, 성별, 나이가 바뀝니다.

아래 예제는 다음 스키마를 가정합니다: CREATE TABLE users (id integer PRIMARY KEY, name text NOT NULL, gender text NOT NULL, age integer NOT NULL);. SQL 예제는 PostgreSQL 서버가 필요하므로 본문에 인라인으로 표시하며, Python 예제는 삽입할지 업데이트할지 결정하는 과정을 간단히 보여 줄 뿐입니다.

다음 예제를 실행합니다:

users = {}

def upsert(user):
    key = user["id"]
    if key in users:
        users[key].update(user)
    else:
        users[key] = user.copy()

upsert({"id": 12, "name": "John Kane", "age": 23})
upsert({"id": 12, "name": "John Kane", "age": 24})
print(users[12])
print("rows:", len(users))

{'id': 12, 'name': 'John Kane', 'age': 24}
rows: 1

이 Python 코드는 결정 과정을 설명하기 위한 예시일 뿐, PostgreSQL에 연결하거나 SQL을 실행하지 않습니다. SQL 패턴은 데이터베이스 예제이므로 앞서 제시한 스키마의 테이블에서 실행해야 합니다. 변경되지 않은 코드와 출력에는 rows: 레이블이 영어로 표시됩니다.

UPDATE 후 INSERT로 행이 있으면 삽입하거나 업데이트하기

업데이트 후 조건에 따라 삽입하는 방식은 두 개의 문장으로 구성됩니다. 먼저 UPDATE users SET name = 'John Kane', gender = 'Male', age = 21 WHERE id = 12 RETURNING id;를 실행합니다. 행이 반환되면 기존 사용자가 업데이트된 것입니다. 행이 반환되지 않으면 INSERT INTO users (id, name, gender, age) VALUES (12, 'John Kane', 'Male', 21);를 실행합니다.

이 문장을 실행하는 애플리케이션이나 트랜잭션은 UPDATE ... RETURNING의 결과를 보고 INSERT를 실행할지 결정해야 합니다. UPDATE와 INSERT를 따로 실행하는 방식은 원자적인 UPSERT가 아닙니다. 두 문장 사이에 다른 트랜잭션이 같은 키를 삽입하면 고유 키 충돌이 발생할 수 있습니다. 동시 쓰기를 처리해야 하는 일반적인 삽입 또는 업데이트 작업에는 아래의 INSERT ... ON CONFLICT를 우선 사용하세요.

INSERT ... ON CONFLICT (UPSERT)로 행이 있으면 삽입하거나 업데이트하기

INSERT ... ON CONFLICT는 삽입을 시도하고, 같은 문장 안에서 고유성 충돌을 처리합니다. 충돌 대상은 사용할 수 있는 고유 제약 조건이나 인덱스를 지정해야 합니다. 이 예제에서는 id가 기본 키이며, EXCLUDED는 삽입하려고 했던 행을 가리킵니다.

INSERT INTO users (id, name, gender, age) VALUES (12, 'John Kane', 'Male', 24) ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, gender = EXCLUDED.gender, age = EXCLUDED.age;를 사용하면 행을 삽입하거나 id = 12인 행이 이미 있을 때 이름, 성별, 나이를 바꿀 수 있습니다. 일부 필드만 변경하려면 DO UPDATE SET 뒤에 해당 필드만 나열합니다. 예를 들어 DO UPDATE SET age = EXCLUDED.age를 지정하면 기존 이름과 성별은 그대로 유지됩니다.

수행되는 작업에 따라 결과가 달라집니다. 새 키이면 입력한 행을 삽입하고, 기존 키이면 DO UPDATE SET에 지정한 필드를 업데이트합니다. 충돌이 발생해도 기존 행을 그대로 두려면 DO NOTHING을 사용할 수도 있습니다. 커뮤니티가 관리하는 배경 자료는 PostgreSQL Wiki의 UPSERT 페이지를 참고하세요.

공통 테이블 식(CTE)으로 행이 있으면 삽입하거나 업데이트하기

데이터를 변경하는 CTE를 사용하면 UPDATE의 RETURNING 결과와 조건부 INSERT를 연결할 수 있습니다. 예를 들면 다음과 같습니다: WITH updated AS (UPDATE users SET name = 'John Kane', gender = 'Male', age = 25 WHERE id = 12 RETURNING id) INSERT INTO users (id, name, gender, age) SELECT 12, 'John Kane', 'Male', 25 WHERE NOT EXISTS (SELECT 1 FROM updated);.

이 문장에서 UPDATE가 행을 반환하지 않을 때만 INSERT가 실행됩니다. 업데이트와 대체 삽입을 하나의 SQL 문장으로 묶어야 할 때 유용할 수 있지만, 동시 쓰기 상황에서 ON CONFLICT를 대신하지는 않습니다. 동시에 실행된 삽입으로 고유 키 위반이 발생할 수 있으므로, 일반적인 충돌 안전 UPSERT 동작이 필요하면 INSERT ... ON CONFLICT를 사용하세요.

PL/pgSQL 함수로 행이 있으면 삽입하거나 업데이트하기

PL/pgSQL 함수에 권장하는 충돌 처리 문장을 담아 재사용할 수 있습니다. 매개변수 이름은 열 이름과 혼동되지 않도록 지정하세요. 예를 들어 함수 본문에서 매개변수 p_id integer, p_name text, p_gender text, p_age integer를 사용해 INSERT INTO users (id, name, gender, age) VALUES (p_id, p_name, p_gender, p_age) ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, gender = EXCLUDED.gender, age = EXCLUDED.age;를 실행할 수 있습니다.

CREATE OR REPLACE FUNCTION upsert_user(p_id integer, p_name text, p_gender text, p_age integer) RETURNS void LANGUAGE plpgsql AS $$ BEGIN INSERT INTO users (id, name, gender, age) VALUES (p_id, p_name, p_gender, p_age) ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, gender = EXCLUDED.gender, age = EXCLUDED.age; END; $$;로 함수를 정의한 다음 SELECT upsert_user(13, 'New User', 'Female', 30);으로 호출합니다. 이렇게 하면 동일한 고유 키 충돌 처리를 데이터베이스에 맡길 수 있으며, 별도의 UPDATE 후 INSERT 방식에서 발생하는 경쟁 조건을 감수할 필요가 없습니다.

결론

일반적인 조건부 쓰기에는 기본 키나 적절한 고유 제약 조건을 충돌 대상으로 지정한 INSERT ... ON CONFLICT (UPSERT)를 사용하세요. 새 행을 삽입하고 해당 키가 이미 있으면 선택한 필드를 적용합니다. UPDATE 후 INSERT와 UPDATE 후 조건부 INSERT를 수행하는 CTE는 두 가지 결과를 설명하는 데 유용하지만, 동시 충돌 처리는 동일하게 제공하지 않습니다. 데이터베이스 안에서 충돌 처리 작업을 재사용하려면 PL/pgSQL 함수를 사용할 수 있습니다.