PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – postgres add partitioning support on non partitioned table

I have existing table which has become too large and I would like to enable partitions for the current year from timestamp field. How can I do this, I can’t find anything in the documentation, is it not possible to make a table partitioned after creation?

ALTER TABLE appointment PARTITION BY RANGE (start_on) -> does not work.

Tags:

2

Answers


  1. 0

    As per documentation:

    It is not possible to turn a regular table into a partitioned table or vice versa. However, it is possible to add an existing regular or partitioned table as a partition of a partitioned table, or remove a partition from a partitioned table turning it into a standalone table…

    So you might want to create a new partitioned table, and attach the old one as a (default) partition to it.

    Login or Signup to reply.
  2. 0

    You would want something like:

    alter table appointment add constraint starton check (start_on >= $1 and start_on < $2) not valid;
alter table appointment validate constraint starton;
create table new_appt (like appointment) partition by range (start_on);
alter table new_appt attach partition appointment for values from ($1) to ($2);

    It will probably be more complex, as you will also need to deal with indexes, foreign keys, etc. Of course you need to provide $1 and $2 to be at least as wide as the existing range in the table.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search