postgresqlview

Dropping all views in postgreSql

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!

Solution

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