← DoniKit 웹 앱으로

DB 코드 스니펫 25종

PostgreSQL 실무 쿼리 모음입니다. 함수·프로시저·익명 블록(DO $$), 윈도우 함수, 재귀 CTE, DDL, 데이터 이관, 배열 컬럼, 다건 INSERT(ON CONFLICT)까지 — 그대로 실행해 보며 다듬을 수 있는 완성형 예제입니다.

웹 앱에서는 빈칸을 채워 완성된 코드를 바로 복사할 수 있습니다.

-- 기본 조회: 조건 · 정렬 · 페이지네이션
SELECT id, name, age
FROM users
WHERE age >= 20 AND name ILIKE '%kim%'
ORDER BY age DESC, name ASC
LIMIT 20 OFFSET 0;

-- INNER: 양쪽 다 있는 행만 · LEFT: 왼쪽 전부 + 매칭(없으면 NULL)
SELECT u.id, u.name, o.amount
FROM users u
INNER JOIN orders o ON o.user_id = u.id
LEFT JOIN coupons c ON c.user_id = u.id
WHERE o.amount > 0;

-- 삽입: RETURNING으로 생성된 id를 바로 받는다
INSERT INTO users (name, age)
VALUES ('홍길동', 30)
RETURNING id;

-- 수정
UPDATE users
SET age = age + 1
WHERE id = 1;

-- 삭제
DELETE FROM users
WHERE id = 1;

-- 그룹별 집계: 도시별 인원 · 평균 나이
SELECT city, COUNT(*) AS cnt, AVG(age)::numeric(10, 1) AS avg_age
FROM users
GROUP BY city
HAVING COUNT(*) >= 5
ORDER BY cnt DESC;

-- 있으면 수정, 없으면 삽입 (PostgreSQL UPSERT)
INSERT INTO users (id, name, age)
VALUES (1, '홍길동', 30)
ON CONFLICT (id) DO UPDATE
SET name = EXCLUDED.name,
    age  = EXCLUDED.age;

-- 여러 행을 한 문장으로 (VALUES를 쉼표로 나열)
INSERT INTO users (id, name, age)
VALUES
  (1, '홍길동', 30),
  (2, '김철수', 28),
  (3, '이영희', 35);

-- 이미 있는 행은 조용히 건너뛰기 (에러 없이 그 행만 스킵)
INSERT INTO users (id, name, age)
VALUES
  (1, '홍길동', 30),
  (2, '김철수', 28)
ON CONFLICT DO NOTHING;

-- 충돌 대상을 지정 (유니크 제약이 여러 개일 때 그중 하나만 무시)
INSERT INTO users (id, name, age)
VALUES (1, '홍길동', 30)
ON CONFLICT (id) DO NOTHING;

-- 실제로 들어간 행만 돌려받기 — 건너뛴 행은 안 나온다
INSERT INTO users (id, name, age)
VALUES (1, '홍길동', 30), (2, '김철수', 28)
ON CONFLICT (id) DO NOTHING
RETURNING id;

-- 조회 결과를 그대로 적재 (INSERT ... SELECT)
INSERT INTO users (id, name, age)
SELECT id, name, age
FROM users_stage
ON CONFLICT (id) DO NOTHING;

-- tags text[] 처럼 배열 컬럼을 가진 테이블 기준
-- 뒤에 값 하나 붙이기
UPDATE posts
SET tags = array_append(tags, 'postgres')
WHERE id = 1;

-- || (연결 연산자) — 여러 개도 한 번에
UPDATE posts
SET tags = tags || ARRAY['sql', 'db']
WHERE id = 1;

-- 앞에 붙이기
UPDATE posts
SET tags = array_prepend('first', tags)
WHERE id = 1;

-- 값 제거 — 같은 값이 여러 개면 전부 사라진다
UPDATE posts
SET tags = array_remove(tags, 'postgres')
WHERE id = 1;

-- 1) 이미 있으면 그대로 두기 (중복 방지)
UPDATE posts
SET tags = array_append(tags, 'postgres')
WHERE id = 1
  AND NOT ('postgres' = ANY(tags));

-- 2) NULL 안전 — 컬럼이 NULL이어도 빈 배열에서 시작
UPDATE posts
SET tags = COALESCE(tags, '{}') || ARRAY['new']
WHERE id = 1;

-- 3) 여러 값 빼기 ① 중첩 (두세 개면 이게 간단)
UPDATE posts
SET tags = array_remove(array_remove(tags, 'a'), 'b')
WHERE id = 1;

