Postgresql trigger constraint

Question

I am trying to figure out how to make a trigger constraint which I have never done before. I am using Java with Postgresql.

I have a table which looks like so:

I need two things:

A unique constraint which prevents any duplicate [type_of_charge + time_of_use + meter_register_code + period_of_availability] - this has already been done with a standard unique constraint.
The trigger constraint needs to prevent any 'anytime' row being inserted if there is already a row of the same meter_register_code + period_of_availability.
The trigger constraint needs to stop any row being inserted if there is already an 'anytime' value. e.g. Off-Peak cannot be inserted if there is an anytime value already, but can if there is a 'peak' value.

Please **[edit]** your question and show us the `create table` statement as [formatted text](http://stackoverflow.com/help/formatting) please, [no screen shots](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557) please — , May 08 '18 at 10:21
2. sounds as if that could be done using an [exclusion constraint](https://www.postgresql.org/docs/current/static/ddl-constraints.html#DDL-CONSTRAINTS-EXCLUSION), but I can't really read your screenshot so I'm not 100% sure and 3. sounds as if it can be done using a partial unique index — , May 08 '18 at 10:23
A trigger can never guarantee a constraint unless you are using `SERIALIZABLE` isolation level. What is wrong with the answer I gave [here](https://stackoverflow.com/a/50229014/6464308)? — Laurenz Albe, May 08 '18 at 10:39

score 3 · Answer 1 · answered May 08 '18 at 13:04

I seem to have resolved it with the following:

 CREATE OR REPLACE FUNCTION validate_network_pricing_code() returns trigger as $$
  DECLARE
    tester int;
  BEGIN
  IF NEW.time_of_use = 'ANYTIME'
  THEN tester := (SELECT COUNT(*) FROM network_pricing_category_code WHERE (
        network_pricing_category_code.meter_register_code = NEW.meter_register_code AND
        network_pricing_category_code.period_of_availability = NEW.period_of_availability AND
        network_pricing_category_code.network_pricing_category_id = NEW.network_pricing_category_id AND
        network_pricing_category_code.id != NEW.id  
    ));
    END IF;
    IF NEW.time_of_use IS NOT NULL AND NEW.time_of_use != 'ANYTIME' 
    THEN tester := (SELECT COUNT(*) FROM network_pricing_category_code WHERE (
        network_pricing_category_code.time_of_use = 'ANYTIME' AND
        network_pricing_category_code.meter_register_code = NEW.meter_register_code AND
        network_pricing_category_code.period_of_availability = NEW.period_of_availability AND
        network_pricing_category_code.network_pricing_category_id = NEW.network_pricing_category_id AND
        network_pricing_category_code.id != NEW.id  
    ));
    END IF;
    IF (tester != 0) THEN
      RAISE EXCEPTION 'ANYTIME rates cannot be combined with other types of rates - PEAK, SHOULDER, OFF-PEAK etc ';
    END IF;
    RETURN NEW;
  END;
  $$ language plpgsql;


 CREATE TRIGGER validate_network_pricing_code_trigger BEFORE INSERT OR UPDATE ON network_pricing_category_code
      FOR EACH ROW EXECUTE PROCEDURE validate_network_pricing_code();

By modifying a writeup I found here: When Validation Is Not Enough: PostgreSQL Triggers for Data Integrity

Postgresql trigger constraint

1 Answers1