UPDATE — 테이블의 행 갱신

테이블에 저장된 기존 행들의 값을 조건에 맞춰 바꾸고 싶을 때 쓰는 명령이에요. 어떤 행을 바꿀지는 WHERE 조건으로 정하고, 어떤 컬럼을 새 값으로 채울지는 SET 절로 정해요.

출처: PostgreSQL 문서

본문

Synopsis

[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]
    SET { column_name = { expression | DEFAULT } |
          ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
          ( column_name [, ...] ) = ( sub-SELECT )
        } [, ...]
    [ FROM from_item [, ...] ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING [ WITH ( { OLD | NEW } AS output_alias [, ...] ) ]
                { * | output_expression [ [ AS ] output_name ] } [, ...] ]

설명 (Description)

UPDATE는 조건을 만족하는 모든 행에서 지정된 컬럼의 값을 변경해요. SET 절에서는 수정할 컬럼들만 언급하면 돼요. 명시적으로 수정되지 않은 컬럼은 이전 값을 유지해요.

다른 테이블에 들어 있는 정보를 사용해 테이블을 수정하는 방법은 두 가지가 있어요. 서브쿼리(sub-select)를 사용하거나, FROM 절에서 추가 테이블을 지정하는 방법이에요. 어느 기법이 더 적절한지는 구체적인 상황에 달려 있어요.

선택적 RETURNING 절은 UPDATE가 실제로 갱신된 각 행을 기반으로 값을 계산해 반환하게 해요. 테이블의 컬럼이나 FROM에 언급된 다른 테이블의 컬럼을 사용하는 어떤 표현식이든 계산할 수 있어요. 기본적으로 테이블 컬럼의 새(갱신 후) 값을 사용하지만, 이전(갱신 전) 값을 요청하는 것도 가능해요. RETURNING 목록의 구문은 SELECT의 출력 목록과 동일해요.

갱신 대상으로 나열된 컬럼에 대해 테이블의 UPDATE 권한이 있어야 해요. expressions나 condition에서 값을 읽는 어떤 컬럼에 대해서도 SELECT 권한이 있어야 해요.

매개변수 (Parameters)

with_query

WITH 절은 UPDATE 쿼리에서 이름으로 참조할 수 있는 하나 이상의 서브쿼리를 지정할 수 있게 해줘요. 자세한 내용은 7.8절과 SELECT 문서를 참고하면 돼요. table_name

갱신할 테이블의 이름(선택적으로 스키마로 한정됨)이에요. 테이블 이름 앞에 ONLY가 지정되면 일치하는 행이 이름이 지정된 테이블에서만 갱신돼요. ONLY가 지정되지 않으면 일치하는 행이 이름이 지정된 테이블에서 상속받는 모든 테이블에서도 갱신돼요. 선택적으로 테이블 이름 뒤에 *를 지정해 하위 테이블이 포함됨을 명시적으로 나타낼 수 있어요. alias

대상 테이블의 대체 이름이에요. 별칭이 제공되면 테이블의 실제 이름을 완전히 가려요. 예를 들어 UPDATE foo AS f가 주어지면, UPDATE 문의 나머지 부분은 이 테이블을 foo가 아니라 f로 참조해야 해요. column_name

table_name이 명명한 테이블의 컬럼 이름이에요. 필요하다면 컬럼 이름을 하위 필드 이름이나 배열 첨자로 한정할 수 있어요. 대상 컬럼 지정에 테이블 이름을 포함하지 마세요. 예를 들어 UPDATE table_name SET table_name.col = 1은 잘못된 문이에요. expression

컬럼에 할당할 표현식이에요. 이 표현식은 테이블의 이 컬럼과 다른 컬럼들의 이전 값을 사용할 수 있어요. DEFAULT

컬럼을 기본값으로 설정해요(특정 기본 표현식이 할당되지 않았다면 NULL이 됨). ID 컬럼(identity column)은 관련 시퀀스가 생성한 새 값으로 설정돼요. 생성 컬럼(generated column)에 대해 이것을 지정하는 것은 허용되지만, 단지 생성 표현식에서 컬럼을 계산하는 일반적인 동작을 지정할 뿐이에요. sub-SELECT

앞의 괄호로 둘러싼 컬럼 목록에 나열된 수만큼의 출력 컬럼을 생성하는 SELECT 서브쿼리예요. 서브쿼리는 실행 시 둘 이상의 행을 산출하면 안 돼요. 한 행을 산출하면 그 컬럼 값이 대상 컬럼에 할당되고, 행을 산출하지 않으면 대상 컬럼에 NULL 값이 할당돼요. 서브쿼리는 갱신 중인 테이블의 현재 행의 이전 값을 참조할 수 있어요. from_item

