Drop all tables in a Redshift schema - without dropping permissions

I solved it through a procedure that deletes all records. Using this technique to truncate fails but deleting it works fine for my intents and purposes.

create or replace procedure sp_truncate_dwh() as $$ DECLARE tables RECORD; BEGIN FOR tables in SELECT tablename FROM pg_tables WHERE schemaname = 'dwh' order by tablename LOOP EXECUTE 'delete from dwh.' || quote_ident(tables.tablename) ; END LOOP; RETURN; END; $$ LANGUAGE plpgsql; --call sp_truncate_dwh() 

In addition to demircioglu's answer, I had to add Commit after every drop statement to drop all tables in my schema. SELECT 'DROP TABLE IF EXISTS ' || tablename || ' CASCADE; COMMIT;' FROM pg_tables WHERE schemaname = '<your_schema>' P.S.: I do not have required reputation to add this note as a comment and had to add as an answer.

Using Python and pyscopg2 locally on my PC I came up with this script to delete all tables in schema:

import psycopg2 schema = "schema_to_be_deleted" try: conn = psycopg2.connect("dbname='{}' port='{}' host='{}' user='{}' password='{}'".format("DB_NAME", "DB_PORT", "DB_HOST", "DB_USER", "DB_PWD")) cursor = conn.cursor() cursor.execute("SELECT tablename FROM pg_tables WHERE schemaname = '%s'" % schema) rows = cursor.fetchall() for row in rows: cursor.execute("DROP TABLE {}.{}".format(schema, row[0])) cursor.close() conn.commit() except psycopg2.DatabaseError as error: logger.error(error) finally: if conn is not None: conn.close() 

Replace correctly values for DB_NAME, DB_PORT, DB_HOST, DB_USER and DB_PWD to connect to the Redshift DB

The following recipe differs from other answers in the regard that it generates one SQL statement for all tables we're going to delete.

SELECT 'DROP TABLE ' || LISTAGG("table", ', ') || ';' FROM svv_table_info WHERE "table" LIKE 'staging_%'; 

Example result:

DROP TABLE staging_077815128468462e9de8ca6fec22f284, staging_abc, staging_123; 

As in other answers, you will need to copy the generated SQL and execute it separately.

References

• || operator concatenates strings
• LISTAGG function concatenates every table name into a string with a separator
• The table svv_table_info is used because LISTAGG doesn't want to work with pg_tables for me. Complaint:

One or more of the used functions must be applied on at least one user created tables. Examples of user table only functions are LISTAGG, MEDIAN, PERCENTILE_CONT, etc

UPD. I just now noticed that SVV_TABLE_INFO page says:

The SVV_TABLE_INFO view doesn't return any information for empty tables.

...which means empty tables will not be in the list returned by this query. I usually delete transient tables to save disk space, so this does not bother me much; but in general this factor should be considered.