Mastering PostgreSQL 12 - Third Edition

Moving tables in and out of partitioned structures

Suppose you have an inherited structure. Data is partitioned by date, and you want to provide the most recent years to the end user. At some point, you may want to remove some data from the scope of the user without actually touching it. You may want to put data into some sort of archive.

PostgreSQL provides a simple means to achieve exactly that. First, a new parent can be created:

test=# CREATE TABLE t_history (LIKE t_data);CREATE TABLE

The LIKE keyword allows you to create a table that has exactly the same layout as the t_data table. If you have forgotten which columns the t_data table actually has, this may come in handy as it saves you a lot of work. It is also possible to include indexes, ...