-- 4) 여러 값 빼기 ② unnest + 재집계 (뺄 값이 많을 때 = 차집합)
UPDATE posts
SET tags = (
  SELECT COALESCE(array_agg(t), '{}')
  FROM unnest(tags) AS t
  WHERE t <> ALL(ARRAY['a', 'b', 'c'])
)
WHERE id = 1;

-- 값 하나가 들어있는 행
SELECT id, tags
FROM posts
WHERE 'postgres' = ANY(tags);

-- @> 전부 포함 · && 하나라도 겹침
SELECT id FROM posts WHERE tags @> ARRAY['sql', 'db'];
SELECT id FROM posts WHERE tags && ARRAY['sql', 'db'];

-- 배열을 행으로 펼쳐 집계 (태그별 개수)
SELECT t AS tag, COUNT(*) AS cnt
FROM posts, unnest(tags) AS t
GROUP BY t
ORDER BY cnt DESC;

-- 길이 · 원소 접근 (인덱스는 1부터)
SELECT array_length(tags, 1) AS len, tags[1] AS first_item
FROM posts
WHERE id = 1;

-- 포함 검색이 잦으면 GIN 인덱스
CREATE INDEX idx_posts_tags ON posts USING GIN (tags);

-- 값 하나를 돌려주는 함수 (변수 선언 · IF 분기 · RETURN)
CREATE OR REPLACE FUNCTION fn_user_name(p_id bigint)
RETURNS text
LANGUAGE plpgsql
AS $$
DECLARE
  v_name text;
BEGIN
  SELECT name INTO v_name FROM users WHERE id = p_id;

  IF v_name IS NULL THEN
    RETURN '(없음)';
  END IF;
  RETURN v_name;
END;
$$;

-- 호출: 함수는 SELECT로 부른다
SELECT fn_user_name(1);

-- 여러 행(결과셋)을 돌려주는 함수 — 호출부에서 테이블처럼 쓴다
CREATE OR REPLACE FUNCTION fn_adult_users(p_min_age int)
RETURNS TABLE (id bigint, name text, age int)
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN QUERY
  SELECT u.id, u.name, u.age
  FROM users u
  WHERE u.age >= p_min_age
  ORDER BY u.age DESC;
END;
$$;

-- 호출: FROM 자리에 넣는다
SELECT * FROM fn_adult_users(20);

-- 프로시저: 반환값 없는 작업 묶음 · 본문에서 COMMIT 가능
CREATE OR REPLACE PROCEDURE sp_withdraw(p_id bigint, p_amount numeric)
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE accounts
  SET balance = balance - p_amount
  WHERE id = p_id;

  INSERT INTO account_logs (account_id, amount, logged_at)
  VALUES (p_id, p_amount, now());

  COMMIT; -- 함수는 불가, 프로시저만 가능
END;
$$;

-- 호출: SELECT가 아니라 CALL
CALL sp_withdraw(1, 500);

-- 익명 블록: 함수·프로시저를 만들지 않고 PL/pgSQL을 그 자리에서 실행한다.
-- 일회성 백필·마이그레이션·운영 점검처럼 "한 번 돌리고 버릴" 로직에 쓴다.
DO $$
DECLARE
  v_count integer;
BEGIN
  SELECT count(*) INTO v_count
  FROM orders
  WHERE status = 'PENDING';

  IF v_count = 0 THEN
    RAISE NOTICE '대상 없음 — 건너뜀';
    RETURN; -- 블록 종료 (반환값은 없다)
  END IF;

  UPDATE orders
  SET status = 'DONE', updated_at = now()
  WHERE status = 'PENDING';

  RAISE NOTICE '% 건 처리 완료', v_count;

EXCEPTION WHEN OTHERS THEN
  RAISE NOTICE '실패: %', SQLERRM;
  RAISE; -- 다시 던져야 롤백된다. 삼키면 그대로 커밋됨
END $$;

-- 유저별 주문 중 "가장 최근 1건"만 (조회 화면 단골)
SELECT *
FROM (
  SELECT o.*,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM orders o
) t
WHERE t.rn = 1;

-- 순위 · 누계
SELECT name, score,
       RANK() OVER (ORDER BY score DESC)     AS ranking,
       SUM(score) OVER (ORDER BY score DESC) AS running_total
FROM users;

-- 1단계: 월별 매출 집계 → 2단계: 전월 대비 증감
WITH monthly AS (
  SELECT date_trunc('month', created_at) AS ym, SUM(amount) AS total
  FROM orders
  GROUP BY 1
),
compared AS (
  SELECT ym, total,
         LAG(total) OVER (ORDER BY ym) AS prev_total
  FROM monthly
)
SELECT ym, total, total - prev_total AS diff
FROM compared
ORDER BY ym;

