I need to create a table like the following
CREATE TABLE test_schema.ranged_table
(
a text NOT NULL,
b text NOT NULL,
myrange int4range NOT NULL CHECK (NOT isempty(myrange)),
_upper int4 GENERATED ALWAYS AS (upper(myrange)) STORED
) PARTITION BY RANGE (upper(myrange));
and I need a primary key and a and a exclusion constraint on the fields (a,b, myrange).
Unfortunately, postgres doesn't allow creating keys and constraints on partitioned tables when expressions are used in the partition keys.
My workaround was to add those only to the template table like this:
CREATE TABLE test_schema.dummy_template
(
LIKE test_schema.ranged_table INCLUDING ALL,
PRIMARY KEY (a, b, myrange),
EXCLUDE USING GIST (a WITH =, b WITH =, myrange WITH &&)
);
And finally configure partman to use this template:
-- Init partman
SELECT partman.create_parent(
p_parent_table => 'test_schema.ranged_table',
p_control => '_upper',
p_control_not_null => false,
p_template_table := 'test_schema.dummy_template',
p_type => 'range',
p_interval => '30',
p_premake => 2
);
I was expecting to see the PK and the exclusion constraints, but I can see only the first:
SELECT conrelid::regclass, conname, contype
FROM pg_constraint
WHERE contype in ('x', 'p') AND conrelid::regclass::text LIKE 'test_schema.%';
gives:
conrelid,conname,contype
test_schema.dummy_template,dummy_template_pkey,p
test_schema.dummy_template,dummy_template_a_b_myrange_excl,x
test_schema.ranged_table_p0,ranged_table_p0_pkey,p
test_schema.ranged_table_p30,ranged_table_p30_pkey,p
test_schema.ranged_table_p60,ranged_table_p60_pkey,p
test_schema.ranged_table_default,ranged_table_default_pkey,p
Instead, if I manually add the constraint on the already generated partitions, it goes through.
Am I missing something?
I need to create a table like the following
and I need a
primary keyand aand a exclusion constrainton the fields(a,b, myrange).Unfortunately, postgres doesn't allow creating keys and constraints on partitioned tables when expressions are used in the partition keys.
My workaround was to add those only to the template table like this:
And finally configure partman to use this template:
I was expecting to see the
PKand theexclusion constraints, but I can see only the first:gives:
Instead, if I manually add the constraint on the already generated partitions, it goes through.
Am I missing something?