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?

• user1822: What exactly are you trying to achieve with the index? Do you want to make sure that there is only a single product for each offer? Or that each product has only a single price? Or that you do not have overlapping intervals for valid_from, valid_to? Or that you do not have overlapping intervals with the same price? I also don't understand why price_alt is a character column? Why don't you store the alternate price as a number?
• Erik van de Ven: Yes exactly. The table contains scraped data, which means it sometimes can contain a value like "name a price" or "highest bid" and not an actually price. We know if price_alt is NULL we have to get our price from the price column and vice versa. So that's the reason I'm looking for the UID column solution.

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

• Erik van de Ven: I don't think that will work ;) According to the PostgreSQL documentation postgresql.org/docs/9.0/static/indexes-unique.html : "Null values are not considered equal.". Which counts for unique indexes and constraints.
• user1822: @ErikVandeVen: Feike is not indexing null values. The expression replaces NULLs with something else.
• Erik van de Ven: Sorry for that, just read the coalesce documentation... Uhm it sounds nice. Still very strange nobody thought about this, after I've discussed this problem on some other "big" forums as well... So I'm still a bit suspicious ;) But i'll try!
• Erik van de Ven: Ok, it works perfect! Thank you ;) Tried to add an existing row again (which contains NULL values) and I do receive an unique violation error :)

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

• Daniel Vérité: Are you sure the way you're using md5 with concat is correct to generate a unique key? It seems like you interpret product_id=123 and price_old=4 as identical to product_id=12 and price_old=34, since both produce 1234 where concatenated.
• Erik van de Ven: Ahaa very clever! ;) Yes indeed, probably I had to add a delimiter or something... Anyway, Feike's answer solved my problem so far ;)