PostgreSQL 教程

PostgreSQL 教程

PostgreSQL（简称 PG）是一款功能强大的开源关系型数据库，以其高可靠性、丰富的数据类型和强大的扩展性著称。它支持标准 SQL 的超集，被广泛用于金融、电商、GIS 等对数据一致性要求高的场景。

PostgreSQL 的特点

Linux 上安装 PostgreSQL

在 Linux 上安装 PostgreSQL：

# Ubuntu/Debian
sudo apt update
sudo apt install -y postgresql postgresql-contrib

# CentOS/RHEL
sudo yum install -y postgresql-server postgresql-contrib
sudo postgresql-setup initdb
sudo systemctl start postgresql

# 启动服务（Ubuntu）
sudo systemctl start postgresql
sudo systemctl enable postgresql

# 切换到 postgres 用户
sudo -u postgres psql

# 查看版本
psql --version

Windows 上安装 PostgreSQL

在 Windows 上安装 PostgreSQL：

# 1. 下载安装包
# https://www.postgresql.org/download/windows/
# 选择 EDB Installer（官方）

# 2. 安装要点
# - 设置 postgres 超级用户密码
# - 选择端口（默认 5432）
# - 勾选安装 pgAdmin 4

# 3. 安装后验证
# 打开 SQL Shell (psql)
# 输入密码后进入

# 或通过命令行
psql -U postgres -h localhost

# 查看版本
SELECT version();

Mac OS 上安装 PostgreSQL

在 Mac OS 上安装 PostgreSQL：

# 方式一：Homebrew（推荐）
brew install postgresql@16

# 启动服务
brew services start postgresql@16

# 默认无密码，直接连接
psql postgres

# 方式二：Postgres.app（图形化）
# 下载 https://postgresapp.com/
# 拖入 Applications 启动，菜单栏图标可操作

# 连接测试
psql -U postgres

# 创建用户
CREATE USER myuser WITH PASSWORD 'mypass';
CREATE DATABASE mydb OWNER myuser;

PostgreSQL pgAdmin

pgAdmin 是 PostgreSQL 官方图形化管理工具：

# 启动 pgAdmin
pgadmin4

# 浏览器访问（默认）
# http://127.0.0.1:5050

# 连接服务器
# 右键 Servers -> Register -> Server
# Name: 任意
# Host: localhost  Port: 5432
# 用户名/密码: 安装时设置

# 常用快捷键
# F5 执行 SQL
# F7 打开查询工具
# Shift+F1 帮助

PostgreSQL 语法

PostgreSQL 语法基础：

-- 语句以分号结尾
SELECT 'Hello, PostgreSQL';

-- 大小写：关键字不敏感，标识符敏感
SELECT 1;  -- 等价于 select 1;

-- 注释
-- 单行注释
/* 多行
   注释 */

-- 字符串：单引号
SELECT 'hello';

-- 字符串内单引号转义：两个单引号
SELECT 'it''s';   -- it's

-- 常量
SELECT TRUE, FALSE, NULL;
SELECT 42, 3.14, 1e3;

-- 运算
SELECT 1 + 1, 2 * 3, 10 / 3, 10 % 3;

-- 当前版本
SELECT version();

PostgreSQL 数据类型

PostgreSQL 数据类型丰富：

-- 建表使用各类类型
CREATE TABLE demo (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INTEGER,
    score NUMERIC(5,2),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT NOW(),
    tags TEXT[],
    meta JSONB,
    ip INET
);

-- 类型转换
SELECT '123'::INTEGER;         -- 123
SELECT CAST('3.14' AS NUMERIC);
SELECT '2026-01-01'::DATE;

PostgreSQL 创建数据库

创建数据库：

-- 命令行创建
CREATE DATABASE mydb;

-- 指定所有者与编码
CREATE DATABASE mydb
    OWNER myuser
    ENCODING 'UTF8'
    TEMPLATE template0;

-- 存在才创建（无 IF NOT EXISTS 时）
-- PG 15+ 支持
CREATE DATABASE IF NOT EXISTS mydb;

# psql 命令行创建
createdb -U postgres mydb

# 带所有者
createdb -O myuser mydb

# 查看数据库列表
psql -l
# 或
\l

PostgreSQL 选择数据库

