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

Question

I have a view, one of he column is timetaken type integer i want to change it as numeric.For this I used below syntax

ALTER VIEW view_timesheets  ALTER COLUMN timetaken type numeric;

When I run this I got the exception as

"view_timesheets" is not a table, composite type, or foreign table

Please explain how to alter column type.Thank You

Answer 1

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.

Answer 2

I have also faced a similar problem while converting the column type of view.

I used the CAST() operator to convert the type from Integer to Varchar(5).

I had a column named age which is of type Integer in my table. So the view query created using that table was also having the type as Integer. So I used the CAST() operator in my view query to change the column type.

CASE
  WHEN vhcl_insp_dtls.age = 0 THEN CAST('NEW' AS VARCHAR(5))
  ELSE CAST(vhcl_insp_dtls.age AS VARCHAR(5))
END AS age,

So In this way, you can modify your view query without dropping it.