CODEWITHSUNDEEP
postgresqltypeorm
Ben Beri
Ben Beri

Reputation: 1201

Postgres unique SET of enums?

Is there a way to create an unique set of enums in PostgreSQL?

enter image description here

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?

Upvotes: 1

Views: 780

Answers (2)

klin
klin

Reputation: 121834

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"

Upvotes: 2

Laurenz Albe
Laurenz Albe

Reputation: 247665

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}).

Upvotes: 1

Related Questions