PostgreSQL 실습

0. 실습 환경 준비 (Ubuntu)

0-1) PostgreSQL 설치 (Ubuntu 22.04/24.04 공통)

sudo apt update
sudo apt install -y postgresql postgresql-contrib
sudo systemctl enable --now postgresql

상태 확인:

systemctl status postgresql --no-pager

0-2) 실습 DB/사용자 생성

PostgreSQL 기본 계정 postgres로 접속:

sudo -iu postgres psql

psql에서 실행:

CREATE DATABASE bootcamp_shop;
CREATE USER bootcamp WITH PASSWORD '1234';
GRANT ALL PRIVILEGES ON DATABASE bootcamp_shop TO bootcamp;
 
\\q

권한부여(스키마 권한까지 안정적으로):

sudo -u postgres psql -d bootcamp_shop -c "GRANT ALL ON SCHEMA public TO bootcamp;"
sudo -u postgres psql -d bootcamp_shop -c "ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO bootcamp;"
sudo -u postgres psql -d bootcamp_shop -c "ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO bootcamp;"

0-3) 접속 테스트

psql "host=localhost dbname=bootcamp_shop user=bootcamp password=1234"

1. 스키마/샘플 데이터 적재

1-1) SQL 파일로 저장 후 실행

예: bootcamp_shop_pg.sql 생성:

cat > bootcamp_shop_pg.sql <<'SQL'
-- =========================================================
-- bootcamp_shop (PostgreSQL) Schema
-- =========================================================
 
-- (옵션) 실습 반복을 위한 초기화
DROP TABLE IF EXISTS payments CASCADE;
DROP TABLE IF EXISTS order_items CASCADE;
DROP TABLE IF EXISTS orders CASCADE;
DROP TABLE IF EXISTS products CASCADE;
DROP TABLE IF EXISTS customers CASCADE;
 
-- =========================================================
-- 1. customers
-- =========================================================
CREATE TABLE customers (
  customer_id  BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  email        TEXT NOT NULL UNIQUE,
  name         TEXT NOT NULL,
  status       TEXT NOT NULL DEFAULT 'ACTIVE' CHECK (status IN ('ACTIVE', 'SUSPENDED')),
  created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
-- =========================================================
-- 2. products
-- =========================================================
CREATE TABLE products (
  product_id   BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  sku          TEXT NOT NULL UNIQUE,
  name         TEXT NOT NULL,
  category     TEXT NOT NULL,
  price        NUMERIC(12,2) NOT NULL CHECK (price >= 0),
  stock        INTEGER NOT NULL CHECK (stock >= 0),
  is_active    BOOLEAN NOT NULL DEFAULT TRUE,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
-- =========================================================
-- 3. orders (order header)
-- =========================================================
CREATE TABLE orders (
  order_id      BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  customer_id   BIGINT NOT NULL REFERENCES customers(customer_id),
  status        TEXT NOT NULL DEFAULT 'CREATED'
               CHECK (status IN ('CREATED', 'PAID', 'CANCELLED', 'REFUNDED')),
  ordered_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  total_amount  NUMERIC(12,2) NOT NULL DEFAULT 0 CHECK (total_amount >= 0)
);
 
-- 인덱스(조회 빈도가 높은 컬럼)
CREATE INDEX idx_orders_customer_id_ordered_at
  ON orders(customer_id, ordered_at DESC);
 
-- =========================================================
-- 4. order_items (order lines)
-- =========================================================
CREATE TABLE order_items (
  order_item_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  order_id      BIGINT NOT NULL REFERENCES orders(order_id) ON DELETE CASCADE,
  product_id    BIGINT NOT NULL REFERENCES products(product_id),
  quantity      INTEGER NOT NULL CHECK (quantity > 0),
  unit_price    NUMERIC(12,2) NOT NULL CHECK (unit_price >= 0),
 
  -- PostgreSQL: 생성 컬럼(Generated Column)로 라인 금액 자동 계산 (MySQL과 문법/지원 차이 주의)
  line_amount   NUMERIC(12,2) GENERATED ALWAYS AS (quantity * unit_price) STORED,
 
  -- 한 주문에서 같은 상품이 중복 라인으로 들어가는 것을 방지(정책에 따라 선택)
  CONSTRAINT uq_order_items_order_product UNIQUE (order_id, product_id)
);
 
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);
 
-- =========================================================
-- 5. payments
-- =========================================================
CREATE TABLE payments (
  payment_id    BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  order_id      BIGINT NOT NULL REFERENCES orders(order_id) ON DELETE CASCADE,
  status        TEXT NOT NULL DEFAULT 'PENDING'
               CHECK (status IN ('PENDING', 'PAID', 'FAILED', 'REFUNDED')),
  method        TEXT NOT NULL DEFAULT 'CARD'
               CHECK (method IN ('CARD', 'TRANSFER', 'VIRTUAL')),
  amount        NUMERIC(12,2) NOT NULL CHECK (amount >= 0),
  paid_at       TIMESTAMPTZ NULL,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
 
  -- 일반적으로 "주문 1건당 결제 1건" 정책이라면 UNIQUE
  CONSTRAINT uq_payments_order UNIQUE (order_id)
);
 
CREATE INDEX idx_payments_status_created_at ON payments(status, created_at DESC);
 
-- =========================================================
-- bootcamp_shop Sample Data (PostgreSQL)
-- =========================================================
 
-- 1) customers
INSERT INTO customers (customer_id, email, name, status, created_at) VALUES
(1, 'minsu@example.com',  '김민수', 'ACTIVE',    NOW() - INTERVAL '40 days'),
(2, 'jiyoon@example.com', '박지윤', 'ACTIVE',    NOW() - INTERVAL '25 days'),
(3, 'seojun@example.com', '이서준', 'ACTIVE',    NOW() - INTERVAL '10 days'),
(4, 'yuna@example.com',   '최유나', 'SUSPENDED', NOW() - INTERVAL '5 days'),
(5, 'dohyuk@example.com', '정도혁', 'ACTIVE',    NOW() - INTERVAL '2 days');
 
