PostgreSQL DELETE居然有这些操作？返回数据、连表删你试过没？

DELETE语句的基本用法

语法与核心逻辑

DELETE语句用于从表中删除满足条件的行，其基本语法（源自官方文档）如下：

[ WITH [ RECURSIVE ] with_query [, ...] ]
DELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ]
    [ USING from_item [, ...] ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING { * | output_expression [ [ AS ] output_name ] } [, ...] ]

核心逻辑：通过WHERE条件筛选要删除的行，若省略WHERE，则删除表中所有行（需谨慎使用）。

示例：删除特定条件的行

假设films表存储电影信息，kind字段表示类型，要删除所有非音乐剧类型的电影：

-- 删除所有非音乐剧的电影
DELETE FROM films WHERE kind <> 'Musical';

执行后，films表中所有kind不等于'Musical'的行将被删除。

注意事项

• 无WHERE条件的风险：DELETE FROM films;会清空表中所有行，生产环境中务必避免这种操作，除非你明确要清空表。

使用WHERE条件精准控制删除范围

WHERE条件是DELETE的核心，它通过布尔表达式决定哪些行会被删除。常见的条件包括：

• 比较运算：=（等于）、<>（不等于）、>（大于）、<（小于）等；
• 逻辑运算：AND（并且）、OR（或者）、NOT（非）；
• 子查询：IN、EXISTS等。

示例：多条件组合删除

要删除2020年之前上映且评分低于6.0的电影：

DELETE FROM films 
WHERE release_year < 2020 
AND rating < 6.0;

只有同时满足“2020年前上映”和“评分<6.0”的行才会被删除。

连接其他表删除关联数据（USING clause）

当需要根据其他表的数据决定删除哪些行时，USING子句可以简化关联删除的逻辑。它允许在DELETE中连接其他表，类似SELECT的FROM子句。

示例：删除指定制片人的电影

假设films（电影）和producers（制片人）通过producer_id关联，要删除由名为'foo'的制片人制作的电影：

-- 方法1：使用USING clause（PostgreSQL扩展）
DELETE FROM films 
USING producers 
WHERE films.producer_id = producers.id 
AND producers.name = 'foo';

逻辑：将films与producers连接，找到producer_id匹配且制片人名为'foo'的电影行，删除这些电影。

对比：标准SQL子查询方式

如果你更习惯标准SQL，也可以用子查询实现：

-- 方法2：子查询（标准SQL）
DELETE FROM films 
WHERE producer_id IN (
    SELECT id FROM producers WHERE name = 'foo'
);

两种方法结果一致，但USING在关联表较大时可能更高效。

返回删除的行（RETURNING clause）

默认情况下，DELETE仅返回删除的行数（如DELETE 5表示删除5行）。若需确认具体删除了哪些数据（比如审计或回滚），可使用RETURNING子句返回被删除行的字段或计算结果。

示例1：返回删除的完整行

删除tasks表中状态为'DONE'的任务，并返回所有字段：

DELETE FROM tasks 
WHERE status = 'DONE' 
RETURNING *;

执行结果类似SELECT，会显示被删除行的所有列（如id、title、status）。

示例2：返回计算字段

删除任务时，返回任务id和删除时间：

DELETE FROM tasks 
WHERE status = 'DONE' 
RETURNING id, now() AS deleted_at;

结果会包含id（任务ID）和deleted_at（当前时间，由now()函数生成）。

使用游标删除特定行（WHERE CURRENT OF）

若需删除游标当前指向的行（比如逐行处理数据时），可使用WHERE CURRENT OF子句。这种方式适用于精准定位某一行的场景。

步骤与示例

1. 声明游标：针对目标表创建非分组查询的游标：

   -- 声明游标c_tasks，指向tasks表的所有行
   DECLARE c_tasks CURSOR FOR SELECT * FROM tasks;
   

2. 移动游标：用FETCH将游标定位到目标行（比如第一行）：

   -- 定位到第一行
   FETCH NEXT FROM c_tasks;
   

3. 删除当前行：通过WHERE CURRENT OF删除游标指向的行：

   -- 删除游标当前指向的行
   DELETE FROM tasks WHERE CURRENT OF c_tasks;
   