选择/切换数据库：

# psql 方式：连接时指定
psql -d mydb
psql -U postgres -h localhost mydb

# 已连接后切换
\c mydb
\connect mydb

# 查看当前数据库
SELECT current_database();

# 查看连接信息
\conninfo

# 列出所有数据库
\l

PostgreSQL 删除数据库

删除数据库：

-- 删除数据库（不能有活动连接）
DROP DATABASE mydb;

-- 强制删除（断开连接）
DROP DATABASE mydb WITH (FORCE);   -- PG 13+

PostgreSQL 创建表格

创建表格：

-- 基本建表
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE,
    age INTEGER CHECK (age >= 0),
    created_at TIMESTAMP DEFAULT NOW()
);

-- 复合主键
CREATE TABLE order_items (
    order_id BIGINT,
    product_id BIGINT,
    qty INTEGER,
    PRIMARY KEY (order_id, product_id)
);

-- 外键
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT REFERENCES users(id),
    total NUMERIC(10,2)
);

-- IF NOT EXISTS
CREATE TABLE IF NOT EXISTS users (...);

-- 临时表（会话结束消失）
CREATE TEMP TABLE tmp_data (x INTEGER);

-- 查看表结构
\d users

PostgreSQL 删除表格

删除表格：

-- 删除表
DROP TABLE users;

-- 存在才删
DROP TABLE IF EXISTS users;

-- 级联删除（连同依赖对象）
DROP TABLE IF EXISTS users CASCADE;

-- 删除多个
DROP TABLE IF EXISTS a, b, c;

-- 清空数据保留表
TRUNCATE TABLE users;
TRUNCATE TABLE a, b RESTART IDENTITY;   -- 重置自增

-- 只清空但不重置
DELETE FROM users;

PostgreSQL 模式

模式（Schema）：数据库内的命名空间，类似"文件夹"：

-- 查看当前模式
SELECT current_schema();

-- 创建模式
CREATE SCHEMA sales;
CREATE SCHEMA IF NOT EXISTS marketing AUTHORIZATION myuser;

-- 在模式中建表
CREATE TABLE sales.orders (
    id BIGSERIAL PRIMARY KEY,
    amount NUMERIC(10,2)
);

-- 查询带模式前缀
SELECT * FROM sales.orders;

-- 设置搜索路径（简化前缀）
SET search_path TO sales, public;
SELECT * FROM orders;   -- 自动找 sales.orders

-- 删除模式
DROP SCHEMA sales CASCADE;

-- 查看所有模式
\dn

PostgreSQL INSERT INTO

INSERT INTO 插入数据：

-- 单行插入
INSERT INTO users (name, email, age)
VALUES ('张三', 'zhang@example.com', 25);

-- 多行插入
INSERT INTO users (name, email, age) VALUES
    ('李四', 'li@example.com', 30),
    ('王五', 'wang@example.com', 22);

-- 省略列名
INSERT INTO users VALUES (DEFAULT, '赵六', 'zhao@example.com', 28);

-- 插入并返回
INSERT INTO users (name, email) VALUES ('孙七', 'sun@example.com')
RETURNING id, name;

-- 插入查询结果
INSERT INTO vip_users (name, email)
SELECT name, email FROM users WHERE age > 30;

-- ON CONFLICT（upsert）
INSERT INTO users (id, name, email) VALUES (1, '张三', 'new@example.com')
ON CONFLICT (id) DO UPDATE SET email = EXCLUDED.email;

-- 批量插入（COPY 最快）
\copy users FROM 'users.csv' WITH (FORMAT csv, HEADER true);

PostgreSQL SELECT

SELECT 查询：

-- 全部
SELECT * FROM users;

-- 指定列
SELECT name, age FROM users;

-- 别名
SELECT name AS 姓名, age AS 年龄 FROM users;

-- 去重
SELECT DISTINCT city FROM users;

-- 条件
SELECT * FROM users WHERE age >= 18 AND city = '北京';

-- 排序
SELECT * FROM users ORDER BY age DESC, name;

-- 分页
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;

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

-- 窗口函数（PG 强项）
SELECT name, age,
       RANK() OVER (ORDER BY age DESC) AS age_rank
FROM users;

