PostgreSQL DELETE 语句
PostgreSQL DELETE 语句完全指南
DELETE是移除数据的核心命令，支持 条件删除、级联删除、返回数据、FROM 子句、性能优化、安全防护 等高级特性。
本文涵盖全部场景，帮助你安全、高效地删除数据。
一、DELETE 基本语法
DELETE FROM table_name
[WHERE condition]
[RETURNING * | column1, column2, ...];没有
WHERE= 删除全表！
二、核心示例表
CREATE TABLE employees (
id BIGSERIAL PRIMARY KEY,
name TEXT NOT NULL,
email TEXT,
department TEXT,
salary NUMERIC(10,2),
is_active BOOLEAN DEFAULT true,
tags TEXT[] DEFAULT '{}',
config JSONB DEFAULT '{}',
created_at TIMESTAMPTZ DEFAULT now(),
deleted_at TIMESTAMPTZ
);
-- 插入测试数据
INSERT INTO employees (name, email, department, salary, tags, config)
VALUES
('Alice', 'alice@company.com', 'IT', 75000, '{"dev","lead"}', '{"level": 5}'),
('Bob', 'bob@company.com', 'HR', 52000, '{"hr"}', '{"level": 3}'),
('Carol', 'carol@company.com', 'IT', 85000, '{"dev","ai"}', '{"level": 6}'),
('Dave', 'dave@company.com', 'IT', 92000, '{"dev","lead"}', '{"level": 7}'),
('Eve', 'eve@company.com', 'HR', 48000, '{"hr"}', '{"level": 2}');三、基础 DELETE 用法
| 场景 | SQL |
|---|---|
| 按主键删除 | DELETE FROM employees WHERE id = 1; |
| 按条件删除 | DELETE FROM employees WHERE department = 'HR'; |
| 多条件 | DELETE FROM employees WHERE salary < 50000 AND is_active = false; |
| 返回删除数据 | DELETE FROM employees WHERE name = 'Bob' RETURNING *; |
-- 删除并返回
DELETE FROM employees
WHERE department = 'HR' AND salary < 50000
RETURNING id, name, salary;输出：
id | name | salary
----+------+--------
5 | Eve | 48000四、关键特性：RETURNING
-- 获取被删除的记录（用于日志、审计）
DELETE FROM employees
WHERE created_at < '2024-01-01'
RETURNING id, name, email, deleted_at;用途：审计、软删除前备份、触发后续逻辑
五、条件删除（WHERE 子句）
1. 基础过滤
DELETE FROM employees WHERE is_active = false;2. 复杂逻辑
DELETE FROM employees
WHERE (department = 'IT' AND tags @> '{"lead"}')
OR (salary < 40000 AND created_at < '2023-01-01');3. 子查询删除
-- 删除薪资低于部门平均的员工
DELETE FROM employees e1
USING (
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
) e2
WHERE e1.department = e2.department
AND e1.salary < e2.avg_salary;六、关联删除：USING 子句（类似 JOIN）
-- 删除有订单的员工（假设有 orders 表）
DELETE FROM employees e
USING orders o
WHERE e.id = o.employee_id
AND o.status = 'cancelled';
USING=FROM+JOIN，专为DELETE设计
七、级联删除（CASCADE）
1. 外键设置 ON DELETE CASCADE
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
employee_id BIGINT REFERENCES employees(id) ON DELETE CASCADE
);
-- 删除员工 → 自动删除其订单
DELETE FROM employees WHERE id = 1;2. 手动级联（无外键）
-- 先删子表，再删主表
DELETE FROM orders WHERE employee_id = 1;
DELETE FROM employees WHERE id = 1;八、软删除（推荐生产实践）
-- 1. 添加 deleted_at 字段
ALTER TABLE employees ADD COLUMN deleted_at TIMESTAMPTZ;
-- 2. 软删除
UPDATE employees
SET deleted_at = now(), is_active = false
WHERE id = 1;
-- 3. 查询时过滤
SELECT * FROM employees WHERE deleted_at IS NULL;优点：可恢复、有审计
缺点：数据膨胀，需索引
-- 为软删除建索引
CREATE INDEX idx_emp_deleted ON employees(deleted_at) WHERE deleted_at IS NULL;九、安全删除：防止误删
1. 永远加 WHERE！
-- 危险！删全表
DELETE FROM employees;
-- 安全写法
DELETE FROM employees WHERE id = 999;2. 先 SELECT 验证
-- 先看要删的行
SELECT * FROM employees WHERE department = 'HR' AND salary < 50000;
-- 再删除
DELETE FROM employees WHERE department = 'HR' AND salary < 50000;3. 事务回滚
BEGIN;
DELETE FROM employees WHERE name = 'Alice';
-- 检查
SELECT * FROM employees;
-- 不满意？回滚
ROLLBACK;
-- 满意？提交
COMMIT;十、性能优化
| 技巧 | 说明 |
|---|---|
| 加索引 | WHERE 列建索引 |
| 批量删除 | 分批执行，避免锁表 |
| 避免函数列 | WHERE UPPER(name) = 'ALICE' → 慢 |
| 分区表 | 删除分区 = 秒删亿级 |
-- 分批删除（每批 1000 条）
DO $$
BEGIN
FOR i IN 1..100 LOOP
DELETE FROM employees
WHERE id IN (
SELECT id FROM employees
WHERE created_at < '2020-01-01'
LIMIT 1000
);
EXIT WHEN NOT FOUND;
END LOOP;
END $$;十一、触发器：删除前/后操作
-- 记录删除日志
CREATE TABLE employee_delete_log (
emp_id BIGINT,
name TEXT,
deleted_at TIMESTAMPTZ DEFAULT now()
);
CREATE OR REPLACE FUNCTION log_employee_delete()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO employee_delete_log (emp_id, name)
VALUES (OLD.id, OLD.name);
RETURN OLD;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_emp_delete
BEFORE DELETE ON employees
FOR EACH ROW
EXECUTE FUNCTION log_employee_delete();十二、常见错误与解决
| 错误 | 原因 | 解决 |
|---|---|---|
DELETE 0 rows | WHERE 不匹配 | 检查条件 |
cannot delete from table because it is referenced | 外键约束 | 用 CASCADE 或先删子表 |
permission denied | 无权限 | GRANT DELETE ON employees TO user; |
deadlock detected | 并发删除 | 重试或分批 |
十三、一键安全删除脚本
-- 安全删除：备份 + 软删除 + 日志
WITH deleted AS (
DELETE FROM employees
WHERE department = 'HR' AND salary < 50000
RETURNING id, name, email, now() AS deleted_at
)
INSERT INTO employee_archive (id, name, email, deleted_at, data)
SELECT id, name, email, deleted_at, row_to_json(employees.*)
FROM deleted;十四、最佳实践 Checklist
| 项目 | 建议 |
|---|---|
| 永远加 WHERE | 防止全表删除 |
| 用 RETURNING | 获取删除数据 |
| 先 SELECT 验证 | 确认影响行 |
| 用事务 | 可回滚 |
| 软删除优先 | 生产环境 |
| 建索引 | WHERE 列 |
| 分批删除 | 大表 |
十五、快速上手：5 分钟掌握 DELETE
-- 1. 简单删除
DELETE FROM employees WHERE name = 'Bob';
-- 2. 条件 + 返回
DELETE FROM employees
WHERE salary < 50000
RETURNING id, name, salary;
-- 3. USING 联合删除
DELETE FROM employees e
USING (VALUES ('HR', 50000)) AS v(dept, max_sal)
WHERE e.department = v.dept AND e.salary < v.max_sal;
-- 4. 软删除
UPDATE employees
SET deleted_at = now(), is_active = false
WHERE department = 'IT' AND created_at < '2023-01-01';现在就动手：
在 employees 表中：
- 删除
salary < 60000且is_active = false的员工，RETURNING * - 用
USING删除department = 'HR'且有tags @> '{"hr"}'的员工 - 实现软删除：添加
deleted_at字段，更新代替删除 - 写一个防误删事务（
BEGIN; ... ROLLBACK;）
需要我生成：
- 1000 条测试数据 + 批量 DELETE 脚本？回复
测试数据 - 自动归档触发器？回复
归档触发器 - Python 安全 DELETE？回复
Python DELETE - 分区表秒删亿级数据？回复
分区删除
随时告诉我！