SQL删除2019年后数据行两类报错问题排查

业务背景 #

• 需删除数据库中2019年及之后的新增数据行：当前正在将跨度12年的历史文件传输至服务器，因2019年数据未完成全量传输，需移除该部分数据，即删除日期晚于2018-12-30的数据行。

第一次执行报错 #

首次执行的SQL代码：

BEGIN TRANSACTION
DELETE * FROM mlode WHERE "kd" > DATEFROMPARTS(2018, 12, 31)
COMMIT TRANSACTION;

返回的错误信息：

ERROR:  syntax error at or near "DELETE"
LINE 2: DELETE * FROM mlode WHERE "kd" > DATEFROMPARTS(2018, 12, 31)
        ^
SQL state: 42601

调整写法后的二次报错 #

调整SQL写法后执行的代码：

BEGIN TRANSACTION;
DELETE FROM mlode WHERE kd >= DATE '2019-01-01';
COMMIT TRANSACTION;

返回新的错误信息：

ERROR:  current transaction is aborted, commands ignored until end of transaction block

问题原因与解决方案 #

• 第一次语法错误原因：
  1. DELETE语句语法错误：标准SQL中DELETE语句不需要在DELETE关键字后加*，*是SELECT语句用于查询全字段的语法，不能套用到DELETE操作中，正确基础结构为DELETE FROM 表名 WHERE 筛选条件。
  2. 函数适配问题：从报错的SQL状态码可以判断当前使用的是PostgreSQL数据库，DATEFROMPARTS是SQL Server的内置日期生成函数，PostgreSQL中直接使用DATE 'yyyy-mm-dd'的标准日期字面量写法即可。
• 第二次事务报错原因：第一次SQL执行失败后，当前开启的事务已经进入异常中止状态，该状态下除了ROLLBACK、COMMIT这类事务控制语句，其余所有SQL执行请求都会被直接拒绝，必须先回滚异常事务才能执行新的操作。
• 正确操作流程：
  1. 先执行ROLLBACK;命令，回滚之前卡住的异常事务。
  2. 建议开启事务先校验数据范围再执行删除，避免误删：

     BEGIN;
     -- 先统计待删除的数据量，确认筛选范围符合预期
     SELECT COUNT(*) FROM mlode WHERE kd >= DATE '2019-01-01';
     -- 范围确认无误后执行删除
     DELETE FROM mlode WHERE kd >= DATE '2019-01-01';
     -- 核对返回的删除行数符合预期后，提交事务生效
     COMMIT;
     

内容的提问来源于stack exchange，提问作者maciej.o