-- 正则匹配
SELECT * FROM users WHERE email ~ '@gmail\.com$';

-- 模糊
SELECT * FROM users WHERE name LIKE '张%';

PostgreSQL 运算符

PostgreSQL 运算符：

-- 算术
SELECT 2 ^ 10;              -- 1024（幂）
SELECT 10 / 3;              -- 3（整数除法）
SELECT 10.0 / 3;            -- 3.333...
SELECT 10 % 3;              -- 1

-- 字符串
SELECT 'Hello' || ' ' || 'PG';   -- Hello PG
SELECT concat('a', 'b', 'c');    -- abc

-- 比较
SELECT 'a' = 'a';           -- t
SELECT 1 < 2;               -- t

-- 匹配
SELECT 'abc' LIKE 'a%';     -- t
SELECT 'ABC' ILIKE 'abc';   -- t（忽略大小写）
SELECT 'abc123' ~ '\d+';    -- t（正则）

-- 判断
SELECT NULL IS NULL;        -- t
SELECT 5 BETWEEN 1 AND 10;  -- t

PostgreSQL 表达式

表达式：

-- 算术表达式
SELECT (2 + 3) * 4;              -- 20

-- 条件表达式 CASE
SELECT
    CASE
        WHEN age >= 60 THEN '老年'
        WHEN age >= 18 THEN '成年'
        ELSE '未成年'
    END AS age_group
FROM users;

-- COALESCE：首个非 NULL
SELECT COALESCE(NULL, 'default', 42);    -- default

-- NULLIF：两值相等返回 NULL
SELECT NULLIF(5, 5);                     -- NULL
SELECT NULLIF(5, 0);                     -- 5

-- GREATEST / LEAST
SELECT GREATEST(1, 5, 3), LEAST(1, 5, 3);  -- 5, 1

-- 类型转换
SELECT '42'::INTEGER + 8;   -- 50
SELECT NOW()::DATE;         -- 今天

-- 子查询表达式
SELECT (SELECT MAX(age) FROM users);

PostgreSQL WHERE

WHERE 条件过滤：

-- 比较
SELECT * FROM users WHERE age >= 18;

-- 范围
SELECT * FROM products WHERE price BETWEEN 10 AND 100;

-- 列表
SELECT * FROM users WHERE city IN ('北京', '上海');

-- 模糊
SELECT * FROM users WHERE name LIKE '张%';

-- 正则
SELECT * FROM users WHERE phone ~ '^1[3-9]\d{9}$';

-- 空值
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;

-- 组合（注意括号）
SELECT * FROM users
WHERE (age >= 18 AND city = '北京') OR vip = TRUE;

-- 全文本搜索（PG 特色）
SELECT * FROM articles
WHERE to_tsvector('chinese', content) @@ to_tsquery('chinese', '数据库');

-- NOT
SELECT * FROM users WHERE NOT city = '北京';

PostgreSQL AND & OR

AND & OR 逻辑组合：

-- AND：都满足
SELECT * FROM users WHERE age > 18 AND vip = TRUE;

-- OR：任一满足
SELECT * FROM users WHERE city = '北京' OR city = '上海';

-- 混合（AND 优先级高）
SELECT * FROM users
WHERE age > 18 OR city = '北京' AND vip = TRUE;
-- 等价于 age>18 OR (city='北京' AND vip=TRUE)

-- 括号调整
SELECT * FROM users
WHERE (age > 18 OR city = '北京') AND vip = TRUE;

-- NOT 优先级最高
SELECT * FROM users
WHERE NOT city = '北京' AND age > 18;

PostgreSQL UPDATE

UPDATE 更新数据：

-- 基本更新
UPDATE users SET age = 26 WHERE name = '张三';

-- 多列
UPDATE users
SET age = 30, city = '上海'
WHERE id = 1;

-- 返回更新后的数据
UPDATE users SET vip = TRUE
WHERE city = '北京'
RETURNING id, name, vip;

-- 基于子查询更新
UPDATE users u
SET city = (SELECT city FROM temp_city t WHERE t.id = u.id);

-- 多表更新（PG 语法）
UPDATE orders o
SET total = o.qty * p.price
FROM products p
WHERE o.product_id = p.id;

-- 全表更新
UPDATE users SET last_login = NOW();

