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?
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');