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 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 to drop the dependent objects too.

The DROP TABLE clause will warn us that there are depending objects and will refuse to drop those tables. The CASCADE clause is needed to force PostgreSQL to actually remove those objects, along with the parent table. The following example shows us how to use a cascaded DROP TABLE:

test=# DROP TABLE t_data CASCADE;
NOTICE: drop cascades to 3 other objects DETAIL: drop cascades to default for table t_data_2013 column id drop cascades to table t_data_2016 drop cascades to table t_data_2015 DROP TABLE
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset