Attach partition LIST to existing table in postgres 11

Question

I am trying to ALTER a table to use partitions LIST in postgres 11. I have been trying for hours but i keep getting errors.

I have a huge table, clients, with ( client_id, customer_id, value).

I have already created a new empty table, clients, by renaming the old table to clients_old and then created the new table with: CREATE TABLE clients( like clients_old including all).

And from here I am stuck when trying to add the LIST partition.

I have tried to:

ALTER TABLE Clients attach PARTITION BY LIST  (client_id) --> fail;
ALTER TABLE Clients attach PARTITION  LIST  (client_id) --> fail;
ALTER TABLE Clients ADD PARTITION  LIST  (client_id) --> fail;

What syntax should I use to alter the table to use partitions?

Answer 1

Quote from the manual

It is not possible to turn a regular table into a partitioned table or vice versa

So, you can not change an existing non-partitioned table to a partitioned table.

You need to create a new table (with a different name) that is partitioned, create all necessary partitions and then copy the data from the old table to the new, partitioned table.

Something like:

create table clients_partitioned
(
  .... all columns ...
)
PARTITION BY LIST  (client_id);

Then create the partitions:

create table clients_1 
   partition of clients_partioned
   for values in (1,2,3);

create table clients_1 
   partition of clients_partioned
   for values in (4,5,6);

Then copy the data:

insert into clients_partitioned
select *
from clients;

Once that is done, you can drop the old table and rename the new table:

drop table clients;
alter table clients_partitioned rename to clients;

Don't forget to re-create your foreign keys and indexes.