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

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

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.

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

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

SELECT definition FROM pg_views WHERE viewname = 'view_name';

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

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

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.

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

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:

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;

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

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.

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

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.

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

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