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

Question

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.

• Laurenz Albe: Do you want to forbid or ignore the insert?
• PaxPrz: i want to ignore it
• Akina: Now when I try to insert B (again) in any column Critical, High or Low. It should ignore the insert. Normalize to (priority - value) and set value as unique. Use INSERT .. ON CONFLICT DO NOTHING.
• PaxPrz: @Akina. How do I normalize it in postgres? could you please elaborate
• Laurenz Albe: Right, there is no way to do it with the table design you currently have. "Normalize" is not a database operation, it is a table design technique.

Answer 1

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

• PaxPrz: Thank you. I'll study your implementation :)