Constraint for values with different labels when all other columns are equal

Question

I would like to create a constraint that verifies if a value (from the 'nominal_value' column) classified as "minimum" in the 'stats_type' column is equal or smaller than a value classified as "average", when all values in the other columns are equal. In other words, given corresponding tuples, except for columns 'oid', 'stats_type' and 'nominal_value', I'd like to ensure the value labeled "minimum" is always equal or smaller than the value labeled as "average".

It is difficult to explain, so I made the example below:

CREATE TABLE price (
    oid SERIAL  NOT NULL,
    product INTEGER  NOT NULL,
    territory INTEGER  NOT NULL,
    stats_type INTEGER  NOT NULL,
    year INTEGER  NOT NULL,
    nominal_value NUMERIC(6,2)  NOT NULL,
    data_source INTEGER  NOT NULL,
    CONSTRAINT pk_price PRIMARY KEY (oid),
    CONSTRAINT price_1 UNIQUE (product, territory, stats_type, year, data_source),
    CONSTRAINT price_2 CHECK (nominal_value > 0)
);

INSERT INTO price (oid, product, territory, stats_type, year, nominal_value, data_source) VALUES (1, 55, 5611, 1, 2014, 120, 3);
INSERT INTO price (oid, product, territory, stats_type, year, nominal_value, data_source) VALUES (2, 55, 5611, 2, 2014, 160, 3);
INSERT INTO price (oid, product, territory, stats_type, year, nominal_value, data_source) VALUES (3, 55, 5615, 1, 2014, 60, 3);
INSERT INTO price (oid, product, territory, stats_type, year, nominal_value, data_source) VALUES (4, 55, 5611, 3, 2014, 180, 3);
INSERT INTO price (oid, product, territory, stats_type, year, nominal_value, data_source) VALUES (5, 62, 5615, 1, 2013, 1500, 3);
INSERT INTO price (oid, product, territory, stats_type, year, nominal_value, data_source) VALUES (6, 62, 5615, 2, 2013, 1300, 3);

The 'stats_type' labels are: 1 = minimum, 2 = average, 3 = maximum.

Looking at the first two rows; they are equal, except for 'oid', 'stats_type' and 'nominal_value'; so I'd like to verify: is 120 (which is labeled as minimum in the first row) smaller or equal than 160 (the value labeled as average)? In this case the answer is yes, so it passed the verification.

Now, the last two rows (5 and 6) also have a match regarding columns 'product', 'territory', 'year' and 'data_source'. However, it happens that the nominal value 1500 in row 5 should not be smaller than 1300 in row 6 (because 1500 was supposed to be minimum, and 1300 an average value).

How can I do this? Is it possible to accomplish such task using a 'check' constraint?

Answer 1

This cannot be done using a check constraint because of necessity of querying the table. You can use a trigger:

create or replace function price_trigger()
returns trigger language plpgsql as $$
begin
    if exists (
        select 1
        from price p
        where (p.product, p.territory, p.year, p.data_source) = 
              (new.product, new.territory, new.year, new.data_source)
        and (
            p.stats_type < new.stats_type and p.nominal_value > new.nominal_value
            or
            p.stats_type > new.stats_type and p.nominal_value < new.nominal_value
            )
        )
    then
        raise exception 'Nominal value error';
    end if;
    return new;
end $$;

create trigger price_trigger
before insert or update on price
for each row
execute procedure price_trigger();

The trigger function checks all conditions (min < avg < max).