-- 기준 사원 아래 모든 부하를 계층으로 (자기참조 테이블 employees: id, name, manager_id)
WITH RECURSIVE subordinates AS (
  -- 앵커(시작 행): 기준 사원 한 명
  SELECT id, name, manager_id, 1 AS depth
  FROM employees
  WHERE id = 1

  UNION ALL

  -- 재귀: 직전 결과(s)의 부하를 계속 이어붙인다
  SELECT e.id, e.name, e.manager_id, s.depth + 1
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.id
)
SELECT repeat('    ', depth - 1) || name AS tree, depth
FROM subordinates
ORDER BY depth, name;

-- 1년 지난 주문을 아카이브로 "옮기기": 삭제하며 뽑은 행을 그대로 적재 (원자적)
WITH moved AS (
  DELETE FROM orders
  WHERE created_at < now() - interval '1 year'
  RETURNING *
)
INSERT INTO orders_archive
SELECT * FROM moved;

-- 묶음 작업: 전부 성공 아니면 전부 취소
BEGIN;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

-- 중간 저장점 — 일부만 되돌리기
SAVEPOINT before_log;
INSERT INTO transfer_logs (from_id, to_id, amount) VALUES (1, 2, 500);
ROLLBACK TO SAVEPOINT before_log; -- 저장점 이후만 취소

COMMIT; -- 확정 (전체 취소는 COMMIT 대신 ROLLBACK;)

-- 일회성 이관: 매핑을 TEMP TABLE로 한 번 구체화 → dry-run·본 이관 전 단계가 같은 기준 공유
-- (CTE WITH는 문장 하나에만 유효 — 여러 문장이 공유하려면 TEMP TABLE)
BEGIN;

-- ON COMMIT DROP: COMMIT/ROLLBACK 때 자동 삭제 → 재실행해도 already exists 없음.
-- 반드시 BEGIN 뒤(트랜잭션 안)에서 생성 — 밖에서 만들면 문장이 끝나는 즉시 사라진다.
CREATE TEMP TABLE mig_map ON COMMIT DROP AS
SELECT s.id                             AS legacy_store_id,
       replace(s.store_key, 'old-', '') AS member_region_code, -- 실소속 코드 (하위 뎁스 포함)
       hq.id                            AS target_store_id     -- 통합될 본부 매장
FROM stores s
JOIN region_codes rc ON rc.code = replace(s.store_key, 'old-', '')
JOIN stores hq       ON hq.region_code = rc.root_code -- 행이 실존하는 레벨(root)로 — parent는 매장 행이 없다
WHERE s.store_key LIKE 'old-%';

-- 1) dry-run SELECT로 건수·샘플 검토 → 문제 있으면 ROLLBACK; 후 중단
SELECT count(*) FROM mig_map;
-- 2) 본 이관 문장 순서대로 실행 — 문장마다 "UPDATE n" 건수가 예상과 맞는지 확인
-- 3) 검증 SELECT (이관 결과 샘플 확인)

COMMIT; -- 이상하면 대신 ROLLBACK;

-- 매핑 조인 UPDATE: 매핑되는 행만 갱신 — 스칼라 서브쿼리의 "조용한 NULL" 원천 차단
UPDATE orders o
SET store_id = m.target_store_id
FROM mig_map m
WHERE o.store_id = m.legacy_store_id;

-- 이동시키면 UNIQUE(store_id, account_id) 충돌인 행은 먼저 제거
DELETE FROM store_staff st
USING mig_map m
WHERE st.store_id = m.legacy_store_id
  AND EXISTS (SELECT 1 FROM store_staff x
              WHERE x.store_id = m.target_store_id AND x.account_id = st.account_id);

-- 소속 이관: 본부로 이동 + 실소속 코드 기록
UPDATE store_staff st
SET store_id    = m.target_store_id,
    region_code = m.member_region_code,
    updated_at  = CURRENT_TIMESTAMP
FROM mig_map m
WHERE st.store_id = m.legacy_store_id;

-- 원본은 hard DELETE 대신 소프트 삭제 — 이력 테이블 FK가 참조 중이면 삭제가 실패하거나 이력을 잃는다
UPDATE stores s
SET status = 'CLOSED', deleted_at = CURRENT_TIMESTAMP
WHERE s.id IN (SELECT legacy_store_id FROM mig_map);