-- 2) products
INSERT INTO products (product_id, sku, name, category, price, stock, is_active, created_at) VALUES
(1, 'SKU-KEY-001',  'Mechanical Keyboard', 'INPUT',  119000.00, 50, TRUE,  NOW() - INTERVAL '60 days'),
(2, 'SKU-MOU-002',  'Gaming Mouse',        'INPUT',   59000.00, 80, TRUE,  NOW() - INTERVAL '60 days'),
(3, 'SKU-MON-003',  '27inch Monitor',      'DISPLAY', 279000.00, 20, TRUE,  NOW() - INTERVAL '45 days'),
(4, 'SKU-HDS-004',  'External SSD 1TB',    'STORAGE', 149000.00, 30, TRUE,  NOW() - INTERVAL '30 days'),
(5, 'SKU-USB-005',  'USB-C Hub',           'ACCESS',  39000.00, 100, TRUE, NOW() - INTERVAL '20 days'),
(6, 'SKU-CAM-006',  'Webcam',              'ACCESS',  69000.00, 35, TRUE,  NOW() - INTERVAL '15 days'),
(7, 'SKU-CHA-007',  'Office Chair',        'FURN',   189000.00, 10, TRUE,  NOW() - INTERVAL '90 days'),
(8, 'SKU-MIC-008',  'USB Microphone',      'AUDIO',   99000.00, 25, TRUE,  NOW() - INTERVAL '12 days');
 
-- 3) orders (총액은 일단 0으로 넣고, 아래에서 order_items 기준으로 갱신)
INSERT INTO orders (order_id, customer_id, status, ordered_at, total_amount) VALUES
(1001, 3, 'CREATED',  NOW() - INTERVAL '1 day',  0),
(1002, 2, 'PAID',     NOW() - INTERVAL '3 days', 0),
(1003, 1, 'PAID',     NOW() - INTERVAL '8 days', 0),
(1004, 3, 'CANCELLED',NOW() - INTERVAL '15 days',0),
(1005, 5, 'CREATED',  NOW() - INTERVAL '2 hours',0);
 
-- 4) order_items (unit_price는 “주문 시점 가격”으로 저장하는 패턴)
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
(1001, 1, 1, 119000.00),
(1001, 5, 2,  39000.00),
 
(1002, 2, 1,  59000.00),
(1002, 4, 1, 149000.00),
 
(1003, 3, 1, 279000.00),
(1003, 6, 1,  69000.00),
(1003, 8, 1,  99000.00),
 
(1004, 5, 1,  39000.00),
 
(1005, 2, 2,  59000.00),
(1005, 1, 1, 119000.00);
 
-- 5) orders.total_amount 갱신 (order_items의 line_amount 합계)
UPDATE orders o
SET total_amount = t.sum_amount
FROM (
  SELECT order_id, SUM(line_amount) AS sum_amount
  FROM order_items
  GROUP BY order_id
) t
WHERE o.order_id = t.order_id;
 
