Delete column names on a match

[Hexxx]

I have a table with attributes like this

catA_totals
catA_averages
catA_median
catB_totals
catB_averages
catB_median
catC_totals
catC_averages
catC_median
catD_totals
catD_averages
catD_median
.... etc

and I decide I no longer want to have the median tables, how can I select all these tables and drop them... something like drop *._medians or something like that. I saw the alter table can use multiple drops, but only when all the column names are explicitly typed.

[Piranha]

I think you should reconsider the database design, it violates the rules for database normalization. If it would follow at least up to NF3 then it would be no problem at all to do what you want.

[Hexxx]

It's a fact table for a datawarehouse, so I am not sure if it has to go up to 3NF.

[Sxooter]

I'd do it from bash or php or something. But how to do it depends on the db engine you're running. Since it's probably mysql and I run a different engine, I can't tell you the exact steps.

Something along the lines of select tablename from pg_tables where tablename like '%median';
then iterate that list with drop table $tablename cascade for each one.