ALTER TABLE DROP PARTITION

Drops one or more partitions from an existing table.

Similar to dropping columns, dropping of partitions is a non-blocking and non-waiting operation. While atomic for single partitions, dropping multiple partitions is in itself non-atomic. The operation will exit on the first failure and will not continue through a list of partitions if one fails to be dropped.

info

The most recent (i.e. currently active) partition cannot be removed

caution

Use DROP PARTITION with care as QuestDB cannot recover data from dropped partitions!

Syntax

Drop partition by name

The partition name must match the name of the directory for the given partition. The naming convention is detailed in Partitions.

Examples

Drop a single partition

--DAY

ALTER TABLE measurements DROP PARTITION LIST '2019-05-18';

--MONTH

ALTER TABLE measurements DROP PARTITION LIST '2019-05';

--YEAR

ALTER TABLE measurements DROP PARTITION LIST '2019';

Drop multiple partitions

ALTER TABLE measurements DROP PARTITION LIST '2018','2019';

Drop partitions using boolean expression

Drops partitions based on a boolean expression on the designated timestamp column.

Examples

Drop one partition

ALTER TABLE measurements

DROP PARTITION

WHERE timestamp = to_timestamp('2019-01-01:00:00:00', 'yyyy-MM-dd:HH:mm:ss');

Drop all partitions older than 2018

ALTER TABLE measurements

DROP PARTITION

WHERE timestamp < to_timestamp('2018-01-01:00:00:00', 'yyyy-MM-dd:HH:mm:ss');