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.

  • 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 '16 at 22:40
  • but refreshing is faster than recreating, surely? and i can carry on using my site in the meantime. – Richard Jan 21 '16 at 13:00
  • Have you measured how long it takes to refresh? – dizzystar Jan 21 '16 at 22:31
  • It takes about 36 hours to refresh. – Richard Jan 22 '16 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 '16 at 21:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.