plpgsql function - column name is ambiguous

Question

(Am using Postgres 11.5)

I've been a Postgres user for a while now and am aware of the usual ways to avoid ambiguity (e.g. naming function inputs with a prefix etc.). But I am really struggling with ON CONFLICT

I have also seen this SO question How to disambiguate a plpgsql variable name in a ON CONFLICT clause?, but either I've misread it or it doesn't really seem to be the issue I am having.

This is my function:

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=statcount+1 where category=p_category and datapoint=p_datapoint;
        IF FOUND THEN
                return 1;
        ELSE
                return 0;
        END IF;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

This returns the error:

ERROR: column reference "statcount" is ambiguous

Answer 1

You need to prefix the columns on the right hand side of the assignment in the SET part (the left hand side is unambiguous):

INSERT INTO foobar(category,datapoint,statcount) 
VALUES(p_category,p_datapoint,0)
ON CONFLICT(category,datapoint) DO UPDATE
  SET statcount = foobar.statcount + 1 
where category = p_category 
  and datapoint = p_datapoint;

Answer 2

The ambiguity is between the original and the new column value. Qualify the column with the table name for the original value and with EXCLUDED for the new value:

SET statcount=foobar.statcount+1

You cannot qualify the first statcount since that is not allowed SQL syntax (the column automatically belongs to the updated table, so there is no ambiguity).