Unique constraints name on partitioned tables

Question

I'm using partitioned tables for multi-tenant application and I have to check unique constraint name to return suitable error.

How I can check unique constraint name in each partitioned table? How unique constraint name in partitioned table is generated?

Thanks!

test@0:test> \d+ issuefield
  ...
  15   Indexes:
  16       "issuefield_pk_cp" PRIMARY KEY, btree (id, org_id)
  17       "issuefield_key_uq" UNIQUE CONSTRAINT, btree (key, org_id)
  18       "issuefield_lower_name_uq" UNIQUE, btree (lower(name::text), org_id) WITH (fillfactor='90')
  19       "issuefield_name_uq" UNIQUE CONSTRAINT, btree (name, org_id)
  20   Check constraints:
  21       "key_check" CHECK (key::text ~ '^[a-zA-Z][a-zA-Z0-9_]*$'::text AND (lower(key::text) <> ALL (ARRAY['organization'::text, 'space'::text, 'status'::text, 'statusCategory'::text, 'fun
       ction'::text])))
  22       "name_check" CHECK (COALESCE(btrim(name::text), ''::text) <> ''::text)
  23   Partition key: LIST (org_id)
  24   Partitions: public.issuefield_abc FOR VALUES IN ('eaed29a9-0813-4948-9e47-40da6db51d15')
  25
Time: 0.034s

test@0:test> \d+ issuefield_abc
       STDIN
  ...
  15   Indexes:
  16       "issuefield_abc_pkey" PRIMARY KEY, btree (id, org_id)
  17       "issuefield_abc_key_org_id_key" UNIQUE CONSTRAINT, btree (key, org_id)
  18       "issuefield_abc_lower_org_id_idx" UNIQUE, btree (lower(name::text), org_id) WITH (fillfactor='90')
  19       "issuefield_abc_name_org_id_key" UNIQUE CONSTRAINT, btree (name, org_id)
  20   Check constraints:
  21       "key_check" CHECK (key::text ~ '^[a-zA-Z][a-zA-Z0-9_]*$'::text AND (lower(key::text) <> ALL (ARRAY['organization'::text, 'space'::text, 'status'::text, 'statusCategory'::text, 'fun
       ction'::text])))
  22       "name_check" CHECK (COALESCE(btrim(name::text), ''::text) <> ''::text)
  ....
  30   Partition of: public.issuefield FOR VALUES IN ('eaed29a9-0813-4948-9e47-40da6db51d15')
  31   Partition constraint: ((org_id IS NOT NULL) AND (org_id = 'eaed29a9-0813-4948-9e47-40da6db51d15'::uuid))
  32   Has OIDs: no

user_6464308 · Answer

As you can see, the name of the check constraint is the same for all partitions. That does not work for primary and unique keys, because their name is the same as the index name, and that is unique per schema.

PostgreSQL just picks a name for the indexes and constraints on the partition, just like it does when you create a constraint or index without specifying a name. To have more control over that, proceed as follows:

create the partition without PARTITION OF first:

CREATE TABLE issuefield_abc (LIKE issuefield);

create the constraints and indexes with a name of your choice
attach the table as a partition – the constraints and indexes will automatically become partitions of the respective constraints and indexes on the partitioned table

Unique constraints name on partitioned tables

1 Answers