Change Postgresql Columns Used in Views

ERROR: cannot alter type of a column used by a view or rule DETAIL: rule _RETURN on view depends on column status

The only possible way is to drop and re-create the view.

But that is no problem, you can easily get the view definition with the pg_get_viewdef function.

If you have a lot of dependent views, take a look at this answer to get all dependent views in the correct order.

Don't worry about the _RETURN rule: that is just an implementation detail of how views are implemented in PostgreSQL: as ON SELECT DO INSTEAD rule named _RETURN.

---

do $$            
  declare v_employee_details_def text;
  declare exec_text text;
begin          
  v_employee_details_def := pg_get_viewdef('v_employee_details');
  drop view v_employee_details;
  
  -- do your other stuff
  
  exec_text := format('create view v_employee_details as %s', 
      v_employee_details_def);
  execute exec_text;
end $$;

Change PostgreSQL columns used in views

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.

How To change or alter column type in Views using PostgreSQL?

It is not possible. You will have to recreate the view by providing its complete definition. Also note that you cannot even CREATE OR REPLACE VIEW when you change the types of the columns. If you have views that depend on the view that changes you will have to DROP / CREATE them also.

In my company we use the strategy where everything that is recreatable in a database (like views, functions, etc.) is stored in a bunch of large SQL files which we execute everytime anything changes in the underlying table structures, so we don't have to care for dependant views.

The view part in these files is basically like:

DROP VIEW IF EXISTS vw_a CASCADE;
CREATE OR REPLACE VIEW vw_a AS
...;

DROP VIEW IF EXISTS vw_b_depending_on_a CASCADE;
CREATE OR REPLACE VIEW vw_b_depending_on_a AS
...;

Of course the second CASCADE as well as the OR REPLACE seems useless, but they maek it possible to copy&paste changed definitions easily into a running dev database without much thinking.

Changing the type of a column used in other views

I think this does what you want, though I moved the view list to the end of args to be compatible with VARIADIC semantics.

CREATE OR REPLACE FUNCTION recreate_views(run_me text, VARIADIC views text[])
  RETURNS void
AS  $$
DECLARE
  view_defs text[];
  i integer;
  def text;
BEGIN
  for i in array_lower(views,1) .. array_upper(views,1) loop
    select definition into def from pg_views where viewname = views[i];
    view_defs[i] := def;
    EXECUTE 'DROP VIEW ' || views[i];
  end loop;

  EXECUTE run_me;

  for i in reverse array_upper(views,1) .. array_lower(views,1) loop
    def = 'CREATE OR REPLACE VIEW ' || quote_ident( views[i] ) || ' AS ' || view_defs[i];
    EXECUTE def;
  end loop;

END
$$
LANGUAGE plpgsql;

Problem with Postgres ALTER TABLE

I have run into this problem and couldn't find any way around it. Unfortunately, as best I can tell, one must drop the views, alter the column type on the underlying table, and then recreate the views. This can happen entirely in a single transaction.

Constraint deferral doesn't apply to this problem. In other words, even SET CONSTRAINTS ALL DEFERRED has no impact on this limitation. To be specific, constraint deferral does not apply to the consistency check that prints ERROR: cannot alter type of a column used by a view or rule when one tries to alter the type of a column underlying a view.

Change column type in a materialized view

It's not what you were hoping to get, but currently the only way to change the query on which a materialized view is based is to drop and recreate it. Still the case in Postgres 10.

ALTER MATERIALIZED VIEW can only change auxiliary properties. You can also change column names, but not data types.

If concurrent access is required and the MV takes a long time to recreate, you might create a new MV under a different name, populate it and use it instead of the old one to keep downtime to a minimum - if that's an option.

Related:

• Replace a materialized view in Postgres

---

Related Topics