Is there any unique enum array type in postgres?

Question

Suppose there's an enum days_of_the_week with values from 'Sunday' to 'Saturday'.

Now, let's suppose that we have a table with columns company_name and days, where days is an array which represents what days the company is active.

In that case, we would want the values of the array to be compatible with the enum and for the values to be unique within the array (e.g. not allow two Mondays' in one value).

Is there any datatype which would handle all this and have the benefit of optimizing lookup queries?

You would need to write a function that checks for duplicates in the array, then use that function in a check constraints. But enums and arrays are pretty much anti-patterns when doing a proper relational design. If you normalize this properly you can easily get this using a unique index — user330315
– user330315, Commented Jun 7, 2018 at 10:38

Thaw · Accepted Answer · 2018-06-07 10:47:50Z

The following may not be exactly what you are looking for, but I don't think there is a way to add a unique constraint on an array. I don't like to use them anyway.

CREATE TYPE days_of_the_week as enum (
    'Sunday',
    'Monday',
    'Tuesday',
    'Wednesday',
    'Thursday',
    'Friday',
    'Saturday'
);

CREATE TABLE company (
    id SERIAL PRIMARY KEY NOT NULL,
    company_name CHARACTER VARYING(1024) NOT NULL
);

CREATE TABLE company_active (
    id_company integer NOT NULL REFERENCES company(id) ON DELETE CASCADE,
    days days_of_the_week NOT NULL
);

ALTER TABLE company_active
ADD CONSTRAINT company_active_unique UNIQUE (id_company, days);

INSERT INTO company (company_name) VALUES ('test');

INSERT INTO company_active (id_company, days)
SELECT id, 'Monday'::days_of_the_week
FROM company;

INSERT INTO company_active (id_company, days)
SELECT id, 'Tuesday'::days_of_the_week
FROM company;

Collectives™ on Stack Overflow

Is there any unique enum array type in postgres?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related