PostgreSQL 命令速查

PostgreSQL 快速參考：psql 客戶端指令、DDL／DML 陳述式、join／彙總、索引、交易與備份／還原——附常用參數與實作範例。

35 條命令

連線

psql -h <host> -p <port> -U <user> -d <db>

對遠端或本機資料庫開啟互動式 psql 工作階段。

-h 主機；-p 預設 5432；-U 使用者；-d 資料庫；-W 強制詢問密碼；-f 檔案執行腳本；-c 單一命令

PGPASSWORD=secret psql -h db.example.com -U app -d app_production

psql -U <user> <db> --variable='ON_ERROR_STOP=1' -f <file.sql>

對資料庫執行 SQL 腳本，遇到第一個錯誤就停止。

psql -U app -d app -v ON_ERROR_STOP=1 -f migrations/0001_init.sql

psql 中繼指令

\l[+] \c[onnect] [<db>]

列出資料庫；接著切換到其中之一。

\l+ 含大小／tablespace；\c db user 切換目標

\l+; \c app_dev

\dt[+] [<pattern>]

列出當前 schema（預設 public）中符合模式的資料表。

\dt+ 含大小描述；\dt *.* 所有 schema；\d table 描述資料表

\dt+ public.*

\d <table|view|index|seq|matview>

描述物件結構：欄位、索引、外鍵、註解。

\d orders

\dn / \du / \dv / \di

分別列出 schema／角色／視圖／索引。

\du; \di+ idx_orders_user_id

\timing / \x / \? / \q

切換查詢計時、展開顯示；顯示說明；離開工作階段。

\timing on; \x auto; SELECT * FROM orders LIMIT 2; \q

\copy <table> FROM '<file>' DELIMITER ',' CSV HEADER

從檔案批次載入資料（伺服器端語意，但走用戶端檔案系統）。

\copy orders(order_id,user_id,total,created_at) FROM '/tmp/orders.csv' WITH CSV HEADER

資料庫與 Schema

CREATE DATABASE <db>

建立新資料庫，可指定樣板與各項選項。

OWNER 角色；TEMPLATE template0；ENCODING 'UTF8'；LC_COLLATE

CREATE DATABASE app_dev OWNER app_user ENCODING 'UTF8' TEMPLATE template0

DROP DATABASE <db>

刪除資料庫（不得有連線；常搭配 IF EXISTS 與強制選項）。

DROP DATABASE WITH (FORCE)；postgres 13+

DROP DATABASE IF EXISTS app_old;

CREATE SCHEMA / DROP SCHEMA

為資料表／視圖提供命名空間；屬於預設 search_path 的一部分。

CREATE SCHEMA IF NOT EXISTS audit; DROP SCHEMA staging CASCADE;

資料表與 DDL

CREATE TABLE

定義新資料表，含欄位、型別、預設值與約束。

PRIMARY KEY；FOREIGN KEY ... REFERENCES；UNIQUE；CHECK；INHERITS；PARTITION BY RANGE|LIST

CREATE TABLE orders (id bigserial PRIMARY KEY, user_id int NOT NULL REFERENCES users(id), total numeric(10,2) NOT NULL DEFAULT 0, created_at timestamptz NOT NULL DEFAULT now());

ALTER TABLE

新增／刪除／重新命名欄位，變更型別，加入約束，設定儲存參數。

ADD COLUMN；DROP COLUMN；RENAME TO；ADD CONSTRAINT；ALTER COLUMN ... TYPE

ALTER TABLE orders ADD COLUMN currency char(3) NOT NULL DEFAULT 'USD', ADD CONSTRAINT chk_cur CHECK (currency IN ('USD','EUR','CNY'));

DROP TABLE / TRUNCATE

移除資料表（若被外鍵參照需 CASCADE）；TRUNCATE 可快速清空。

DROP TABLE ... CASCADE；TRUNCATE ... RESTART IDENTITY CASCADE

TRUNCATE TABLE staging.events RESTART IDENTITY CASCADE;

COMMENT ON <table|column> IS '...'

為資料庫物件加上註解——會顯示於 \d+ 與 IDE 提示中。

COMMENT ON COLUMN orders.total IS '稅前小計'

DML

INSERT INTO <table> [(cols)] VALUES (...) [RETURNING ...]

插入一筆或多筆資料；RETURNING 可回傳剛插入的欄位。

INSERT ... SELECT；ON CONFLICT DO NOTHING / DO UPDATE（upsert）；RETURNING

INSERT INTO orders(user_id, total) VALUES (42, 19.95) RETURNING id, created_at;

UPDATE ... SET ... [WHERE ...] [RETURNING ...]

更新符合條件的列；除非刻意，務必加上 WHERE。

UPDATE ... FROM t2 WHERE ...；CTE（WITH ...）+ UPDATE；... RETURNING

UPDATE orders SET status='paid' WHERE user_id = 42 AND created_at < now() - interval '7 days' RETURNING id;

DELETE FROM ... [WHERE ...] [RETURNING ...]

刪除符合條件的列；要清空整張表時 TRUNCATE 更快。

DELETE ... USING t2 WHERE ...；RETURNING

DELETE FROM orders WHERE status='cancelled' AND created_at < now() - interval '30 days' RETURNING id;

查詢與篩選

SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT N OFFSET M

讀取資料列，搭配篩選、排序與分頁。

WHERE col op ANY/ALL(array)；LIMIT n OFFSET n；FETCH FIRST n ROWS ONLY；FOR UPDATE／SHARE 鎖定

SELECT id, status, total FROM orders WHERE user_id = 42 ORDER BY created_at DESC LIMIT 10 OFFSET 20;

DISTINCT / GROUP BY / HAVING

