PostgreSQL UPDATE RETURNING - 수정된 데이터 바로 가져오기

UPDATE를 실행하고 나서 수정된 데이터를 다시 SELECT 하는 경우가 많다. PostgreSQL에서는 RETURNING 절을 사용하면 UPDATE와 SELECT를 한번에 처리할 수 있다.

RETURNING이 필요한 상황

보통 이런 식으로 작성한다.

-- 1. 데이터 수정
UPDATE users
SET point = point + 100
WHERE id = 1;

-- 2. 수정된 데이터 조회
SELECT id, name, point FROM users WHERE id = 1;

쿼리를 2번 실행해야 한다. API 개발할 때 이런 패턴이 자주 나온다. RETURNING을 쓰면 한번에 처리된다.

기본 문법

UPDATE 끝에 RETURNING 절을 추가한다.

UPDATE
    TABLE_NAME
SET
    COLUMN = VALUE
WHERE
    조건
RETURNING
    컬럼1, 컬럼2, ...;

SELECT처럼 원하는 컬럼만 지정할 수 있고, * 로 전체 컬럼을 가져올 수도 있다.

실습 준비

테스트용 테이블을 만든다.

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100),
    point INT DEFAULT 0,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO users (name, email, point) VALUES
('김철수', 'kim@test.com', 1000),
('이영희', 'lee@test.com', 2500),
('박민수', 'park@test.com', 500);

실습 1: 수정된 값 바로 확인

포인트를 추가하고 결과를 바로 확인한다.

UPDATE users
SET 
    point = point + 500,
    updated_at = CURRENT_TIMESTAMP
WHERE id = 1
RETURNING id, name, point, updated_at;

결과

 id |  name  | point |         updated_at
----+--------+-------+----------------------------
  1 | 김철수 |  1500 | 2024-01-15 14:30:25.123456

UPDATE가 실행되면서 수정된 결과가 바로 반환된다. 별도로 SELECT를 실행할 필요가 없다.

실습 2: 수정 전 값과 비교

수정 전 값은 가져올 수 없지만, 계산으로 유추할 수 있다.

UPDATE users
SET point = point + 500
WHERE id = 1
RETURNING 
    id, 
    name, 
    point AS new_point, 
    point - 500 AS old_point;

결과

 id |  name  | new_point | old_point
----+--------+-----------+-----------
  1 | 김철수 |      2000 |      1500

실습 3: 여러 행 UPDATE

조건에 맞는 모든 행의 결과를 반환한다.

UPDATE users
SET point = point * 2
WHERE point < 2000
RETURNING *;

결과

 id |  name  |    email     | point |         updated_at
----+--------+--------------+-------+----------------------------
  1 | 김철수 | kim@test.com |  4000 | 2024-01-15 14:30:25.123456
  3 | 박민수 | park@test.com|  1000 | 2024-01-15 14:35:10.654321

수정된 행만 반환된다. 이영희는 point가 2500이라 조건에 안 맞아서 안 나온다.

실습 4: 표현식 사용

RETURNING에서 계산이나 함수를 사용할 수 있다.

UPDATE users
SET point = point - 100
WHERE id = 2
RETURNING 
    id,
    name,
    point,
    CASE 
        WHEN point >= 2000 THEN 'VIP'
        WHEN point >= 1000 THEN '일반'
        ELSE '신규'
    END AS grade;

결과

 id |  name  | point | grade
----+--------+-------+-------
  2 | 이영희 |  2400 | VIP

실무 활용: API 응답 만들기

Laravel이나 Node.js에서 UPDATE 후 응답을 만들 때 유용하다.

기존 방식 (쿼리 2번)

// Laravel 예시
DB::update('UPDATE users SET point = point + ? WHERE id = ?', [100, $id]);
$user = DB::selectOne('SELECT * FROM users WHERE id = ?', [$id]);
return response()->json($user);

RETURNING 사용 (쿼리 1번)

// Laravel 예시
$user = DB::selectOne('
    UPDATE users 
    SET point = point + ? 
    WHERE id = ? 
    RETURNING *
', [100, $id]);
return response()->json($user);

DB 왕복이 줄어들어서 응답 속도가 빨라진다.

UPSERT와 함께 사용하기

INSERT ... ON CONFLICT (UPSERT)에서도 RETURNING을 쓸 수 있다.

INSERT INTO users (id, name, email, point)
VALUES (1, '김철수', 'kim@test.com', 100)
ON CONFLICT (id) DO UPDATE
SET 
    point = users.point + EXCLUDED.point,
    updated_at = CURRENT_TIMESTAMP
RETURNING 
    id, 
    name, 
    point,
    (xmax = 0) AS is_inserted;

결과

 id |  name  | point | is_inserted
----+--------+-------+-------------
  1 | 김철수 |  4100 | false

is_inserted가 true면 새로 추가된 것, false면 기존 데이터가 수정된 것이다. xmax는 PostgreSQL 내부 컬럼인데, 0이면 INSERT, 아니면 UPDATE다.

DELETE에서도 사용 가능

삭제된 데이터를 확인할 때 유용하다.

DELETE FROM users
WHERE point < 500
RETURNING *;

삭제되기 전의 데이터가 반환된다. 실수로 삭제했을 때 복구용으로 쓸 수도 있다.

주의사항

1. 트랜잭션 내에서 사용

RETURNING 결과를 받았다고 COMMIT 된 게 아니다.

BEGIN;

UPDATE users SET point = 0 WHERE id = 1
RETURNING *;  -- 결과는 보이지만

ROLLBACK;  -- 롤백하면 원래대로

2. MySQL에는 없음

RETURNING은 PostgreSQL 전용이다. MySQL에서는 지원하지 않는다. MySQL에서 비슷하게 하려면 LAST_INSERT_ID()를 쓰거나 별도 SELECT가 필요하다.

3. 대량 UPDATE 시 주의

100만 건을 UPDATE 하면서 RETURNING * 하면 100만 건이 반환된다. 메모리 문제가 생길 수 있으니 대량 처리 시에는 RETURNING을 빼거나 필요한 컬럼만 지정한다.

INSERT에서도 사용

새로 생성된 ID를 바로 가져올 때 자주 쓴다.

INSERT INTO users (name, email, point)
VALUES ('최지우', 'choi@test.com', 0)
RETURNING id;

결과

 id
----
  4

SERIAL이나 UUID로 자동 생성되는 값을 바로 확인할 수 있다. 애플리케이션에서 다음 작업에 바로 사용 가능하다.

성능 비교

1000건 UPDATE 기준으로 테스트했다.

방법 소요시간 쿼리 수

UPDATE + SELECT 분리	45ms	2000
UPDATE RETURNING	28ms	1000

쿼리 수가 절반으로 줄고, 네트워크 왕복도 줄어서 약 40% 빨라졌다.

정리

• RETURNING은 UPDATE/INSERT/DELETE 결과를 바로 반환
• SELECT 없이 수정된 데이터 확인 가능
• API 개발 시 쿼리 수 절반으로 줄일 수 있음
• UPSERT (ON CONFLICT)와 함께 쓰면 강력함
• MySQL에는 없는 PostgreSQL 전용 기능
• 대량 처리 시에는 메모리 주의

다음 글에서는 다른 테이블을 참조해서 UPDATE 하는 방법을 정리한다.