Postgresql: Disallow duplicate value to be inserted if already in any of multiple columns

postgresqltableunique-constraint

Suppose I have 3 columns:

Critical, Medium, Low

And I have data UUIDs to be inserted from another tables. And I want to disallow insertion of same UUID in any of the column if it is already present any one of these columns. How can I do that in postgres? How should I setup my table?

example:

--------------------------
|Critical | High  | Low   |
--------------------------
|   A     | null  | null  |
|   null  |  B    | null  |
---------------------------

Now when I try to insert B (again) in any column Critical, High or Low. It should ignore the insert.

Best Answer

How do I normalize it in postgres? could you please elaborate – Pax

Structure and predefined data:

-- Create table with priorities.
CREATE TABLE priorities (priority_id INT NOT NULL PRIMARY KEY, 
                         priority VARCHAR(255));
INSERT INTO priorities VALUES (1, 'Critical'), 
                              (2, 'High'), 
                              (3, 'Low');

-- Create table for data. Series column added.
CREATE TABLE data (series INT NOT NULL,
                   priority_id INT NOT NULL,
       -- reference to priorities table
                   CONSTRAINT fk_priority FOREIGN KEY (priority_id) REFERENCES priorities (priority_id),
                   value VARCHAR(255) NOT NULL PRIMARY KEY,
       -- forbid duplicated values
                   UNIQUE (series, priority_id));

Insertion pattern:

INSERT INTO data 
    VALUES {values list}
    ON CONFLICT DO NOTHING;

Retrieving the information:

SELECT series, 
       MAX(value) FILTER (WHERE priority_id = 1) Critical,
       MAX(value) FILTER (WHERE priority_id = 2) High,
       MAX(value) FILTER (WHERE priority_id = 3) Low
FROM data
GROUP BY series
ORDER BY series;

fiddle

PS. The scheme does not allow to insert more than one value for the same priority in the same series also (see last insert).

PPS. priorities table is not used in shown code (it is used for reference integrity only), but you may...

Related Question