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 for partition directories is as follows:

Examples

--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';

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

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

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