CODEWITHSUNDEEP
postgresqlunique-constraintupsertcheck-constraintspostgresql-11
Alechko
Alechko

Reputation: 1598

PostgreSQL: check constraint validation before uniqueness constraint on UPSERT

Purpose: I want to trigger CHECK constraint validation after uniqueness constraint validation.

Consider the following table definition:

CREATE TABLE IF NOT EXISTS counters (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL UNIQUE,
  val INT NOT NULL CHECK(val >= 0)
);

This table holds unique names and non negative counter values. On table creation, two constraints are defined:

counters_val_check - a CHECK constraint that asserts the values of `val` are non negative.
counters_name_key - uniqueness constraint that asserts the values of `name` are unique.

Here's my UPSERT query:

INSERT INTO counters (name, val) VALUES ('alex', 2)
ON CONFLICT ON CONSTRAINT "counters_name_key" DO
UPDATE SET val = counters.val + excluded.val;

When the values of val are positive - there's no problem. However, when I try to UPSERT a negative value for an existing name - the constraint counters_val_check is validated before the constraint counters_name_key and the query fails:

INSERT INTO counters (name, val) VALUES ('alex', -2)
ON CONFLICT ON CONSTRAINT "counters_name_key" DO
UPDATE SET val = counters.val + excluded.val;

--------------------------------------------------------------------------------
Query 1 ERROR: ERROR:  new row for relation "counters" violates check constraint "counters_val_check"

Since I know that negative val updates should only take place for existing names, this kind of negative value update is totally fine. What I want to achieve is flipping the order of validation, so that counters_val_check will only be validated after counters_name_key validation passed.

Notes

Upvotes: 2

Views: 757

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247485

The best I can think of is a deferred constraint trigger instead of a check constraint:

CREATE FUNCTION howl_on_negative_val() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   IF NEW.val < 0 THEN
      RAISE EXCEPTION '"val" cannot be negative';
   END IF;

   RETURN NEW
END;$$;

CREATE CONSTRAINT TRIGGER howl_on_negative_val
   AFTER INSERT OR UPDATE ON counters
   DEFERRABLE INITIALLY DEFERRED
   FOR EACH ROW
   EXECUTE FUNCTION howl_on_negative_val();

Such a trigger will fire at the end of the transaction, so the condition will be checked after the primary key.

Upvotes: 1

Related Questions