What causes "Cannot alter type of column used by view or rule"?

One or more views are defined that reference the column you are trying to alter A rule (explicitly created via CREATE RULE) depends on the column, preventing type changes PostgreSQL's view implementation uses the rule system, and rules create hard dependencies on columns

Is the "Cannot alter type of column used by view or rule" error serious?

It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

How to fix Cannot alter type of column used by view or rule in PostgreSQL

PostgreSQLINTERMEDIATEMEDIUMUpdated Dec 29, 2025· 268 views

PostgreSQL prevents you from directly changing a column's data type if that column is used in a view or rule dependency. You must drop the dependent view(s) first, alter the column, and then recreate them in a transaction.

Quick fix

Fixes Cannot alter type of column used by view or rule

SELECT v.schemaname, v.viewname, a.attname
FROM pg_views v
JOIN pg_class c ON c.relname = v.viewname
JOIN pg_namespace n ON n.oid = c.relnamespace AND n.nspname = v.schemaname
JOIN pg_depend d ON d.refobjid = c.oid
JOIN pg_class t ON t.oid = d.objid
JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = d.refobjsubid
WHERE t.relname = 'your_table_name'
ORDER BY v.viewname;

Identify all views and rules that depend on the column
Get the view definitions before dropping them
Begin a transaction and drop the dependent views
Alter the column type

postgresql — error

$SELECT v.schemaname, v.viewname, a.attname
FROM pg_views v
JOIN pg_class c ON c.relname = v.viewname
JOIN pg_namespace n ON n.oid = c.relnamespace AND n.nspname = v.schemaname
JOIN pg_depend d ON d.refobjid = c.oid
JOIN pg_class t ON t.oid = d.objid
JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = d.refobjsubid
WHERE t.relname = 'your_table_name'
ORDER BY v.viewname;
Cannot alter type of column used by view or rule

What this error means

PostgreSQL implements views using the rule system. When you create a view, PostgreSQL creates an underlying rule (usually named "_RETURN") that depends on the columns referenced in that view. Because of this dependency, you cannot directly modify a column's data type if it's referenced by any view or rule—PostgreSQL will refuse the operation to maintain consistency and prevent data corruption. The error occurs because the database cannot automatically track how changing a column type might affect the view's behavior downstream.

Typical symptoms

Attempting to change a column type with ALTER TABLE fails with "ERROR: 0A000: cannot alter type of a column used by a view or rule"
The error message includes a DETAIL line naming the specific view or rule that depends on the column (e.g., "rule _RETURN on view myview depends on column mycolumn")
The ALTER TABLE command is rejected even if the column itself is not modified in a problematic way
You have one or more views that SELECT the column you are trying to alter
Materialized views or regular views created with CREATE VIEW referencing this column block the alteration

Common causes

One or more views are defined that reference the column you are trying to alter
A rule (explicitly created via CREATE RULE) depends on the column, preventing type changes
PostgreSQL's view implementation uses the rule system, and rules create hard dependencies on columns
The view was created after the column was created, so the dependency chain was established
You attempted to alter the column type directly without first removing the view dependency

How to fix "Cannot alter type of column used by view or rule"

1Identify all views and rules that depend on the column

Query the PostgreSQL system catalog to find which views depend on the column you want to alter:

sql

SELECT v.schemaname, v.viewname, a.attname
FROM pg_views v
JOIN pg_class c ON c.relname = v.viewname
JOIN pg_namespace n ON n.oid = c.relnamespace AND n.nspname = v.schemaname
JOIN pg_depend d ON d.refobjid = c.oid
JOIN pg_class t ON t.oid = d.objid
JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = d.refobjsubid
WHERE t.relname = 'your_table_name'
ORDER BY v.viewname;

Replace 'your_table_name' with the actual table containing the column. This lists all views referencing columns in that table. Note down the view names you need to drop.

2Get the view definitions before dropping them

Before dropping views, save their definitions so you can recreate them:

