Mult-row constraint to prevent duplicate values when ordered-CodePudding

Given this table:

CREATE TABLE keyvalues (
  id         serial    PRIMARY KEY,
  key        text 
  type       text,
  value      text,
  updated_at timestamp NOT NULL DEFAULT current_timestamp
);

Where we can store key values. Key and value can be duplicate, the updated_at will determine the latest value. Here is an example:

1 | log_level | string | info | timestamp...
2 | log_level | string | warn | timestamp...
3 | log_level | number |   0  | timestamp...

This is all fine, but how can I define a constraint, that ensures that I don't store two rows as ordered by updated_at where type and value would be the same, for example:

1 | log_level | string | info | 2022-01-12 01:00:00
2 | log_level | string | info | 2022-01-12 01:02:00
3 | log_level | string | info | 2022-01-12 01:10:00

Should not be allowed.

This, however, would be okay:

1 | log_level | string | info | 2022-01-12 01:00:00
2 | log_level | string | warn | 2022-01-12 01:02:00
3 | log_level | string | info | 2022-01-12 01:10:00

As well as:

1 | log_level | string | info | 2022-01-12 01:00:00
2 | logging   | string | info | 2022-01-12 01:02:00

CodePudding user response：

Since you said the table is append only, it's enough to compare the row being inserted with the last row, which could be done with a trigger before insert.

CREATE OR REPLACE FUNCTION keyvalues_row_validity() RETURNS TRIGGER AS $$
DECLARE 
  last_key   TEXT;
  last_type  TEXT;
  last_value TEXT;

BEGIN
SELECT key, type, value
INTO last_key, last_type, last_value
FROM keyvalues
ORDER BY updated_at DESC
LIMIT 1;

IF last_key = NEW.key AND last_type = NEW.type AND last_value = NEW.value
THEN RETURN NULL;
END IF;

RETURN NEW;

END;
$$

Create the trigger:

CREATE TRIGGER keyvalues_row_validity
  BEFORE INSERT ON keyvalues
  FOR EACH ROW EXECUTE PROCEDURE keyvalues_row_validity();