0

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.

7
  • Do you want to forbid or ignore the insert? Mar 12, 2021 at 7:12
  • i want to ignore it
    – PaxPrz
    Mar 12, 2021 at 7:16
  • 1
    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.
    – Akina
    Mar 12, 2021 at 7:18
  • @Akina. How do I normalize it in postgres? could you please elaborate
    – PaxPrz
    Mar 12, 2021 at 7:22
  • 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. Mar 12, 2021 at 7:33

1 Answer 1

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

1
  • Thank you. I'll study your implementation :)
    – PaxPrz
    Mar 12, 2021 at 7:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.