Drops one or more partitions from an existing table.
Syntax
Description
Drops one or more table partitions.
Just like with columns dropping of partitions is a non-blocking and non-waiting operation. While being atomic for a single partitions, dropping of multiple partitions is in itself non-atomic. Drop partition will bail on the first failure and will not continue with the list.
:::note
The last partition (active partition) cannot be removed. This will be implemented in a future release.
:::
Drop partition by name
Partition name must match the name of the directory for the given partition.
Naming convention for partition directories is as follows:
Table Partition | Partition format |
---|---|
DAY | YYYY-MM-DD |
MONTH | YYYY-MM |
YEAR | YYYY |
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');