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

Question

I am trying to update a column of type integer to numeric(2) in postgres

ALTER TABLE employee_status
    ALTER COLUMN status TYPE numeric(2);

but getting the error ERROR: cannot alter type of a column used by a view or rule DETAIL: rule _RETURN on view v_employee_details depends on column "status"

Without dropping or deleting data, how can i achieve this? is there any way to disable the rule or the view

Answer 1

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

Answer 2

I found one more solution more simple, but may be not a best practice.

UPDATE pg_attribute SET atttypmod = 35+4 WHERE attrelid = 'TABLE1'::regclass AND attname = 'COL1';

You need a SUPERUSER access or similar to change Postgres system table - pg_attribute. Here's explanation link!