PostgreSQL DELETE

DELETE 删除数据：

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

-- 条件删除
DELETE FROM orders WHERE total < 10;

-- 返回删除的行
DELETE FROM users WHERE age > 100
RETURNING id, name;

-- 子查询删除
DELETE FROM users
WHERE id IN (SELECT user_id FROM banned WHERE reason = 'spam');

-- USING 多表删除
DELETE FROM orders o
USING products p
WHERE o.product_id = p.id AND p.discontinued = TRUE;

-- 清空表
TRUNCATE TABLE users;

PostgreSQL LIKE

LIKE 模糊匹配：

-- % 任意字符，_ 单个字符
SELECT * FROM users WHERE name LIKE '张%';
SELECT * FROM users WHERE name LIKE '%三';
SELECT * FROM users WHERE name LIKE '%小%';
SELECT * FROM users WHERE name LIKE '张_';

-- 忽略大小写
SELECT * FROM users WHERE email ILIKE '%GMAIL%';

-- NOT LIKE
SELECT * FROM users WHERE name NOT LIKE '张%';

-- 转义
SELECT * FROM logs WHERE msg LIKE '100\%' ESCAPE '\\';

-- 相似度（pg_trgm 扩展）
CREATE EXTENSION IF NOT EXISTS pg_trgm;
SELECT * FROM users
WHERE name % '张珊';   -- 模糊相似

-- SIMILAR TO（SQL 标准）
SELECT 'abc' SIMILAR TO 'a%';   -- t

PostgreSQL LIMIT

LIMIT 限制行数（分页）：

-- 取前 10 条
SELECT * FROM users LIMIT 10;

-- OFFSET 跳过
SELECT * FROM users LIMIT 10 OFFSET 20;

-- 简写语法（兼容）
SELECT * FROM users LIMIT 20, 10;   -- 不支持！PG 用 OFFSET

-- TOP N
SELECT * FROM users ORDER BY age DESC LIMIT 5;

-- 分页（page=3, size=10）
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;

-- 随机取 N 条
SELECT * FROM users ORDER BY RANDOM() LIMIT 5;

-- 游标分页（大数据量推荐）
SELECT * FROM users
WHERE id > 12345            -- 上一页最后 ID
ORDER BY id LIMIT 10;

PostgreSQL ORDER BY

ORDER BY 排序：

-- 基本
SELECT * FROM users ORDER BY age;
SELECT * FROM users ORDER BY age DESC;

-- 多列
SELECT * FROM users ORDER BY city ASC, age DESC;

-- 表达式排序
SELECT * FROM users ORDER BY age * 2;

-- NULL 位置控制
SELECT * FROM users ORDER BY email NULLS FIRST;
SELECT * FROM users ORDER BY email NULLS LAST;

-- 按列位置
SELECT name, age FROM users ORDER BY 2 DESC;

-- 字典序（按拼音/区域排序）
SELECT * FROM users ORDER BY name COLLATE "zh_CN";

-- 随机
SELECT * FROM users ORDER BY RANDOM();

PostgreSQL GROUP BY

GROUP BY 分组聚合：

-- 基础分组
SELECT city, COUNT(*) FROM users GROUP BY city;

-- 多列分组
SELECT city, vip, COUNT(*) FROM users GROUP BY city, vip;

-- 聚合函数
SELECT
    city,
    COUNT(*)  AS cnt,
    AVG(age)  AS avg_age,
    SUM(amount) AS total,
    MIN(age)  AS min_age,
    MAX(age)  AS max_age
FROM orders
GROUP BY city;

-- 分组后排序
SELECT city, COUNT(*) cnt
FROM users
GROUP BY city
ORDER BY cnt DESC;

-- 按表达式分组
SELECT DATE_TRUNC('month', created_at) AS month, COUNT(*)
FROM orders
GROUP BY month;

-- 去重计数
SELECT city, COUNT(DISTINCT vip) FROM users GROUP BY city;

PostgreSQL WITH

WITH（CTE 公共表表达式）：让复杂查询更清晰：

-- 基本 CTE
WITH recent_users AS (
    SELECT * FROM users WHERE created_at > NOW() - INTERVAL '30 days'
)
SELECT * FROM recent_users WHERE age > 18;

