PostgreSQL 基础命令
本文汇总 psql、库表 DDL、增删改查与常用运维 SQL。类型与 JSONB 见 架构与数据类型；高级查询见 高级查询。
对照：MySQL 基础命令。
连接与 psql
# 本机默认用户 postgres
psql -U postgres
# 指定主机、端口、库
psql -h 127.0.0.1 -p 5432 -U app -d app_db
# 单次执行
psql -U app -d app_db -c "SELECT COUNT(*) FROM orders;"psql 元命令（反斜杠）
| 命令 | 说明 |
|---|---|
\l | 列出所有数据库 |
\c mydb | 切换数据库 |
\dt | 当前 schema 下所有表 |
\d users | 表结构 |
\du | 角色列表 |
\q | 退出 |
会话内常用 SQL
SELECT version();
SELECT current_database();
SELECT current_user;
SET client_encoding TO 'UTF8';库与表（DDL）
数据库
CREATE DATABASE app_db
ENCODING 'UTF8'
LC_COLLATE 'en_US.UTF-8'
LC_CTYPE 'en_US.UTF-8';
DROP DATABASE IF EXISTS test_db;建表示例
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(64) NOT NULL,
email VARCHAR(128) NOT NULL UNIQUE,
status SMALLINT NOT NULL DEFAULT 1,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_users_status_created ON users(status, created_at);自增：SERIAL / BIGSERIAL 见 架构与数据类型。
改表
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users ALTER COLUMN name TYPE VARCHAR(128);
ALTER TABLE users ADD CONSTRAINT uk_phone UNIQUE (phone);
ALTER TABLE users DROP CONSTRAINT uk_phone;
ALTER TABLE users DROP COLUMN phone;删表 / 清空
DROP TABLE IF EXISTS tmp_import;
TRUNCATE TABLE logs_2023; -- 快；有外键时需 TRUNCATE ... CASCADE查询（SELECT）
SELECT id, name, email FROM users WHERE status = 1;
SELECT * FROM users WHERE id = 1001;
SELECT id, name FROM users ORDER BY created_at DESC LIMIT 20;
SELECT id, name FROM users WHERE id > 1000 ORDER BY id LIMIT 20;
SELECT * FROM users WHERE name LIKE '张%';
SELECT * FROM users WHERE created_at >= '2024-01-01'
AND created_at < '2025-01-01';
SELECT * FROM users WHERE status IN (1, 2);
SELECT status, COUNT(*) AS cnt FROM users GROUP BY status;
SELECT DISTINCT user_id FROM orders;执行计划：
EXPLAIN SELECT id, name FROM users WHERE status = 1;
EXPLAIN ANALYZE SELECT ...;详见 EXPLAIN 与运维。
插入 / 更新 / 删除（DML）
INSERT INTO users (name, email, status) VALUES ('张三', 'a@b.com', 1);
INSERT INTO users (name, email) VALUES ('李四', 'c@d.com'), ('王五', 'e@f.com');
INSERT INTO users (id, name, email)
VALUES (1, '张三', 'a@b.com')
ON CONFLICT (id) DO UPDATE
SET name = EXCLUDED.name, updated_at = NOW();
UPDATE users SET status = 0 WHERE id = 1001;
UPDATE users SET name = '李四' WHERE id = 1002;
DELETE FROM users WHERE id = 1003;
DELETE FROM logs WHERE created_at < NOW() - INTERVAL '90 days';UPSERT 细节：实用语法。
事务
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- ROLLBACK;MVCC 与 SAVEPOINT：MVCC 与事务。
权限（简表）
CREATE ROLE app_user WITH LOGIN PASSWORD 'secret';
GRANT CONNECT ON DATABASE app_db TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;备份与恢复
# 逻辑备份
pg_dump -U postgres -d app_db -F c -f app_db.dump
# 恢复
pg_restore -U postgres -d app_db_restored app_db.dump
# 纯 SQL 文本
pg_dump -U postgres app_db > app_db.sql
psql -U postgres -d app_db_restored -f app_db.sql常用运维 SQL
-- 表与索引
SELECT tablename FROM pg_tables WHERE schemaname = 'public';
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'users';
-- 活跃连接（见 EXPLAIN与运维）
SELECT pid, usename, state, query FROM pg_stat_activity WHERE state != 'idle';
ANALYZE users;
VACUUM ANALYZE users;