책에 포함된 노트비개발자를 위한 PostgreSQL 입문: 업무 데이터를 안전하게 다루는 가장 쉬운 방법

작은 프로젝트로 완성하는 업무용 데이터베이스

2026-07-09Views 4약 8분생성형 AI 활용

Summary

이번 장에서 만들 작은 업무 시스템

지금까지는 SQL 문장을 하나씩 배웠습니다. 이제는 배운 내용을 한 번에 묶어 작은 업무용 데이터베이스를 만들어 보겠습니다. 예제는 작은 쇼핑몰 주문 관리입니다.

업무에서 자주 묻는 질문은 대체로 비슷합니다.

고객 명단을 관리하고 싶다.
상품 재고를 보고 싶다.
주문 내역을 고객명, 상품명과 함께 보고 싶다.
월별 매출과 우수 고객을 알고 싶다.
대량 수정 전에는 안전하게 확인하고 싶다.

엑셀로 하면 고객 시트, 상품 시트, 주문 시트를 따로 만들고 필요할 때 필터와 피벗 테이블을 쓰는 일에 가깝습니다. PostgreSQL에서는 이 시트들을 테이블로 만들고, SQL로 연결해 답을 찾습니다.

이 장의 목표는 완벽한 시스템을 만드는 것이 아닙니다. 작게 만들고, 안전하게 넣고, 필요한 보고서를 뽑고, 백업하는 전체 흐름을 경험하는 것입니다.

작은 프로젝트로 완성하는 업무용 데이터베이스 AI 생성 이미지

1단계: 어떤 장부가 필요한지 정하기

먼저 업무 질문을 보고 필요한 장부를 나눕니다. 한 장부에 모든 것을 넣으면 처음에는 편해 보이지만, 고객 전화번호가 주문마다 반복되고 상품 가격이 여러 곳에 흩어집니다. 나중에 수정할 때 빠뜨리기 쉽습니다.

이 프로젝트에서는 네 개의 테이블을 사용합니다.

테이블	역할	한 행의 의미
`customers`	고객 명단	고객 한 명
`products`	상품 목록과 재고	상품 한 개
`orders`	주문 머리글	주문 한 건
`order_items`	주문 상세	한 주문 안의 상품 한 줄

orders와 order_items를 나누는 이유가 중요합니다. 주문 한 건에 상품이 여러 개 들어갈 수 있기 때문입니다. 주문서는 한 장이지만, 그 안에는 상품 줄이 여러 줄 있을 수 있습니다.

2단계: 테이블 만들기

아래 SQL은 pgAdmin이나 DBeaver의 SQL 편집기에서 실행할 수 있습니다. 실제 업무 데이터베이스가 아니라 연습용 데이터베이스에서 실행하세요.

하려는 일은 이것입니다.

고객, 상품, 주문, 주문상세 장부를 만들고, 각 장부의 대표 번호와 연결 규칙을 정합니다.

CREATE TABLE customers (
    customer_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_name text NOT NULL,
    email text UNIQUE,
    phone text,
    city text,
    joined_date date DEFAULT CURRENT_DATE
);

CREATE TABLE products (
    product_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_name text NOT NULL,
    category text,
    price numeric(12, 2) NOT NULL CHECK (price >= 0),
    stock_qty integer NOT NULL DEFAULT 0 CHECK (stock_qty >= 0),
    active boolean NOT NULL DEFAULT true
);

CREATE TABLE orders (
    order_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id integer NOT NULL REFERENCES customers(customer_id),
    order_date date NOT NULL,
    order_status text NOT NULL DEFAULT '결제완료'
);

CREATE TABLE order_items (
    order_item_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id integer NOT NULL REFERENCES orders(order_id),
    product_id integer NOT NULL REFERENCES products(product_id),
    quantity integer NOT NULL CHECK (quantity > 0),
    unit_price numeric(12, 2) NOT NULL CHECK (unit_price >= 0)
);

결과 해석은 간단합니다. 네 개의 장부가 만들어졌습니다. customer_id, product_id, order_id는 각 행을 구분하는 내부 번호입니다. REFERENCES는 다른 장부의 번호를 가리키는 연결 규칙입니다. 예를 들어 orders.customer_id에는 customers.customer_id에 실제로 존재하는 고객 번호만 들어갈 수 있습니다.

