데이터 변경 SQL은 조회보다 더 조심해야 한다
SELECT는 데이터를 읽는다. 조건을 잘못 써도 보통 결과가 이상하게 보이는 데서 끝난다. 반면 INSERT, UPDATE, DELETE는 데이터를 바꾼다. 특히 UPDATE와 DELETE는 WHERE 조건을 빠뜨려도 문법 오류가 나지 않는다. PostgreSQL은 사용자가 요청한 대로 전체 행을 바꾼다.
그래서 데이터 변경 SQL은 다음 순서로 다루는 습관이 필요하다.
바꾸려는 대상을
SELECT로 먼저 확인한다.예상 행 수를 확인한다.
실제 변경 SQL에는 가능한 한
RETURNING을 붙여 결과를 확인한다.중요한 변경은 트랜잭션 안에서 실행하고, 이상하면
ROLLBACK한다.
트랜잭션은 뒤에서 자세히 다루지만, 여기서는 최소한 다음 흐름만 기억하자.
BEGIN;
-- 변경 SQL 실행
-- RETURNING 결과나 count(*) 결과 확인
-- 결과가 맞으면
COMMIT;결과가 예상과 다르다면 COMMIT 대신 다음처럼 되돌린다.
ROLLBACK;COMMIT 전에는 되돌릴 수 있다. 하지만 COMMIT 후에는 같은 데이터를 자동으로 복원해 주지 않는다. 그때는 백업이나 별도 복구 절차가 필요하다.
이 장의 예제는 다음과 같은 쇼핑몰 테이블이 있다고 가정한다. 실제 실습 환경의 열 이름이 다르면 알맞게 바꾸어 실행하면 된다.
customers(customer_id, name, email, phone, created_at, updated_at)
products(product_id, name, price, stock_quantity, is_active, created_at)
orders(order_id, customer_id, status, total_amount, created_at)
AI 생성 이미지
INSERT: 새 행을 넣고 생성 결과 확인하기
가장 기본적인 입력은 INSERT INTO ... VALUES 형식이다.
INSERT INTO customers (name, email, phone)
VALUES ('김하나', '[email protected]', '010-1111-2222');열 이름을 생략하고 값을 넣을 수도 있지만 실무에서는 권장하지 않는다.
-- 권장하지 않는 방식
INSERT INTO customers
VALUES (1, '김하나', '[email protected]', '010-1111-2222', now(), now());이 방식은 테이블 열 순서에 의존한다. 나중에 열이 추가되거나 순서에 대한 착각이 생기면 엉뚱한 값이 들어갈 수 있다. INSERT에서는 항상 입력할 열을 명시하는 편이 안전하다.
PostgreSQL에서는 RETURNING을 붙여 생성된 행을 바로 확인할 수 있다. 자동 생성된 기본 키나 기본값으로 들어간 시각을 확인할 때 유용하다.
INSERT INTO customers (name, email, phone)
VALUES ('박민수', '[email protected]', '010-3333-4444')
RETURNING customer_id, name, email, created_at;예상 결과는 다음과 비슷하다.
customer_id | name | email | created_at
-------------+--------+--------------------+-------------------------------
2 | 박민수 | [email protected] | 2026-05-30 10:15:21.123456+09created_at 같은 열에 DEFAULT now()가 정의되어 있다면 값을 직접 넣지 않아도 PostgreSQL이 기본값을 채운다. 특정 열에 기본값을 명시적으로 사용하고 싶다면 DEFAULT 키워드를 쓸 수 있다.
INSERT INTO orders (customer_id, status, total_amount, created_at)
VALUES (2, DEFAULT, 0, DEFAULT)
RETURNING order_id, customer_id, status, total_amount, created_at;테이블의 모든 열이 기본값으로 채워질 수 있다면 다음처럼 쓸 수도 있다.
INSERT INTO some_log_table DEFAULT VALUES
RETURNING *;여러 행을 한 번에 넣을 때는 VALUES 뒤에 행을 여러 개 적는다.
INSERT INTO products (name, price, stock_quantity)
VALUES
('무선 마우스', 25000, 100),
('기계식 키보드', 89000, 50),
('USB-C 케이블', 12000, 200)
RETURNING product_id, name, price, stock_quantity;반복해서 한 행씩 넣는 것보다 여러 행을 한 번에 넣는 편이 보통 효율적이다. 다만 수천, 수만 건 이상 대량 입력은 별도의 적재 방식이 더 적합할 수 있다. 이 책에서는 먼저 SQL의 기본 변경 흐름을 익히는 데 집중한다.
UPDATE: 바꾸기 전에 반드시 대상부터 확인하기
UPDATE는 기존 행의 값을 바꾼다. 기본 형식은 다음과 같다.
UPDATE products
SET price = 27000
WHERE product_id = 1;문제는 WHERE를 빠뜨려도 실행된다는 점이다.
-- 매우 위험한 SQL
UPDATE products
SET price = 27000;이 문장은 products 테이블의 모든 상품 가격을 27000으로 바꾼다. PostgreSQL 입장에서는 잘못된 문장이 아니다. 사용자가 전체 변경을 요청한 것으로 처리한다.
따라서 UPDATE 전에는 같은 조건으로 SELECT를 먼저 실행한다.
SELECT product_id, name, price
FROM products
WHERE product_id = 1;대상이 맞으면 그다음 변경한다.
UPDATE products
SET price = 27000
WHERE product_id = 1
RETURNING product_id, name, price;RETURNING은 변경된 행을 보여 준다. 업데이트 후 다시 SELECT를 실행하지 않아도 실제로 어떤 행이 바뀌었는지 확인할 수 있다.
여러 행을 바꿀 때는 더 조심해야 한다. 예를 들어 재고가 0이고 비활성 상태인 상품명을 정리한다고 하자. 먼저 대상 수를 확인한다.
SELECT count(*)
FROM products
WHERE stock_quantity = 0
AND is_active = false;그리고 샘플 행도 확인한다.
SELECT product_id, name, stock_quantity, is_active
FROM products
WHERE stock_quantity = 0
AND is_active = false
ORDER BY product_id
LIMIT 10;대상이 맞을 때만 변경한다.
UPDATE products
SET name = '[판매종료] ' || name
WHERE stock_quantity = 0
AND is_active = false
RETURNING product_id, name;문자열 앞에 값을 붙일 때 ||는 PostgreSQL의 문자열 연결 연산자다.
실무에서는 다음 패턴을 자주 사용한다.
BEGIN;
SELECT count(*)
FROM products
WHERE stock_quantity = 0
AND is_active = false;
UPDATE products
SET name = '[판매종료] ' || name
WHERE stock_quantity = 0
AND is_active = false
RETURNING product_id, name;여기까지 실행한 뒤 반환된 행을 확인한다. 결과가 예상과 다르면 다음처럼 되돌린다.
ROLLBACK;결과가 맞다면 ROLLBACK하지 않고 다음처럼 확정한다.
COMMIT;또 하나의 흔한 실수는 NULL 비교다.
-- 잘못된 조건
UPDATE customers
SET phone = '미입력'
WHERE phone = NULL;NULL은 값이 없거나 알 수 없다는 뜻이므로 =로 비교하지 않는다. 다음처럼 써야 한다.
UPDATE customers
SET phone = '미입력'
WHERE phone IS NULL
RETURNING customer_id, name, phone;반대로 전화번호가 들어 있는 행을 찾고 싶다면 IS NOT NULL을 사용한다.
SELECT customer_id, name, phone
FROM customers
WHERE phone IS NOT NULL;DELETE: 필요한 행만 지우고, 외래 키를 확인하기
DELETE는 행을 삭제한다.
DELETE FROM customers
WHERE customer_id = 10;UPDATE와 마찬가지로 먼저 대상 확인이 필요하다.
SELECT customer_id, name, email
FROM customers
WHERE customer_id = 10;그다음 삭제한다.
DELETE FROM customers
WHERE customer_id = 10
RETURNING customer_id, name, email;RETURNING을 붙이면 어떤 행이 삭제되었는지 확인할 수 있다. 삭제 후에는 같은 조건으로 조회해도 행이 남아 있지 않기 때문에, 삭제 순간에 결과를 확인하는 습관이 중요하다.
하지만 고객을 바로 삭제하려고 하면 실패할 수 있다.
DELETE FROM customers
WHERE customer_id = 2;이 고객의 주문이 orders 테이블에 남아 있고, orders.customer_id가 customers.customer_id를 외래 키로 참조한다면 PostgreSQL은 삭제를 막는다. 주문이 존재하는데 고객만 사라지면 주문 데이터가 누구의 것인지 알 수 없기 때문이다.
이때 선택지는 업무 규칙에 따라 달라진다.
주문 이력이 있으면 고객을 물리적으로 삭제하지 않고 탈퇴 상태로 표시한다.
자식 데이터인 주문을 먼저 삭제한 뒤 고객을 삭제한다.
외래 키에
ON DELETE CASCADE를 설정해 부모 삭제 시 자식도 함께 삭제되게 한다.외래 키에
ON DELETE SET NULL을 설정해 부모 삭제 시 참조 값을NULL로 만든다.
쇼핑몰에서는 보통 주문 이력을 보존해야 한다. 따라서 고객 행을 지우기보다 status, deleted_at, is_active 같은 열로 상태를 표시하는 방식이 더 안전한 경우가 많다. 이를 흔히 소프트 삭제라고 부른다.
예를 들어 customers 테이블에 탈퇴 시각을 저장하는 deleted_at 열이 있다면 다음처럼 처리할 수 있다.
UPDATE customers
SET
deleted_at = now(),
updated_at = now()
WHERE customer_id = 2
RETURNING customer_id, name, deleted_at, updated_at;또는 status 열을 두고 active, withdrawn처럼 상태를 관리할 수도 있다.
UPDATE customers
SET
status = 'withdrawn',
updated_at = now()
WHERE customer_id = 2
RETURNING customer_id, name, status, updated_at;위 예제의 deleted_at이나 status는 처음에 가정한 간단한 테이블 목록에는 없던 열이다. 실제 업무에서는 이런 상태 관리용 열을 미리 설계해 두고, 주문 이력처럼 보존해야 하는 데이터와 함께 다룬다.
DELETE와 TRUNCATE는 다르다
테이블의 모든 행을 지우는 방법은 두 가지가 있다.
DELETE FROM some_table;TRUNCATE TABLE some_table;둘 다 조건 없이 쓰면 전체 데이터가 사라진다. 하지만 성격이 다르다.
DELETE는 행 단위 삭제다. WHERE 조건을 붙일 수 있고, RETURNING으로 삭제된 행을 확인할 수 있다.
DELETE FROM products
WHERE is_active = false
RETURNING product_id, name;TRUNCATE는 테이블을 빠르게 비우는 명령이다. WHERE를 붙일 수 없다. 즉, 일부 행만 골라 지우는 용도로 사용할 수 없다.
TRUNCATE TABLE temp_import_products;임시 적재 테이블처럼 "내용을 전부 버리고 다시 채울" 목적이라면 TRUNCATE가 적합하다. 반대로 고객, 주문, 결제 같은 업무 핵심 테이블에는 매우 조심해서 사용해야 한다.
PostgreSQL에서 TRUNCATE도 트랜잭션 안에서 실행하면 ROLLBACK으로 되돌릴 수 있다.
BEGIN;
TRUNCATE TABLE temp_import_products;
-- 실수였다면
ROLLBACK;또는 의도한 작업이 맞다면 다음처럼 확정한다.
COMMIT;하지만 이미 COMMIT했다면 상황이 달라진다. 그때는 단순히 ROLLBACK할 수 없다. 백업, 복구 지점, 별도 로그가 필요하다. 그래서 운영 환경에서 TRUNCATE는 실행 전 테이블 이름과 접속 데이터베이스를 반드시 확인해야 한다.
외래 키가 연결된 테이블에도 주의해야 한다. 다른 테이블이 참조 중인 테이블을 TRUNCATE하려 하면 실패할 수 있다. CASCADE를 붙이면 관련 테이블까지 함께 비울 수 있지만, 그만큼 위험하다.
-- 매우 위험할 수 있음
TRUNCATE TABLE customers CASCADE;이런 명령은 실습용 데이터베이스가 아니라면 혼자 판단해 실행하지 않는 것이 좋다.
UPSERT: 있으면 갱신하고 없으면 입력하기
서비스에서는 "없으면 만들고, 이미 있으면 갱신"해야 하는 상황이 자주 있다. 회원 가입에서 같은 이메일 계정이 두 번 생성되면 안 된다. 이때 필요한 것이 UPSERT다. PostgreSQL에서는 INSERT ... ON CONFLICT를 사용한다.
먼저 충돌을 판단할 기준이 필요하다. 이메일 중복을 막으려면 email에 UNIQUE 제약 조건이나 유니크 인덱스가 있어야 한다.
ALTER TABLE customers
ADD CONSTRAINT customers_email_unique UNIQUE (email);이미 같은 제약 조건이 있다면 다시 만들 필요는 없다.
중복이면 아무것도 하지 않는 방식은 다음과 같다.
INSERT INTO customers (name, email, phone)
VALUES ('이서연', '[email protected]', '010-5555-6666')
ON CONFLICT (email) DO NOTHING
RETURNING customer_id, name, email;새로 입력되면 행이 반환된다. 이미 같은 이메일이 있으면 아무 행도 반환되지 않는다. 이 방식은 "중복 요청이면 무시한다"는 정책에 맞다.
반대로 이미 있으면 일부 정보를 갱신하고 싶을 수 있다.
INSERT INTO customers (name, email, phone, updated_at)
VALUES ('이서연', '[email protected]', '010-7777-8888', now())
ON CONFLICT (email) DO UPDATE
SET
name = EXCLUDED.name,
phone = EXCLUDED.phone,
updated_at = now()
RETURNING customer_id, name, email, phone, updated_at;여기서 EXCLUDED는 입력하려 했지만 충돌 때문에 제외된 새 값을 뜻한다. 위 예제에서는 같은 이메일이 이미 있으면 이름과 전화번호를 새 값으로 바꾼다.
UPSERT를 사용할 때도 무조건 모든 열을 덮어쓰면 안 된다. 예를 들어 회원의 가입일, 등급, 누적 구매액 같은 값은 회원 가입 요청이 다시 들어왔다고 바뀌면 안 된다. DO UPDATE SET에는 정말 갱신해도 되는 열만 적어야 한다.
변경 SQL 실행 전 체크리스트
데이터 변경은 문법보다 절차가 중요하다. 다음 항목을 매번 확인하자.
INSERT에서는 열 이름을 명시했는가?자동 생성 값이나 기본값은
RETURNING으로 확인했는가?UPDATE,DELETE전에 같은WHERE조건으로SELECT를 실행했는가?예상 행 수와 실제 변경 행 수가 맞는가?
WHERE조건에NULL비교 실수는 없는가?외래 키로 연결된 테이블에 영향을 주지 않는가?
대량 변경은
BEGIN후 실행하고, 확인 뒤COMMIT했는가?운영 데이터라면 백업이나 복구 방법이 준비되어 있는가?
INSERT, UPDATE, DELETE는 모두 짧은 SQL로 끝난다. 그러나 실무에서는 짧은 SQL일수록 더 천천히 확인해야 한다. 데이터를 바꾸는 능력은 SQL 문법을 아는 것에서 끝나지 않는다. 바꾸기 전 대상을 확인하고, 바꾼 뒤 결과를 검증하며, 되돌릴 수 있는 범위 안에서 실행하는 습관까지 포함한다.
이 노트는 보도·비평·교육·연구 목적으로 작성되었습니다. 저작권 문의가 있으시면 에서 알려주세요.