1

I am creating a table which would have 512 partitions in PostgreSQL 12. The table will be partitioned using hashing.

How can I create the 512 partitions without having to create each one manually?

Would creating a loop using pl/pgsql would be the best route?

create table dept_1 partition of dept FOR VALUES WITH (MODULUS 512, REMAINDER 0);
create table dept_2 partition of dept FOR VALUES WITH (MODULUS 512, REMAINDER 1);
create table dept_3 partition of dept FOR VALUES WITH (MODULUS 512, REMAINDER 2);
......
create table dept_512 partition of dept FOR VALUES WITH (MODULUS 512, REMAINDER 511);
david
  • 997
  • 3
  • 14
  • 34

1 Answers1

2

you can use plpgsql inside DO command

DO $$
BEGIN
  FOR i IN 0..511
  LOOP
    EXECUTE format('create table %I partition of dept for values with (modulus 512, remainder %s)', 'depth_' || i + 1, i);
  END LOOP;
END;
$$;
Pavel Stehule
  • 42,331
  • 5
  • 91
  • 94