4

I'm working in Postgres 9.4. I want to alter the type of a column in a table:

ALTER TABLE mytable ALTER COLUMN quantity TYPE integer;

But I get this:

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

This is because there are materialized views derived from that table.

Is there a way to temporarily disconnect the materialized views from this table so that I can update this table, then refresh the materialized views? (or if necessary, refresh them separately)

Or do I absolutely have to delete all the materialized views before I can change the column type?

UPDATE: It took three days to build the materialized views, and will take as long to rebuild them - which is why I'm asking this question. It's not trivial just to delete and recreate them.

Improve this question
5
  • I don't understand the 3 days thing. If you want fresh data, you have to refresh regardless, so you have to wait 3 days no matter what, right? As far as I know, there is no way to alter the view, but I'm not sure if I'm wrong on this.
    – dizzystar
    Jan 20, 2016 at 22:40
  • but refreshing is faster than recreating, surely? and i can carry on using my site in the meantime.
    – Richard
    Jan 21, 2016 at 13:00
  • Have you measured how long it takes to refresh?
    – dizzystar
    Jan 21, 2016 at 22:31
  • It takes about 36 hours to refresh.
    – Richard
    Jan 22, 2016 at 15:06
  • Perhaps you could hide what is happening by starting a transaction, dropping the materialised views, changing the column type, rebuilding the materialised views and then closing the transactions. That way your existing materialised views will still be available for use whilst your rebuild chugs away for three days.
    – Max Murphy
    May 27, 2016 at 21:24

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.