Create a uniqueness index on multiple columns depending on the value to be added postgres 9.6-CodePudding

I am not sure if my query is executable after reading all the postgres documentation, but I want to ask if it is possible to write a uniqueness index, so that it considers unique not only those values that are EQUAL to existing ones, but for example if the date of a new record being added is less than or equal to existing records with an identical id ?

CREATE UNIQUE INDEX my_unique_index
    ON my_table (id, currentDate)
    WHERE currentDate < NEW.currentDate

So INSERT would behave like this:

INSERT INTO my_table (id, currentDate) VALUES 
(0, '12.21.2022'), - succefull added
(0, '12.24.2022'), - succefull added
(0, '12.23.2022'), - unique error
(1, '12.24.2022') - succefull added

I'm actually trying to do a much more complex uniqueness condition across multiple tables and with more conditions, but because of the problems even at the beginning of creating an index, I would like to know this first

CodePudding user response：

While you can make indexes on expressions, I don't think you can refer to other rows or make queries. Same with check constraints. You need a trigger, as the Postgresql check docs say...

If what you desire is a one-time check against other rows at row insertion, rather than a continuously-maintained consistency guarantee, a custom trigger can be used to implement that. (This approach avoids the dump/restore problem because pg_dump does not reinstall triggers until after restoring data, so that the check will not be enforced during a dump/restore.)

CodePudding user response：

No, this is not possible. Uniqueness is a criterion applied to a set of rows regardless of their order or insertion time, it does not distinguish a "new row" and compare it to "existing ones".

To achieve that, you would need to use a trigger that can check the inserted/updated row against the maximum date.

An alternative would be to use an exclusion constraint on a range of dates, and make each row that you insert cover the range since the last insert, preventing further rows to be inserted with a date before that.