-- 多 CTE
WITH
    adults AS (SELECT * FROM users WHERE age >= 18),
    vip_adults AS (SELECT * FROM adults WHERE vip = TRUE)
SELECT COUNT(*) FROM vip_adults;

-- CTE + 聚合
WITH city_stats AS (
    SELECT city, COUNT(*) cnt FROM users GROUP BY city
)
SELECT * FROM city_stats WHERE cnt > 10;

-- 递归 CTE（树形结构）
WITH RECURSIVE tree AS (
    SELECT id, name, parent_id, 1 AS depth
    FROM categories WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.name, c.parent_id, t.depth + 1
    FROM categories c
    JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree ORDER BY depth;

PostgreSQL HAVING

HAVING 过滤分组：

-- 过滤分组结果
SELECT city, AVG(age) AS avg_age
FROM users
GROUP BY city
HAVING AVG(age) > 25;

-- WHERE + GROUP BY + HAVING
SELECT city, COUNT(*) cnt, SUM(amount) total
FROM orders
WHERE created_at > '2026-01-01'     -- 行级过滤
GROUP BY city
HAVING SUM(amount) > 10000          -- 组级过滤
ORDER BY total DESC;

-- HAVING 用别名（PG 允许）
SELECT city, COUNT(*) cnt
FROM users
GROUP BY city
HAVING cnt > 2;

PostgreSQL DISTINCT

DISTINCT 去重：

-- 单列去重
SELECT DISTINCT city FROM users;

-- 多列组合去重
SELECT DISTINCT city, vip FROM users;

-- DISTINCT ON（PG 特色：每组分取第一条）
SELECT DISTINCT ON (city) id, city, name, age
FROM users
ORDER BY city, age DESC;
-- 每个城市取年龄最大的一条

-- 去重计数
SELECT COUNT(DISTINCT city) FROM users;

-- NULL 处理
SELECT DISTINCT email FROM users;   -- NULL 只出现一次

PostgreSQL 约束

约束（Constraints）：

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE,
    age INTEGER CHECK (age >= 0 AND age <= 150),
    status VARCHAR(20) DEFAULT 'active',
    CONSTRAINT fk_company FOREIGN KEY (company_id)
        REFERENCES companies(id) ON DELETE CASCADE
);

-- 添加约束
ALTER TABLE users ADD CONSTRAINT uq_phone UNIQUE (phone);
ALTER TABLE users ADD CONSTRAINT ck_age CHECK (age >= 0);

-- 删除约束
ALTER TABLE users DROP CONSTRAINT uq_phone;

-- 延迟约束（事务提交时检查）
CREATE TABLE t (
    a INTEGER CHECK (a > 0) DEFERRABLE INITIALLY DEFERRED
);

-- 排除约束（排他，如时间不重叠）
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE meetings (
    room TEXT,
    during TSRANGE,
    EXCLUDE USING gist (room WITH =, during WITH &&)
);

PostgreSQL JOIN

JOIN 关联查询：

-- INNER JOIN
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN（保留左表）
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- RIGHT JOIN
SELECT u.name, o.total
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- FULL OUTER JOIN
SELECT * FROM a FULL OUTER JOIN b ON a.id = b.id;

-- CROSS JOIN（笛卡尔积）
SELECT * FROM users CROSS JOIN products;

-- 多表 JOIN
SELECT u.name, o.total, p.name AS product
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;

-- USING 简写（列名相同）
SELECT * FROM users JOIN orders USING (id);

-- NATURAL JOIN（自动匹配同名列）
SELECT * FROM users NATURAL JOIN profiles;

PostgreSQL UNION

UNION 合并结果：

-- UNION（去重）
SELECT name FROM users_a
UNION
SELECT name FROM users_b;

-- UNION ALL（不去重，更快）
SELECT name FROM users_a
UNION ALL
SELECT name FROM users_b;

-- 排序
SELECT name FROM users_a
UNION ALL
SELECT name FROM users_b
ORDER BY name;

-- INTERSECT 交集
SELECT name FROM users_a
INTERSECT
SELECT name FROM users_b;

-- EXCEPT 差集（A 有 B 没有）
SELECT name FROM users_a
EXCEPT
SELECT name FROM users_b;