sql

-- Save the view definition to a file or notes
SELECT pg_get_viewdef('view_name'::regclass, true) AS view_definition;

Alternatively, use psql's \d+ view_name to see the full view definition, or query:

sql

SELECT definition FROM pg_views WHERE viewname = 'view_name';

Save each view's CREATE VIEW statement for later restoration.

3Begin a transaction and drop the dependent views

Wrap all your operations in a transaction to ensure atomicity. Start by dropping the views that reference the column:

sql

BEGIN;

DROP VIEW IF EXISTS view1 CASCADE;
DROP VIEW IF EXISTS view2 CASCADE;

Use CASCADE if there are views or other objects depending on these views. Without CASCADE, PostgreSQL will refuse to drop a view that other views depend on.

4Alter the column type

Now that the dependent views are dropped, you can safely alter the column type:

sql

ALTER TABLE your_table_name
ALTER COLUMN your_column TYPE new_data_type USING expression;

For example, to change a VARCHAR(50) to TEXT or an INTEGER to BIGINT:

sql

ALTER TABLE users
ALTER COLUMN email TYPE TEXT;

-- If type conversion is needed, use USING:
ALTER TABLE orders
ALTER COLUMN amount TYPE numeric(10,2) USING amount::numeric;

5Recreate the views

After the column is successfully altered, recreate the views using their saved definitions:

sql

CREATE VIEW view1 AS
SELECT col1, col2, your_column FROM your_table_name;

CREATE VIEW view2 AS
SELECT * FROM your_table_name WHERE your_column IS NOT NULL;

Use the exact definitions you saved in Step 2. If the view definitions are complex, paste them exactly as they were before.

6Commit the transaction

Once all views are recreated and validated, commit the transaction:

sql

COMMIT;

If any step fails, PostgreSQL will automatically roll back all changes (if you haven't issued COMMIT yet). You can also manually roll back with ROLLBACK; if needed before committing.

7Verify the alteration and views

After committing, verify that the column type was changed and the views work correctly:

sql

-- Check the column type
\d your_table_name

-- Query the views to ensure they still work
SELECT * FROM view1 LIMIT 1;
SELECT * FROM view2 LIMIT 1;

If you get errors when querying the recreated views, check that the view definitions are correct and that the column type change is compatible with the view logic.

Advanced notes

For complex schemas with many interdependent views, consider creating helper functions to automate the drop and recreate process. PostgreSQL also offers CASCADE constraints that can drop views automatically, but use them carefully—ensure you understand all downstream dependencies before using CASCADE. In some cases where you cannot modify views (third-party apps, read-only views), you may need to create a new table with the corrected column type, migrate data, and then swap table names (using schemas or renaming). For materialized views, the process is the same but requires additional consideration: dropping a materialized view does not automatically recreate its index. Always test this procedure on a development or staging database first to avoid unexpected downtime on production systems. If you have triggers or functions that also depend on the column, you may need to recreate those as well.

Frequently asked questions

What does "Cannot alter type of column used by view or rule" mean?: PostgreSQL implements views using the rule system. When you create a view, PostgreSQL creates an underlying rule (usually named "_RETURN") that depends on the columns referenced in that view. Because of this dependency, you cannot directly modify a column's data type if it's referenced by any view or rule—PostgreSQL will refuse the operation to maintain consistency and prevent data corruption.
How do I fix "Cannot alter type of column used by view or rule"?: PostgreSQL prevents you from directly changing a column's data type if that column is used in a view or rule dependency. You must drop the dependent view(s) first, alter the column, and then recreate them in a transaction.
What causes "Cannot alter type of column used by view or rule"?: One or more views are defined that reference the column you are trying to alter A rule (explicitly created via CREATE RULE) depends on the column, preventing type changes PostgreSQL's view implementation uses the rule system, and rules create hard dependencies on columns
Is the "Cannot alter type of column used by view or rule" error serious?: It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.