Drop all tables in a Redshift schema - without dropping permissions

Question

I would be interested to drop all tables in a Redshift schema. Even though this solution works

DROP SCHEMA public CASCADE;
CREATE SCHEMA public;

is NOT good for me since that it drops SCHEMA permissions as well.

A solution like

DO $$ DECLARE
r RECORD;
BEGIN
    -- if the schema you operate on is not "current", you will want to
    -- replace current_schema() in query with 'schematodeletetablesfrom'
    -- *and* update the generate 'DROP...' accordingly.
    FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = current_schema()) LOOP
        EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(r.tablename) || ' CASCADE';
    END LOOP;
END $$;

as reported in this thread How can I drop all the tables in a PostgreSQL database? would be ideal. Unfortunately it doesn't work on Redshift (apparently there is no support for for loops).

Is there any other solution to achieve it?

Write the loop in python or some other scripting language, external to your cluster? — MatBailie, Mar 26 '19 at 14:09

demircioglu · Answer 1 · 2021-05-18T13:18:31.720

14

Run this SQL and copy+paste the result on your SQL client. If you want to do it programmatically you need to built little bit code around it.

SELECT 'DROP TABLE IF EXISTS ' || tablename || ' CASCADE;' 
FROM pg_tables 
WHERE schemaname = '<your_schema>'

edited May 18 '21 at 13:18

answered Mar 26 '19 at 16:58

demircioglu

3,069
1
15
22

would you please format correctly the SQL query to improve readability of your answer? – Vzzarr May 18 '21 at 08:14

score 2 · Answer 2 · answered Jun 17 '19 at 12:37

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()

Procedures in Redshift have been available only since 17 May 2019 https://aws.amazon.com/about-aws/whats-new/2019/05/amazon-redshift-now-supports-stored-procedures/ So this could be an interesting solution by using procedures, I'll give it a try — Vzzarr, Jun 19 '19 at 10:17

Vzzarr · Answer 3 · 2021-05-18T13:41:29.597

1

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

edited May 18 '21 at 13:41

answered Mar 28 '19 at 11:08

Vzzarr

4,600
2
43
80

How do you import the library? – Henrov Jun 06 '19 at 09:06
if you're referring to `psycopg2` I think an easy `pip install psycopg2` should do the job if you use pip as a packet manager https://pypi.org/project/psycopg2/ – Vzzarr Jun 06 '19 at 09:16
Shouldn't that library be imported in AWS? To be used in AWS? – Henrov Jun 17 '19 at 12:32
@Henrov I executed this Python script into my local PC as a client and connected to Redshift cluster through credentials, just like any other DBMS – Vzzarr Jun 20 '19 at 09:36

score 1 · Answer 4 · answered Dec 18 '19 at 19:23

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.

Anatoly Scherbakov · Answer 5 · 2021-05-21T04:17:36.930

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.

Drop all tables in a Redshift schema - without dropping permissions

5 Answers5

References