-- 6) payments (PAID 상태인 주문만 결제 생성)
INSERT INTO payments (payment_id, order_id, status, method, amount, paid_at, created_at) VALUES
(5001, 1002, 'PAID', 'CARD',     (SELECT total_amount FROM orders WHERE order_id = 1002), NOW() - INTERVAL '3 days', NOW() - INTERVAL '3 days'),
(5002, 1003, 'PAID', 'TRANSFER', (SELECT total_amount FROM orders WHERE order_id = 1003), NOW() - INTERVAL '8 days', NOW() - INTERVAL '8 days');
 
-- 7) 재고 차감(샘플) - “PAID 주문만” 반영했다고 가정
UPDATE products p
SET stock = stock - x.qty
FROM (
  SELECT oi.product_id, SUM(oi.quantity) AS qty
  FROM order_items oi
  JOIN orders o ON o.order_id = oi.order_id
  WHERE o.status = 'PAID'
  GROUP BY oi.product_id
) x
WHERE p.product_id = x.product_id;
 
-- 검증용 조회
SELECT * FROM customers ORDER BY customer_id;
SELECT * FROM products  ORDER BY product_id;
SELECT * FROM orders    ORDER BY order_id;
SELECT order_id, product_id, quantity, unit_price, line_amount
FROM order_items
ORDER BY order_id, product_id;
SELECT * FROM payments ORDER BY payment_id;
 
SQL

실행:

psql "host=localhost dbname=bootcamp_shop user=bootcamp password=1234" -f bootcamp_shop_pg.sql

1-2) 데이터 검증

psql 접속 후:

SELECT COUNT(*) FROM customers;
SELECT COUNT(*) FROM products;
SELECT COUNT(*) FROM orders;
SELECT COUNT(*) FROM order_items;
SELECT COUNT(*) FROM payments;
 
SELECT * FROM orders ORDER BY order_id;

2. 데이터 모델 이해 실습 (ERD 관점)

핵심 관계

customers(1) ── (N) orders
orders(1) ── (N) order_items
products(1) ── (N) order_items
orders(1) ── (0..1) payments (UNIQUE(order_id))

2-1) FK 제약 확인

SELECT
  tc.table_name, kcu.column_name, ccu.table_name AS ref_table, ccu.column_name AS ref_column
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
  ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu
  ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
ORDER BY tc.table_name, kcu.column_name;

실습문제(3단계)

(하) orders 테이블의 status 허용값을 확인하라.
(중) order_items의 유니크 제약(한 주문에서 같은 상품 중복 방지)을 조회로 확인하라.
(상) payments가 “주문 1건당 결제 1건”을 어떻게 보장하는지 제약조건 기반으로 설명하라.

힌트:

SELECT conname, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'order_items'::regclass;

3. 기본 조회(SELECT) + 정렬/필터링

3-1) 고객 상태별 목록

SELECT customer_id, email, name, status, created_at
FROM customers
ORDER BY created_at DESC;

3-2) 활성 상품만 보기 + 재고 임계치

SELECT product_id, sku, name, category, price, stock
FROM products
WHERE is_active = TRUE
  AND stock <= 30
ORDER BY stock ASC, price DESC;

실습문제(3단계)

(하) SUSPENDED 고객만 조회하라.
(중) ACCESS 카테고리 상품을 가격 내림차순으로 3개만 조회하라.
(상) 최근 20일 내 등록된 상품 중 재고가 50 이하인 상품을 “등록일 최신순”으로 조회하라.

4. JOIN 실습

4-1) 주문 헤더 + 고객명

SELECT o.order_id, o.status, o.ordered_at, o.total_amount,
       c.customer_id, c.name, c.email
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
ORDER BY o.ordered_at DESC;

4-2) 주문 상세(라인) + 상품명

SELECT o.order_id, o.status, o.ordered_at,
       p.sku, p.name AS product_name,
       oi.quantity, oi.unit_price, oi.line_amount
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
ORDER BY o.order_id, p.product_id;

4-3) “결제된 주문”만 + 결제수단

SELECT o.order_id, o.total_amount, p.status AS payment_status, p.method, p.paid_at
FROM orders o
JOIN payments p ON p.order_id = o.order_id
WHERE p.status = 'PAID'
ORDER BY p.paid_at DESC;

실습문제(3단계)

(하) 주문 1003의 주문자 이름, 주문상태, 주문일을 조회하라.
(중) 고객별 총 주문금액 합계를 구하고, 합계가 큰 순으로 정렬하라.
(상) “결제(PAID)가 존재하는 주문만” 주문별 상품 라인(상품명, 수량, 라인금액)을 모두 펼쳐서 보여라.

5. 집계(GROUP BY)와 분석 포인트

5-1) 고객별 주문 건수/총액

