column reference xxx is ambiguous (candidates: excluded.xxx,table.xxx) in upsert statement

Question

I would like to perform upsert sql statement in cockroach DB.

I would like to insert row into table when specific id does not exist in table and update name column when id with given value exist in table.

Table definition:

CREATE TABLE IF NOT EXISTS programs_tiers (
  id STRING PRIMARY KEY,
  program_id STRING NOT NULL REFERENCES programs (id),
  name STRING,
  created TIMESTAMPTZ DEFAULT current_timestamp(),
  UNIQUE(program_id, name)
);

Here is my sql statement using knex.

 const response =  await this.knex.raw('INSERT INTO programs_tiers (id,name) values(?,?) ON CONFLICT(id) DO UPDATE SET name = excluded.name  WHERE program_id = ? AND id = ?',[tierId, tierName, programId, tierId]);

I'm getting following error:

error: column reference "id" is ambiguous (candidates: excluded.id, programs_tiers.id).

How could i solve this ?

score 1 · Accepted Answer · answered Jun 03 '19 at 15:20

1

Specify the table name to fix the ambiguity:

[...] WHERE program_id = ? AND programs_tiers.id = ?
                               ^^^^^^^^^^^^^^^

answered Jun 03 '19 at 15:20

Jonas Staudenmeir

24,815
6
63
109

yes, that was the problem. Thanks! but why when I'm operating on one table ? – spruser Jun 03 '19 at 15:38
1

The database creates a "virtual" `excluded` table that contains the inserted values. This table also has an `id` column. – Jonas Staudenmeir Jun 03 '19 at 15:51

column reference xxx is ambiguous (candidates: excluded.xxx,table.xxx) in upsert statement

1 Answers1