How to DROP TABLE in Redshift

How do I safely use DROP TABLE in Amazon Redshift?

DROP TABLE permanently deletes one or more tables and their data from an Amazon Redshift database.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why use DROP TABLE in Redshift?

Remove obsolete tables to free storage and simplify schemas. The command is irreversible, so verify backups before running.

What is the basic syntax?

Use DROP TABLE table_name; to delete a single table. Add IF EXISTS to avoid errors if the table is missing.

How do I drop multiple tables at once?

Comma-separate table names: DROP TABLE IF EXISTS customers_old, orders_2020; This executes in a single transaction.

When should I use CASCADE vs. RESTRICT?

CASCADE automatically removes dependent objects such as views. RESTRICT blocks the drop if dependencies exist. Choose based on safety needs.

Can I drop a table in another schema?

Yes. Qualify the name: DROP TABLE IF EXISTS analytics.daily_sales; Ensure your role has ownership or DROP privileges on that schema.

How do I check before dropping?

Query pg_table_def or run SELECT 1 FROM information_schema.tables WHERE table_name='orders'; to confirm existence.

Best practice checklist

Back up data to S3 with UNLOAD or snapshots.
Use IF EXISTS to prevent errors in deployment scripts.
Apply RESTRICT in production to avoid unintended cascade deletions.
Audit drops via system table stl_ddltext.

Why How to DROP TABLE in Redshift is important

How to DROP TABLE in Redshift Example Usage


-- Drop a temporary analysis table after exporting insights
DROP TABLE IF EXISTS analytics.high_value_customers_temp RESTRICT;

How to DROP TABLE in Redshift Syntax


DROP TABLE [ IF EXISTS ] table_name [ , ... ] [ CASCADE | RESTRICT ];

-- Examples in an ecommerce context
-- 1. Remove a staging copy of the Customers table
DROP TABLE IF EXISTS customers_staging RESTRICT;

-- 2. Delete old reporting tables in one go
DROP TABLE IF EXISTS monthly_sales_2021, monthly_sales_2022 CASCADE;

Common Mistakes

Omitting backups. Users drop tables without unloading critical data to S3. Always export or snapshot first to enable recovery.
Ignoring dependencies. Running DROP TABLE without RESTRICT drops dependent views, causing downstream query failures. Use RESTRICT or review pg_depend first.