PostgreSQL: Use column with hash based on other columns as unique index?

Question

My previous problem is still not fixed: We cannot set a unique index because NULL values are allowed...

we use this table in our database:

CREATE TABLE offer (
   offer_id   serial PRIMARY KEY
 , product_id int NOT NULL REFERENCES product
 , price_old  numeric(10,2);
 , price      numeric(10,2); 
 , price_alt  text          -- overrules price if present
 , valid_from timestamp NOT NULL
 , valid_to   timestamp     -- optional
 --   more attributes of the offer
 , CONSTRAINT some_kind_of_price_required
      CHECK (price IS NOT NULL OR price_alt IS NOT NULL)
);

And we use a unique index which doesn't work: ALTER TABLE offers ADD CONSTRAINT offer_unique_index UNIQUE(product_id, price_old, price, price_alt, valid_from, valid_to);

But the unique index doesn't work because price_old, price and price_alt can be NULL.... It's possible that only price is filled, only price and price_old or only price_alt...

Possible solution:

Now I might have a solution, but I don't know how to realize it exactly...

I thought if I could use NEWID() or MD5() to create an extra column named "uid" (which contains a hash based on the data of all other columns) and add a unique_index on that "uid" column, it could fix the problem. So when I try to insert the exact same values (prices, valid dates etc.), it will create a same hash (because the values are the same, obviously) and that triggers the unique index violation for the hash column.

Nice idea? But more important: possible?

Answer 1

PostgreSQL 15 and higher

For PostgreSQL 15 and higher, this is solved by having the NULLS NOT DISTINCT support for unique constraints:

For the purpose of a unique constraint, null values are not considered equal, unless NULLS NOT DISTINCT is specified. https://www.postgresql.org/docs/15/sql-createtable.html

ALTER TABLE offer
    ADD CONSTRAINT offer_unq
    UNIQUE NULLS NOT DISTINCT (
        product_id,
        price_old,
        price,
        price_alt,
        valid_from,
        valid_to
    );

PostgreSQL < 15:

You could use a UNIQUE INDEX instead of the UNIQUE CONSTRAINT using the function coalesce to treat null as a regular value for your uniqueness:

CREATE UNIQUE INDEX ON offer (
        product_id,
        coalesce(price_old,-1),
        coalesce(price,-1),
        coalesce(price_alt,''),
        valid_from,
        valid_to);

This will enforce your uniqueness as you described.

For details between a Unique constraint and a unique index,see: https://stackoverflow.com/questions/23542794/postgres-unique-constraint-vs-index

Answer 2

For now I fixed it this way:

We use a PostgreSQL function for inserting offers (or update when duplicated), so I changed it a bit (see the md5 method in VALUES)

CREATE OR REPLACE FUNCTION insert_record_offers(
    product_id_in integer,
    price_old_in numeric,
    price_in numeric,
    price_alt_in character varying,
    valid_from_in timestamp with time zone,
    valid_to_in timestamp with time zone)
  RETURNS integer AS
$BODY$
  DECLARE offerid int;
  BEGIN
    -- try to insert the record
    BEGIN
      INSERT INTO offers (product_id, price_old, price, price_alt, valid_from, valid_to, uid) 
                 VALUES (product_id_in,price_old_in, price_in, price_alt_in, valid_from_in, valid_to_in, 
                         md5(concat(product_id, price_old, price, price_alt, valid_from, valid_to)::TEXT))
      RETURNING id INTO offerid;
    EXCEPTION WHEN unique_violation THEN
      -- if there is a duplicate key exceptions
      -- update the record
      UPDATE offers SET price_old = price_old_in, price = price_in, price_alt = price_alt_in
      WHERE product_id = product_id_in AND price_old = price_old_in AND price = price_in AND price_alt = price_alt_in AND valid_from = valid_from_in AND valid_to = valid_to_in
      RETURNING id INTO offerid;
    END;
    RETURN offerid;
  END;
  $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION insert_record_offers(integer, numeric, numeric, character varying, timestamp with time zone, timestamp with time zone)
  OWNER TO postgres;

And I created an unique_index on the uid column inside the offers table:

ALTER TABLE offers
  ADD CONSTRAINT offer_unique_index UNIQUE(uid);

So everytime the postgresql function gets the same price and valid values, it will try to insert the same md5 hash and so will violate the unique constraint.

I don't think it's very nice to create such WHERE condition inside the EXCPTION WHEN unique_violation block, but I couldn't think of a solution where the INSERT INTO will pass the created md5 to the EXCEPTION block, so I'd be able to use a WHERE uid = <md5_hash> condition