RE: Updating a pre-10 partitioned table to use PG 10 partitioning

-----Original Message-----
From: Alban Hertroys [mailto:haramrae(at)gmail(dot)com]
Sent: Thursday, January 11, 2018 10:41 AM
To: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: Updating a pre-10 partitioned table to use PG 10 partitioning

I'm trying to update an existing table that was created in PG9.6 using the old approach with manual inheritance and check constraints to make use of the new approach in PG 10 using 'partitioned by', 'attach partition', etc.

I ran into some how-to's on the internet, but they all assume you start partitioning from a new table instead of modifying an existing table into partitions. It seems to me that would be a fairly common requirement, no?

I realise that I skip over the fact that I'm not attempting to partition an unpartitioned table, but instead attempt to update the existing partitioning to the new approach.

Currently the table in question really only has 1 partition filtered on a type column (we plan to create 1 partition per type), but this table is already 1.5GB on disk and has a number of indexes and views associated to it. Creating a new table out of it and recreating all the related stuff seems a bit roundabout...

It would be nice if I could update the table to use PG 10 partitioning using just a simple alter table, but I can't seem to find the right syntax.

What is a good approach here?

______________________________________________________________________________________________________________________

Not in one step, but it's doable.
Here is what I do when switching from inheritance to declarative partitioning.

1. Alter all partitions to "NO INHERIT:

ALTER <partition_table_name> NO INHERIT <parent_table_name>

2. Create new partitioned table just like old "parent" table with different name using "PARTITION BY ..." clause.

3. Drop old "parent" table.

4. Rename new partitioned table giving it <parent_table_name>.

5. Finally, "old" partitions to new partitioned table (previously "parent"):

ALTER TABLE < parent_table_name > ATTACHE PARTITION <partition_table_name> FOR VALUES ...

Regards,
Igor Neyman