PostgreSQL 数据删除 #
删除概述 #
PostgreSQL 提供两种删除数据的方式：DELETE 和 TRUNCATE。
text
┌─────────────────────────────────────────────────────────────┐
│ 删除方式对比 │
├─────────────────────────────────────────────────────────────┤
│ │
│ DELETE │
│ ├── 可以带 WHERE 条件 │
│ ├── 触发触发器 │
│ ├── 返回被删除的行 │
│ ├── 较慢（逐行删除） │
│ └── 可以回滚 │
│ │
│ TRUNCATE │
│ ├── 删除表中所有数据 │
│ ├── 不触发触发器 │
│ ├── 更快（一次性清空） │
│ ├── 重置自增序列 │
│ └── 可以回滚（在事务中） │
│ │
└─────────────────────────────────────────────────────────────┘
DELETE 语句 #
基本语法 #
sql
DELETE FROM table_name
[ WHERE condition ]
[ RETURNING * | column1, column2, ... ];
删除所有数据 #
sql
-- 创建示例表
CREATE TABLE logs (
id SERIAL PRIMARY KEY,
message TEXT,
level VARCHAR(10),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO logs (message, level) VALUES
('Error 1', 'ERROR'),
('Warning 1', 'WARN'),
('Info 1', 'INFO'),
('Error 2', 'ERROR');
-- 删除所有数据（危险操作！）
DELETE FROM logs;
-- 注意：不带 WHERE 条件会删除所有行
条件删除 #
sql
-- 根据条件删除
DELETE FROM logs WHERE level = 'ERROR';
-- 使用多个条件
DELETE FROM logs
WHERE level = 'WARN'
AND created_at < CURRENT_DATE;
-- 使用 IN
DELETE FROM logs
WHERE level IN ('DEBUG', 'TRACE');
-- 使用 BETWEEN
DELETE FROM logs
WHERE created_at BETWEEN '2025-01-01' AND '2025-12-31';
-- 使用 LIKE
DELETE FROM logs
WHERE message LIKE '%deprecated%';
-- 使用子查询
DELETE FROM logs
WHERE id IN (
SELECT id FROM logs
WHERE level = 'DEBUG'
LIMIT 100
);
RETURNING 子句 #
sql
-- 返回被删除的行
DELETE FROM logs
WHERE level = 'ERROR'
RETURNING *;
-- 输出：
-- id | message | level | created_at
-- ----+----------+-------+----------------------------
-- 1 | Error 1 | ERROR | 2026-03-29 10:00:00.000000
-- 4 | Error 2 | ERROR | 2026-03-29 10:00:00.000000
-- 返回特定列
DELETE FROM logs
WHERE id = 1
RETURNING id, message;
-- 返回计算结果
DELETE FROM logs
WHERE level = 'ERROR'
RETURNING id, message,
EXTRACT(DAY FROM CURRENT_TIMESTAMP - created_at) AS days_old;
使用子查询删除 #
sql
-- 创建关联表
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
status VARCHAR(20)
);
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES users(id),
amount DECIMAL(10, 2),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO users (name, status) VALUES
('Alice', 'active'),
('Bob', 'inactive'),
('Charlie', 'inactive');
INSERT INTO orders (user_id, amount) VALUES
(1, 100.00),
(2, 50.00),
(3, 75.00);
-- 删除没有订单的用户
DELETE FROM users
WHERE id NOT IN (SELECT DISTINCT user_id FROM orders);
-- 删除订单金额小于平均值的订单
DELETE FROM orders
WHERE amount < (SELECT AVG(amount) FROM orders);
-- 使用 EXISTS
DELETE FROM users
WHERE NOT EXISTS (
SELECT 1 FROM orders WHERE orders.user_id = users.id
);
使用 USING 子句 #
sql
-- PostgreSQL 特有的 USING 语法
-- 删除特定用户的订单
DELETE FROM orders
USING users
WHERE orders.user_id = users.id
AND users.status = 'inactive';
-- 多表关联删除
CREATE TABLE order_items (
id SERIAL PRIMARY KEY,
order_id INTEGER REFERENCES orders(id),
product_name VARCHAR(100)
);
INSERT INTO order_items (order_id, product_name) VALUES
(1, 'Product A'),
(1, 'Product B'),
(2, 'Product C');
-- 删除特定用户的所有订单项
DELETE FROM order_items
USING orders, users
WHERE order_items.order_id = orders.id
AND orders.user_id = users.id
AND users.status = 'inactive';
TRUNCATE 语句 #
基本语法 #
sql
TRUNCATE [ TABLE ] table_name [ , ... ]
[ RESTART IDENTITY | CONTINUE IDENTITY ]
[ CASCADE | RESTRICT ];
清空表数据 #
sql
-- 清空单个表
TRUNCATE logs;
-- 清空多个表
TRUNCATE logs, temp_data;
-- 清空并重置自增序列
TRUNCATE logs RESTART IDENTITY;
-- 清空并级联删除关联表数据
TRUNCATE users CASCADE;
TRUNCATE 参数 #
text
┌─────────────────┬─────────────────────────────────────────────┐
│ 参数 │ 说明 │
├─────────────────┼─────────────────────────────────────────────┤
│ RESTART IDENTITY│ 重置自增序列到初始值 │
│ CONTINUE IDENTITY│ 保持序列当前值（默认） │
│ CASCADE │ 级联清空有外键引用的表 │
│ RESTRICT │ 如果有外键引用则报错（默认） │
└─────────────────┴─────────────────────────────────────────────┘
TRUNCATE vs DELETE #
sql
-- 性能对比
-- DELETE：逐行删除，记录日志，触发触发器
DELETE FROM large_table; -- 较慢
-- TRUNCATE：一次性清空，更快
TRUNCATE large_table; -- 更快
-- 具体区别
┌─────────────────┬────────────────────┬─────────────────────┐
│ 特性 │ DELETE │ TRUNCATE │
├─────────────────┼────────────────────┼─────────────────────┤
│ WHERE 条件 │ 支持 │ 不支持 │
│ 触发器 │ 触发 │ 不触发 │
│ RETURNING │ 支持 │ 不支持 │
│ 速度 │ 较慢 │ 很快 │
│ 序列重置 │ 不重置 │ 可重置 │
│ 事务回滚 │ 支持 │ 支持 │
│ VACUUM │ 需要手动 │ 不需要 │
└─────────────────┴────────────────────┴─────────────────────┘
级联删除 #
外键级联删除 #
sql
-- 创建带级联删除的表
CREATE TABLE categories (
id SERIAL PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
category_id INTEGER REFERENCES categories(id) ON DELETE CASCADE
);
INSERT INTO categories (name) VALUES ('Electronics'), ('Books');
INSERT INTO products (name, category_id) VALUES
('Laptop', 1),
('Phone', 1),
('Novel', 2);
-- 删除分类时，关联的产品也会被删除
DELETE FROM categories WHERE id = 1;
-- 产品 Laptop 和 Phone 也会被删除
外键删除行为 #
sql
-- ON DELETE 选项
-- CASCADE: 级联删除
-- SET NULL: 设置为 NULL
-- SET DEFAULT: 设置为默认值
-- RESTRICT: 限制删除（默认）
-- NO ACTION: 同 RESTRICT
-- SET NULL 示例
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
amount DECIMAL(10, 2)
);
-- 删除用户时，订单的 user_id 会被设为 NULL
DELETE FROM users WHERE id = 1;
手动级联删除 #
sql
-- 先删除关联数据，再删除主数据
BEGIN;
DELETE FROM order_items WHERE order_id IN (
SELECT id FROM orders WHERE user_id = 1
);
DELETE FROM orders WHERE user_id = 1;
DELETE FROM users WHERE id = 1;
COMMIT;
-- 使用 CTE 删除
WITH deleted_orders AS (
DELETE FROM orders
WHERE user_id = 1
RETURNING id
)
DELETE FROM order_items
WHERE order_id IN (SELECT id FROM deleted_orders);
软删除 #
实现软删除 #
sql
-- 添加删除标记列
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP;
ALTER TABLE users ADD COLUMN is_deleted BOOLEAN DEFAULT FALSE;
-- 软删除（不实际删除，只标记）
UPDATE users
SET
is_deleted = TRUE,
deleted_at = CURRENT_TIMESTAMP
WHERE id = 1;
-- 查询时过滤已删除数据
SELECT * FROM users WHERE is_deleted = FALSE;
-- 创建视图简化查询
CREATE VIEW active_users AS
SELECT id, name, email FROM users WHERE is_deleted = FALSE;
-- 恢复软删除的数据
UPDATE users
SET
is_deleted = FALSE,
deleted_at = NULL
WHERE id = 1;
-- 永久删除软删除的数据
DELETE FROM users
WHERE is_deleted = TRUE
AND deleted_at < CURRENT_DATE - INTERVAL '30 days';
使用触发器自动过滤 #
sql
-- 创建触发器函数
CREATE OR REPLACE FUNCTION soft_delete_filter()
RETURNS TRIGGER AS $$
BEGIN
-- 插入时设置默认值
IF TG_OP = 'INSERT' THEN
NEW.is_deleted := COALESCE(NEW.is_deleted, FALSE);
RETURN NEW;
END IF;
-- 更新时检查
IF TG_OP = 'UPDATE' THEN
IF OLD.is_deleted = TRUE AND NEW.is_deleted = FALSE THEN
NEW.deleted_at := NULL;
END IF;
RETURN NEW;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
-- 创建触发器
CREATE TRIGGER trg_users_soft_delete
BEFORE INSERT OR UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION soft_delete_filter();
性能优化 #
分批删除 #
sql
-- 大批量删除时分批进行
-- 方法1：使用 LIMIT
DELETE FROM logs
WHERE created_at < '2025-01-01'
AND id IN (
SELECT id FROM logs
WHERE created_at < '2025-01-01'
LIMIT 10000
);
-- 方法2：使用 ID 范围
DELETE FROM logs
WHERE id BETWEEN 1 AND 100000;
-- 方法3：循环删除
DO $$
DECLARE
deleted_count INTEGER;
BEGIN
LOOP
DELETE FROM logs
WHERE created_at < '2025-01-01'
LIMIT 10000;
GET DIAGNOSTICS deleted_count = ROW_COUNT;
EXIT WHEN deleted_count = 0;
COMMIT;
END LOOP;
END $$;
使用索引 #
sql
-- 确保删除条件使用索引
CREATE INDEX idx_logs_created_at ON logs(created_at);
-- 这个删除会使用索引
DELETE FROM logs WHERE created_at < '2025-01-01';
-- 查看执行计划
EXPLAIN DELETE FROM logs WHERE created_at < '2025-01-01';
删除后维护 #
sql
-- 大量删除后执行 VACUUM
DELETE FROM logs WHERE created_at < '2025-01-01';
VACUUM ANALYZE logs;
-- 使用 VACUUM FULL 回收空间（会锁表）
VACUUM FULL logs;
-- 使用 pg_repack 在线回收空间（需要扩展）
-- pg_repack --table=logs database_name
安全删除 #
使用事务 #
sql
-- 使用事务确保安全
BEGIN;
-- 先查询要删除的数据
SELECT * FROM users WHERE status = 'inactive';
-- 确认无误后删除
DELETE FROM users WHERE status = 'inactive';
-- 确认结果
SELECT * FROM users WHERE status = 'inactive';
-- 确认无误后提交
COMMIT;
-- 如果有问题，回滚
-- ROLLBACK;
删除前备份 #
sql
-- 删除前创建备份
CREATE TABLE users_backup AS
SELECT * FROM users WHERE status = 'inactive';
-- 确认备份
SELECT COUNT(*) FROM users_backup;
-- 执行删除
DELETE FROM users WHERE status = 'inactive';
-- 如果需要恢复
INSERT INTO users SELECT * FROM users_backup;
常见错误 #
外键约束错误 #
sql
-- ERROR: update or delete on table "users" violates foreign key constraint
DELETE FROM users WHERE id = 1;
-- 解决方案：
-- 1. 先删除关联数据
DELETE FROM orders WHERE user_id = 1;
DELETE FROM users WHERE id = 1;
-- 2. 使用 CASCADE
-- ALTER TABLE orders DROP CONSTRAINT orders_user_id_fkey;
-- ALTER TABLE orders ADD CONSTRAINT orders_user_id_fkey
-- FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;
-- 3. 使用 TRUNCATE CASCADE
TRUNCATE users CASCADE;
权限错误 #
sql
-- ERROR: permission denied for table users
DELETE FROM users WHERE id = 1;
-- 解决方案：授予权限
GRANT DELETE ON users TO myuser;
学习路径 #
text
基础阶段
├── 数据库操作
├── 表操作
├── 数据插入
├── 数据更新
├── 数据删除（本文）
└── 基础查询
下一步 #
掌握了数据删除后，接下来学习 基础查询，了解如何查询数据！
最后更新：2026-03-29