PostgreSQL 9.5是否支持在WITH查询中使用INSERT ON CONFLICT UPDATE语句？

Question

我正在尝试从CTE中插入（或冲突更新）行，但很难找到正确的语法。我要插入的表长这样（为了清晰起见而简化）

        Column        |           Type           |                            Modifiers
----------------------+--------------------------+------------------------------------------------------------------
 id                   | integer                  | not null default nextval('"QuestionStatistic_id_seq"'::regclass)
 questionId           | integer                  |
 count                | integer                  | not null default 0
Indexes:
    "QuestionStatistic_pkey" PRIMARY KEY, btree (id)
    "QuestionStatistic_questionId_key" UNIQUE CONSTRAINT, btree ("questionId")

这是我的查询：

with "Statistic" as (
   select "questionId", "count" from "SomeTable"
)
INSERT INTO "QuestionStatistic" ("questionId", "count") SELECT "questionId", "count" FROM "Statistics" 
ON CONFLICT ("questionId") DO UPDATE SET "count" = "Statistics"."count"

我遇到了一个问题，当我执行SET "count" = "Statistics"."count"时，出现了ERROR：缺少表“Statistics”的FROM子句条目的错误提示。我也尝试在更新语句中添加FROM，但是得到了ERROR：在或附近的语法错误“FROM”的提示。有没有办法让INSERT ON CONFLICT UPDATE与CTE一起使用？

- fourslashw

---

也许你应该使用 excluded.count？.. 你能分享一个可重复的沙盒吗？.. - Vao Tsun

@VaoTsun 谢谢，使用“excluded”作为表名完全没问题。 - fourslashw

Answer 1

https://www.postgresql.org/docs/9.5/static/sql-insert.html

在ON CONFLICT DO UPDATE语句的SET和WHERE子句中，可以使用表名（或别名）访问现有行，并使用特殊的excluded表访问提议插入的行。

因此尝试：

with "Statistic" as (
   select "questionId", "count" from "SomeTable"
)
INSERT INTO "QuestionStatistic" ("questionId", "count")
SELECT "questionId", "count" FROM "Statistics" 
ON CONFLICT ("questionId") DO UPDATE 
SET "count" = excluded."count"

- Vao Tsun