创建物化视图时如何指示列不可为空? [英] How to indicate that a column is not nullable when creating a materialized view?
问题描述
我正在使用以下查询创建物化视图:
I am creating a materialized view using the following query:
CREATE MATERIALIZED VIEW article_view AS
SELECT
id,
alternative_headline,
article_author_id,
created_at,
description,
headline,
preview_paragraph_image_id,
published_at,
updated_at
FROM article
WHERE
published_at IS NOT NULL
WITH NO DATA;
CREATE UNIQUE INDEX ON article_view (id);
我希望它在 实体化视图表明published_at
列不可为空.
I want it to be represented in the materialized-view that the published_at
column is not nullable.
想要将 published_at
列表示为不可为空的原因是因为我使用的是 脚手架工具 根据数据库模式生成数据库查询和类型.在这种特殊情况下,published_at
被错误地表示为触发严格类型检查错误的可空列.
The reason for wanting to represent the published_at
column as not nullable, is because I am using a scaffolding tool that generates database queries and types based on the database schema. In this particular case, the published_at
is being falsely represented as a nullable column triggering strict type checking errors.
上述脚手架工具使用以下查询来描述数据库:
The said scaffolding tool is using the following query to describe the database:
SELECT
pc1.relname AS "tableName",
pa1.attname AS "columnName",
pg_catalog.format_type (pa1.atttypid, NULL) "dataType",
pc1.relkind = 'm' "isMaterializedView",
NOT(pa1.attnotnull) "isNullable"
FROM
pg_class pc1
JOIN pg_namespace pn1 ON pn1.oid = pc1.relnamespace
JOIN
pg_attribute pa1 ON pa1.attrelid = pc1.oid
AND pa1.attnum > 0
AND NOT pa1.attisdropped
WHERE
pn1.nspname = 'public' AND
pc1.relkind IN ('r', 'm')
推荐答案
目前的语法不支持 CREATE 或 ALTER 形式.我在猜测,但由于 CREATE MATERIALIZED VIEW 语句接受任何查询,它无法可靠地从引用的表中复制非空约束.
The syntax doesn't support it in CREATE or ALTER forms as it stands today. I am guessing, but since the CREATE MATERIALIZED VIEW statement accepts any query, it cannot reliably copy the not null constraints from the referenced table.
然而,您可以更新 pg_catalog.pg_attribute 本身以执行您想要的操作.
You can however UPDATE the pg_catalog.pg_attribute itself to do what you want.
UPDATE pg_catalog.pg_attribute
SET attnotnull = true
WHERE attrelid = the_oid_of_the_published_at_column;
这篇关于创建物化视图时如何指示列不可为空?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!