PostgreSQL: Use column with hash based on other columns as unique index? 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?
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