여기서 PRIMARY KEY는 각 행을 구분하는 대표 번호이고, REFERENCES로 연결된 값은 외래키라고 부릅니다. 이름은 조금 어렵지만 생각은 단순합니다. 주문 장부에 고객 번호를 적을 때, 고객 장부에 없는 번호는 적지 못하게 막아 주는 안전장치입니다.

3단계: CSV 데이터를 먼저 검수용 테이블에 넣기

실무에서는 엑셀 파일을 바로 운영 테이블에 넣지 않는 편이 안전합니다. 먼저 CSV UTF-8로 저장한 뒤, 검수용 테이블에 가져와 확인합니다.

예를 들어 고객 CSV를 가져온다고 하겠습니다. 파일에는 다음 열이 있다고 가정합니다.

customer_name,email,phone,city,joined_date

하려는 일은 이것입니다.

고객 CSV를 바로 고객 테이블에 넣지 않고, 먼저 검수용 장부에 담아 오류를 확인합니다.

CREATE TABLE import_customers (
    customer_name text,
    email text,
    phone text,
    city text,
    joined_date text
);

이제 pgAdmin이나 DBeaver의 Import 기능으로 CSV를 import_customers에 넣습니다. 이때 확인할 항목은 네 가지입니다.

Encoding은 가능하면 UTF-8로 선택합니다.
Header가 있으면 첫 줄을 컬럼명으로 사용합니다.
구분자는 보통 쉼표입니다.
날짜는 아직 text로 받습니다. 잘못된 날짜가 섞여도 먼저 확인하기 위해서입니다.

가져온 뒤에는 바로 운영 테이블로 옮기지 말고 검수합니다.

하려는 일은 이것입니다.

이메일이 중복된 고객이 있는지 확인합니다. 앞뒤 공백과 대소문자 차이 때문에 같은 이메일이 다르게 보이는 경우도 함께 줄여 봅니다.

SELECT lower(trim(email)) AS email, COUNT(*) AS count_rows
FROM import_customers
WHERE email IS NOT NULL
  AND trim(email) <> ''
GROUP BY lower(trim(email))
HAVING COUNT(*) > 1;

결과가 나오면 같은 이메일이 여러 줄 있다는 뜻입니다. 실제 같은 고객인지, 다른 고객이 같은 이메일을 쓴 것인지 확인해야 합니다. 운영 테이블의 email에는 UNIQUE 규칙이 있으므로, 중복이 남아 있으면 나중에 INSERT가 실패할 수 있습니다.

하려는 일은 이것입니다.

고객명이나 이메일처럼 중요한 값이 비어 있는 행을 찾습니다.

SELECT *
FROM import_customers
WHERE customer_name IS NULL
   OR trim(customer_name) = ''
   OR email IS NULL
   OR trim(email) = '';

결과가 나오면 입력이 빠진 행입니다. 고객명은 운영 테이블에서 NOT NULL이므로 비어 있으면 넣을 수 없습니다. 이메일도 이 예제에서는 고객을 구분하는 중요한 값으로 사용하므로, 비어 있는 행은 먼저 정리하는 편이 좋습니다.

하려는 일은 이것입니다.

가입일이 날짜처럼 보이지 않는 행을 찾습니다. 여기서는 2026-06-26처럼 연-월-일 형식을 기준으로 확인합니다.

SELECT *
FROM import_customers
WHERE joined_date IS NOT NULL
  AND trim(joined_date) <> ''
  AND trim(joined_date) !~ '^d{4}-d{2}-d{2}$';

결과가 나오면 날짜 형식이 섞여 있다는 뜻입니다. 예를 들어 2026.06.26, 26/06/2026, 미상 같은 값이 있을 수 있습니다. 이런 값은 엑셀에서 정리하거나, 검수용 테이블에서 별도 규칙으로 바꾼 뒤 넣습니다.