SELECT c.customer_id, c.name,
       COUNT(o.order_id) AS order_cnt,
       COALESCE(SUM(o.total_amount), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name
ORDER BY total_spent DESC, order_cnt DESC;

5-2) 카테고리별 판매수량/매출(결제된 주문만)

SELECT p.category,
       SUM(oi.quantity) AS sold_qty,
       SUM(oi.line_amount) AS revenue
FROM order_items oi
JOIN orders o   ON o.order_id = oi.order_id
JOIN products p ON p.product_id = oi.product_id
WHERE o.status = 'PAID'
GROUP BY p.category
ORDER BY revenue DESC;

실습문제(3단계)

(하) 주문상태(status)별 주문 건수를 구하라.
(중) 상품별 “결제된 주문” 기준 판매수량 TOP 3를 구하라.
(상) 고객별 “평균 주문금액(AOV)”을 구하고, 주문 2건 이상인 고객만 출력하라.

6. 서브쿼리/CTE(Common Table Expression)로 “읽기 쉬운 SQL” 만들기

6-1) CTE로 주문별 라인합(재계산) vs 저장된 total_amount 비교

WITH calc AS (
  SELECT oi.order_id, SUM(oi.line_amount) AS calc_total
  FROM order_items oi
  GROUP BY oi.order_id
)
SELECT o.order_id, o.total_amount, c.calc_total,
       (o.total_amount - c.calc_total) AS diff
FROM orders o
JOIN calc c ON c.order_id = o.order_id
ORDER BY o.order_id;

6-2) “가장 최근 주문”을 고객별 1건만

SELECT DISTINCT ON (c.customer_id)
  c.customer_id, c.name, o.order_id, o.ordered_at, o.status, o.total_amount
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.ordered_at DESC;

DISTINCT ON은 PostgreSQL에서 자주 쓰는 패턴입니다.

실습문제(3단계)

(하) total_amount가 가장 큰 주문 1건을 찾기(주문ID, 금액).
(중) “최근 7일” 주문만 대상으로 고객별 총 구매액을 구하라.
(상) 고객별 최근 주문 1건에 대해 “주문상품 개수(라인 수)”까지 함께 보여라(CTE 활용 권장).

7. DML 실습: 주문 생성 흐름(트랜잭션)

실무에서는 “주문 생성 → 라인 추가 → 주문 총액 갱신 → 결제 생성 → 재고 차감”이 하나의 트랜잭션으로 처리되는 경우가 많습니다.

7-1) 트랜잭션으로 신규 주문 만들기(예시)

아래 예시는 고객 2가 상품 6(1개), 상품 5(1개) 구매했다고 가정합니다.

BEGIN;
 
-- 1) 주문 헤더 생성
INSERT INTO orders (customer_id, status, ordered_at, total_amount)
VALUES (2, 'CREATED', NOW(), 0)
RETURNING order_id;

반환된 order_id를 예: 1006이라 가정하고 계속 진행(실습에서는 실제 반환값 사용).

-- 2) 라인 추가 (주문 시점 단가 저장)
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES
(1006, 6, 1, (SELECT price FROM products WHERE product_id = 6)),
(1006, 5, 1, (SELECT price FROM products WHERE product_id = 5));
 
-- 3) 주문 총액 갱신
UPDATE orders o
SET total_amount = t.sum_amount
FROM (
  SELECT order_id, SUM(line_amount) AS sum_amount
  FROM order_items
  WHERE order_id = 1006
  GROUP BY order_id
) t
WHERE o.order_id = t.order_id;
 
-- 4) 결제 생성 + 주문 상태 변경(결제 성공 가정)
INSERT INTO payments (order_id, status, method, amount, paid_at)
VALUES (
  1006, 'PAID', 'CARD',
  (SELECT total_amount FROM orders WHERE order_id = 1006),
  NOW()
);
 
UPDATE orders SET status = 'PAID' WHERE order_id = 1006;
 
-- 5) 재고 차감(결제 성공 기준)
UPDATE products p
SET stock = stock - x.qty
FROM (
  SELECT product_id, SUM(quantity) AS qty
  FROM order_items
  WHERE order_id = 1006
  GROUP BY product_id
) x
WHERE p.product_id = x.product_id;
 
COMMIT;

7-2) 검증 쿼리

SELECT * FROM orders WHERE order_id = 1006;
SELECT * FROM order_items WHERE order_id = 1006 ORDER BY product_id;
SELECT * FROM payments WHERE order_id = 1006;
SELECT product_id, stock FROM products WHERE product_id IN (5,6);

실습문제(3단계)