다른 테이블의 컬럼이 WHERE 조건과 갱신 표현식에 나타날 수 있게 하는 테이블 표현식이에요. SELECT 문의 FROM 절과 동일한 구문을 사용해요. 예를 들어 테이블 이름에 대한 별칭을 지정할 수 있어요. 셀프 조인(self-join)을 의도하지 않는 한 대상 테이블을 from_item으로 반복하지 마세요(그 경우에는 from_item에서 별칭과 함께 나타나야 해요). condition

boolean 타입의 값을 반환하는 표현식이에요. 이 표현식이 true를 반환하는 행만 갱신돼요. cursor_name

WHERE CURRENT OF 조건에서 사용할 커서의 이름이에요. 갱신될 행은 이 커서에서 가장 최근에 가져온(fetched) 행이에요. 커서는 UPDATE의 대상 테이블에 대한 비그룹화(non-grouping) 쿼리여야 해요. WHERE CURRENT OF는 부울 조건과 함께 지정할 수 없다는 점을 유의하세요. 커서를 WHERE CURRENT OF와 함께 사용하는 방법에 대한 자세한 내용은 DECLARE 문서를 참고하면 돼요. output_alias

RETURNING 목록에서 OLD 또는 NEW 행에 대한 선택적 대체 이름이에요.

기본적으로 대상 테이블의 이전 값은 OLD.column_name 또는 OLD.*로 작성해 반환할 수 있고, 새 값은 NEW.column_name 또는 NEW.*로 작성해 반환할 수 있어요. 별칭이 제공되면 이 이름들은 숨겨지고 이전/새 행은 별칭을 사용해 참조해야 해요. 예를 들어 RETURNING WITH (OLD AS o, NEW AS n) o.*, n.*처럼요. output_expression

각 행이 갱신된 후 UPDATE 명령이 계산해 반환할 표현식이에요. 표현식은 table_name이 명명한 테이블이나 FROM에 나열된 테이블의 어떤 컬럼 이름이든 사용할 수 있어요. 모든 컬럼을 반환하려면 *를 작성해요.

컬럼 이름이나 *는 OLD 또는 NEW로, 또는 OLD/NEW에 해당하는 output_alias로 한정해 이전 값이나 새 값이 반환되게 할 수 있어요. 한정되지 않은 컬럼 이름이나 *, 또는 대상 테이블 이름이나 별칭으로 한정된 컬럼 이름이나 *는 새 값을 반환해요. output_name

반환된 컬럼에 사용할 이름이에요.

출력 (Outputs)

성공적으로 완료되면 UPDATE 명령은 다음 형태의 명령 태그(command tag)를 반환해요.

UPDATE count

count는 값이 변경되지 않은 일치하는 행을 포함해 갱신된 행 수예요. BEFORE UPDATE 트리거에 의해 갱신이 억제되었을 때는 그 수가 condition과 일치한 행 수보다 적을 수 있다는 점을 유의하세요. count가 0이면 쿼리로 갱신된 행이 없다는 뜻이에요(이것은 오류로 간주되지 않아요).

UPDATE 명령에 RETURNING 절이 포함되면, 결과는 RETURNING 목록에 정의된 컬럼과 값을 포함하는 SELECT 문의 결과와 비슷할 거예요. 명령으로 갱신된 행들에 대해 계산된 결과죠.

참고 (Notes)

FROM 절이 있으면 본질적으로 대상 테이블이 from_item 목록에 언급된 테이블들과 조인되고, 조인의 각 출력 행은 대상 테이블에 대한 하나의 갱신 작업을 나타내요. FROM을 사용할 때는 조인이 수정할 각 행에 대해 둘 이상의 출력 행을 만들지 않도록 해야 해요. 다시 말해, 대상 행이 다른 테이블에서 둘 이상의 행과 조인되지 않아야 해요. 그렇게 되면 조인 행 중 하나만 대상 행을 갱신하는 데 사용되지만, 어느 것이 사용될지는 쉽게 예측할 수 없어요.

이런 불확정성 때문에 다른 테이블을 서브쿼리 안에서만 참조하는 것이 더 안전하지만, 조인을 사용하는 것보다 읽기 어렵고 느린 경우가 많아요.

