CODEWITHSUNDEEP
postgresql
N M
N M

Reputation: 616

Is there any unique enum array type in postgres?

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?

Upvotes: 3

Views: 997

Answers (1)

Thaw
Thaw

Reputation: 861

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;

Upvotes: 1

Related Questions