PostgreSQL will not change the type of a column that a view reads. The ALTER TABLE fails with cannot alter type of a column used by a view or rule, and the only way through is to drop every view that reads the column, change the type, and create the views again, in one transaction.

That much fits in a sentence. The work is in the details the error leaves out: it names one view when there may be several, it says nothing about views built on top of that one, and dropping a view throws away its grants and its comment. Every result below was reproduced on PostgreSQL 18.3 in a throwaway container.

Why does PostgreSQL refuse to change a column type when a view uses it?

A view in PostgreSQL is not a saved query string. It is a parsed rewrite rule (the _RETURN rule named in the error’s DETAIL line) that records the columns it reads, and the database tracks that dependency column by column. Changing a type underneath it would leave the rule describing a column that no longer exists in that form, so PostgreSQL refuses instead.

Here is the schema used in every example:

CREATE TABLE orders (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_email varchar(50) NOT NULL,
    total integer NOT NULL
);

CREATE VIEW order_summary AS SELECT id, customer_email, total FROM orders;
CREATE VIEW big_orders AS SELECT id, total FROM order_summary WHERE total > 1000;
CREATE MATERIALIZED VIEW order_totals AS SELECT sum(total) AS s FROM orders;

CREATE ROLE reporting;
GRANT SELECT ON order_summary TO reporting;

Three things about the refusal are easy to get wrong:

• It applies to harmless changes too. ALTER TABLE orders ALTER COLUMN customer_email TYPE varchar(100) needs no table rewrite and cannot lose a value, and it is still refused: rule _RETURN on view order_summary depends on column "customer_email". The varchar to text change is refused the same way.
• The error names one view. Changing total to bigint reported only order_summary, although the materialized view order_totals reads total too. You fix the first one and find the next on the following run. (A DROP COLUMN is more helpful: its error listed all three.)
• Only the columns a view reads count. A view of SELECT a FROM t did not block a type change on b. A view written as SELECT * FROM t did, because PostgreSQL expands the star into every column when the view is created.

A rename is the exception. ALTER TABLE orders RENAME COLUMN customer_email TO email succeeded, and pg_get_viewdef('order_summary') afterwards read email AS customer_email: the view follows the new name and keeps its own output name.

How do I fix cannot alter type of a column used by a view or rule?

You have three choices, and only one is safe to put in a schema migration without knowing exactly what the database holds:

A plain DROP VIEW order_summary is the safer tool because it refuses: cannot drop view order_summary because other objects depend on it, with view big_orders depends on view order_summary in the DETAIL and a HINT suggesting CASCADE. Do not take the hint. That refusal is the database telling you about a view your migration forgot.

The migration, once you know every view in the chain:

BEGIN;
SET LOCAL lock_timeout = '5s';
DROP VIEW big_orders;
DROP VIEW order_summary;
DROP MATERIALIZED VIEW order_totals;

ALTER TABLE orders ALTER COLUMN total TYPE bigint;

CREATE VIEW order_summary AS SELECT id, customer_email, total FROM orders;
CREATE VIEW big_orders AS SELECT id, total FROM order_summary WHERE total > 1000;
CREATE MATERIALIZED VIEW order_totals AS SELECT sum(total) AS s FROM orders;
GRANT SELECT ON order_summary TO reporting;
COMMIT;

Drop from the outside in and create from the inside out. Take each CREATE VIEW from the database before the migration runs, not from memory or an old migration file, because the database’s copy is the one in production. pg_dump --schema-only --table=order_summary gives the whole statement; pg_get_viewdef gives only the SELECT, and a view created WITH (security_invoker = true) or WITH CHECK OPTION keeps those options in pg_class.reloptions, so a recreate built from pg_get_viewdef alone quietly drops them.

What does dropping and recreating a view lose?

Everything attached to the view rather than written in its definition. After a drop and a recreate in the test database:

• Grants were gone. has_table_privilege('reporting', 'order_summary', 'SELECT') returned false until the GRANT ran again. A reporting role or a BI tool that reads the view starts failing with a permission error.
• The comment was gone. COMMENT ON VIEW text is stored against the view, so obj_description returned nothing after the recreate.
• The owner changed. Both views came back owned by the role that ran the migration, not the role that created them, and a view checks the tables it reads with its owner’s privileges unless it uses security_invoker.
• A materialized view’s indexes were gone. A unique index on order_totals did not survive, and REFRESH MATERIALIZED VIEW CONCURRENTLY needs one.
• A materialized view’s data is recomputed. CREATE MATERIALIZED VIEW runs its query again and stores the result, so the migration takes as long as that query does.

