> 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}).
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
