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;

Answer 3

Test this out and see if it works. I'm pulling this out of memory so there may be some syntax issues.

BEGIN TRANSACTION;
    DO $$DECLARE r record;
         DECLARE s TEXT;
        BEGIN
            FOR r IN select table_schema,table_name
                     from information_schema.views
                     where table_schema = 'public'
            LOOP
                s := 'DROP VIEW ' ||  quote_ident(r.table_schema) || '.' || quote_ident(r.table_name) || ';';

                EXECUTE s;

                RAISE NOTICE 's = % ',s;

            END LOOP;
        END$$;
    ROLLBACK TRANSACTION;

Answer 4

DROP ALL VIEW (2022/03/12 version)

shorter version command:

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

output

DROP VIEW v_mytable, v_my_view cascade;

DROP ALL MATERIALIZED VIEW

   
SELECT 'DROP MATERIALIZED VIEW ' || string_agg(oid::regclass::text, ', ') || ' cascade;'
FROM   pg_class
WHERE  relkind = 'm';

Answer 5

When I drop particular views first I get a backup with the script below;

WITH dump_options AS (
  -- feel free to add any dump options
  SELECT '--file=/tmp/create_views.sql --host=... my_db_name' AS dump_options
)
SELECT
    format(e'pg_dump --schema-only --format=plain \\\n%s \\\n%s', 
      string_agg(
        format('--table="%s.%s"', v.schemaname, v.viewname),
        e' \\\n'
      ),
      d.dump_options
    ) create_views
  FROM pg_views v, dump_options d
  WHERE 
    v.schemaname NOT IN (
      'pg_catalog',
      'information_schema',
      'any_other_schema'
    ) AND
    v.viewname NOT IN (
      'pg_stat_statements'
    ) AND
    (v.schemaname, v.viewname) NOT IN (
      ('a_particular_schema', 'a_particular_view')
    )
    -- AND ...

  GROUP BY d.dump_options

The script creates a dump command which will get backup of all views with owners and privileges when we run it;

pg_dump --schema-only --format=plain \
--table="a_schema.a_view" \
--table="another_schema.another_view" \
... \
--file=/tmp/create_views.sql --host=... my_db_name

Finally, I drop views with the command below (psql recommended);

SELECT
    format('DROP VIEW IF EXISTS %I.%I;', v.schemaname, v.viewname) drop_views
  FROM pg_views v
  WHERE -- the very same where clause used above 
    v.schemaname NOT IN (
      'pg_catalog',
      'information_schema',
      'any_other_schema'
    ) AND
    v.viewname NOT IN (
      'pg_stat_statements'
    ) AND
    (v.schemaname, v.viewname) NOT IN (
      ('a_particular_schema', 'a_particular_view')
    )
    -- AND ...
  ;  -- \gexec