PostgreSQL: Drop column and recreate dependent views

Question

I have below test table and dependent views created.

create table test_col_drp (col1 varchar(100), col2 varchar(100), col3 varchar(100));
create view test_col_drp_vw1 as select col1 col1_vw1, col2 col2_vw1, col3 col3_vw1 from test_col_drp;
create view test_col_drp_vw2 as select col1_vw1 col1_vw2, col2_vw1 col2_vw2, col3_vw1 col3_vw2 from test_col_drp_vw1;

I'm trying to drop a column from the table but getting below error:

alter table test_col_drp drop column col3;

ERROR:  cannot drop table test_col_drp column col3 because other objects depend on it
DETAIL:  view test_col_drp_vw1 depends on table test_col_drp column col3 view test_col_drp_vw2 depends on view test_col_drp_vw1
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
********** Error **********

What would be the best way to drop the column and recreate all the dependent views?

Working on: PostgreSQL 9.6.6 on x86_64-pc-mingw64, compiled by gcc.exe (Rev5, Built by MSYS2 project) 4.9.2, 64-bit

Windows 10

For your example you can not just "recreate" the view(s), you should to drop the existing view and create another one probably with same name and with different columns set (with different body in general). Not sure that it could be automated at least in simple way especially if column that you want to drop takes part in the logic. — Abelisto, May 21 '18 at 20:50
http://www.postgresqltutorial.com/postgresql-drop-column/ There's a guide on how to drop a column with dependent views. And then as Thiago pointed out you'll need to re-create them. — Martin Svoboda, Jul 18 '19 at 12:49

Thiago Procaci · Answer 1 · 2018-05-21T23:21:18.403

First, you have to drop the views. Then, execute the alter table and finally create the views again. See:

  -- This is what you have in your database  

 create table test_col_drp (col1 varchar(100), col2 varchar(100), col3 varchar(100));
 create view test_col_drp_vw1 as select col1 col1_vw1, col2 col2_vw1, col3 col3_vw1 from test_col_drp;
 create view test_col_drp_vw2 as select col1_vw1 col1_vw2, col2_vw1 col2_vw2, col3_vw1 col3_vw2 from test_col_drp_vw1;

-- drop views and alter table
drop view test_col_drp_vw2;
drop view test_col_drp_vw1;

alter table test_col_drp drop column col3;

-- creating the views again without col3
create view test_col_drp_vw1 as select col1 col1_vw1, col2 col2_vw1 from test_col_drp;
create view test_col_drp_vw2 as select col1_vw1 col1_vw2, col2_vw1 col2_vw2 from test_col_drp_vw1;

An alternative is to use the drop cascade. In this case, you do not need to drop each view individually but you still need to recreate them:

alter table test_col_drp drop column col3 cascade;

--  creating the views again without col3
create view test_col_drp_vw1 as select col1 col1_vw1, col2 col2_vw1 from test_col_drp;
create view test_col_drp_vw2 as select col1_vw1 col1_vw2, col2_vw1 col2_vw2 from test_col_drp_vw1;

Another option is to create a SQL command that generates your alter table using cascade and the create view:

with query_result as (
    SELECT dependent_ns.nspname as dependent_schema
    , dependent_view.relname as dependent_view 
    , source_ns.nspname as source_schema
    , source_table.relname as source_table
    , pg_attribute.attname as column_name
    , row_number() over() as id
    FROM pg_depend 
    JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid 
    JOIN pg_class as dependent_view ON pg_rewrite.ev_class = dependent_view.oid 
    JOIN pg_class as source_table ON pg_depend.refobjid = source_table.oid 
    JOIN pg_attribute ON pg_depend.refobjid = pg_attribute.attrelid 
        AND pg_depend.refobjsubid = pg_attribute.attnum 
    JOIN pg_namespace dependent_ns ON dependent_ns.oid = dependent_view.relnamespace
    JOIN pg_namespace source_ns ON source_ns.oid = source_table.relnamespace

    WHERE 1=1
    -- AND source_ns.nspname = 'public'
    AND source_table.relname like 'test_col_drp%'
    AND pg_attribute.attnum > 0 
    AND pg_attribute.attname = 'col3'
) 

select concat('alter table ', source_table, ' drop column ' , column_name, ' cascade;' ) as sql_command from query_result where id = 1
union all
select concat('create or replace view ', dependent_view, ' as select * from ', source_table, ';') as  sql_command from query_result

Then, the output will be:

alter table test_col_drp drop column col3 cascade;
create or replace view test_col_drp_vw1 as select * from test_col_drp;
create or replace view test_col_drp_vw2 as select * from test_col_drp_vw1;

The answers are correct. But what a pain in the ass that you cannot change a thing without dropping and (re)creating! This really stifles development in cases where you try to combine queries (code) and tables (or views) into (materialized) views rather than in a mess of tables without such reference. From a developers point of view, this is really, as stated in https://www.postgresql.org/message-id/CA%2BTgmoaxCWscityNPFx2qFP1HSjdL8y%2B%3D2XS0HiDa417pK-Qsw%40mail.gmail.com, a 'sharp stick in the eye'. — Jan, Feb 11 '23 at 12:14

PostgreSQL: Drop column and recreate dependent views

1 Answers1