이 검사는 날짜의 겉모양을 먼저 보는 단계입니다. 2026-02-31처럼 모양은 맞지만 실제로는 존재하지 않는 날짜가 남아 있으면, 뒤에서 date로 바꿀 때 오류가 날 수 있습니다. 그럴 때는 오류가 난 행을 다시 찾아 고친 뒤 진행하면 됩니다. 중요한 것은 운영 테이블에 바로 넣지 않고, 검수용 테이블에서 한 번 멈춰 확인한다는 점입니다.

4단계: 검수한 데이터를 운영 테이블에 넣기

데이터를 넣는 작업은 실제 장부를 바꾸는 일입니다. 따라서 순서는 항상 같습니다.

백업 → 미리보기 SELECT → 트랜잭션 시작 → INSERT → 확인 SELECT → COMMIT 또는 ROLLBACK

먼저 pgAdmin이나 DBeaver에서 데이터베이스 백업을 하나 남깁니다. 파일명은 예를 들어 다음처럼 씁니다.

shopdb_before_import_customers_20260626.backup

그다음 미리보기 SELECT를 실행합니다.

하려는 일은 이것입니다.

운영 고객 테이블에 들어갈 행을 미리 확인합니다. 이미 같은 이메일이 있는 고객은 제외합니다.

SELECT
    NULLIF(trim(customer_name), '') AS customer_name,
    lower(NULLIF(trim(email), '')) AS email,
    NULLIF(trim(phone), '') AS phone,
    NULLIF(trim(city), '') AS city,
    COALESCE(NULLIF(trim(joined_date), '')::date, CURRENT_DATE) AS joined_date
FROM import_customers ic
WHERE NULLIF(trim(email), '') IS NOT NULL
  AND NOT EXISTS (
      SELECT 1
      FROM customers c
      WHERE c.email = lower(NULLIF(trim(ic.email), ''))
  );

결과 해석은 이렇습니다. 이 결과표가 바로 새로 들어갈 고객 목록입니다. 행 수와 내용을 확인한 뒤에만 INSERT를 실행합니다. 만약 여기서 날짜 변환 오류가 나면, 아직 운영 테이블에 넣기 전이므로 검수용 테이블의 날짜 값을 먼저 고치면 됩니다.

이제 트랜잭션 안에서 INSERT를 실행합니다. 다만 아래 SQL을 사용할 때는 COMMIT까지 한 번에 실행하지 않는 것이 좋습니다. 먼저 BEGIN부터 확인 SELECT까지 실행하고, 결과를 눈으로 확인한 다음 COMMIT 또는 ROLLBACK을 따로 실행하세요.

하려는 일은 이것입니다.

미리 본 고객 목록을 운영 고객 테이블에 넣고, 확정하기 전에 전체 고객 수를 확인합니다.

BEGIN;

INSERT INTO customers (customer_name, email, phone, city, joined_date)
SELECT
    NULLIF(trim(customer_name), '') AS customer_name,
    lower(NULLIF(trim(email), '')) AS email,
    NULLIF(trim(phone), '') AS phone,
    NULLIF(trim(city), '') AS city,
    COALESCE(NULLIF(trim(joined_date), '')::date, CURRENT_DATE) AS joined_date
FROM import_customers ic
WHERE NULLIF(trim(email), '') IS NOT NULL
  AND NOT EXISTS (
      SELECT 1
      FROM customers c
      WHERE c.email = lower(NULLIF(trim(ic.email), ''))
  );

SELECT COUNT(*) AS total_customers
FROM customers;

SELECT COUNT(*) 결과가 예상과 맞고, 오류 메시지도 없다면 확정합니다.

COMMIT;

결과가 예상보다 너무 많거나 이상하면 확정하지 말고 취소합니다.

ROLLBACK;

COMMIT은 확정 도장이고, ROLLBACK은 취소 버튼입니다. SQL 편집기에서 여러 문장을 드래그해 한 번에 실행하는 습관이 있다면 특히 조심하세요. 확인하기 전에는 COMMIT 문장을 선택하지 않는 것이 안전합니다.

상품, 주문, 주문상세 CSV도 같은 방식으로 처리합니다. 처음부터 완벽하게 자동화하려고 하지 말고, 검수용 테이블에 넣고 SELECT로 확인한 뒤 운영 테이블로 옮기는 습관을 들이는 것이 더 중요합니다.

