Today I just wanted to change character varying in one of my table on postgreSQL database with this command:
alter table “TABLE_Name” alter column “COLUMN_NAME” type character varying (72);
But I found that I can not change the value and I got this Error:
ERROR: cannot alter type of a column used by a view or rule
DETAIL: rule_RETURN on view “VIEW_NAME” depends on column “COLUMN_NAME”
SQL state: 0A000
After searching on the internet I got that the only way to solve the problem is:
- Droping the dependent view
- Changing the character varying
- Recreating the view
But before that I need to copy all the configuration within my View then delete it, and because I use pgAdmin as my management interface I go to the view section, right click on the related view and click on properties:
1.Check what was the name and who was the owner
2.On the definition tab copy all the definition from the box like below screen shot
3.Note the privileges to rebuild it
Then we can right click on the related View and click on Delete/Drop and Yes to confirm.
Now we can easily Issue the following command to increase the length of a column to “72”:
alter table “TABLE_Name” alter column “COLUMN_NAME” type character varying (72);
Now we should create the view, Just click on the create view and try to fill out the “Definition” and “Security” tab as the same as it was in previous steps.