-- (a) 매핑 안 되는 행: 기준 테이블에 없는 코드 → 개별 판단 대상
SELECT s.id, s.store_key, s.name
FROM stores s
WHERE s.store_key LIKE 'old-%'
  AND NOT EXISTS (SELECT 1 FROM region_codes rc
                  WHERE rc.code = replace(s.store_key, 'old-', ''));

-- (b) 통합 후 UNIQUE 충돌 예정: 같은 대상으로 2건 이상 모이는 행
SELECT m.target_store_id, st.account_id, count(*)
FROM store_staff st
JOIN mig_map m ON m.legacy_store_id = st.store_id
GROUP BY 1, 2
HAVING count(*) > 1;

-- (c) 테이블의 PK·UNIQUE·FK 제약 정의 조회 (rename·통합 전 충돌 축 확인)
SELECT conname, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'stores'::regclass;

-- (d) 삭제 전 — 이 테이블을 참조하는 FK 전수 조회
SELECT conrelid::regclass AS referencing, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE confrelid = 'stores'::regclass;

CREATE TABLE orders (
  id         bigserial PRIMARY KEY,
  user_id    bigint      NOT NULL REFERENCES users (id),
  status     varchar(20) NOT NULL DEFAULT 'READY',
  amount     numeric(12, 2) NOT NULL CHECK (amount >= 0),
  memo       text,
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (user_id, created_at)
);

-- 자주 검색하는 컬럼은 인덱스
CREATE INDEX idx_orders_user ON orders (user_id);

-- 컬럼 추가
ALTER TABLE orders ADD COLUMN cancel_yn char(1) DEFAULT 'N';

-- 현재 시각 · 기간 연산 · 구간 절단
SELECT
  now()                          AS current_ts,
  now() - interval '7 days'      AS week_ago,
  date_trunc('month', now())     AS month_start,
  age(now(), '2000-01-01'::date) AS elapsed;

-- 쓰는 법: 비교할 기준 형태를 하나 고르고, 그 영역의 변환으로 완성해서 비교한다.
-- 예시 값: created_at = timestamp 컬럼(안의 값: 2026-07-30 14:30:25),
--          '20260730' = 자바·화면에서 넘어온 문자열 파라미터

-- ══ 1. 'yyyymmdd' 문자열로 완성해서 비교 — 완성형: '20260730' ══════
SELECT * FROM orders
WHERE  to_char(created_at, 'YYYYMMDD') = '20260730';  -- timestamp(2026-07-30 14:30:25) → '20260730'

-- varchar 'yyyymmdd' 컬럼 ↔ timestamp 컬럼 조인도 같은 방식
SELECT * FROM legacy_t l
JOIN   modern_t m ON l.ymd = to_char(m.created_at, 'YYYYMMDD');

-- ══ 2. 'yyyymmddhh24miss' 문자열로 완성해서 비교 — 완성형: '20260730143025' ══
SELECT * FROM orders
WHERE  to_char(created_at, 'YYYYMMDDHH24MISS')       -- timestamp → '20260730143025'
       >= '20260730' || '000000';                      -- '20260730' → '20260730000000' (자정 붙이기)

-- ══ 3. timestamp로 완성해서 비교 — 완성형: 2026-07-30 14:30:25 ══
-- 문자열 쪽을 타입으로 바꾼다 — 컬럼이 그대로라 인덱스를 탄다 (대량 테이블 권장)
SELECT * FROM orders
WHERE  created_at >= to_timestamp('20260730143025', 'YYYYMMDDHH24MISS'); -- '20260730143025' → 2026-07-30 14:30:25

-- 하루 범위 조건 (BETWEEN 대신 미만 비교)
SELECT * FROM orders
WHERE  created_at >= to_date('20260730', 'YYYYMMDD')                -- '20260730' → 2026-07-30 00:00:00
AND    created_at <  to_date('20260730', 'YYYYMMDD') + interval '1 day';

-- ══ 4. date로 완성해서 비교 — 완성형: 2026-07-30 (일 단위) ══
SELECT * FROM orders
WHERE  created_at::date = to_date('20260730', 'YYYYMMDD'); -- 2026-07-30 14:30:25 → 2026-07-30 (시각 버림)

-- 연결 · 부분추출 · 치환 · 분리 · 패턴
SELECT
  'a' || '-' || 'b'               AS concat,
  substring('hello' FROM 1 FOR 3) AS sub,
  replace('a,b,c', ',', ';')      AS replaced,
  split_part('a,b,c', ',', 2)     AS second,
  'HELLO' ILIKE 'hel%'            AS matched;