파티션 테이블의 경우 행을 갱신하면 해당 행이 포함된 파티션의 파티션 제약 조건을 더 이상 만족하지 못할 수 있어요. 그 경우, 파티션 트리에 이 행이 파티션 제약 조건을 만족하는 다른 파티션이 있다면 행은 그 파티션으로 이동해요. 그런 파티션이 없다면 오류가 발생해요. 내부적으로 행 이동은 실제로 DELETE와 INSERT 작업이에요.

이동 중인 행에 대한 동시 UPDATE나 DELETE가 직렬화 실패(serialization failure) 오류를 얻을 가능성이 있어요. 세션 1이 파티션 키에 대해 UPDATE를 수행하고 있는데, 그 사이에 이 행이 보이는 동시 세션 2가 이 행에 대해 UPDATE나 DELETE 작업을 수행한다고 가정해 봐요. 그런 경우 세션 2의 UPDATE나 DELETE는 행 이동을 감지하고 직렬화 실패 오류(SQLSTATE 코드 '40001'과 함께 항상 반환됨)를 발생시켜요. 애플리케이션은 이런 경우 트랜잭션을 재시도하기를 원할 수 있어요. 테이블이 파티션되지 않았거나 행 이동이 없는 일반적인 경우라면, 세션 2는 새로 갱신된 행을 식별하고 이 새 행 버전에 대해 UPDATE/DELETE를 수행했을 거예요.

행은 로컬 파티션에서 외부 테이블 파티션으로 이동할 수 있지만(외부 데이터 래퍼가 튜플 라우팅을 지원한다면), 외부 테이블 파티션에서 다른 파티션으로는 이동할 수 없다는 점을 유의하세요.

한 파티션에서 다른 파티션으로 행을 이동하려는 시도는, 외래 키가 UPDATE 쿼리에 언급된 조상과 같지 않은 소스 파티션의 조상을 직접 참조하는 것으로 발견되면 실패해요.

예시 (Examples)

테이블 films의 kind 컬럼에서 Drama라는 단어를 Dramatic으로 바꿔요:

UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama';

테이블 weather의 한 행에서 온도 항목을 조정하고 강수량을 기본값으로 재설정해요:

UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT
  WHERE city = 'San Francisco' AND date = '2003-07-03';

같은 작업을 수행하면서 갱신된 항목과 이전 강수량 값을 반환해요:

UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT
  WHERE city = 'San Francisco' AND date = '2003-07-03'
  RETURNING temp_lo, temp_hi, prcp, old.prcp AS old_prcp;

대체 컬럼 목록 구문을 사용해 같은 갱신을 수행해요:

UPDATE weather SET (temp_lo, temp_hi, prcp) = (temp_lo+1, temp_lo+15, DEFAULT)
  WHERE city = 'San Francisco' AND date = '2003-07-03';

FROM 절 구문을 사용해 Acme Corporation의 계정을 관리하는 영업사원의 판매 수를 증가시켜요:

UPDATE employees SET sales_count = sales_count + 1 FROM accounts
  WHERE accounts.name = 'Acme Corporation'
  AND employees.id = accounts.sales_person;

WHERE 절에서 서브쿼리를 사용해 같은 작업을 수행해요:

UPDATE employees SET sales_count = sales_count + 1 WHERE id =
  (SELECT sales_person FROM accounts WHERE name = 'Acme Corporation');

accounts 테이블의 연락처 이름을 현재 할당된 영업사원과 일치하도록 갱신해요:

UPDATE accounts SET (contact_first_name, contact_last_name) =
    (SELECT first_name, last_name FROM employees
     WHERE employees.id = accounts.sales_person);

조인으로도 비슷한 결과를 얻을 수 있어요:

UPDATE accounts SET contact_first_name = first_name,
                    contact_last_name = last_name
  FROM employees WHERE employees.id = accounts.sales_person;

하지만 employees.id가 고유 키가 아니라면 두 번째 쿼리는 예기치 않은 결과를 줄 수 있는 반면, 첫 번째 쿼리는 id가 여러 번 일치하면 오류를 발생시키는 것이 보장돼요. 또한 특정 accounts.sales_person 항목에 일치하는 것이 없다면, 첫 번째 쿼리는 해당 이름 필드를 NULL로 설정하지만 두 번째 쿼리는 그 행을 전혀 갱신하지 않아요.

현재 데이터와 일치하도록 요약 테이블의 통계를 갱신해요:

