可更新的VIEW不适用于Postgres 9.5中的ON CONFLICT [英] Updatable VIEW doesn't work with ON CONFLICT in Postgres 9.5

问题描述

PostgreSQL版本:9.5.4

PostgreSQL version: 9.5.4

我有一个表定义为:

CREATE TABLE IF NOT EXISTS TEST_1 (
ID       SERIAL PRIMARY KEY,
C1       BYTEA,
C2       TEXT NOT NULL,
C3       BOOLEAN NOT NULL DEFAULT FALSE,
CONSTRAINT TEST_1_unique_idx UNIQUE(C1, C2)
);

我的视图定义为:

create or replace view test as select * from test_1 with cascaded check
option;

这是从表名中抽象出来的，而应用程序代码是 通过视图名称进行工作(以实现一种简单的分区，并在需要时替换表)

This is necessary to abstract from table name while application code is working via view name (to implement a kind of simple partitioning with replacing tables when needed)

当我在视图上运行以下查询时:

When I run the following query on view:

insert into test (c1, c2, c3) values (decode('MTIzAAE=', 'base64'), 'text', true) on conflict (c1, c2) do update set c3=excluded.c3

我收到以下错误:

[0A000] ERROR: ON CONFLICT is not supported on table "test" used as a catalog table
  Position: 83

但是表上的相同查询可以正常工作.根据Postgres文档，这也应该与视图一起使用，因为可更新的视图完全支持ON CONFLICT

But the same query on table works as expected. According to Postgres documentation this should work with view as well since ON CONFLICT is fully supported with updatable views https://www.postgresql.org/docs/9.5/static/sql-createview.html

有什么想法我想念什么?

Any ideas what am I missing?

推荐答案

显然，使用with check option

如果删除with cascaded check option，则此方法有效.

If you remove with cascaded check option this works.

手册中未明确提及，因此这可能是文档疏忽.

This is not explicitly mentioned in the manual, so this might be a documentation oversight.

这篇关于可更新的VIEW不适用于Postgres 9.5中的ON CONFLICT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！