去除重複、依欄位彙總，再對彙總結果做篩選。

GROUP BY ROLLUP(a,b)；GROUPING SETS；HAVING 對彙總過濾；FILTER (WHERE ...) 保留 NULL

SELECT user_id, count(*) FILTER (WHERE status='paid') AS paid, sum(total) AS gmv FROM orders GROUP BY user_id HAVING count(*) >= 3;

連接

[INNER | LEFT | RIGHT | FULL] JOIN ... ON ...

合併多張資料表的列；LEFT JOIN 會保留左側無對應的列。

JOIN LATERAL；NATURAL JOIN；USING(col) 取代 ON；CROSS JOIN 笛卡兒積

SELECT u.email, count(o.id) FROM users u LEFT JOIN orders o ON o.user_id = u.id GROUP BY u.email;

WITH cte AS (...) SELECT ...

把子查詢具體化為 CTE；遞迴 CTE 適用於階層／圖形結構。

WITH RECURSIVE tree AS (SELECT id, parent_id, name FROM cats WHERE parent_id IS NULL UNION ALL SELECT c.id, c.parent_id, c.name FROM cats c JOIN tree t ON c.parent_id = t.id) SELECT * FROM tree;

彙總

count() / sum() / avg() / min() / max() / array_agg() / string_agg()

內建的彙總函式；搭配 FILTER 子句或 GROUP BY 很好用。

count／sum 內可加 DISTINCT；array_agg／string_agg 內可加 ORDER BY

SELECT date_trunc('day', created_at) AS day, sum(total) AS gmv FROM orders GROUP BY day ORDER BY day DESC LIMIT 7;

索引與視圖

CREATE INDEX ...

加速 WHERE／ORDER BY；partial index 可針對熱點子集。

CONCURRENTLY（不取獨佔鎖）；INCLUDE (a,b) 涵蓋式；USING btree|gin|gist|hash；partial WHERE

CREATE INDEX CONCURRENTLY idx_orders_user_created ON orders(user_id, created_at DESC) INCLUDE (total);

EXPLAIN [ANALYZE] <query>

顯示查詢計畫；ANALYZE 會實際執行並回報時間與列數。

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)；FORMAT JSON / TEXT / YAML

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = 42 ORDER BY created_at DESC LIMIT 10;

VACUUM / ANALYZE / REINDEX

維護儲存與統計：回收死 tuple、更新 planner 統計、重建索引。

VACUUM (FULL) 重寫整張表；ANALYZE 更新統計；REINDEX 重建索引；平時由 autovacuum 處理

VACUUM (ANALYZE) orders;

CREATE VIEW / MATERIALIZED VIEW

儲存查詢定義；materialized view 會實際快取資料列（需 refresh）。

CREATE OR REPLACE VIEW；MATERIALIZED VIEW ... WITH NO DATA；REFRESH MATERIALIZED VIEW CONCURRENTLY

CREATE MATERIALIZED VIEW mv_user_orders AS SELECT user_id, count(*) FROM orders GROUP BY user_id; CREATE UNIQUE INDEX ON mv_user_orders(user_id);

交易

BEGIN / COMMIT / ROLLBACK

把多個陳述式組成一個原子工作單元（ACID）。

BEGIN ISOLATION LEVEL SERIALIZABLE；COMMIT／ROLLBACK AND CHAIN；SAVEPOINT 標籤

BEGIN ISOLATION LEVEL REPEATABLE READ; UPDATE orders SET ... WHERE ...; ROLLBACK ON ERROR; COMMIT;

SELECT ... FOR UPDATE / FOR SHARE

在交易結束前鎖定選取的列；防止並發更新。

FOR UPDATE / SHARE；NOWAIT 或 SKIP LOCKED 適合做佇列

SELECT id FROM jobs WHERE status='queued' ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;

備份與還原

pg_dump <db> -Fc -f <file.dump>

將資料庫匯出為 custom 格式檔案（支援平行還原與選擇性還原）。

-Fc custom；-Ft tar；-Fp 純 SQL；--schema=；--exclude-table=；-j 平行（搭配 -Fd）

pg_dump -Fc -d app -f /backup/app-$(date +%F).dump

pg_restore -d <db> <file.dump>

還原 custom／tar／dir 格式的備份；可選擇只還原需要的部分。

--clean 先刪除物件；--if-exists；--schema=；--table=；--jobs=N；--no-owner；--single-transaction

pg_restore -d app_dev --clean --if-exists --jobs=4 /backup/app.dump

psql -d <db> -f <file.sql>

還原純 SQL 格式的備份（pg_dump -Fp 的輸出）。

psql -U app -d app_new -v ON_ERROR_STOP=1 -f /backup/app.sql

使用者與權限

CREATE ROLE / ALTER ROLE

角色可登入（LOGIN）並持有物件；可逐角色授與屬性。

CREATE ROLE reporting LOGIN PASSWORD '...' NOSUPERUSER NOCREATEDB; ALTER ROLE app SET statement_timeout = '5s'

GRANT / REVOKE

授與資料表、schema、資料庫、函式上的權限；亦可到欄位層級。

GRANT SELECT, INSERT ON TABLE ...；GRANT USAGE ON SCHEMA ...；WITH GRANT OPTION

GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting;

\dt <schema>.* / pg_hba.conf

調整 search_path 與可見物件（\dt public.*）；pg_hba.conf 控制誰能連線。

SHOW search_path；ALTER ROLE app IN DATABASE app SET search_path = app, public；pg_hba.conf hostssl all all 0.0.0.0/0 scram-sha-256

ALTER ROLE app SET search_path = app, public;

速查頁版本 1.0.0

適用於 PostgreSQL 14+