Just wondering what others have done for using enum or uuid columns in exclusion constraints?
I have a solution now, but I just wanted to see what others have ended up doing as well and see if what i'm doing is sane. If i'm doing something unsafe, or you know of a better way, please chime in.
For enum columns, I use a function in the constraint to convert the enum value to an oid. The function is defined as such:
CREATE OR REPLACE FUNCTION enum_to_oid(
_enum_schema text,
_enum_name text,
_enum anyenum)
RETURNS oid AS
$BODY$
SELECT e.oid
FROM pg_type t
INNER JOIN pg_enum e
ON t.oid = e.enumtypid
INNER JOIN pg_catalog.pg_namespace n
ON n.oid = t.typnamespace
WHERE true
AND n.nspname = _enum_schema
AND t.typname = _enum_name
AND e.enumlabel = _enum::text;
$BODY$
LANGUAGE sql STABLE;
For uuid columns, I use another function in the constraint to convert it to a bytea type defined here:
CREATE OR REPLACE FUNCTION uuid_to_bytea(_uuid uuid)
RETURNS bytea AS
$BODY$
select decode(replace(_uuid::text, '-', ''), 'hex');
$BODY$
LANGUAGE sql IMMUTABLE;
And i'd use these functions in the constraint like this:
CONSTRAINT claim_product_reason_code_active_range_excl EXCLUDE
USING gist (uuid_to_bytea(claim_product_id) WITH =, enum_to_oid('enum'::text, 'claim_reason_type'::text, claim_reason_type) WITH =, enum_to_oid('enum'::text, 'claim_reason_code'::text, claim_reason_code) WITH =, active_range WITH &&)
And as a closing note on this, I really can't wait until these are supported types for gist indexes. It would be great not to have to play games like this to have exclusion constraints on my tables just because I am using uuids instead of ints, and enums instead of lookup tables (when an enum really fits the problem well).