Change PostgreSQL columns used in views

Question

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

Don't think of a view as a table...it doesn't physically exist, it's a subquery you are referring to. There really is no create overhead in the create or replace view statement, and no alter view statement for this reason. — Twelfth
– Twelfth, Commented Dec 15, 2011 at 18:42
I stated this whole thing poorly... give me a minute to explain. — Larsenal
– Larsenal, Commented Dec 15, 2011 at 18:44
Ah sorry. Given your reputation I was wondering if that was really what you were asking for. I'm curious if there's an answer here as I get the feeling I'm going to be doing this in the coming year. — Twelfth
– Twelfth, Commented Dec 15, 2011 at 19:00
@Tewlfth: There actually is an ALTER VIEW statement in PostgreSQL. You can't change the view's defining query, but there are other auxiliary attributes. — Erwin Brandstetter
– Erwin Brandstetter, Commented Dec 16, 2011 at 17:35

Erwin Brandstetter · Accepted Answer · 2022-05-20 21:43:25Z

22

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.

edited May 20, 2022 at 21:43

answered Dec 15, 2011 at 22:56

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Michael Krelin - hacker Over a year ago

Sorry about not @ing you, I had a feeling that once you start commenting you're being notified of everything in that thread. +1 and my deleted.

Nishchit Over a year ago

Thank you @Erwin, What if I want to add new column in the TABLE? How It'll handled by the VIEW ?

Erwin Brandstetter Over a year ago

@NishchitDhanani: A view only includes columns listed at creation time. SELECT * is resolved to the current list of columns at creation time ("early binding"). Columns added to the underlying table later are not reflected in a view automatically. You need to recreate it / create a new view to include more columns.

Collectives™ on Stack Overflow

Change PostgreSQL columns used in views

1 Answer 1

Permanent solution for this case

Detailed explanation

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Permanent solution for this case

Detailed explanation

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related