(하) 트랜잭션 없이 주문/라인을 넣었을 때 생길 수 있는 문제를 2가지 적어라.
(중) “재고가 부족하면 주문 생성 자체를 막는” 흐름을 설계해보라(힌트: UPDATE 시점 체크 또는 SELECT FOR UPDATE).
(상) 결제 실패(FAILED)라면 주문 상태와 재고는 어떻게 처리해야 하는지 정책을 정의하고 SQL 흐름으로 제시하라.

8. 인덱스와 실행계획

8-1) 현재 인덱스 확인

SELECT tablename, indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY tablename, indexname;

8-2) 실행계획 확인(EXPLAIN / EXPLAIN ANALYZE)

EXPLAIN
SELECT o.order_id, o.ordered_at, c.name
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.customer_id = 3
ORDER BY o.ordered_at DESC;

실제로 실행시간까지:

EXPLAIN ANALYZE
SELECT o.order_id, o.ordered_at, c.name
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.customer_id = 3
ORDER BY o.ordered_at DESC;

실습문제(3단계)

(하) idx_orders_customer_id_ordered_at 인덱스가 어떤 쿼리에 유리한지 설명하라.
(중) WHERE ordered_at >= NOW() - INTERVAL '7 days' 조건 쿼리에 인덱스가 도움 될지 토론하라(데이터량 관점).
(상) order_items(product_id) 인덱스가 필요한 이유를 “상품별 판매 집계” 쿼리와 연결해 설명하라.

9. 뷰(View)

9-1) 주문 상세 뷰

CREATE OR REPLACE VIEW v_order_detail AS
SELECT
  o.order_id, o.status AS order_status, o.ordered_at, o.total_amount,
  c.customer_id, c.name AS customer_name, c.email,
  p.product_id, p.sku, p.name AS product_name, p.category,
  oi.quantity, oi.unit_price, oi.line_amount
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id;

조회:

SELECT * FROM v_order_detail
ORDER BY order_id, product_id;

실습문제(3단계)

(하) v_order_detail에서 order_id=1003만 조회하라.
(중) v_order_detail을 이용해 고객별 매출 합계를 구하라.
(상) “PAID 주문만” 대상으로 카테고리별 매출 리포트를 v_order_detail 기반으로 작성하라.

10. (선택) 실습 종료 후 초기화

데이터를 다시 처음부터:

DROP TABLE IF EXISTS payments CASCADE;
DROP TABLE IF EXISTS order_items CASCADE;
DROP TABLE IF EXISTS orders CASCADE;
DROP TABLE IF EXISTS products CASCADE;
DROP TABLE IF EXISTS customers CASCADE;

그 후 스키마/샘플데이터 SQL을 재실행.

cd

Tree

PostgreSQL 실습

0. 실습 환경 준비 (Ubuntu)

0-1) PostgreSQL 설치 (Ubuntu 22.04/24.04 공통)

0-2) 실습 DB/사용자 생성

0-3) 접속 테스트

1. 스키마/샘플 데이터 적재

1-1) SQL 파일로 저장 후 실행

1-2) 데이터 검증

2. 데이터 모델 이해 실습 (ERD 관점)

핵심 관계

2-1) FK 제약 확인

실습문제(3단계)

3. 기본 조회(SELECT) + 정렬/필터링

3-1) 고객 상태별 목록

3-2) 활성 상품만 보기 + 재고 임계치

실습문제(3단계)

4. JOIN 실습

4-1) 주문 헤더 + 고객명

4-2) 주문 상세(라인) + 상품명

4-3) “결제된 주문”만 + 결제수단

실습문제(3단계)

5. 집계(GROUP BY)와 분석 포인트

5-1) 고객별 주문 건수/총액

5-2) 카테고리별 판매수량/매출(결제된 주문만)

실습문제(3단계)

6. 서브쿼리/CTE(Common Table Expression)로 “읽기 쉬운 SQL” 만들기

6-1) CTE로 주문별 라인합(재계산) vs 저장된 total_amount 비교

6-2) “가장 최근 주문”을 고객별 1건만

실습문제(3단계)

7. DML 실습: 주문 생성 흐름(트랜잭션)

7-1) 트랜잭션으로 신규 주문 만들기(예시)

7-2) 검증 쿼리

실습문제(3단계)

8. 인덱스와 실행계획

8-1) 현재 인덱스 확인

8-2) 실행계획 확인(EXPLAIN / EXPLAIN ANALYZE)

실습문제(3단계)

9. 뷰(View)

9-1) 주문 상세 뷰

실습문제(3단계)

10. (선택) 실습 종료 후 초기화

그래프 뷰

목차

백링크