5단계: 업무에서 바로 쓰는 조회 만들기

이제 데이터가 들어왔다면 업무 질문에 답해 봅니다.

먼저 고객명과 상품명이 붙은 주문 내역을 보겠습니다.

하려는 일은 이것입니다.

주문번호, 주문일, 고객명, 상품명, 수량, 판매금액을 한 표로 봅니다.

SELECT
    o.order_id,
    o.order_date,
    c.customer_name,
    p.product_name,
    oi.quantity,
    oi.unit_price,
    oi.quantity * oi.unit_price AS line_amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
ORDER BY o.order_date DESC, o.order_id DESC;

결과 한 줄은 "어느 주문의 어떤 상품 한 줄"을 뜻합니다. 주문 한 건에 상품이 세 개 있으면 결과도 세 줄이 나옵니다. 이것이 주문 머리글과 주문 상세를 나눈 구조의 특징입니다.

6단계: 월별 매출 리포트 만들기

보고서는 원본 장부를 요약한 표입니다. 월별 매출은 주문상세 금액을 월 단위로 묶으면 됩니다.

하려는 일은 이것입니다.

결제완료 주문만 대상으로 월별 주문 수와 매출 합계를 계산합니다.

SELECT
    date_trunc('month', o.order_date)::date AS sales_month,
    COUNT(DISTINCT o.order_id) AS order_count,
    SUM(oi.quantity * oi.unit_price) AS sales_amount
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_status = '결제완료'
GROUP BY date_trunc('month', o.order_date)::date
ORDER BY sales_month;

결과 해석은 쉽습니다. sales_month는 매출 월, order_count는 주문 건수, sales_amount는 해당 월의 매출 합계입니다. 엑셀의 피벗 테이블에서 주문일을 월로 묶고 금액을 합계로 보는 것과 비슷합니다.

7단계: 우수 고객과 재고 부족 상품 찾기

다음은 고객별 누적 구매금액입니다.

하려는 일은 이것입니다.

결제완료 주문을 기준으로 고객별 구매금액을 합산하고, 금액이 큰 고객부터 봅니다.

SELECT
    c.customer_id,
    c.customer_name,
    c.city,
    COUNT(DISTINCT o.order_id) AS order_count,
    SUM(oi.quantity * oi.unit_price) AS total_amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_status = '결제완료'
GROUP BY c.customer_id, c.customer_name, c.city
ORDER BY total_amount DESC
LIMIT 10;

이 결과는 우수 고객 후보 목록입니다. 단순히 주문 건수만 보지 않고 실제 구매금액을 함께 보는 것이 좋습니다.

이번에는 재고 부족 상품을 찾겠습니다.

하려는 일은 이것입니다.

현재 재고가 10개 이하인 판매 중 상품을 찾습니다.

SELECT
    product_id,
    product_name,
    category,
    stock_qty
FROM products
WHERE active = true
  AND stock_qty <= 10
ORDER BY stock_qty ASC, product_name;

결과에 나온 상품은 발주 검토 대상입니다. 재고 기준은 회사마다 다르므로 10이라는 숫자는 업무에 맞게 바꾸면 됩니다.

8단계: 자주 쓰는 리포트는 뷰로 저장하기

월별 매출 리포트를 매주 실행한다면 SQL을 매번 복사해 붙여 넣을 필요가 없습니다. 뷰로 저장하면 이름 붙은 보고서처럼 사용할 수 있습니다.

하려는 일은 이것입니다.

월별 매출 SQL을 monthly_sales_report라는 저장된 조회문으로 만듭니다.

CREATE VIEW monthly_sales_report AS
SELECT
    date_trunc('month', o.order_date)::date AS sales_month,
    COUNT(DISTINCT o.order_id) AS order_count,
    SUM(oi.quantity * oi.unit_price) AS sales_amount
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_status = '결제완료'
GROUP BY date_trunc('month', o.order_date)::date;

이제부터는 긴 SQL 대신 뷰를 조회하면 됩니다.

하려는 일은 이것입니다.

