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

From: Alban Hertroys <haramrae(at)gmail(dot)com>
To: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: Updating a pre-10 partitioned table to use PG 10 partitioning
Date: 2018-01-11 15:40:30
Message-ID: CAF-3MvOnTxqwdiaZDo9CSKT4026asHrta=9Av-7DBJdT35VcNw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Curt Tilmes 2018-01-11 16:23:17 Multiple central connection service files
Previous Message Andreas Joseph Krogh 2018-01-11 12:51:21 Sv: ORDER BY custom type