更改视图中使用的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 varying而不是character varying(n). 在手册中了解这些数据类型.

Permanent solution for this case

To avoid the problem altogether use the data type text or varchar / character varying without a length specifier instead of character varying(n). Read about these data types in the manual.

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特权，添加注释甚至定义列默认值(对于规则ON INSERT TO my_view DO 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屋！