Postgresql SQL throws ambiguous column error

Question

I have the following table in my Postgres database

CREATE TABLE "public"."zuffs" 
(
 "hash" bigint NOT NULL,
 "zuff" BIGINT NOT NULL,
 "lat" INTEGER NOT NULL,
 "lng" INTEGER NOT NULL,
 "weather" INTEGER DEFAULT 0,
 "expires" INTEGER DEFAULT 0,
 "clients" INTEGER DEFAULT 0,
 CONSTRAINT "zuffs_hash" PRIMARY KEY ("hash")
) WITH (oids = false);

to which I want to add a new row or update the weather, expires & clients columns if the row already exists. To do this I get my PHP script to generate the following SQL

INSERT INTO zuffs (hash,zuff,lat,lng,weather,expires)         
VALUES(5523216,14978310951341,4978,589,105906435,4380919) ON CONFLICT(hash) DO UPDATE SET 
weather = 105906435,expires = 4380919,clients = clients + 1;

which fails with the error

ERROR: column reference "clients" is ambiguous

I fail to see why this might be happening. I hope that someone here can explain

user330315 · Accepted Answer · 2020-02-20 16:13:00Z

4

In the UPDATE part you should use the EXCLUDED "row" to reference the values. And to reference the existing value, you need to prefix the column with the table again to avoid the ambiguity between "excluded" and "current" values:

INSERT INTO zuffs (hash,zuff,lat,lng,weather,expires)         
VALUES (5523216,14978310951341,4978,589,105906435,4380919) 
ON CONFLICT(hash) DO UPDATE 
  SET weather = excluded.weather,
      expires = excluded.expires,
      clients = zuffs.clients + 1;

answered Feb 20, 2020 at 16:13

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

DroidOS Over a year ago

Thank you, this works. However, it is not at all clear to me what ÈXCLUDED means in this context. The documentation appears ratther dense on this point - or perhaps it is just me. Should it be interpreted as meaning the row currently in question (i.e identified by the unique Hash value). Also why is clients = zuffs.clients + 1 avoiding ambiguity? Ambiguity implies that I might somehow have been referring to some other clients column and I cannot see how.

user330315 Over a year ago

excluded refers to the row (values) that was not inserted. The left side of the assignment is in-ambigous. However the right hand side could refer to the excluded value or the current value in the table.

Collectives™ on Stack Overflow

Postgresql SQL throws ambiguous column error

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related