Postgresql SQL 抛出不明确的列错误 [英] Postgresql SQL throws ambiguous column error

问题描述

我的 Postgres 数据库中有下表

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);

我想向其中添加新行或更新 天气、到期和如果该行已经存在，则为 clients 列.为此，我让我的 PHP 脚本生成以下 SQL

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;

由于错误而失败

错误:列引用clients"不明确

ERROR: column reference "clients" is ambiguous

我不明白为什么会发生这种情况.希望有人能解释一下

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

推荐答案

在 UPDATE 部分中，您应该使用 EXCLUDED 行"来引用值.并且要引用现有值，您需要再次使用表为列添加前缀以避免排除"和当前"值之间的歧义:

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;

这篇关于Postgresql SQL 抛出不明确的列错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！