Mastering PostgreSQL 9.6

Cleaning up data

One advantage of partitioned tables is the ability to clean up data quickly. Suppose that we want to delete an entire year. If 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 therefore 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 ...