Control / ddl · All operations

Drop a table

A variant of Create or change tables

Remove a table, view or other catalogue entry.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Drop a table, a view, a sequence, a type, a macro and a schema

SQL
CREATE SCHEMA staging;
CREATE TABLE staging.scratch (id INTEGER);
CREATE TABLE t2 (id INTEGER);
CREATE VIEW v2 AS SELECT * FROM t2;
CREATE SEQUENCE s1;
CREATE TYPE mood AS ENUM ('ok', 'sad');
CREATE MACRO m1(x) AS x + 1;
CREATE TABLE keep (id INTEGER);
INSERT INTO keep VALUES (1);
CREATE TEMP VIEW entries AS
SELECT 'table' AS kind, schema_name || '.' || table_name AS name FROM duckdb_tables() WHERE database_name = 'memory'
UNION ALL SELECT 'view', schema_name || '.' || view_name FROM duckdb_views() WHERE database_name = 'memory' AND NOT internal
UNION ALL SELECT 'sequence', schema_name || '.' || sequence_name FROM duckdb_sequences() WHERE database_name = 'memory'
UNION ALL SELECT 'type', schema_name || '.' || type_name FROM duckdb_types() WHERE database_name = 'memory' AND NOT internal
UNION ALL SELECT 'macro', schema_name || '.' || function_name FROM duckdb_functions() WHERE database_name = 'memory' AND NOT internal AND function_type = 'macro'
UNION ALL SELECT 'schema', schema_name FROM duckdb_schemas() WHERE database_name = 'memory' AND NOT internal;
CREATE TEMP TABLE before AS SELECT * FROM entries;
-- a view is not dropped with the table it reads, so each is dropped by name
DROP VIEW v2;
DROP TABLE t2;
DROP SEQUENCE s1;
DROP TYPE mood;
DROP MACRO m1;
-- dropping what is not there is an error unless IF EXISTS says that is fine
DROP TABLE IF EXISTS no_such_table;
-- a schema that still holds a table is dropped only with CASCADE, which takes the table with it
DROP SCHEMA staging CASCADE;
CREATE TEMP TABLE after AS SELECT * FROM entries;
SELECT * FROM (SELECT * FROM before EXCEPT SELECT * FROM after) ORDER BY kind, name;
SELECT CASE WHEN ((SELECT list((kind, name) ORDER BY kind, name) FROM (SELECT * FROM before EXCEPT SELECT * FROM after))
      = [('macro', 'main.m1'), ('schema', 'staging'), ('sequence', 'main.s1'), ('table', 'main.t2'), ('table', 'staging.scratch'), ('type', 'main.mood'), ('view', 'main.v2')]
  AND (SELECT count(*) = 0 FROM (SELECT * FROM after EXCEPT SELECT * FROM before))
  AND (SELECT count(*) = 1 FROM keep)) THEN 'as expected' ELSE error('dropping should remove exactly the table t2, the view v2, the sequence, the type, the macro, and the schema with its table, leave every other entry and the data of the table keep, and not complain about the table that was never there') END AS verdict;
  • The statement builds its own data.
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “dropping should remove exactly the table t2, the view v2, the sequence, the type, the macro, and the schema with its table, leave every other entry and the data of the table keep, and not complain about the table that was never there”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

DuckDB (1)
  • DROPread 2026-09-27

    Remove a table, view or other catalog entry

Background

A DROP statement in SQL removes a component, such as a table, view or index, from a relational database management system (RDBMS).[1] The kinds of object that can be dropped depend on the RDBMS, but most support dropping tables, users and databases.[1] DELETE and TRUNCATE remove data but leave the table in the database, whereas DROP removes the entire table.[1]

When to use it

Drop a table when nothing should read it again, such as a staging table built only to feed a later step; to empty a table and keep its definition, truncate it instead. (Meridian's view) In PostgreSQL, DROP TABLE always removes the indexes, rules, triggers and constraints that exist for the target table.[2] In PostgreSQL, a table that is referenced by a view can be dropped only when CASCADE is specified, and CASCADE then removes the dependent view entirely.[2] In PostgreSQL, CASCADE automatically drops the objects that depend on the table, such as views, and in turn all objects that depend on those objects.[2] In PostgreSQL, RESTRICT refuses to drop a table if any objects depend on it, and RESTRICT is the default.[2]

DuckDB currently does not track dependencies for views, so dropping a table that a view reads leaves the view in an invalid state.[3] In DuckDB, a view whose table has been dropped fails when queried, with an error that the table does not exist.[3] Before dropping a table in DuckDB, look for the views that read it. (Meridian's view) In DuckDB, dropping a schema that still holds a table fails with a dependency error unless CASCADE is given, which drops the schema and its table together.[3]

In a step that may run against a fresh database, write IF EXISTS, so that the first run does not fail on a table that is not there yet. (Meridian's view) In DuckDB, running DROP TABLE should free the memory the table used, but not always disk space, though the freed blocks are marked as free in the database file.[3]

The method

The typical form of a DROP statement is DROP followed by the object type and the object name, as in DROP TABLE employees.[1] Some systems, such as PostgreSQL, allow DROP and other data definition language (DDL) commands inside a transaction, so that a drop can be rolled back.[1]

PostgreSQL makes sure that objects other objects still depend on cannot be dropped, to keep the integrity of the whole database structure.[4] In PostgreSQL, a table with a foreign key constraint depends on the table it references, and dropping the referenced table with CASCADE removes only the foreign key constraint, not the other table.[4]

Compared with the alternatives

TRUNCATE TABLE removes all rows from a table while its columns, constraints and indexes remain, and DROP TABLE removes the table definition as well as its data.[5] In DuckDB, CREATE OR REPLACE TABLE is shorthand for dropping the existing table and then creating the new one.[6]

Sources

  1. [1]Data definition language - Wikipediasecondary source
  2. [2]PostgreSQL: Documentation: 18: DROP TABLEdocumentation
  3. [3]DROP Statement – DuckDBdocumentation
  4. [4]PostgreSQL: Documentation: 18: 5.15. Dependency Trackingdocumentation
  5. [5]Truncate (SQL) - Wikipediasecondary source
  6. [6]CREATE TABLE Statement – DuckDBdocumentation