백엔드 데이터 인프라 10편. INSERT 단건·여러건·SELECT INSERT·ON CONFLICT UPSERT·COPY 대량 입력의 표준 패턴 풀어쓴 학습 노트.
이 글은 백엔드 데이터 인프라 시리즈 70편 중 10편이에요. 9편 CREATE TABLE 에서 만든 그릇 — 이번 10편은 거기에 데이터를 어떻게 박는가 를 풀어 가요.
INSERT 5가지 패턴
8편 SQL 기초 에서 잠깐 본 INSERT를 5가지 패턴으로 정리했어요.
- 단건 — 한 행씩
- 다중 (Bulk) — 한 SQL에 여러 행
- SELECT INSERT — 다른 쿼리 결과를 입력
- UPSERT (ON CONFLICT) — 있으면 수정·없으면 입력
- COPY — 대량 입력 (CSV 등)
각 패턴마다 "언제 무엇을 쓰는지" 가 또렷이 갈려요.
패턴 1 — 단건
INSERT INTO users (name, email)
VALUES ('Alice', 'alice@example.com');
기본 키(id)는 BIGSERIAL(자동 증가 64bit 정수)이라 생략 — 자동 할당. created_at 같은 DEFAULT 박힌 컬럼도 생략 가능.
RETURNING — 생성된 행 받기
INSERT INTO users (name, email)
VALUES ('Alice', 'alice@example.com')
RETURNING id, created_at;
PG 특별 기능 — 생성된 ID·시각을 그 자리에서 결과로 돌려줘요. "자동 ID를 즉시 알아내고 다음 작업" 표준 패턴.
WITH inserted AS (
INSERT INTO users (name, email)
VALUES ('Alice', 'alice@example.com')
RETURNING id
)
INSERT INTO user_logs (user_id, action)
SELECT id, 'SIGNUP' FROM inserted;
CTE(WITH 절로 임시 결과) + RETURNING 조합으로 "INSERT + 후속 INSERT" 를 한 SQL에 묶어요.
패턴 2 — Bulk
INSERT INTO users (name, email) VALUES
('Alice', 'alice@example.com'),
('Bob', 'bob@example.com'),
('Charlie', 'charlie@example.com');
한 SQL로 여러 행 — 단건 N번보다 N배 이상 빠름. 이유: - 네트워크 왕복(roundtrip) 1번 - 파싱 1번 - 트랜잭션 1번
대량 입력 = Bulk 표준. JPA의 saveAll() 도 내부적으로 Bulk INSERT (Hibernate 설정에 따라).
다중 + RETURNING
INSERT INTO users (name, email) VALUES
('Alice', 'alice@example.com'),
('Bob', 'bob@example.com')
RETURNING id, name;
생성된 모든 행이 한 번에 결과로 돌아와요.
패턴 3 — SELECT INSERT
INSERT INTO archive_users (id, name, email, created)
SELECT id, name, email, created
FROM users
WHERE created < '2025-01-01';
다른 테이블 데이터를 그대로 옮겨 박아요. "데이터 이관·아카이브·집계 결과 저장" 표준.
변환·필터
-- 30일 전보다 오래된 사용자만 아카이브
INSERT INTO archive_users (id, name, email)
SELECT id, name, email
FROM users
WHERE created < NOW() - INTERVAL '30 days';
-- 집계 결과를 통계 테이블로
INSERT INTO daily_stats (date, count)
SELECT DATE(created), COUNT(*)
FROM orders
WHERE created >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY DATE(created);
패턴 4 — ON CONFLICT (UPSERT)
INSERT INTO users (id, name, email)
VALUES (1, 'Alice', 'alice@example.com')
ON CONFLICT (id)
DO UPDATE SET
name = EXCLUDED.name,
email = EXCLUDED.email,
updated_at = NOW();
기본 키 중복 시 UPDATE — "있으면 수정·없으면 생성". PG 9.5+. EXCLUDED = "INSERT 하려던 그 값".
ON CONFLICT DO NOTHING
INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice')
ON CONFLICT (email) DO NOTHING;
중복 시 "그냥 무시" — 에러 없이 패스. 멱등 입력에 유용.
부분 인덱스·조건 충돌
INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice')
ON CONFLICT (email) WHERE deleted_at IS NULL
DO UPDATE SET name = EXCLUDED.name;
활성 사용자에만 적용하는 식의 세밀한 제어가 가능해요.
패턴 5 — COPY (대량 입력)
-- 서버 측 파일 (슈퍼유저 권한 필요)
COPY users (name, email) FROM '/tmp/users.csv' CSV HEADER;
-- psql 클라이언트 측 파일
\copy users (name, email) FROM '~/Downloads/users.csv' CSV HEADER;
psql(PG 명령줄 도구)에서 INSERT 1만 건 = ~10초. COPY 1만 건 = ~1초. 수십 배 빠름 — 대량 데이터 입력 표준.
옵션
\copy users FROM 'file.csv'
WITH (FORMAT csv, HEADER true, DELIMITER ',',
QUOTE '"', NULL '')
| 옵션 | 의미 |
|---|---|
FORMAT |
csv·text·binary |
HEADER |
첫 줄을 컬럼명으로 (생략 가능) |
DELIMITER |
구분자 (기본 = 쉼표) |
QUOTE |
인용 부호 |
NULL |
NULL 표현 ('' 또는 \N) |
데이터 타입 변환
값 형식을 명시적으로 박을 때:
INSERT INTO orders (amount, created_at)
VALUES (10000::INTEGER, '2026-05-17'::TIMESTAMPTZ);
-- 또는 CAST
INSERT INTO orders (amount)
VALUES (CAST('10000' AS INTEGER));
::TYPE 가 PG 단축 — 자바·JS 출신자도 한 번에 익숙해져요.
NULL 입력
-- 명시적 NULL
INSERT INTO users (name, email) VALUES ('Alice', NULL);
-- 컬럼 생략 — DEFAULT 또는 NULL
INSERT INTO users (name) VALUES ('Alice');
-- email은 컬럼 정의에 따라 NULL 또는 DEFAULT
NOT NULL 제약 박힌 컬럼에 NULL 박으면 에러.
DEFAULT 명시
INSERT INTO users (id, name, email, created)
VALUES (DEFAULT, 'Alice', 'alice@example.com', DEFAULT);
DEFAULT 키워드 박으면 — 컬럼의 기본값 사용 (BIGSERIAL은 자동 증가, NOW()는 현재 시각).
함정 7가지
(1) 컬럼 순서 누락
-- ❌ 위험 — 테이블 컬럼 순서에 의존
INSERT INTO users VALUES (1, 'Alice', 'alice@example.com');
-- ✅ 안전 — 명시
INSERT INTO users (id, name, email) VALUES (1, 'Alice', 'alice@example.com');
컬럼 추가·순서 변경 시 무명 INSERT는 깨져요. 항상 명시.
(2) Bulk 너무 큼
INSERT INTO logs VALUES (..., ..., ...); -- 10만 건
한 INSERT에 10만 건 = 메모리·로그·WAL(쓰기 로그) 부담. 1000~5000건 단위로 chunk 처리. COPY가 더 적합.
(3) EXCLUDED 안 박은 UPSERT
ON CONFLICT (id) DO UPDATE SET name = 'Alice'; -- ❌ 항상 'Alice'
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name; -- ✅ INSERT 시 값
(4) COPY 한글 깨짐
CSV 인코딩 = UTF8 무조건. EUC-KR이면 변환부터.
iconv -f EUC-KR -t UTF-8 file.csv > file_utf8.csv
(5) RETURNING 미사용
-- JPA가 자동 생성된 ID 알아내는 방법 = RETURNING 또는 다음 SELECT
-- 직접 SQL = RETURNING이 표준
(6) Bulk 트랜잭션 안 박힘
기본은 각 INSERT가 자동 커밋. 1만 건이면 1만 커밋 → 느림. BEGIN·COMMIT으로 한 묶음.
BEGIN;
INSERT INTO users VALUES (...), (...), ... ;
INSERT INTO users VALUES (...), (...), ... ;
COMMIT;
(7) ON CONFLICT 컬럼 인덱스 없음
ON CONFLICT (email) -- email 컬럼에 UNIQUE 또는 인덱스 필수
인덱스 없으면 에러. UPSERT 박는 컬럼은 UNIQUE 제약 또는 unique 인덱스가 사전 조건.
단건 = INSERT, 10~1000건 = Bulk (한 INSERT 여러 VALUES), 10000+건 = COPY 또는 chunk. 트랜잭션 묶음으로 N배 빠름. RETURNING 으로 ID 즉시 받기.
한 줄 정리 — INSERT 5패턴 = 단건·Bulk·SELECT INSERT·UPSERT·COPY. RETURNING으로 생성 행 즉시, ON CONFLICT로 멱등, COPY로 대량. 컬럼 명시 + EXCLUDED + UTF8 + 트랜잭션 묶음 4가지가 운영 표준.
시험 직전 한 번 더 — INSERT 입문자가 매번 헷갈리는 것
- 단건 =
INSERT INTO t (col) VALUES (val) - Bulk = 한 SQL 여러 VALUES (10~1000건, N배 빠름)
- RETURNING = 생성된 행 반환 (PG 특별)
- SELECT INSERT =
INSERT INTO ... SELECT ...(이관·집계) - ON CONFLICT = UPSERT (PG 9.5+)
- EXCLUDED = INSERT 하려던 값
- ON CONFLICT DO UPDATE SET = 갱신
- ON CONFLICT DO NOTHING = 무시
- 부분 인덱스 =
ON CONFLICT (col) WHERE 조건 - COPY = 대량 입력 (수십 배 빠름)
COPY(서버 파일) vs\copy(psql 클라이언트 파일)- CSV HEADER true = 첫 줄 컬럼명
- 한글 = UTF8 필수
- 컬럼 순서 = 항상 명시 (안 그러면 컬럼 추가 시 깨짐)
- DEFAULT 키워드 = 컬럼 기본값 사용
- NULL 명시 vs 컬럼 생략 차이
- NOT NULL 컬럼에 NULL = 에러
::TYPE= 타입 캐스팅 단축- CAST(val AS type) = 표준 캐스팅
- Bulk 너무 크면 = chunk 1000~5000건
- BEGIN·COMMIT = N배 빠름 (1커밋)
- ON CONFLICT 대상 컬럼 = UNIQUE 인덱스 필수
- WITH (CTE) + RETURNING = 다단계 INSERT
- saveAll() = JPA Bulk INSERT (Hibernate 설정 의존)
시리즈 다른 편 (앞뒤 글 모음)
이전 글:
- 5편 — 데이터베이스 만들기 CREATE DATABASE
- 6편 — PostgreSQL 데이터베이스 접속·전환
- 7편 — PostgreSQL 관계형 모델 핵심 개념
- 8편 — SQL 기초 5가지 동사
- 9편 — CREATE TABLE 첫 테이블 만들기
다음 글: