更改视图中使用的 PostgreSQL 列

发布于 2024-12-21 10:55:33 字数 509 浏览 1 评论 0原文

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

澄清： 我明白了什么是视图。事实上，这是因为视图就像一个子查询，我希望我可以只更改基础表并让视图接受更改。

假设我有以下内容：

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

CREATE OR REPLACE VIEW monkey_names AS 
 SELECT name
   FROM monkey

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

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

原文

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.

Let's say I have the following:

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

三岁铭 2024-12-28 10:55:33

这种情况的永久解决方案

要完全避免该问题，请使用不带长度说明符的数据类型 text 或 varchar / character Varing 而不是 字符变化（n）。阅读手册中有关这些数据类型的信息。

CREATE TABLE monkey(name text NOT NULL);

如果您确实想强制执行最大长度，创建一个 CHECK 约束：

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

您可以随时更改或删除该约束，而无需触及视图等依赖对象，也无需强制 Postgres 写入新行由于类型的改变（在现代版本的 Postgres 中不再需要）。

详细说明

PostgreSQL 中的视图不仅仅是“子查询的别名”。视图被实现为具有规则 ON SELECT TO my_view DO INSTEAD 的特殊表。（这就是为什么您可以使用 ALTER TABLE 命令更改视图。）您可以GRANT 权限、添加注释甚至定义列默认值（对于规则 很有用）在插入到 my_view 时执行...）。请在此处或此处。

如果更改底层对象，则可能还需要更改相关视图。 ALTER VIEW 语句只能更改视图的辅助属性。使用CREATE OR REPLACE VIEW更改查询 - 它将保留任何其他属性。

但是，如果您想要更改结果列的数据类型（如当前的情况），则无法使用CREATE OR REPLACE VIEW。您必须DROP旧视图并CREATE新视图。这永远不会删除基础表的任何数据。不过，它将删除视图的任何其他属性。

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);

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);

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).

Detailed explanation

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.

If you change underlying objects, you may need to change depending views, 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.

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.

回复收藏 0 原文

~没有更多了~