PostgreSQL 快速入门指南

一、安装与连接

安装

macOS（推荐 Homebrew）

brew install postgresql@16
brew services start postgresql@16

Ubuntu / Debian

sudo apt update
sudo apt install postgresql postgresql-contrib
sudo systemctl start postgresql

Windows

从 postgresql.org 下载安装程序，或使用 WSL2 + Ubuntu 方案。

连接数据库

# 本地默认连接
psql -U postgres

# 指定数据库
psql -U postgres -d mydb

# 远程连接（URL）
psql "postgresql://user:pass@host:5432/db"

💡 首次安装后，系统会自动创建 postgres 超级用户，建议立即修改其密码：

ALTER USER postgres WITH PASSWORD '新密码';

二、基础操作

创建与切换数据库

-- 创建数据库
CREATE DATABASE myapp;

-- 切换数据库（在 psql 中）
\c myapp

-- 查看所有数据库
\l

创建表

CREATE TABLE users (
  id         SERIAL PRIMARY KEY,
  username   VARCHAR(50) UNIQUE NOT NULL,
  email      VARCHAR(100) NOT NULL,
  age        INTEGER CHECK (age >= 0),
  created_at TIMESTAMP DEFAULT NOW()
);

常用数据类型

三、增删改查（CRUD）

INSERT — 插入数据

-- 插入单行
INSERT INTO users (username, email, age)
VALUES ('alice', 'alice@example.com', 28);

-- 插入多行
INSERT INTO users (username, email, age) VALUES
  ('bob', 'bob@example.com', 32),
  ('carol', 'carol@example.com', 25)
RETURNING id;  -- 返回自增 ID

SELECT — 查询数据

-- 基础查询
SELECT * FROM users WHERE age > 18;

-- 常用子句
SELECT username, email
FROM users
WHERE age BETWEEN 20 AND 35
ORDER BY created_at DESC
LIMIT 10 OFFSET 20;

-- 聚合
SELECT AVG(age), COUNT(*) FROM users;

UPDATE — 更新数据

UPDATE users
SET email = 'new@example.com', age = 29
WHERE username = 'alice'
RETURNING *;

DELETE — 删除数据

-- 删除指定行
DELETE FROM users WHERE id = 5;

-- 清空表（保留结构）
TRUNCATE users;

⚠️ UPDATE / DELETE 不加 WHERE 会影响全表。养成先用 SELECT 确认范围的习惯。

四、psql 快捷命令

五、进阶技巧

索引

-- 普通索引
CREATE INDEX idx_users_email ON users(email);

-- 唯一索引
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- 部分索引（节省空间）
CREATE INDEX idx_active ON users(created_at)
WHERE age >= 18;

-- 查看执行计划
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'x@y.com';

事务

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- 出错时：ROLLBACK;

JSON / JSONB 查询

-- 查询 JSONB 字段
SELECT data->>'name' FROM products WHERE data @> '{"in_stock": true}';

-- 创建 GIN 索引加速 JSONB
CREATE INDEX idx_gin ON products USING GIN(data);

备份与恢复

# 备份
pg_dump mydb > backup.sql

# 恢复
psql mydb < backup.sql

连接与慢查询管理

-- 查看当前连接
SELECT pid, usename, state, query FROM pg_stat_activity;

-- 终止慢查询
SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE state = 'active' AND query_start < NOW() - INTERVAL '5 min';

🚀 性能调优首选：EXPLAIN ANALYZE 找慢查询 → 添加合适索引 → 调整 postgresql.conf 参数（work_mem、shared_buffers）。