-- 要求列数一致
SELECT id, name FROM users_a
UNION
SELECT name FROM users_b;   -- 报错！列数不同

PostgreSQL NULL

NULL 值处理：

-- 判断
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;

-- 错误写法（结果永远为空）
SELECT * FROM users WHERE email = NULL;

-- 填充
SELECT name, COALESCE(email, '未填写') FROM users;

-- NULLIF：把特定值当 NULL
SELECT NULLIF(score, 0) FROM users;   -- 0 显示为 NULL

-- 聚合忽略 NULL
SELECT AVG(age) FROM users;    -- NULL 不参与
SELECT COUNT(email) FROM users;  -- 只计非 NULL
SELECT COUNT(*) FROM users;    -- 计所有行

-- 排序控制
SELECT * FROM users ORDER BY email NULLS LAST;

-- 窗口函数中的 NULL
SELECT name, LAG(name) OVER (ORDER BY id) FROM users;

PostgreSQL 别名

别名（Alias）：

-- 列别名
SELECT name AS 姓名, age AS 年龄 FROM users;
SELECT name 姓名 FROM users;      -- AS 可省略

-- 表别名
SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id;

-- 别名在表达式
SELECT amount * 0.9 AS discounted FROM orders;

-- 别名在 ORDER BY/HAVING
SELECT name, age * 2 AS double_age
FROM users
ORDER BY double_age DESC;

-- 中文别名（建议加双引号）
SELECT name AS "姓名" FROM users;

-- 聚合别名
SELECT city AS c, COUNT(*) AS cnt
FROM users
GROUP BY c
HAVING cnt > 2;

PostgreSQL 触发器

触发器（Trigger）：

-- 创建触发器函数（返回 TRIGGER）
CREATE OR REPLACE FUNCTION log_user_changes()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO user_logs (action, user_name, ts)
    VALUES (TG_OP, NEW.name, NOW());
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器
CREATE TRIGGER trg_user_log
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW
EXECUTE FUNCTION log_user_changes();

-- BEFORE 触发器（修改新值）
CREATE OR REPLACE FUNCTION set_defaults()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.created_at IS NULL THEN
        NEW.created_at := NOW();
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_set_defaults
BEFORE INSERT ON users
FOR EACH ROW
EXECUTE FUNCTION set_defaults();

-- 查看触发器
\d users
-- 或
SELECT * FROM information_schema.triggers;

-- 删除触发器
DROP TRIGGER trg_user_log ON users;

PostgreSQL 索引

索引（Index）：

-- B-tree（默认）
CREATE INDEX idx_users_name ON users(name);

-- 唯一索引
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- 复合索引
CREATE INDEX idx_orders_user_amount ON orders(user_id, amount);

-- 部分索引
CREATE INDEX idx_users_vip ON users(age) WHERE vip = TRUE;

-- 表达式索引
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

-- GIN（数组/JSONB 全文）
CREATE INDEX idx_users_tags ON users USING GIN (tags);
CREATE INDEX idx_users_meta ON users USING GIN (meta);

-- 查看索引
\di
-- 或
SELECT * FROM pg_indexes WHERE tablename = 'users';

-- 删除
DROP INDEX idx_users_name;

-- 查询是否走索引
EXPLAIN ANALYZE SELECT * FROM users WHERE name = '张三';

PostgreSQL ALTER TABLE 命令

ALTER TABLE 修改表结构：

-- 添加列
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- 添加列 + 默认值 + 非空
ALTER TABLE users
ADD COLUMN vip BOOLEAN NOT NULL DEFAULT FALSE;

-- 删除列
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users DROP COLUMN IF EXISTS phone;

-- 修改类型
ALTER TABLE users ALTER COLUMN age TYPE BIGINT;

-- 修改默认值
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'inactive';
ALTER TABLE users ALTER COLUMN status DROP DEFAULT;

-- 设置/取消非空
ALTER TABLE users ALTER COLUMN name SET NOT NULL;
ALTER TABLE users ALTER COLUMN name DROP NOT NULL;

-- 重命名列/表
ALTER TABLE users RENAME COLUMN phone TO mobile;
ALTER TABLE users RENAME TO members;

-- 加约束
ALTER TABLE users ADD CONSTRAINT uq_phone UNIQUE (mobile);