저장해 둔 월별 매출 리포트에서 최근 월부터 확인합니다.

SELECT *
FROM monthly_sales_report
ORDER BY sales_month DESC;

뷰는 원본 데이터를 복사한 엑셀 파일이 아닙니다. 저장된 SELECT 문에 가깝습니다. 원본 주문 데이터가 바뀌면 뷰 결과도 그 기준으로 다시 계산됩니다.

9단계: 대량 수정은 반드시 안전 절차로 처리하기

마지막으로 재고를 조정하는 상황을 보겠습니다. 예를 들어 특정 카테고리의 재고를 5개씩 늘려야 한다고 가정합니다. UPDATE는 실제 데이터를 바꾸므로 바로 실행하면 안 됩니다.

순서는 다시 확인합니다.

백업 → 미리보기 SELECT → BEGIN → UPDATE → 확인 SELECT → COMMIT 또는 ROLLBACK

하려는 일은 이것입니다.

생활용품 카테고리의 현재 재고를 먼저 확인합니다.

SELECT
    product_id,
    product_name,
    category,
    stock_qty
FROM products
WHERE category = '생활용품'
ORDER BY product_id;

이 결과가 수정 대상입니다. 예상한 상품만 나왔는지 확인합니다.

하려는 일은 이것입니다.

생활용품 카테고리 상품의 재고를 5개씩 늘리고, 변경 결과를 확인합니다.

BEGIN;

UPDATE products
SET stock_qty = stock_qty + 5
WHERE category = '생활용품';

SELECT
    product_id,
    product_name,
    category,
    stock_qty
FROM products
WHERE category = '생활용품'
ORDER BY product_id;

확인 SELECT 결과가 맞으면 그때 확정합니다.

COMMIT;

확인 SELECT 결과가 이상하면 확정하지 말고 취소합니다.

ROLLBACK;

대량 수정에서 가장 위험한 실수는 WHERE를 빠뜨리는 것입니다. WHERE 없는 UPDATE products SET stock_qty = stock_qty + 5;는 모든 상품의 재고를 바꿉니다. 그래서 변경 SQL을 쓰기 전에 같은 조건으로 SELECT를 먼저 보는 습관이 필요합니다. 그리고 트랜잭션을 사용할 때는 확인 SELECT를 본 뒤에 COMMIT 또는 ROLLBACK을 따로 실행하는 습관을 들이세요.

마무리 점검표

작은 프로젝트를 끝낼 때는 다음을 확인하세요.

테이블은 고객, 상품, 주문처럼 한 행의 의미가 분명한가?
기본키와 외래키로 연결이 안전하게 잡혀 있는가?
CSV는 운영 테이블이 아니라 검수용 테이블에 먼저 넣었는가?
중복, 빈 값, 날짜 형식 오류를 SELECT로 확인했는가?
조인으로 고객명과 상품명이 붙은 주문 내역을 만들 수 있는가?
집계로 월별 매출, 우수 고객, 재고 부족 상품을 만들 수 있는가?
자주 쓰는 보고서는 뷰로 저장했는가?
INSERT, UPDATE, DELETE 전에는 백업과 미리보기 SELECT를 했는가?
대량 변경은 트랜잭션 안에서 실행하고, 확인 후 COMMIT 또는 ROLLBACK을 선택했는가?

PostgreSQL을 잘 쓴다는 것은 어려운 문법을 많이 외우는 일이 아닙니다. 업무 데이터를 장부처럼 나누어 보관하고, 필요한 질문을 SQL로 차분히 바꾸고, 데이터를 바꿀 때는 안전 절차를 지키는 일입니다.

이제 여러분은 고객 명단, 주문 내역, 상품 재고, 매출 리포트를 하나의 흐름으로 다룰 수 있습니다. 작은 데이터베이스라도 직접 설계하고 운영해 보면 PostgreSQL은 개발자만의 도구가 아니라, 업무를 더 정확하게 만드는 실용적인 장부라는 것을 알게 됩니다.

책 목록

비개발자를 위한 PostgreSQL 입문: 업무 데이터를 안전하게 다루는 가장 쉬운 방법

전체 목차 보기