0

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?

  • 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? – a_horse_with_no_name Jan 20 '15 at 10:36
  • 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. – Erik van de Ven Jan 20 '15 at 10:57
1

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

  • 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. – Erik van de Ven Jan 20 '15 at 12:54
  • @ErikVandeVen: Feike is not indexing null values. The expression replaces NULLs with something else. – a_horse_with_no_name Jan 20 '15 at 12:58
  • 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 Jan 20 '15 at 13:01
  • 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 :) – Erik van de Ven Jan 20 '15 at 13:13
0

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

  • 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. – Daniel Vérité Jan 20 '15 at 17:10
  • Ahaa very clever! ;) Yes indeed, probably I had to add a delimiter or something... Anyway, Feike's answer solved my problem so far ;) – Erik van de Ven Jan 21 '15 at 8:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.