# PostgreSQL: Cannot Alter Type of a Column Used by a View - How to Change It, and How to Find Every View in the Way

> PostgreSQL refuses `ALTER COLUMN ... TYPE` on any column a view or materialized view reads, even a widening like `varchar(50)` to `varchar(100)`. The fix is to drop the views, change the type and recreate them in one transaction, which means finding views built on views first and restoring their grants afterwards. `CASCADE` makes the error go away by deleting views you may not know you have.

Source: https://schemity.com/blog/postgres-alter-column-type-used-by-view/

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:

```sql
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](https://schemity.com/blog/postgres-varchar-vs-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:

| Approach | What happens | When to use it |
| --- | --- | --- |
| `CREATE OR REPLACE VIEW` with a cast | Refused: `cannot change data type of view column "total" from integer to bigint` | Never, for a type change |
| `DROP VIEW ... CASCADE`, alter, recreate | Works, and silently drops every view built on this one; the only trace is a `NOTICE: drop cascades to view big_orders` | Only when you have listed every view it will take with it |
| Drop each view explicitly, alter, recreate, re-grant, in one transaction | Works, and fails loudly if you missed a view, because a plain `DROP VIEW` refuses while another view depends on it | The default |

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:

```sql
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:

```sql
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](https://schemity.com/blog/postgres-serial-vs-identity/). 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](https://schemity.com/doc/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.

![Schemity's Impact drawer after changing orders.total from INTEGER to BIGINT: three findings, a table rewrite of about 1M rows and 87 MB, reads and writes held back on orders, and 2 objects depending on orders.total that make the database refuse the change, the view order_summary and the materialized view order_totals, with all three views shown on the canvas next to orders](https://schemity.com/images/blog/postgres-alter-column-type-used-by-view/impact-total-bigint-refused.webp)

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

![Schemity's Preview changes drawer for the same edit: the planned migration ALTER TABLE orders ALTER COLUMN total TYPE BIGINT USING total::BIGINT, one planned change, column orders.total altered, no lint findings, and the same three impact findings including the refusal from order_summary and order_totals](https://schemity.com/images/blog/postgres-alter-column-type-used-by-view/preview-total-bigint.webp)

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.

![Schemity's SQL migration drawer after analysing a pasted file of three statements, DROP VIEW big_orders, DROP VIEW order_summary and ALTER TABLE orders ALTER COLUMN total TYPE bigint: all 3 statements analysed, the table rewrite and the held-back reads and writes still reported, and 1 object, the materialized view order_totals the file forgot to drop, still making the database refuse the change](https://schemity.com/images/blog/postgres-alter-column-type-used-by-view/analyse-migration-file-view-dropped.webp)

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](https://schemity.com/blog/should-ai-agents-write-database-migrations/), and next to the per-statement checks a [migration linter](https://schemity.com/blog/schema-linting-vs-migration-linting/) runs on locks and rewrites.

For more on choosing column types so this migration happens less often, see [numeric vs double precision vs money](https://schemity.com/blog/postgres-numeric-vs-double-precision-vs-money/) and [adding a NOT NULL column to a large table](https://schemity.com/blog/postgres-add-not-null-column-large-table/).

## Frequently asked questions

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

Save each view's definition with `pg_get_viewdef`, then in one transaction drop the views (the outermost first), run the `ALTER TABLE ... ALTER COLUMN ... TYPE`, recreate the views and grant their privileges again. PostgreSQL DDL is transactional, so other sessions never see the views missing; queries against them wait for the commit and then run.

### Can I use CREATE OR REPLACE VIEW instead of dropping the view?

Not for a type change. `CREATE OR REPLACE VIEW` cannot change the type of an existing view column; PostgreSQL 18.3 answers `cannot change data type of view column` and leaves the view as it was. It can only add new columns at the end, so the view has to be dropped and created again.

### Does renaming a column break a view in PostgreSQL?

No. A view stores the columns it reads by position, not by name, so `ALTER TABLE ... RENAME COLUMN` succeeds and the view's definition follows the new name. The view keeps its own output column name, which is why `pg_get_viewdef` then shows `email AS customer_email`. Only a type change or a drop is refused.