INSERT ... ON CONFLICT DO UPDATE 中的不明确列引用 [英] Ambiguous column reference in INSERT ... ON CONFLICT DO UPDATE

问题描述

给定一张桌子:

CREATE TABLE IF NOT EXISTS test.TestCounter";(id"文本主键，计数"整数);

如果记录已经存在，我想插入一条记录并增加计数器

INSERT INTO 测试.TestCounter"(身份证")值('id-0')冲突时(id")做更新设置计数"=(计数"+ 1)返回计数"

目前我收到此错误:

错误:列引用计数"；模棱两可第 4 行:设置计数"=计数"；+ 1^SQL 状态:42702字符数:107

解决方案

您需要对可能存在歧义的列进行表限定.
使用虚拟表名excluded 来引用输入行.但是你可能想引用目标列，所以用目标表的名称限定:

INSERT INTO test.test_counter (id)值('id-0')冲突 (id) 做更新SET count = test_counter.count + 1 -- 这里返回计数；

手册:<块引用>

请注意，特殊的 excluded 表用于引用最初建议插入的值.

虚拟输入表中的单行excluded包含目标表的所有列，即使没有列在INSERT<的目标列列表中/code> 或 VALUES 表达式.所以你遇到的歧义总是存在，无论 count 是否明确定位.

旁白:目标列列表中省略的列默认为其各自列的 DEFAULT 值，默认为 NULL(NULL 是默认列 DEFAULT).即，在您的设置中默认为 NULL，在我下面改进的设置中默认为 1.并且应用行级触发器BEFORE INSERT(如果有).

但这都不适用于示例，因为它毕竟指的是 target 列.

值得注意的是，列名 count 的另外两个实例是明确的(因此不需要表限定)，因为它们只能引用 target 表.

当列 count 未定义 NOT NULL 时，您的设置很容易中断，因为 NULL + 1 仍然是 NULL.这种设置会更有意义:

创建表 test.test_counter (id 文本主键, 计数整数 NOT NULL DEFAULT 1);

在我的示例中也不使用引用的 CaMeL 案例名称.见:

• PostgreSQL 列名是否区分大小写?

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

解决方案

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?

这篇关于INSERT ... ON CONFLICT DO UPDATE 中的不明确列引用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！