SELECT
    TRIM('  안녕하세요  ')           AS trim_both,
    LTRIM('  안녕하세요  ')          AS trim_left,
    RTRIM('  안녕하세요  ')          AS trim_right,
    TRIM(BOTH '0' FROM '00042000')   AS trim_zeros;

SELECT
    UPPER('hello World')   AS upper_case,
    LOWER('Hello WORLD')   AS lower_case,
    INITCAP('hello world') AS title_case;

SELECT
    LENGTH('안녕하세요')        AS byte_length,
    CHAR_LENGTH('안녕하세요')   AS char_length,
    LENGTH('hello')             AS ascii_length;

실행 완료 또는 조회 결과가 표시됩니다.

SELECT
    SUBSTRING('Hello, World!' FROM 1 FOR 5)    AS first_5,
    SUBSTRING('Hello, World!' FROM 8)          AS from_8,
    SUBSTR('Hello, World!', 8, 5)              AS world;

SELECT
    REPLACE('010-1234-5678', '-', '')          AS no_dash,
    REPLACE('홍길동 선생님', ' 선생님', '')     AS name_only;

SELECT
    SPLIT_PART('hong@gmail.com', '@', 1)  AS username,
    SPLIT_PART('hong@gmail.com', '@', 2)  AS domain,
    SPLIT_PART('2024-03-15', '-', 1)      AS year,
    SPLIT_PART('2024-03-15', '-', 2)      AS month;

SELECT
    LEFT('010-1234-5678', 3)    AS area_code,
    RIGHT('010-1234-5678', 4)   AS last_4;

SELECT
    POSITION('@' IN 'user@example.com')     AS at_position,
    STRPOS('user@example.com', '@')         AS at_strpos;

SELECT
    '성: ' || '홍' || ' 이름: ' || '길동'          AS 연결1,
    CONCAT('홍', '길동')                            AS 연결2,
    CONCAT_WS(', ', '서울', '강남구', NULL, '역삼동') AS 연결3;

SELECT
    LPAD('42', 8, '0')        AS zero_padded,
    RPAD('hello', 10, '.')    AS right_padded;

SELECT
    email,
    LOWER(SPLIT_PART(email, '@', 2)) AS domain
FROM users
WHERE email IS NOT NULL;

UPDATE users
SET phone = REGEXP_REPLACE(phone, '[^0-9]', '', 'g')
WHERE phone IS NOT NULL;

SELECT
    full_name,
    SPLIT_PART(full_name, ' ', 1) AS first_name,
    SPLIT_PART(full_name, ' ', 2) AS last_name
FROM users_import;

SELECT *
FROM users
WHERE email !~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';

SELECT
    NOW()             AS now_with_tz,
    CURRENT_TIMESTAMP AS current_ts,
    CURRENT_DATE      AS today,
    CURRENT_TIME      AS now_time,
    CLOCK_TIMESTAMP() AS real_now;

SELECT
    DATE_TRUNC('year',   NOW()) AS this_year_start,
    DATE_TRUNC('month',  NOW()) AS this_month_start,
    DATE_TRUNC('week',   NOW()) AS this_week_start,
    DATE_TRUNC('day',    NOW()) AS today_start,
    DATE_TRUNC('hour',   NOW()) AS this_hour_start,
    DATE_TRUNC('minute', NOW()) AS this_minute_start;

SELECT
    DATE_TRUNC('month', created_at) AS month,
    COUNT(*) AS orders,
    SUM(amount) AS revenue
FROM orders
WHERE status = 'completed'
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY 1;

SELECT
    EXTRACT(YEAR  FROM NOW()) AS year,
    EXTRACT(MONTH FROM NOW()) AS month,
    EXTRACT(DAY   FROM NOW()) AS day,
    EXTRACT(HOUR  FROM NOW()) AS hour,
    EXTRACT(DOW   FROM NOW()) AS day_of_week,
    EXTRACT(DOY   FROM NOW()) AS day_of_year,
    EXTRACT(EPOCH FROM NOW()) AS unix_timestamp;

SELECT
    EXTRACT(DOW FROM created_at) AS day_of_week,
    TO_CHAR(created_at, 'Day')   AS day_name,
    COUNT(*)                     AS order_count
