SQL DROP Statements

DROP removes an object from the database. There is a separate statement per object type, and using the wrong one is an error rather than a silent success, which is the first piece of good news on this page.

drop table   orders_backup;
drop view    customer_revenue;
drop schema  staging;
drop sequence customer_id_seq;
drop type    order_status;
drop macro   gross_margin;
drop index   idx_orders_customer;

Dropping something that is not there raises an error, which is why cleanup scripts fail on their second run. IF EXISTS turns the missing case into a no-op and works on every one of those statements.

drop view  if exists customer_revenue;
drop table if exists customer_revenue_snapshot;
drop macro if exists gross_margin;

IF EXISTS protects you from less than you think

IF EXISTS only covers the object not existing. It does not cover the object existing as something else. Point DROP TABLE IF EXISTS at a view and DuckDB still refuses:

create or replace view customer_revenue as
select customer_id from orders;

-- both of these fail the same way
drop table customer_revenue;
drop table if exists customer_revenue;

The message is Existing object customer_revenue is of type View, trying to drop type Table, and the view is left untouched. This is the single most common way a "safe" teardown block dies halfway through: the names are right, the statement is wrong, and everything after it in the script never runs.

What CASCADE actually does

CASCADE and RESTRICT say what to do about objects that depend on the one you are dropping. RESTRICT, the default, means "refuse if anything depends on this". CASCADE means "drop those too".

In DuckDB the important thing is knowing which dependencies are tracked at all, because they are not all tracked, and the answer is not intuitive.

Dropping a schema

Schemas are the one place DuckDB's dependency tracking behaves the way most people expect, which makes DROP SCHEMA ... CASCADE the tidiest way to clean up a sandbox.

create schema if not exists sandbox;

create table sandbox.staged_orders as
select * from orders;

create view sandbox.recent_orders as
select * from sandbox.staged_orders
where order_date >= date '2024-03-01';

-- refuses: the view depends on the schema
drop schema sandbox;

-- removes the schema and everything in it
drop schema sandbox cascade;

Building throwaway work inside a named schema and dropping the schema at the end is a better habit than dropping objects one at a time, because you cannot forget one.

Macros have two spellings

DROP MACRO removes a scalar macro, and it also removes a table macro. DROP MACRO TABLE only removes a table macro, and reports that no such function exists when pointed at a scalar one. When you are not sure which kind you created, DROP MACRO is the one that works either way.

Finding what is there before you drop it

Guessing at names is how you end up dropping the wrong object. The catalog knows what exists and what type each object is.

select table_name as name, 'table' as kind
from duckdb_tables()
where not internal

union all

select view_name, 'view'
from duckdb_views()
where not internal

order by kind, name

Confirming the type is the part that matters, since that is what decides which DROP statement will work.

A teardown block that reruns cleanly

Put the cleanup at the top of a script rather than the bottom. Then rerunning the script always starts from a known state, even when the previous run died in the middle.

drop view  if exists top_categories;
drop table if exists category_revenue_snapshot;

create table category_revenue_snapshot as
select p.category_id,
       sum(oi.quantity * oi.unit_price) as revenue
from order_items oi
join products p on p.product_id = oi.product_id
group by p.category_id;

create view top_categories as
select category_id, revenue
from category_revenue_snapshot
order by revenue desc
limit 3;

Drop dependents before what they depend on (the view before the table here) so nothing is left pointing at something that no longer exists.

Before you drop anything that matters

• Look at it first. select count(*) from thing costs a second and has stopped a lot of bad afternoons.
• Snapshot it. create table thing_backup as select * from thing before a drop you are unsure about.
• Check the type in the catalog so you reach for the right statement.
• Check what pointed at it afterwards. DuckDB will not tell you that you just broke three views.
• Add CASCADE deliberately, never as a reflex to make an error go away. Read what it will take with it first.