Dropping all views in postgreSql

Question

It seems a pretty simple problem but I can't find an answer to it! How can you delete views in bulk from the postgreSQL console? I have got 10,000 views that I made just to test something and now I can't get rid of them!

Answer 1

you can select the views from the meta tables, like this, (the actual select may differ if you use older version, see here e.g. http://www.alberton.info/postgresql_meta_info.html)

SELECT 'DROP VIEW ' || table_name || ';'
  FROM information_schema.views
 WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
   AND table_name !~ '^pg_';

So you fix this select according your actual version, run it, save the results into a .sql file, and run the .sql file.

Answer 2

I add this answer which references a comment by @greg which in turn references an answer, which seems to be gone, from a user named justbob. This comment helped me a lot but might easily be overlooked by others.

Although it doesn't answer samachs question, because his views were already made, it might help others like me who found this question after a google search for a way to delete all views in a bulk operation.

I have a few views which are dependent on another and are generated by an ever-evolving script. So there will be some views added or removed from the script. To limit the places where I have to make changes when adding or removing views I want to drop them all at the beginning of the script before I create them again.

The solution I use now is to create all views in a separate schema which holds nothing but the views.

At the beginning of my script, I have the following statements before creating my views:

DROP SCHEMA IF EXISTS dbview_schema CASCADE;
CREATE SCHEMA dbview_schema;

After that, I recreate all my views in their own schema:

CREATE VIEW dbview_schema.my_view AS
  SELECT * FROM my_table
  ORDER BY my_table.my_column;