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

Symptoms

An ALTER TABLE … ALTER COLUMN … TYPE was blocked because the column is used by a view or rule. PostgreSQL raises SQLSTATE 0A000 (feature_not_supported).

• Changing a column type that a view/rule depends on.
• PostgreSQL won’t silently break dependent objects.
• Drop and recreate the dependent view to proceed.

What the server log shows

ERROR:  cannot alter type of a column used by a view or rule
DETAIL:  rule _RETURN on view order_summary depends on column "amount"

Why PostgreSQL raises this — what the manual says

As the ALTER TABLE reference (SET DATA TYPE) explains:

Changing a column’s data type requires reparsing dependent object definitions; because the stored definition of a view cannot be automatically converted to the new type, PostgreSQL blocks the change until the dependent view is dropped or recreated.

Views and rules store the types of the columns they reference. Changing the base column’s type could invalidate them, and PostgreSQL does not automatically rewrite dependents, so it reports 0A000.

Common causes

• A view or rule depends on the column being altered.
• Materialized views or rules referencing the column.
• Nested view dependencies on the column.

How to fix it

1. Drop the dependent view(s)/rule(s), alter the column, then recreate them.
2. Use a migration tool that scripts drop/recreate of dependents.
3. Identify dependents via pg_depend before altering.

Related & next steps

• SQLSTATE 0A000 — Feature Not Supported (full reference)
• Message: cannot drop … because other objects depend on it
• Glossary: Object dependency

Reference: PostgreSQL 18 — ALTER TABLE.