UPDATE summary s SET (sum_x, sum_y, avg_x, avg_y) =
    (SELECT sum(x), sum(y), avg(x), avg(y) FROM data d
     WHERE d.group_id = s.group_id);

재고 수량과 함께 새 재고 품목을 삽입하려고 시도해요. 품목이 이미 존재하면 대신 기존 품목의 재고 수를 갱신해요. 전체 트랜잭션을 실패시키지 않고 이를 수행하려면 세이브포인트(savepoint)를 사용해요:

BEGIN;
-- other operations
SAVEPOINT sp1;
INSERT INTO wines VALUES('Chateau Lafite 2003', '24');
-- Assume the above fails because of a unique key violation,
-- so now we issue these commands:
ROLLBACK TO sp1;
UPDATE wines SET stock = stock + 24 WHERE winename = 'Chateau Lafite 2003';
-- continue with other operations, and eventually
COMMIT;

커서 c_films가 현재 위치한 행에서 테이블 films의 kind 컬럼을 변경해요:

UPDATE films SET kind = 'Dramatic' WHERE CURRENT OF c_films;

많은 행에 영향을 주는 갱신은 테이블 블로트(bloat), 복제 지연 증가, 잠금 경합 증가 같은 시스템 성능에 부정적인 영향을 줄 수 있어요. 그런 상황에서는 작업을 더 작은 배치로 수행하고, 배치 사이에 테이블에 대한 VACUUM 작업을 수행하는 것이 합리적일 수 있어요. UPDATE에는 LIMIT 절이 없지만, 공통 테이블 표현식(Common Table Expression)과 셀프 조인을 사용해 비슷한 효과를 얻을 수 있어요. 표준 PostgreSQL 테이블 접근 방법에서는 시스템 컬럼 ctid에 대한 셀프 조인이 매우 효율적이에요:

WITH exceeded_max_retries AS (
  SELECT w.ctid FROM work_item AS w
    WHERE w.status = 'active' AND w.num_retries > 10
    ORDER BY w.retry_timestamp
    FOR UPDATE
    LIMIT 5000
)
UPDATE work_item SET status = 'failed'
  FROM exceeded_max_retries AS emr
  WHERE work_item.ctid = emr.ctid;

이 명령은 갱신할 행이 남지 않을 때까지 반복해야 해요. (ctid를 이렇게 사용하는 것은 쿼리를 반복 실행해 변경된 ctid 문제를 피하기 때문에 안전해요.) ORDER BY 절을 사용하면 어떤 행을 먼저 갱신할지 우선순위를 정할 수 있고, 다른 갱신 작업이 같은 순서를 사용한다면 교착 상태(deadlock)를 막을 수도 있어요. 잠금 경합이 우려된다면 여러 명령이 같은 행을 갱신하지 못하도록 CTE에 SKIP LOCKED를 추가할 수 있어요. 단, 그런 경우에는 일치하는 행이 빠뜨려지지 않았는지 확인하기 위해 SKIP LOCKED나 LIMIT가 없는 최종 UPDATE가 필요해요.

호환성 (Compatibility)

이 명령은 FROM과 RETURNING 절이 PostgreSQL 확장이고, UPDATE와 함께 WITH를 사용할 수 있는 것도 PostgreSQL 확장이라는 점을 제외하면 SQL 표준을 준수해요.

일부 다른 데이터베이스 시스템은 대상 테이블이 FROM 안에서 다시 나열되어야 하는 FROM 옵션을 제공해요. PostgreSQL은 FROM을 그렇게 해석하지 않아요. 이 확장을 사용하는 애플리케이션을 이식할 때는 주의해야 해요.

표준에 따르면 괄호로 둘러싼 대상 컬럼 이름의 하위 목록에 대한 소스 값은 올바른 수의 컬럼을 산출하는 어떤 행 값(row-valued) 표현식이든 될 수 있어요. PostgreSQL은 소스 값이 행 생성자(row constructor) 또는 서브-SELECT이기만을 허용해요. 개별 컬럼의 갱신 값은 행 생성자의 경우 DEFAULT로 지정할 수 있지만, 서브-SELECT 안에서는 지정할 수 없어요.

더 알아보기 (Learn more)

행을 넣는 INSERT와 지우는 DELETE 명령도 함께 알아두면 돼요.
RETURNING 절과 FROM 절 구문은 SELECT 문서에서 더 자세히 볼 수 있어요.
파티셔닝과 행 이동에 대한 내용은 5.11절과 CREATE TABLE의 PARTITION BY를 참고하면 돼요.