PostgreSQL: check constraint validation before uniqueness constraint on UPSERT

Question

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

I initially wanted to catch both constraints in the INSERT block, but I didn't see any way to do that.

Any reason not to use the upsert for increments and a separate `UPDATE` statement for decrements? — Bergi, Nov 17 '20 at 15:40
The main reason is that I don't know whether or not the rows exist or not before doing the UPSERT. I would like to avoid SELECT to get which rows exist, then INSERT and UPDATE because it's performance sensitive code and I want to avoid three queries. — Alechko, Nov 17 '20 at 16:05
Additionally, I considered using subqueries for getting whatever rows exist (relying on uniqueness constraint) then using this information to UPDATE the rows that exist and INSERT those that don't. This is also problematic in my case because then I only see the `affected_rows_count` from the last subquery and cannot effectively verify that the correct number of rows were affected. — Alechko, Nov 17 '20 at 16:06
You wrote "*negative `val` updates should only take place for existing names*", so it seemed like you knew for those. Or is the `val` dynamically computed within the query? — Bergi, Nov 17 '20 at 17:16
Value of `val` is computed dynamically outside the query. I wanted to find an elegant way to the upsert without custom logic which depends on the value of `val`. — Alechko, Nov 30 '20 at 10:54

score 1 · Answer 1 · answered Nov 17 '20 at 15:36

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.

PostgreSQL: check constraint validation before uniqueness constraint on UPSERT

Notes

1 Answers1