PostgreSQL删除数据报错：子查询返回多行，求问题原因排查

问题场景 #

需要实现两个删除逻辑：

1. 删除属于请求组1或3的事件模板中的所有事件
2. 删除与失效请求关联的所有事件模板中的事件
   但执行以下DELETE语句时触发错误：ERROR: more than one row returned by a subquery used as an expression

报错的SQL语句 #

DELETE FROM
    public.events_tmp_acc
WHERE "tmpid" IN (
        SELECT "id"
        FROM public.tmp_acc
        WHERE "from" IN (
                SELECT "id" 
                FROM
                    public.events_req_form 
                WHERE
                    to_date("date", 'YYYY-MM-DD') < to_date( (
                            SELECT
                                "date"
                            FROM
                                public.events_req_form
                            WHERE
                                "isActive" = true
                        ),
                        'YYYY-MM-DD'
                    )
                    AND "isAccepted" = true
            )
    );

错误原因 #

错误出现在嵌套最深的子查询：SELECT "date" FROM public.events_req_form WHERE "isActive" = true。这个子查询如果返回多行结果（比如数据库中有多个标记为活跃的请求），to_date()函数只能接收单个值，就会触发"子查询返回多行当作表达式使用"的错误。

修正方案 #

1. 先修复失效请求关联的删除逻辑

如果需求是将请求日期与所有活跃请求中最早的日期比较，用MIN()函数提取单个日期值：

DELETE FROM
    public.events_tmp_acc
WHERE "tmpid" IN (
        SELECT "id"
        FROM public.tmp_acc
        WHERE "from" IN (
                SELECT "id" 
                FROM
                    public.events_req_form 
                WHERE
                    to_date("date", 'YYYY-MM-DD') < (
                            SELECT MIN(to_date("date", 'YYYY-MM-DD'))
                            FROM
                                public.events_req_form
                            WHERE
                                "isActive" = true
                        )
                    AND "isAccepted" = true
            )
    );

2. 补充实现请求组1或3的删除逻辑

结合最初的两个需求，完整SQL如下（假设请求组字段为events_req_form.group_id）：

DELETE FROM
    public.events_tmp_acc
WHERE 
    -- 与失效请求关联的事件
    "tmpid" IN (
        SELECT "id"
        FROM public.tmp_acc
        WHERE "from" IN (
                SELECT "id" 
                FROM
                    public.events_req_form 
                WHERE
                    to_date("date", 'YYYY-MM-DD') < (
                            SELECT MIN(to_date("date", 'YYYY-MM-DD'))
                            FROM
                                public.events_req_form
                            WHERE
                                "isActive" = true
                        )
                    AND "isAccepted" = true
            )
    )
    -- 属于请求组1或3的事件模板中的事件
    OR "tmpid" IN (
        SELECT "id"
        FROM public.tmp_acc
        WHERE "from" IN (
                SELECT "id" 
                FROM public.events_req_form
                WHERE group_id IN (1, 3)
            )
    );

内容的提问来源于stack exchange，提问作者zet0