🐘
PostgreSQL 치트시트

LIMIT OFFSET 페이징, JSONB 연산자, ON CONFLICT(Upsert), RETURNING 문, WITH RECURSIVE, EXPLAIN ANALYZE 및 실무 레퍼런스
#postgresql#postgres#sql#database#rdbms#jsonb#upsert#cte최종 업데이트: 2026-09-26
PostgreSQL 치트시트 핵심 명령어 및 실전 가이드

LIMIT OFFSET 페이징, JSONB 연산자, ON CONFLICT(Upsert), RETURNING 문, WITH RECURSIVE, EXPLAIN ANALYZE 및 실무 레퍼런스
## 1. 페이징 쿼리 (LIMIT & OFFSET)
```sql
-- 1. 표준 LIMIT / OFFSET 페이징
SELECT id, name, created_at
FROM users
ORDER BY id DESC
LIMIT 10 OFFSET 20; -- 21번째부터 10건 조회

-- 2. SQL 표준 문법 (FETCH FIRST)도 동일 지원
SELECT id, name
FROM users
ORDER BY id
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
```

## 2. 날짜 & 시간 연산 (Timestamp & Interval)
```sql
-- 현재 일시
SELECT NOW(), CURRENT_TIMESTAMP;

-- 1. 날짜 가감 (INTERVAL 키워드)
SELECT NOW() + INTERVAL '1 day';     -- 1일 후
SELECT NOW() - INTERVAL '3 months';  -- 3달 전
SELECT NOW() + INTERVAL '2 hours 30 minutes'; -- 2시간 30분 후

-- 2. 포맷 변환 (TO_CHAR / TO_TIMESTAMP)
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS');
SELECT TO_TIMESTAMP('2026-09-26 14:30:00', 'YYYY-MM-DD HH24:MI:SS');

-- 3. 날짜 자르기 (DATE_TRUNC)
SELECT DATE_TRUNC('month', NOW()); -- 이번 달 1일 00:00:00
SELECT DATE_TRUNC('day', NOW());   -- 오늘 00:00:00

-- 4. 연속 날짜/숫자 생성기 (GENERATE_SERIES)
SELECT GENERATE_SERIES('2026-09-01'::DATE, '2026-09-05'::DATE, '1 day'::INTERVAL);
```

## 3. 강력한 타입 변환 (Type Casting `::`)
```sql
-- PostgreSQL 특유의 초간편 이중 콜론(::) 캐스팅 문법
SELECT '123'::INTEGER;
SELECT '2026-09-26'::DATE;
SELECT 45.67::NUMERIC(5, 1); -- 45.7
SELECT 'true'::BOOLEAN;
SELECT '{"name": "kim"}'::JSONB;

-- 표준 CAST 함수와 동일
SELECT CAST('123' AS INTEGER);
```

## 4. JSON & JSONB 다루기 (PostgreSQL의 특장점)
```sql
-- 테이블에 JSONB 컬럼 생성 및 데이터 삽입
CREATE TABLE user_profiles (
  id SERIAL PRIMARY KEY,
  data JSONB NOT NULL
);

INSERT INTO user_profiles (data) 
VALUES ('{"name": "kim", "age": 28, "tags": ["dev", "db"], "address": {"city": "Seoul"}}');

-- 1. -> : JSON 객체/배열 반환 (따옴표 유지)
SELECT data->'address' FROM user_profiles; -- {"city": "Seoul"}

-- 2. ->> : 텍스트 문자열로 추출 (따옴표 제거)
SELECT data->>'name' FROM user_profiles;    -- kim
SELECT data->'address'->>'city' FROM user_profiles; -- Seoul

-- 3. @> : 포함 여부 확인 (GIN 인덱스 지원으로 초고속 검색!)
SELECT * FROM user_profiles WHERE data @> '{"tags": ["db"]}';
SELECT * FROM user_profiles WHERE data @> '{"age": 28}';

-- 4. GIN 인덱스 생성
CREATE INDEX idx_user_data ON user_profiles USING GIN (data);
```

