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

Solution

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

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

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

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.

Comments

[Michael Krelin - hacker]

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]

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

[Erwin Brandstetter]

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