注意事项

• 游标必须是针对DELETE目标表的非分组查询（不能用GROUP BY）；
• 不能同时使用WHERE CURRENT OF和布尔条件（如WHERE CURRENT OF c_tasks AND status = 'DONE'是错误的）。

批量删除（模拟LIMIT）

PostgreSQL的DELETE没有LIMIT子句（不能直接写DELETE FROM table LIMIT 1000），但可通过WITH子查询模拟批量删除，避免一次性删除过多行导致锁表或性能问题。

示例：批量删除归档日志

假设user_logs表存储用户日志，status为'archived'表示已归档。每次删除10000行归档日志：

WITH delete_batch AS (
  -- 选择10000行归档日志，按创建时间排序并加锁（FOR UPDATE）
  SELECT ctid FROM user_logs 
  WHERE status = 'archived' 
  ORDER BY creation_date 
  FOR UPDATE 
  LIMIT 10000
)
-- 删除选中的行（通过ctid定位）
DELETE FROM user_logs 
USING delete_batch 
WHERE user_logs.ctid = delete_batch.ctid;

关键解释

• ctid：PostgreSQL中每行的物理位置标识符（格式为(块号, 行号)），用于精准定位行；
• WITH子查询：先选出要删除的10000行（delete_batch），再通过USING连接user_logs表删除这些行；
• FOR UPDATE：为选中的行加排它锁，防止并发修改，避免删除时的冲突。

清空表：DELETE vs TRUNCATE

若需清空表中所有行，DELETE FROM table;和TRUNCATE TABLE table;均可，但二者有显著区别：

示例：快速清空表

若要快速清空films表（无需触发触发器），用TRUNCATE：

-- 快速清空films表（推荐）
TRUNCATE TABLE films;

若需触发DELETE触发器（比如审计），则用DELETE：

-- 逐行删除所有行（触发触发器）
DELETE FROM films;

课后Quiz

问题1：如何删除films表中由名为'bar'的制片人制作的所有电影？写出两种方法。

答案： 方法1（USING子句）：

DELETE FROM films 
USING producers 
WHERE films.producer_id = producers.id 
AND producers.name = 'bar';

方法2（子查询）：

DELETE FROM films 
WHERE producer_id IN (
    SELECT id FROM producers WHERE name = 'bar'
);

问题2：如何在删除tasks表中已完成的任务时，返回被删除行的id和title？

答案： 使用RETURNING子句：

DELETE FROM tasks 
WHERE status = 'DONE' 
RETURNING id, title;

问题3：为什么有时候用TRUNCATE代替DELETE清空表？

答案： TRUNCATE比DELETE更快，因为它直接清空表的物理存储（不逐行删除），且仅写少量WAL日志（用于恢复）。适合清空大表时提升性能。但TRUNCATE不会触发DELETE触发器，若需触发触发器则仍用DELETE。

常见报错及解决方案

报错1：ERROR: syntax error at or near "LIMIT"

原因：DELETE没有LIMIT子句，直接写DELETE FROM user_logs LIMIT 1000会报错。
解决：用WITH子查询模拟LIMIT（参考“批量删除”部分示例）。

报错2：ERROR: permission denied for table films

原因：当前用户无films表的DELETE权限。
解决：授予权限（需表所有者或超级用户执行）：

GRANT DELETE ON films TO your_username;

报错3：ERROR: cannot use WHERE CURRENT OF with a condition

原因：同时使用WHERE CURRENT OF和布尔条件（如WHERE CURRENT OF c_tasks AND status = 'DONE'）。
解决：去掉布尔条件，或在游标查询时加条件（如DECLARE c_tasks CURSOR FOR SELECT * FROM tasks WHERE status = 'DONE'）。

报错4：ERROR: relation "producers" does not exist

原因：USING子句中引用的表不存在或拼写错误。
解决：检查表名拼写，用d producers在psql中确认表存在。

参考链接

• PostgreSQL官方文档：DELETE语句详解 www.postgresql.org/docs/17/sql…
• PostgreSQL官方文档：TRUNCATE语句 www.postgresql.org/docs/17/sql…
• PostgreSQL官方文档：游标（CURSOR） www.postgresql.org/docs/17/sql…