I'm having a little problem right now, we use this table in our database:

   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);

It 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...

I don't know a good way to fix it. Probably multiple Unique indexes, but I cannot figure out right know how many and how to construct them. Can anybody help me out?

  • 1
    What is the goal of that index? Only having a single offer per product? Then you don't need to include the price columns at all. Unless you can have different prices for the same product for the same period (which I doubt). You probably want an exclusion constraint on valid_from, valid_to for the same product_id – a_horse_with_no_name Dec 16 '14 at 11:17
  • 1
    How does it "not work"? Does it permit entries you do not want permitted? Does it prohibit entries you want permitted? Please be specific. Also, remember that NULL = NULL is NULL. – Craig Ringer Dec 16 '14 at 14:27
  • 1
    Do you really allow two different prices in the same (from, to) interval? Say 42€ between 10:00 to 14:00 and 48€ between 11:00 to 13:00 (on the same day)? What is the price at 12:00 then? If you really allow that, this sounds like a terrible idea. – a_horse_with_no_name Dec 16 '14 at 15:05
  • 1
    Well, if it's not possible to have the same price at the same time, then you do want a unique index with an exclusion constraint as I mentioned. Did you go through the link from my second comment? As far as I can tell it is essentially the same thing as your question. – a_horse_with_no_name Dec 16 '14 at 15:26
  • 1
    I agree with @a_horse_with_no_name, the linked question is an identical problem and has the same solution. The various price columns should not be in the unique constraints anyway. – ypercubeᵀᴹ Dec 16 '14 at 15:38

Your Answer

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

Browse other questions tagged or ask your own question.