Postgresql: Partitioning a table for values in SELECT

Question

I want to use PostgreSQL's declarative table partitioning on a column with UUID values. I want it partitioned by values that are returned by a SELECT.

CREATE TABLE foo (
    id uuid NOT NULL,
    type_id uuid NOT NULL,
    -- other columns
    PRIMARY KEY (id, type_id)
) PARTITION BY LIST (type_id);

CREATE TABLE foo_1 PARTITION OF foo
    FOR VALUES IN (SELECT id FROM type_ids WHERE type_name = 'type1');
CREATE TABLE foo_2 PARTITION OF foo
    FOR VALUES IN (SELECT id FROM type_ids WHERE type_name = 'type2');

I don't want to use specific UUIDs in FOR VALUES IN ('uuid'), since the UUIDs may differ by environment (dev, qa, prod). However, the SELECT syntax doesn't seem to be accepted by Postgres. Any suggestions?

Why not? Because nobody implemented it. Probably because it simply doesn't make sense. What if the rows from type_ids where deleted after you create the partitions? Or suddenly new types appear with additional UUIDs? — user330315
– user330315, Commented Jan 29, 2021 at 20:10
Oh, and I don't want it to be dynamically evaluated. I just wanted the SELECT to be evaluated at the table creation time. — silverberry
– silverberry, Commented Jan 29, 2021 at 20:19

user330315 · Accepted Answer · 2021-01-29 21:59:18Z

2

I just wanted the SELECT to be evaluated at the table creation time

You should have made that clear in the question, not in a comment.

In that case - if this is a one time thing, you can use dynamic SQL, e.g. wrapped into a procedure

create procedure create_partition(p_part_name text, p_type_name text)
as
$$
declare
  l_sql text;
  l_id uuid;
begin
  select id
    into l_id
  from type_ids
  where type_name = p_type_name;

  l_sql := format('CREATE TABLE %I PARTITION OF foo FOR VALUES IN (%L)', p_part_name, l_id);
  execute l_sql;
end;
$$
language plpgsql;

Then you can do:

call create_partition('foo_1', 'type1');
call create_partition('foo_2', 'type2');

answered Jan 29, 2021 at 21:59

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql: Partitioning a table for values in SELECT

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related