// Developer Guide

PostgreSQL Developer Guide

คู่มือสำหรับ Developer — ตั้งแต่ PSQL Commands, DDL/DML พื้นฐาน ไปจนถึง PL/pgSQL, Triggers และ Advanced SQL Techniques

// PSQL Commands

คำสั่ง PSQL ที่ใช้บ่อย

-- เชื่อมต่อ PostgreSQL
$ psql -h localhost -U postgres -d mydb

-- Meta Commands
\l        -- List databases
\c mydb   -- Connect to database
\dt       -- List tables
\d users  -- Describe table
\di       -- List indexes
\df       -- List functions
\dv       -- List views
\du       -- List users/roles
\timing   -- Toggle query timing
\e        -- Open editor
\i file.sql -- Execute SQL file
\o file.txt -- Output to file
\q        -- Quit
        

-- DDL พื้นฐาน
CREATE DATABASE mydb
  ENCODING 'UTF8'
  LC_COLLATE 'th_TH.UTF-8';

CREATE TABLE users (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email      TEXT NOT NULL UNIQUE,
  name       TEXT NOT NULL,
  role       TEXT DEFAULT 'user',
  is_active  BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Alter Table
ALTER TABLE users ADD COLUMN avatar TEXT;
ALTER TABLE users DROP COLUMN avatar;
ALTER TABLE users RENAME TO members;
        

// DML — Data Manipulation

DML ที่ทรงพลังของ PostgreSQL

-- INSERT with RETURNING
INSERT INTO users (email, name)
VALUES ('john@example.com', 'John')
RETURNING id, created_at;

-- UPSERT (INSERT ON CONFLICT)
INSERT INTO users (email, name)
VALUES ('john@example.com', 'John Doe')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name, updated_at = NOW();

-- UPDATE with RETURNING
UPDATE users SET is_active = FALSE
WHERE last_login < NOW() - INTERVAL '1 year'
RETURNING id, email;

-- DELETE with RETURNING
DELETE FROM logs
WHERE created_at < NOW() - INTERVAL '90 days'
RETURNING count(*);

-- MERGE (PostgreSQL 15+)
MERGE INTO inventory AS target
USING new_stock AS source ON target.product_id = source.product_id
WHEN MATCHED THEN
  UPDATE SET qty = target.qty + source.qty
WHEN NOT MATCHED THEN
  INSERT (product_id, qty) VALUES (source.product_id, source.qty);
      

// Advanced SQL

CTEs & Window Functions

Common Table Expressions (WITH)

-- Recursive CTE สำหรับ Tree Structure
WITH RECURSIVE dept_tree AS (
  SELECT id, name, parent_id, 0 AS level
  FROM departments
  WHERE parent_id IS NULL
  UNION ALL
  SELECT d.id, d.name, d.parent_id, dt.level + 1
  FROM departments d
  JOIN dept_tree dt ON d.parent_id = dt.id
)
SELECT repeat('  ', level) || name AS tree
FROM dept_tree;
          

Window Functions

-- Rank, Running Total, Lead/Lag
SELECT
  name,
  salary,
  RANK() OVER (ORDER BY salary DESC) AS rank,
  SUM(salary) OVER (ORDER BY name) AS running_total,
  LAG(salary) OVER (ORDER BY name) AS prev_salary,
  LEAD(salary) OVER (ORDER BY name) AS next_salary
FROM employees;

-- Percentile
SELECT
  dept_id,
  PERCENTILE_CONT(0.5) WITHIN GROUP
    (ORDER BY salary) AS median_salary
FROM employees GROUP BY dept_id;
          

// PL/pgSQL

PL/pgSQL — Stored Procedures & Functions

-- Function สำหรับคำนวณ
CREATE OR REPLACE FUNCTION get_user_stats(p_user_id BIGINT)
RETURNS TABLE(order_count INT, total_spent NUMERIC, avg_order NUMERIC)
LANGUAGE plpgsql AS $$
BEGIN
  RETURN QUERY
  SELECT
    COUNT(*)::INT,
    COALESCE(SUM(total), 0),
    COALESCE(AVG(total), 0)
  FROM orders
  WHERE user_id = p_user_id AND status = 'completed';
END;
$$;

-- Trigger Function
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  NEW.updated_at := NOW();
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_updated_at
BEFORE UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION set_updated_at();
      

ต้องการเรียนรู้เพิ่มเติม?

ดูคู่มือ DBA และ Performance Tuning สำหรับ PostgreSQL

DBA Guide → Performance Tuning