FROM orders
GROUP BY 1, 2
ORDER BY 1;

SELECT
    NOW() + INTERVAL '7 days'          AS next_week,
    NOW() - INTERVAL '30 days'         AS last_month,
    NOW() + INTERVAL '1 year 3 months' AS next_period,
    CURRENT_DATE + 7                   AS also_next_week;

SELECT
    AGE('2024-03-15', '2000-06-15')  AS age_interval,
    AGE(NOW(), created_at)           AS account_age
FROM users WHERE id = 1;

SELECT
    EXTRACT(DAY FROM AGE('2024-03-15', '2024-01-01')) AS days_diff,
    ('2024-03-15'::DATE - '2024-01-01'::DATE)         AS date_subtraction;

SELECT id, name, created_at
FROM users
WHERE created_at >= NOW() - INTERVAL '30 days'
ORDER BY created_at DESC;

SELECT id, name, last_login
FROM users
WHERE last_login < NOW() - INTERVAL '90 days'
   OR last_login IS NULL;

SELECT
    TO_CHAR(NOW(), 'YYYY-MM-DD')             AS iso_date,
    TO_CHAR(NOW(), 'YYYY년 MM월 DD일')       AS korean_date,
    TO_CHAR(NOW(), 'HH24:MI:SS')             AS time_24h,
    TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS')  AS full_datetime,
    TO_CHAR(1234567.89, '999,999,999.99')    AS number_format;

SELECT
    TO_CHAR(created_at, 'YYYY-MM')                  AS month_label,
    COUNT(*)                                         AS order_count,
    TO_CHAR(SUM(amount), 'FM999,999,999')            AS revenue_formatted
FROM orders
GROUP BY TO_CHAR(created_at, 'YYYY-MM')
ORDER BY 1;

SHOW timezone;
SELECT current_setting('TimeZone');

SELECT
    NOW()                                  AS utc_now,
    NOW() AT TIME ZONE 'Asia/Seoul'        AS seoul_time,
    NOW() AT TIME ZONE 'America/New_York'  AS ny_time;

CREATE TABLE events (
    id       BIGSERIAL PRIMARY KEY,
    title    VARCHAR(200) NOT NULL,
    event_at TIMESTAMPTZ NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO events (title, event_at)
VALUES ('서울 컨퍼런스', '2024-06-15 09:00:00 Asia/Seoul');

SELECT event_at FROM events;

SELECT event_at AT TIME ZONE 'Asia/Seoul' FROM events;

WHERE LOWER(email) = 'a@x.com'      (email 컬럼에 B-Tree 인덱스 있음)
   │
   ① 입력 값 — 컬럼 email 의 원본 문자열('A@X.com', 'A@X.COM' …)
   │
   ② 함수 적용 — LOWER()가 값을 변환(대소문자·포맷·형변환·부분추출)
   │     인덱스에 저장된 키는 '원본값'인데, 비교 대상은 '변환된 값'
   │
   ③ 옵티마이저 판단 — 인덱스 키(email)와 LOWER(email)이 서로 다름
   │     원본으로 정렬된 인덱스로는 변환값을 찾아갈 수 없다 → 인덱스 포기
   │
   ④ 실행 — 전체 행을 읽어 행마다 LOWER()를 적용한 뒤 비교(Seq Scan)
   ▼     수십만~수백만 행에서 급격히 느려짐
  결과 (정답이지만 느림)

대조:  WHERE email = 'a@x.com'  →  ②③ 없이 인덱스로 바로 탐색(Index Scan)

SELECT g.month,
       COALESCE(o.orders, 0)  AS orders,
       COALESCE(o.revenue, 0) AS revenue
FROM generate_series(
       date_trunc('month', DATE '2026-01-01'),
       date_trunc('month', DATE '2026-06-01'),
       interval '1 month'
     ) AS g(month)
LEFT JOIN (
  SELECT date_trunc('month', created_at) AS month,
         count(*)    AS orders,
         sum(amount) AS revenue
  FROM orders
  WHERE status = 'completed'
  GROUP BY 1
) o ON o.month = g.month
ORDER BY g.month;