9

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
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Larsenal
  • 49,878
  • 43
  • 152
  • 220
  • 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 Dec 15 '11 at 18:42
  • I stated this whole thing poorly... give me a minute to explain. – Larsenal Dec 15 '11 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 Dec 15 '11 at 19:00
  • @Tewlfth: There actually **is** an [`ALTER VIEW` statement in PostgreSQL](http://www.postgresql.org/docs/current/interactive/sql-alterview.html). You can't change the view's defining query, but there are other auxiliary attributes. – Erwin Brandstetter Dec 16 '11 at 17:35

1 Answers1

18

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 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. – Michael Krelin - hacker Dec 16 '11 at 13:12
  • Thank you @Erwin, What if I want to add new column in the TABLE? How It'll handled by the VIEW ? – Nishchit Mar 10 '17 at 10:05
  • @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. – Erwin Brandstetter Mar 10 '17 at 13:46