Is there a way to create an unique set of enums in PostgreSQL?
notifications is a Set of Notification enum. I want this Set to be unique, let's say you cannot have {Notification.PUSH, Notification.PUSH} twice in your set.
Is there a way to set this data type?
You can use the function in a check constraint:
create or replace function array_is_unique(arr anyarray)
returns boolean language sql immutable as
$$
select count(distinct a) = array_length(arr, 1)
from unnest(arr) a
$$;
Example usage:
create type notification as enum ('a', 'b', 'c');
create table my_table(
id serial primary key,
notifications notification[] check(array_is_unique(notifications))
);
Is there a way to set this data type?
You can create a domain, example:
create domain notifications as notification[] check(array_is_unique(value));
drop table if exists my_table;
create table my_table(
id serial primary key,
notifications notifications
);
insert into my_table (notifications)
values ('{a, a}');
ERROR: value for domain notifications violates check constraint "notifications_check"
Yes, that is possible with a check constraint that uses a function:
CREATE TABLE arr(ia int[]);
CREATE FUNCTION check_array_unique(anyarray) RETURNS boolean
LANGUAGE sql IMMUTABLE AS
'SELECT (SELECT count(DISTINCT x) FROM unnest($1) AS x(x)) = cardinality($1)';
ALTER TABLE arr ADD CHECK (check_array_unique(ia));
INSERT INTO arr VALUES (ARRAY[1,2,3,4]);
INSERT 0 1
INSERT INTO arr VALUES (ARRAY[1,2,3,4,1]);
ERROR: new row for relation "arr" violates check constraint "arr_ia_check"
DETAIL: Failing row contains ({1,2,3,4,1}).