-- 一次多条
ALTER TABLE users
    ADD COLUMN a INT,
    ADD COLUMN b TEXT,
    DROP COLUMN c;

PostgreSQL TRUNCATE TABLE

TRUNCATE TABLE 清空表：

-- 清空单表
TRUNCATE TABLE users;

-- 清空多表
TRUNCATE TABLE users, orders;

-- 重置自增序列
TRUNCATE TABLE users RESTART IDENTITY;

-- 级联清空（含外键关联表）
TRUNCATE TABLE users CASCADE;

-- TRUNCATE vs DELETE
-- TRUNCATE: 快、立即释放空间、不可按条件、默认不可回滚（事务内可）
-- DELETE:   慢、可条件、可回滚、不重置序列

PostgreSQL 视图

视图（View）：

-- 创建视图
CREATE VIEW adult_users AS
SELECT id, name, age, city FROM users WHERE age >= 18;

-- 查询视图
SELECT * FROM adult_users;

-- 物化视图（PG 特色：物理存储，性能好）
CREATE MATERIALIZED VIEW city_stats AS
SELECT city, COUNT(*) cnt, AVG(age) avg_age
FROM users GROUP BY city;

-- 刷新物化视图
REFRESH MATERIALIZED VIEW city_stats;

-- 更新视图定义
CREATE OR REPLACE VIEW adult_users AS
SELECT id, name, age FROM users WHERE age >= 18;

-- 删除
DROP VIEW adult_users;
DROP MATERIALIZED VIEW city_stats;

-- 查看
\dv

PostgreSQL 事务

事务（Transaction）：

-- 显式事务
BEGIN;
INSERT INTO orders (user_id, total) VALUES (1, 100);
UPDATE users SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- 回滚
BEGIN;
DELETE FROM orders WHERE id = 999;
ROLLBACK;

-- 保存点
BEGIN;
INSERT INTO a VALUES (1);
SAVEPOINT sp1;
INSERT INTO a VALUES (2);   -- 假设这里出错
ROLLBACK TO sp1;            -- 撤销到保存点
COMMIT;                     -- 只保留第一个插入

-- 隔离级别
BEGIN ISOLATION LEVEL READ COMMITTED;
BEGIN ISOLATION LEVEL SERIALIZABLE;

-- 查看事务状态
SELECT txid_current();

PostgreSQL 锁

锁（Lock）：

-- 行级锁（悲观锁）
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- 锁定该行，防止并发修改

-- 跳过已锁行
SELECT * FROM accounts WHERE id = 1 FOR UPDATE SKIP LOCKED;

-- 共享锁
SELECT * FROM products WHERE id = 1 FOR SHARE;

-- 表级锁
LOCK TABLE orders IN ACCESS EXCLUSIVE MODE;

-- 查看锁
SELECT * FROM pg_locks WHERE NOT granted;

-- 查看阻塞的查询
SELECT pid, wait_event_type, state, query
FROM pg_stat_activity
WHERE state = 'active' AND wait_event_type IS NOT NULL;

PostgreSQL 子查询

子查询：

-- IN 子查询
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 100);

-- 标量子查询
SELECT name,
       (SELECT MAX(total) FROM orders WHERE orders.user_id = users.id)
FROM users;

-- 派生表（FROM 子查询）
SELECT city, cnt
FROM (SELECT city, COUNT(*) cnt FROM users GROUP BY city) s
WHERE cnt > 2;

-- 相关子查询
SELECT * FROM products p
WHERE price > (SELECT AVG(price) FROM products WHERE category = p.category);

-- EXISTS
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- ANY / ALL
SELECT * FROM products
WHERE price > ANY (SELECT price FROM products WHERE category = '手机');
-- 大于其中任意一个

SELECT * FROM products
WHERE price > ALL (SELECT price FROM products WHERE category = '手机');
-- 大于全部

PostgreSQL AUTO INCREMENT

AUTO INCREMENT 自增：

-- 方式一：BIGSERIAL（推荐简写）
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    name TEXT
);

-- 方式二：SERIAL
CREATE TABLE t (id SERIAL PRIMARY KEY);