INSTEAD OF triggers and column comments on the view go the same way. Read the grants with \dp order_summary and the indexes with \d order_totals before the migration, and write them back after the CREATE.

How do I find every view that depends on a column?

The error names one direct dependent. To see all of them, including views on views, walk pg_depend through each view’s rewrite rule:

WITH RECURSIVE deps AS (
    SELECT DISTINCT v.oid::regclass AS view, 1 AS depth
    FROM pg_depend d
    JOIN pg_rewrite r ON r.oid = d.objid
    JOIN pg_class v ON v.oid = r.ev_class
    WHERE d.classid = 'pg_rewrite'::regclass
      AND d.refobjid = 'orders'::regclass
      AND v.oid <> 'orders'::regclass
    UNION
    SELECT DISTINCT v.oid::regclass, deps.depth + 1
    FROM deps
    JOIN pg_depend d ON d.refobjid = deps.view
    JOIN pg_rewrite r ON r.oid = d.objid
    JOIN pg_class v ON v.oid = r.ev_class
    WHERE d.classid = 'pg_rewrite'::regclass
      AND v.oid <> deps.view
)
SELECT view, max(depth) AS depth FROM deps GROUP BY view ORDER BY depth;

On the example schema it returned order_summary and order_totals at depth 1 and big_orders at depth 2. Drop in descending depth, create in ascending depth. This query lists views on the whole table; to narrow it to one column, add AND d.refobjsubid = <the column's attnum> to the first half.

Does the view disappear for other sessions during the migration?

No. PostgreSQL DDL is transactional, so nothing outside the transaction sees the views missing. In the test, a second session ran SELECT count(*) FROM order_summary while the migration transaction held the view dropped: the query waited, and when the migration committed it returned all 1,000,000 rows from the new view. It waited 3.9 seconds, because the test held the transaction open on purpose.

The wait is the real cost. The type change takes an ACCESS EXCLUSIVE lock on the table, and integer to numeric(12,2) rewrote the 1,000,000-row table in 971 ms, so every reader of the table and of its views queues behind the migration until it commits. The same applies to widening an integer key to bigint. Keep the transaction to the drops, the ALTER and the recreates, and start it with SET LOCAL lock_timeout = '5s': if a long-running query holds the table, the migration gives up and can be retried, instead of waiting in the lock queue while every new reader queues behind it.

Seeing the views before the migration runs

The awkward part of this error is when you meet it: at deploy time, from a migration your ORM generated. The ORM knew about the column and nothing about the reporting view someone created by hand two years ago, so the migration holds a single ALTER TABLE and the deploy stops on it.

Schemity is database design software that reads your live database, shows the impact of every schema change before it runs, and keeps the diagram as a file in Git. Change total to bigint in a diagram connected to this database, and impact analysis reports before anything runs that 2 objects depend on orders.total and the database refuses the change, naming order_summary and the materialized view order_totals, alongside the cost of running it anyway: a rewrite of ~1M rows and 87 MB, and a lock that holds back reads and writes to orders while it happens.

Preview changes puts the same three findings under the planned migration, which is the single statement an ORM would generate, ALTER TABLE "orders" ALTER COLUMN "total" TYPE BIGINT USING "total"::BIGINT, so the refusal is in front of you before you press Migrate.

It is the same review for a file you did not write: paste the ORM’s migration into Analyse a migration file and the refusal shows up there, against the real database, without executing a line. That is what it means to see the impact of every change: a refused ALTER is cheap to fix in review and expensive to discover in a deploy.

It follows the file the way the database does. A migration that renames total and then retypes it still finds the views that read the column, since a rename moves the dependency with it. A migration that drops order_summary first, as the fix above does, no longer reports that view as refusing the change, and one it forgot to drop still does.

Here the file drops big_orders and order_summary but forgets order_totals, and that is the one view still reported. If a change you apply with Schemity’s Migrate fails anyway, its dialog shows PostgreSQL’s DETAIL line, so the view in the way is named rather than hidden behind a generic error. Schemity lists the views that read the table directly; views built on those come from the recursive query above.

Two limits are worth knowing. Schemity does not write the drop and recreate statements for you, because a view’s grants, comments and definition are decisions about the database it does not model. And the review is only as good as the moment it happens, which is why it belongs before the merge, as covered in reviewing what an AI agent’s migration does to the schema, and next to the per-statement checks a migration linter runs on locks and rewrites.

For more on choosing column types so this migration happens less often, see numeric vs double precision vs money and adding a NOT NULL column to a large table.