Create array of custom domain postgres

Question

Because of the inherit limitations of enum (you can't add values to the enum from within a function), I'm switching to custom domains with a check constraint verifying the values. I need to be able to create arrays of my custom enums, but when I try something like this:

CREATE DOMAIN foo AS text CHECK (VALUE IN ('foo', 'bar'));
CREATE TABLE foo_table(foo_column foo[]);

I get the error

type "foo[]" does not exist

Doing some googling, I found this from 2004 which made it look like support for this was coming. Is there a way to do this?

Thanks!

UPDATE

I've come up with a hacky solution, which I'll put as the answer if no one comes up with a better solution in a few days. This solution means you can't reuse a type to be an array, you have to create a separate type that acts as the array:

CREATE DOMAIN foo_group AS text[] CHECK (VALUE <@ ARRAY['foo', 'bar']);

CREATE TABLE foo_table(foo_column foo_group);

The following work:

INSERT INTO foo_table VALUES(ARRAY['foo']);
INSERT INTO foo_table VALUES(ARRAY['foo', 'bar']);
INSERT INTO foo_table VALUES(ARRAY['bar']);

The following don't:

INSERT INTO foo_table VALUES(ARRAY['foo', 'baz']);
INSERT INTO foo_table VALUES(ARRAY['baz']);

`CREATE DOMAIN foo_group AS text[] CHECK (VALUE <@ ARRAY['foo', 'bar']);` - without additional function. [Doc](https://www.postgresql.org/docs/current/static/functions-array.html) — Abelisto, Apr 06 '17 at 18:20
That's awesome. Didn't know range operators worked for arrays like that — Noah, Apr 07 '17 at 00:13
It is not "range operator". There are several operators with the same notation for different types of arguments. Execute `\do+ <@` in the `psql`. — Abelisto, Apr 07 '17 at 04:52
Ah, so `arraycontained(left, right)` would have worked as well. Was looking for something like that on the array functions page, guess I missed it. — Noah, Apr 07 '17 at 14:22

score 1 · Answer 1 · answered Aug 07 '17 at 08:16

Another possible workaround is:

CREATE TYPE foo_tup AS (item foo);

Domain types can wrapped in tuples like this and that gives you an array constructor. The downside is now you probably want to create casts:

select array[row('foo')::foo_tup, row('bar')];

For example you could create a function and a cast:

create function foo_tup(foo) returns foo_tup language sql as $$
    select row($1)::foo_tup;
$$ immutable;
create function foo(foo_tup) returns foo language sql as $$
     select $1.item;
$$;
create cast (foo as foo_tup) with function foo_tup(foo);
create cast (foo_tup as foo) with function foo(foo_tup);

Then aggregation becomes easy:

select array_agg(myfoo::foo_tup) from my_table;

though you get extra parentheses.

Create array of custom domain postgres

UPDATE

1 Answers1

Linked