-- 方式三：显式 SEQUENCE（灵活控制）
CREATE SEQUENCE user_id_seq START 1000;
CREATE TABLE users (
    id BIGINT DEFAULT nextval('user_id_seq') PRIMARY KEY,
    name TEXT
);

-- 插入（自增自动）
INSERT INTO users (name) VALUES ('张三');   -- id=1000
INSERT INTO users (name) VALUES ('李四');   -- id=1001

-- 查看序列
SELECT currval('users_id_seq');

-- 重置序列
ALTER SEQUENCE users_id_seq RESTART WITH 1;

-- 手动设置下一个值
SELECT setval('users_id_seq', 5000);

PostgreSQL 权限

权限管理：

-- 创建角色
CREATE ROLE app_user LOGIN PASSWORD 'secret';
CREATE ROLE readonly LOGIN PASSWORD 'read123';

-- 授予数据库连接权限
GRANT CONNECT ON DATABASE mydb TO app_user;

-- 授予模式权限
GRANT USAGE ON SCHEMA public TO app_user;
GRANT USAGE ON SCHEMA public TO readonly;

-- 授予表权限
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

-- 序列权限
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;

-- 未来新建表自动授权
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT ON TABLES TO app_user;

-- 撤销权限
REVOKE DELETE ON users FROM app_user;

-- 删除角色
DROP ROLE readonly;

PostgreSQL 时间/日期

时间/日期函数：

-- 当前时间
SELECT NOW();                    -- 2026-08-12 10:30:00
SELECT CURRENT_TIMESTAMP;
SELECT CURRENT_DATE;             -- 2026-08-12
SELECT CURRENT_TIME;             -- 10:30:00

-- 提取
SELECT EXTRACT(YEAR FROM NOW());
SELECT EXTRACT(MONTH FROM NOW());
SELECT EXTRACT(DOW FROM NOW());  -- 星期(0=周日)
SELECT DATE_PART('hour', NOW());

-- 截断
SELECT DATE_TRUNC('month', NOW());   -- 2026-08-01 00:00
SELECT DATE_TRUNC('day', NOW());

-- 运算
SELECT NOW() + INTERVAL '1 day';
SELECT NOW() - INTERVAL '2 hours';
SELECT NOW() + INTERVAL '1 month';

-- 日期差
SELECT DATE '2026-08-12' - DATE '2026-01-01';   -- 223 天
SELECT AGE('2026-08-12', '2026-01-01');          -- 7 mons 11 days

-- 格式化
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS');
SELECT TO_CHAR(NOW(), 'YYYY年MM月DD日');
SELECT TO_DATE('2026-08-12', 'YYYY-MM-DD');
SELECT TO_TIMESTAMP('2026-08-12 10:00:00', 'YYYY-MM-DD HH24:MI:SS');

PostgreSQL 常用函数

常用函数汇总：

-- 字符串
SELECT UPPER('abc'), LOWER('ABC'), LENGTH('hello');
SELECT SUBSTRING('hello' FROM 2 FOR 3);    -- ell
SELECT REPLACE('hello', 'l', 'L');         -- heLLo
SELECT POSITION('ll' IN 'hello');          -- 3
SELECT TRIM(' hi '), SPLIT_PART('a,b,c', ',', 2);  -- hi, b
SELECT STRING_AGG(name, ', ') FROM users;  -- 拼接聚合

-- 数学
SELECT ABS(-5), ROUND(3.14159, 2), CEIL(4.2), FLOOR(4.8);
SELECT POWER(2, 10), SQRT(16), RANDOM();

-- 聚合
SELECT COUNT(*), SUM(x), AVG(x), MIN(x), MAX(x) FROM t;
SELECT ARRAY_AGG(name) FROM users;          -- 聚合成数组

-- JSON（JSONB）
SELECT '{"a": 1}'::JSONB -> 'a';            -- 1
SELECT meta ->> 'name' FROM users;          -- 取字段
SELECT jsonb_array_length('["a","b"]'::JSONB);

-- 窗口函数
SELECT name, age,
       ROW_NUMBER() OVER (ORDER BY age DESC),
       RANK() OVER (ORDER BY age DESC),
       SUM(amount) OVER (PARTITION BY city)
FROM users;

-- 网络
SELECT '192.168.1.1'::INET << '192.168.0.0/16';  -- t