ALTER COLUMN TYPE blocked because a view uses the column
ERROR: cannot alter type of a column used by a view or ruleShort answer
PostgreSQL will not change a column's type while a view is built on it. The DETAIL line names the view. Save its definition, drop it, run ALTER COLUMN TYPE, then recreate it. If the ALTER then fails on a cast or a default, add a USING clause or drop and reset the default.
Fix it fast
1Find out which cause you have
Read the DETAIL line for the view name. List every view that mentions the column, and save its definition before you change anything.
SELECT pg_get_viewdef('my_view'::regclass, true); \d+ my_view2Match what you see, then jump to its fix
- 1
DETAIL: rule _RETURN on view my_view depends on columnA view depends on the columnFix - 2
the ALTER fails on a missing cast from the old type to the new oneThe new type has no automatic cast from the old oneFix - 3
the ALTER fails on the default value even with USINGThe column default cannot be convertedFix
- 1
What does the error look like?
ERROR: cannot alter type of a column used by a view or ruleDETAIL: rule _RETURN on view my_view depends on column "status"
Cause 1 of 3
A view depends on the column
1Check
PostgreSQL tracks what depends on what. A dependent object blocks changes that would break it, and a DROP without CASCADE shows the dependents in its DETAIL output.
DROP VIEW my_view;2Fix
Drop the view, change the type, recreate the view from the saved definition. If other views are built on this one, the DROP lists them in its DETAIL line; handle those first.
DROP VIEW my_view;
ALTER TABLE orders ALTER COLUMN status TYPE varchar(20);
CREATE VIEW my_view AS
SELECT id, status FROM orders;3Verify
The ALTER succeeds and the view returns rows again.
\d orders
SELECT * FROM my_view LIMIT 1;Confirmed by postgresql.org
Cause 2 of 3
The new type has no automatic cast from the old one
1Check
Without USING, the conversion is an assignment cast. When none exists, USING is required.
SELECT status::integer FROM orders LIMIT 1;2Fix
Add a USING expression that says how to convert each value.
ALTER TABLE orders
ALTER COLUMN status TYPE integer
USING status::integer;3Verify
The command succeeds; run ANALYZE because the column's statistics are removed.
ANALYZE orders;Confirmed by postgresql.org
Cause 3 of 3
The column default cannot be converted
1Check
USING is not applied to the default. A default that does not cast to the new type fails the change.
\d orders2Fix
Drop the default, change the type, set a default of the new type.
ALTER TABLE orders ALTER COLUMN status DROP DEFAULT;
ALTER TABLE orders ALTER COLUMN status TYPE integer USING status::integer;
ALTER TABLE orders ALTER COLUMN status SET DEFAULT 0;3Verify
The table shows the new type and default.
\d ordersConfirmed by postgresql.org
How do I stop it coming back?
- Keep view definitions in migration files, so recreating them is one step.
- List dependent views before a type change, not after the error.
- On large tables, plan for a rewrite: it can need up to double the disk space and take an ACCESS EXCLUSIVE lock.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Related fixes
Databases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
DBeaver cannot download the PostgreSQL driver from Maven
The PostgreSQL driver files are missing and the download does not complete
DBeaver downloads the PostgreSQL JDBC driver from a Maven repository the first time you connect.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
Django save fails on PostgreSQL: null value in a column violates a not-null constraint
django.db.utils.IntegrityError: null value in column "price" of relation "shop_product" violates not-null constraint
A model field is NOT NULL in the database unless it has null=True.
3 causes, each with a fixDatabases
Django migration on PostgreSQL stops with 'pending trigger events'
django.db.utils.OperationalError: cannot ALTER TABLE "mytable" because it has pending trigger events
PostgreSQL runs a Django migration in one transaction.
2 causes, each with a fixDatabases
Django cannot connect to PostgreSQL: connection refused on port 5432, locally or in Docker
Is the server running on that host and accepting TCP/IP connections?
Connection refused means nothing accepted the connection at the host and port Django used.
4 causes, each with a fix