PostgreSQL - 기본 문법, 날짜 처리, CRUD

SQL을 처음 배우는 분들이라면 DDL(데이터 정의), DML(데이터 조작), 그리고 날짜·조건 처리까지 한 번에 정리된 예제가 필요합니다. 이번 글에서는 도서 쇼핑몰을 가정해 book_shop 스키마를 사용하며, 실제로 많이 쓰이는 구문을 단계별로 살펴보겠습니다. 복사해서 바로 실행해도 오류가 없도록 작성했으니 실습용으로 활용해 보세요.

---

1. CRUD 기본기

1‑1. 테이블 생성 (CREATE)

CREATE TABLE book_shop.bs_member (
    member_id   SERIAL PRIMARY KEY,
    email       VARCHAR(320) UNIQUE NOT NULL,
    password    VARCHAR(255)        NOT NULL,
    nickname    VARCHAR(40)         NOT NULL,
    phone       VARCHAR(20),
    join_date   DATE                DEFAULT CURRENT_DATE,
    last_login  TIMESTAMP           DEFAULT CURRENT_TIMESTAMP
);

1‑2. 테이블 삭제 (DROP)

DROP TABLE book_shop.bs_member;

1‑3. 데이터 삽입 (INSERT)

INSERT INTO book_shop.bs_member (
    member_id,
    email,
    password,
    nickname,
    phone,
    join_date,
    last_login
) VALUES (
    1,
    'alice@example.com',
    'p@ssw0rd!',
    'Alice',
    '010‑5555‑1234',
    '2024‑01‑15',
    '2024‑04‑11 09:30:00'
);

1‑4. 데이터 제거 (DELETE)

DELETE FROM book_shop.bs_member
WHERE email = 'alice@example.com';

1‑5. 데이터 수정 (UPDATE)

UPDATE book_shop.bs_member
SET    nickname = 'AliceWonder',
       phone    = '010‑0000‑0000'
WHERE  member_id = 1;

---

2. 데이터 조회 & 날짜 다루기

2‑1. 기본 조회 (SELECT)

-- 전체 조회
SELECT *
FROM   book_shop.bs_member;

-- 컬럼 지정 조회
SELECT member_id,
       email,
       nickname,
       phone,
       join_date,
       last_login
FROM   book_shop.bs_member;

2‑2. where 1 = 1 패턴

SELECT *
FROM   book_shop.bs_member
WHERE  1 = 1
  AND  nickname LIKE 'A%'
  AND  phone IS NOT NULL;

2‑3. 날짜 비교 (>, =, <, BETWEEN)

-- 최근 7일 이내 로그인
SELECT *
FROM   book_shop.bs_member
WHERE  last_login >= CURRENT_TIMESTAMP - INTERVAL '7 days';

-- 특정 날짜 일치
SELECT *
FROM   book_shop.bs_member
WHERE  join_date = DATE '2024‑01‑15';

-- 날짜 범위 지정
SELECT *
FROM   book_shop.bs_member
WHERE  join_date BETWEEN '2024‑01‑01' AND '2024‑03‑31';

2‑4. 날짜 함수 활용

-- 오늘·어제·내일
SELECT CURRENT_DATE      AS today,
       CURRENT_DATE - 1  AS yesterday,
       CURRENT_DATE + 1  AS tomorrow;

-- 두 날짜 차이(일)
SELECT DATE '2024‑04‑01' - DATE '2024‑03‑20' AS diff_days;  -- 12

-- 월별 가입자 수
SELECT EXTRACT(MONTH FROM join_date) AS mth,
       COUNT(*)                      AS cnt
FROM   book_shop.bs_member
GROUP  BY 1
ORDER  BY 1;

2‑5. to_char / to_timestamp 예시

-- 날짜 포맷 변환
SELECT member_id,
       TO_CHAR(join_date, 'YYYY/MM/DD') AS join_ymd
FROM   book_shop.bs_member;

-- 문자 → 타임스탬프 변환 후 비교
SELECT *
FROM   book_shop.bs_member
WHERE  last_login <= TO_TIMESTAMP('20240411090000', 'YYYYMMDDHH24MISS');

---

3. 실전 활용 구문

3‑1. 테이블 합치기 (JOIN)

-- 주문 테이블 생성 예시
CREATE TABLE book_shop.bs_order (
    order_id    SERIAL PRIMARY KEY,
    member_id   INT            NOT NULL REFERENCES book_shop.bs_member(member_id),
    total_price NUMERIC(10,2)  NOT NULL,
    order_date  TIMESTAMP      DEFAULT CURRENT_TIMESTAMP
);

-- 회원 + 주문 내역 조회
SELECT m.member_id,
       m.nickname,
       o.order_id,
       o.total_price,
       o.order_date
FROM   book_shop.bs_member AS m
JOIN   book_shop.bs_order  AS o
  ON   m.member_id = o.member_id;

3‑2. 조건문 (CASE WHEN)

SELECT order_id,
       total_price,
       CASE
           WHEN total_price >= 100000 THEN 'VIP'
           WHEN total_price >=  50000 THEN 'GOLD'
           ELSE 'SILVER'
       END AS grade
FROM   book_shop.bs_order;

3‑3. IN 구문으로 다중 조건

SELECT *
FROM   book_shop.bs_member
WHERE  email IN ('alice@example.com',
                 'bob@example.com',
                 'carol@example.com');

3‑4. BETWEEN ... AND ... 로 날짜 필터

SELECT *
FROM   book_shop.bs_order
WHERE  order_date BETWEEN DATE '2024‑04‑01' AND DATE '2024‑04‑30';

---

4. 마무리

CRUD부터 날짜·조건 처리, JOIN·CASE 문까지 자주 쓰는 패턴을 도서 쇼핑몰 예제로 묶어 보았습니다. 스키마와 컬럼명이 달라도 논리는 동일하니, 직접 테이블을 만들어 실행하며 손에 익혀 보세요. 질문이나 피드백은 언제든 댓글로 남겨 주세요!

 

PostgreSQL 추가 함수 보러가기

https://carpe08.tistory.com/373

[전부 모음] PostgreSQL 날짜 관련된 함수

https://link.coupang.com/a/cpQvuS

이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다.