更改视图中使用的 PostgreSQL 列 [英] Change PostgreSQL columns used in views

问题描述

我想让 PostegreSQL 放松一点.每次我想更改视图中使用的列时，似乎我必须删除视图，更改字段，然后重新创建视图.我可以放弃额外的保护，只告诉 PostgreSQL 让我改变字段，然后找出对视图的调整吗?

I would like PostegreSQL to relax a bit. Every time I want to change a column used in a view, it seems I have to drop the view, change the field and then recreate the view. Can I waive the extra protection and just tell PostgreSQL to let me change the field and then figure out the adjustment to the view?

说明:我明白什么是视图.事实上，这是因为视图就像一个子查询，我希望我可以只更改基础表并让视图接收更改.

Clarification: I understand what a view is. In fact, it's because the view is like a subquery that I wish I could just change the underlying tables and have the view pick up the change.

假设我有以下内容:

CREATE TABLE monkey
(
  "name" character varying(50) NOT NULL,
)

CREATE OR REPLACE VIEW monkey_names AS 
 SELECT name
   FROM monkey

我真的只想在迁移脚本中执行以下操作而不必删除并重新创建视图.

I really just want to do the following in a migration script without having to drop and recreate the view.

ALTER TABLE monkey ALTER COLUMN "name" character varying(100) NOT NULL

推荐答案

此案例的永久解决方案

为了完全避免这个问题，请使用没有长度说明符的数据类型 text 或 varchar/character changed 而不是 character variables(n).在手册中阅读这些数据类型.

CREATE TABLE monkey(name text NOT NULL)

如果您真的想强制执行最大长度，请创建一个 CHECK 约束:

If you really want to enforce a maximum length, create a CHECK constraint:

ALTER TABLE monkey 
  ADD CONSTRAINT monkey_name_len CHECK (length(name) < 101);

您可以随时更改或删除该约束，而无需接触依赖对象(如视图)，也无需由于类型的更改而强制 Postgres 在表中写入新行(在现代版本的 Postgres 中不再需要).

You can change or drop that constraint any time without touching depending objects like views and without forcing Postgres to write new rows in the table due to the change of type (which isn't always necessary any more in modern version of Postgres).

根据@Michael 的建议，我添加了一些更一般的信息:

As proposed by @Michael, I add some more general information:

PostgreSQL 中的视图不仅仅是子查询的别名".视图被实现为具有规则 ON SELECT TO my_view DO INSTEAD 的特殊表.(这就是您可以使用 ALTER TABLE 命令更改视图的原因.)您可以GRANT 权限，添加注释甚至定义列默认值(对于规则 很有用)在插入到 my_view 时做 INSTEAD...).在此处或这里.

A view in PostgreSQL is not just an "alias to subquery". Views are implemented as special tables with a rule ON SELECT TO my_view DO INSTEAD. (That's why you can alter views with an ALTER TABLE command.) You can GRANT privileges to it, add comments or even define column defaults (useful for a rule ON INSERT TO my_view DO INSTEAD...). Read more in the manual here or here.

如果您更改底层对象，您也需要更改任何依赖视图的定义查询.ALTER VIEW 语句只能更改视图的辅助属性.使用 CREATE OR REPLACE VIEW 更改查询 - 它将保留任何其他属性.

If you change underlying objects, you need to change the defining query of any depending view, too. The ALTER VIEW statement can only change auxiliary attributes of a view. Use CREATE OR REPLACE VIEW to change the query - it will preserve any additional attributes.

但是，如果您想更改结果列的数据类型(就像手头的情况)，CREATE OR REPLACE VIEW 是不可能的.您必须DROP 旧视图和CREATE 新视图.这永远不会删除基础表的任何数据.但是，它将删除视图的任何其他属性，这些属性也必须重新创建.

However, if you want to change data types of resulting columns (like in the case at hand), CREATE OR REPLACE VIEW is not possible. You have to DROP the old and CREATE a new view. This will never delete any data of the underlying tables. It will drop any additional attributes of the view, though, which have to be recreated, too.

这篇关于更改视图中使用的 PostgreSQL 列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！