Ambiguous column reference in INSERT ... ON CONFLICT DO UPDATE

Question

Given a table:

CREATE TABLE IF NOT EXISTS test."TestCounter" 
("id" TEXT PRIMARY KEY, 
 "count" INTEGER);

I'd like to insert a record and increment the counter if the record already exists

INSERT INTO test."TestCounter" ("id")
VALUES ('id-0')
 ON CONFLICT ("id") DO UPDATE
 SET "count" = ("count" + 1)
 RETURNING "count"

Currently I get this error:

ERROR:  column reference "count" is ambiguous
LINE 4:  SET "count" = "count" + 1
                       ^
SQL state: 42702
Character: 107

@BeshambherChaukhwan: There is no "Replace Into" in Postgres (or standard SQL). — Erwin Brandstetter, Feb 15 '21 at 05:35
Oh my bad. Please check this answer https://stackoverflow.com/questions/11135501/postgresql-update-if-row-with-some-unique-value-exists-else-insert/11135767 — Beshambher Chaukhwan, Feb 15 '21 at 05:47
@BeshambherChaukhwan: A trigger won't solve this problem either and is no need for, see the answer below. — Frank Heikens, Feb 15 '21 at 08:01
Does this answer your question? [Postgresql column reference "id" is ambiguous](https://stackoverflow.com/questions/9821121/postgresql-column-reference-id-is-ambiguous) — TylerH, Oct 20 '22 at 13:35

Erwin Brandstetter · Accepted Answer · 2021-02-15T05:52:05.207

You need to table-qualify the column where it would be otherwise ambiguous.
Use the virtual table name excluded to refer to the input row. But you probably want to refer to the target column, so qualify with the name of the target table:

INSERT INTO test.test_counter (id)
VALUES ('id-0')
ON CONFLICT (id) DO UPDATE
SET count = test_counter.count + 1  -- here
RETURNING count;

The manual:

Note that the special excluded table is used to reference values originally proposed for insertion.

The single row from the virtual input table excluded contains all columns of the target table, even if not listed in the target column list of the INSERT or the VALUES expression. So the ambiguity you encountered is always there, whether count is targeted explicitly or not.

Aside: Columns omitted in the target column list default to their respective column DEFAULT value, which is NULL by default (NULL being the default column DEFAULT). I.e., it would default to NULL in your setup and 1 in my improved setup below. And row-level triggers BEFORE INSERT (if any) are applied.

But neither of that applies for the example as it refers to the target column after all.

Notably, the other two instances of the column name count are unambiguous (and thus do not require table-qualification) as those can only refer to the target table.

Your setup can easily break while the column count isn't defined NOT NULL, as NULL + 1 is still NULL. This setup would make more sense:

CREATE TABLE test.test_counter (
  id    text PRIMARY KEY
, count integer NOT NULL DEFAULT 1
);

Also not using quoted CaMeL-case names in my example. See:

Are PostgreSQL column names case-sensitive?

Ambiguous column reference in INSERT ... ON CONFLICT DO UPDATE

1 Answers1

Linked