PostgreSQL 11.5函数ON CONFLICT中statcount列引用歧义求助

嘿，作为资深PG用户，你肯定懂用参数前缀避免歧义的套路，但ON CONFLICT这块的坑确实有点特殊，我来帮你捋清楚！

问题根源 #

你遇到的ERROR: column reference "statcount" is ambiguous，是因为在ON CONFLICT ... DO UPDATE块里，PostgreSQL会创建一个名为EXCLUDED的虚拟表，用来存储你试图插入的那一行数据。当你直接写statcount时，数据库没法区分你指的是目标表foobar里的现有列，还是EXCLUDED表中待插入的statcount（也就是你VALUES里的0），所以抛出了歧义错误。

修正后的函数代码 #

直接给你改好的版本，顺便优化了冗余的WHERE子句：

create function foobar_data(p_category text,p_datapoint text) returns integer AS $$
BEGIN
    INSERT INTO foobar(category, datapoint, statcount)
    VALUES(p_category, p_datapoint, 0)
    ON CONFLICT(category, datapoint)
    DO UPDATE SET statcount = foobar.statcount + 1;
    IF FOUND THEN
        return 1;
    ELSE
        return 0;
    END IF;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

关键修改点 #

1. 消除列名歧义：
   用foobar.statcount明确指定要引用目标表中的现有统计列，让数据库清楚知道你要更新的是已存在的那一行的数值，而不是待插入的0。

2. 移除冗余WHERE子句：
   你原本的where category=p_category and datapoint=p_datapoint完全没必要——ON CONFLICT(category, datapoint)已经精准定位到了冲突的那一行，DO UPDATE只会对这一行生效，额外的WHERE条件属于画蛇添足，还可能降低执行效率。

如果之后有特殊场景需要在DO UPDATE里过滤行，也可以用EXCLUDED来引用待插入的参数，比如：

DO UPDATE SET statcount = foobar.statcount + 1
WHERE foobar.category = EXCLUDED.category AND foobar.datapoint = EXCLUDED.datapoint;

不过这个例子里和冲突条件重复了，所以还是去掉更简洁。

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