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?

Blowy

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.

Peregrinate

I think it should be FOR VALUES instead of VALUES – Kempe

Regarding the quote It is not possible to turn a regular table into a partitioned table or vice versa that refers to the current version of documentation. I don't know what version was in 2019, but here in 2022 this line was present in the 14th version. It may have became obsolete in your year, though I doubt it. – Vito

Answer 2

I had to add for tag in order to add the partition:

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

because without for was a syntax error.

Misjoinder