Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

ALTER COLUMN TYPE blocked because a view uses the column

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

Short 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.

  • Applies to PostgreSQL 17, PostgreSQL 18
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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_view
  2. 2Match what you see, then jump to its fix

    1. 1DETAIL: rule _RETURN on view my_view depends on columnA view depends on the columnFix
    2. 2the 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. 3the ALTER fails on the default value even with USINGThe column default cannot be convertedFix

What does the error look like?

  • ERROR: cannot alter type of a column used by a view or rule
  • DETAIL: 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

Did this fix it?

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

Did this fix it?

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 orders

2Fix

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 orders

Confirmed by postgresql.org

Did this fix it?

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

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.