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!

Petition

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.

Lawlor

after executing...hw do i write this to an sql file? copy paste? – Petition

psql - save results of command to a file #5331820 – Lawlor

If some of views reference other views, you may need to do 'DROP VIEW ' || table_name || ' CASCADE;' – Hurtless

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;

Jamila

This is a really nice & simple solution. Wish I'd thought of it a year or two ago. :) I'm going to set things up this way next time. Much cleaner separate the views in general too. – Hermaphroditism

thanks for sharing this idea, also think it is more effective and clear solution! – Forewarn

I have exactly the same usecase and I really like your solution. – Keven

I feel "enlightened" by this answer, it's an elegant solution to the problem of consistently managing view schema. In the context of application schema migrations, it may even be appropriate to have separate view schema for each deployed version of the application using this technique as the basis for managing the different logic required by different versions of the application. – Hertfordshire

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;

Weltanschauung

The schema name will be what ever one the views are in. – Weltanschauung

FYI this DO statement syntax is only in 9.x+. – Weltanschauung

I think justbob has the right answer. In order not to have to do that everytime, just creating all your views in a separate SCHEMA and drop this schema after use would do the job in an easier way. – Undaunted

Thanks! im a bit new to this n dont know how to execute functions. should i write this function in the console? my schema for the views is pg_temp_3 – Petition

The example I gave uses an anonymous code block, more for ad-hoc use. You can run it as is without putting it in function. Just log into your database via psql or pgadmin and paste the statement in the console(psql) or gui(pgadmin) and run it. If you want to use a function read this postgresql.org/docs/9.1/static/sql-syntax-calling-funcs.html. – Weltanschauung

I wrote the statements and after writting END$$; and hitting enter, i get "DO" as output. and the views are still there!! – Petition

Did you take the rollback statement out? – Weltanschauung

no i dont. i outputs Do after END$$ and "ROLLBACK" after I write ROLLBACK TRANSACTION; – Petition

I added a raise notice so you can see the statements it's executing. Run it now and see if the output has the correct drop statements. You need to change rollback to commit once you want the changes to actually happen. I added rollback in case it was doing something bad. – Weltanschauung

I am not getting any output...again only the "DO" and "ROLLBACK" output...in the code u provided, do i need to mention my schema that the views are in? (as u said in ur first comment) – Petition

Make sure you are logged into the database where views are located and change public to the schema name of the schema the views are under. This assumes all views are under the same schema. – Weltanschauung

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';

Elyn

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 

Gamecock

Answer 6

Here's a single statement using a loop. It will also cascade and only drop view if it exists. You can edit where clause to get the right views.

DO $$
DECLARE
    schema_record RECORD;
    drop_view_query TEXT;
    execute_query TEXT;
BEGIN
            FOR schema_record IN
                SELECT table_schema, table_name
                FROM information_schema.views
                WHERE table_schema = '<your table schema>'
            LOOP

                -- Create drop view query
                drop_view_query:= 'DROP VIEW IF EXISTS %I.%I CASCADE';
                execute_query:= format(drop_view_query, schema_record.table_schema, schema_record.table_name);

                -- Execute query
                RAISE NOTICE 'Attempting to execute: %', execute_query;
                EXECUTE execute_query;

            END LOOP;
END $$;

Intine