CODEWITHSUNDEEP
sqlpostgresql
John
John

Reputation: 270

cannot alter type of a column used by a view or rule in PostgreSQL

I wan't to change the size of the column from character(9) to character(12) by

ALTER TABLE product_based_award ALTER COLUMN name TYPE character(12);

But internally this column is used by a MView so it is not letting to alter the table and giving the following error.

ERROR: cannot alter type of a column used by a view or rule

I have found two solutions for this, one is by dropping the mview and recreating it and the other is by updating pg_attribute. But I cannot use these two options because our db is very complicated so updating pg_attribute can cause problems and cannot drop mview as well.

So is there any other best way to resolve this issue.

Upvotes: 2

Views: 9559

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51529

updating pg_attribute is always critically bad idea

to reduce time users wait for a new definition (assuming it loads data for significant time), you can use:

let s117 be old vie as:

t=# create materialized view s117 as select now()::timestamp(1);
SELECT 1
Time: 54.329 ms

change it:

t=# create materialized view s117_new as select now()::timestamp(2);
SELECT 1
Time: 64.024 ms
t=# begin;
BEGIN
Time: 0.099 ms
t=# drop materialized view s117;
DROP MATERIALIZED VIEW
Time: 4.134 ms
t=# alter materialized view s117_new rename to s117;
ALTER MATERIALIZED VIEW
Time: 1.054 ms
t=# end;
COMMIT
Time: 49.256 ms

check:

t=# \d+ s117
                              Materialized view "public.s117"
 Column |              Type              | Modifiers | Storage | Stats target | Description
--------+--------------------------------+-----------+---------+--------------+-------------
 now    | timestamp(2) without time zone |           | plain   |              |
View definition:
 SELECT now()::timestamp(2) without time zone AS now;

Upvotes: 1

Related Questions