## 5. Upsert: ON CONFLICT (충돌 처리)
```sql
-- 중복 충돌 시 무시 (DO NOTHING)
INSERT INTO users (id, name, email)
VALUES (1, '홍길동', 'hong@test.com')
ON CONFLICT (id) DO NOTHING;

-- 중복 충돌 시 업데이트 (DO UPDATE SET) - EXCLUDED 키워드 활용
INSERT INTO users (id, name, email, updated_at)
VALUES (1, '홍길동', 'hong@test.com', NOW())
ON CONFLICT (id) 
DO UPDATE SET 
  name = EXCLUDED.name,
  email = EXCLUDED.email,
  updated_at = NOW();
```

## 6. RETURNING 절 (등록/수정/삭제 즉시 반환)
```sql
-- 1. INSERT 후 자동 발급된 ID 즉시 반환 (별도 SELECT 불필요!)
INSERT INTO users (name, email) 
VALUES ('이순신', 'lee@test.com') 
RETURNING id, created_at;

-- 2. UPDATE 된 행들의 수정 후 상태 반환
UPDATE orders 
SET status = 'SHIPPED' 
WHERE status = 'PAID' 
RETURNING id, total_amount, updated_at;

-- 3. DELETE 된 레코드의 백업용 데이터 반환
DELETE FROM temp_logs 
WHERE created_at < NOW() - INTERVAL '30 days' 
RETURNING *;
```

## 7. 배열(Array) & 집계 (ARRAY_AGG & STRING_AGG)
```sql
-- 1. 배열 컬럼 생성 및 조작
CREATE TABLE posts (id SERIAL, tags TEXT[]);
INSERT INTO posts (tags) VALUES (ARRAY['postgres', 'sql']);
SELECT * FROM posts WHERE 'sql' = ANY(tags);

-- 2. 여러 행을 하나의 콤마 문자열로 합치기
SELECT dept_id, STRING_AGG(name, ', ' ORDER BY name) AS members
FROM employees GROUP BY dept_id;

-- 3. 여러 행을 하나의 JSON/Array 배열로 묶기
SELECT dept_id, ARRAY_AGG(id ORDER BY id) AS member_ids
FROM employees GROUP BY dept_id;
```

## 8. 계층형 재귀 쿼리 (WITH RECURSIVE)
```sql
WITH RECURSIVE org_chart AS (
  -- 1. 초기 쿼리 (루트 대표)
  SELECT emp_id, manager_id, emp_name, 1 AS level, emp_name::TEXT AS path
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- 2. 재귀 쿼리 (하위 팀원)
  SELECT e.emp_id, e.manager_id, e.emp_name, o.level + 1, o.path || ' > ' || e.emp_name
  FROM employees e
  INNER JOIN org_chart o ON e.manager_id = o.emp_id
)
SELECT level, emp_name, path FROM org_chart ORDER BY path;
```

## 9. 성능 분석 (EXPLAIN ANALYZE & BUFFERS)
```sql
-- 실제 쿼리를 실행하여 실제 수행 시간(Actual Time) 및 I/O 버퍼 히트율 확인
EXPLAIN (ANALYZE, BUFFERS, COSTS, VERBOSE)
SELECT * FROM users WHERE email = 'test@example.com';

-- 💡 체크포인트:
-- Seq Scan (풀 스캔) vs Index Scan / Index Only Scan
-- Buffers: shared hit (메모리 캐시 히트) vs read (디스크 I/O)
```

## 10. 실무 유지보수 & 세션 관리 (pg_stat_activity)
```sql
-- 1. 현재 실행 중인 쿼리 및 대기 세션 확인
SELECT pid, usename, client_addr, state, query_start, query
FROM pg_stat_activity
WHERE state != 'idle' AND pid <> pg_backend_pid()
ORDER BY query_start;

-- 2. 세션 안전 취소 (쿼리만 취소)
SELECT pg_cancel_backend(12345);

-- 3. 세션 강제 종료 (커넥션 끊기)
SELECT pg_terminate_backend(12345);

-- 4. 테이블 통계 갱신 및 데드 튜플 정리 (VACUUM)
VACUUM (VERBOSE, ANALYZE) users;
```
💬
PostgreSQL 치트시트

의견 및 질문

댓글 수정

댓글 삭제
