백엔드 데이터 인프라 10편 — INSERT 데이터 입력 표준 패턴

이 글은 백엔드 데이터 인프라 시리즈 70편 중 10편이에요. 9편 CREATE TABLE 에서 만든 그릇 — 이번 10편은 거기에 데이터를 어떻게 박는가 를 풀어 가요.

INSERT 5가지 패턴

8편 SQL 기초 에서 잠깐 본 INSERT를 5가지 패턴으로 정리.

1. 단건 — 한 행씩
2. 다중 (Bulk) — 한 SQL에 여러 행
3. SELECT INSERT — 다른 쿼리 결과를 입력
4. UPSERT (ON CONFLICT) — 있으면 수정·없으면 입력
5. COPY — 대량 입력 (CSV 등)

각 패턴이 "언제 무엇을" 명확.

패턴 1 — 단건

INSERT INTO users (name, email)
VALUES ('Alice', 'alice@example.com');

기본 키(id)는 BIGSERIAL이라 생략 — 자동 할당. 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;

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 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 설정 의존)

시리즈 다른 편

• Part 1 PostgreSQL 튜토리얼: 1편 · 2편 · 3편 · 4편 · 5편 · 6편 · 7편 · 8편 · 9편 · 10편 (현재 글)

시리즈 다음 글

다음 글(11편)에서는 SELECT 데이터 조회 — WHERE·ORDER BY·LIMIT 표준 + 비교 연산자·LIKE·IN·BETWEEN.

공식 문서: PostgreSQL 18 — Tutorial: Populating a Table에서 더 자세한 사양을 확인할 수 있어요.