Mastering PostgreSQL 12 - Third Edition

Cleaning up data

One advantage of partitioned tables is the ability to clean data up quickly. Let's assume that we want to delete an entire year. If the data is partitioned accordingly, a simple DROP TABLE clause can do the job:

test=# DROP TABLE t_data_2014;DROP TABLE

As you can see, dropping a child table is easy. But what about the parent table? There are depending objects and so PostgreSQL naturally errors out to make sure that nothing unexpected happens:

test=# DROP TABLE t_data;ERROR: cannot drop table t_data because other objects depend on it DETAIL: default for table t_data_2013 column id depends on    sequence t_data_id_seq table t_data_2016 depends on table t_data table t_data_2015 depends on table t_data HINT: Use DROP ... CASCADE ...