Re: Trying to create array of enum to array of text for exclusion constraint

> or is there something built in that I have missed?

The intarray extension in the contrib provides a GiST operator class
for int[]. That can be used with exclusion constraints:

> hasegeli=# create extension intarray;
> CREATE EXTENSION
>
> hasegeli=# create type e as enum ('a', 'b');
> CREATE TYPE
>
> hasegeli=# create table t (es e[]);
> CREATE TABLE
>
> hasegeli=# create function es_to_int (e[]) returns int[] language sql immutable as $$
> select array_agg(oid::int) from pg_enum
> where enumtypid = (select oid from pg_type where typname = 'e')
> and enumlabel = any($1::text[])$$;
> CREATE FUNCTION
>
> hasegeli=# alter table t add exclude using gist (es_to_int(es) with &&);
> ALTER TABLE
>
> hasegeli=# insert into t values ('{a,b}');
> INSERT 0 1
>
> hasegeli=# insert into t values ('{a}');
> ERROR: conflicting key value violates exclusion constraint "t_es_to_int_excl"
> DETAIL: Key (es_to_int(es))=({114830}) conflicts with existing key (es_to_int(es))=({114830,114832}).