NULL waardes in unique index PostgreSQL

[Storm90]

Iemand die me hiermee kan helpen? Ik heb de volgende tabel:

code:

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

Ik heb een unique index op 6 kolommen (zie onderstaand voorbeeld). price_old, price en price_alt kunnen NULL zijn, zolang price OF price_alt maar gevuld is (zie bovenstaand CONSTRAINT). Echter negeert SQL de unique constraint zodra één van de 6 kolommen NULL als waarde bevat. Dus onderstaande CONSTRAINT werkt dan niet meer:

code:

ALTER TABLE offers ADD CONSTRAINT offer_unique_index 
UNIQUE(product_id, price_old, price, price_alt, valid_from, valid_to);

Ik heb al een tip gekregen om naar het volgende voorbeeld te kijken (exclude using gist), maar ik zou niet weten hoe dit me kan helpen of hoe dit toe te passen.

code:

alter table item
  add constraint check_manufactured_range
  exclude using gist (type_id with =, manufactured_during with &&);

Iemand die kan helpen?

[NMe]

http://dba.stackexchange....onstraint-and-null-values

[Storm90]

NMe schreef op dinsdag 16 december 2014 @ 17:33:
http://dba.stackexchange....onstraint-and-null-values

Het probleem is in mijn geval dat ik meerdere kolommen heb die NULL kunnen zijn, dus vond ik het al lastiger om bovenstaand toe te passen. Of zou dit volgens jou ook moeten werken?:

code:

CREATE UNIQUE INDEX offer_unique_index ON offers (product_id, valid_from,valid_to) 
WHERE price IS NULL OR pice_old IS NULL OR price_alt IS NULL;

EDIT:
Bij bovenstaand krijg ik een error dat combinatie product_id, valid_from en valid_to al bestaat. Hij lijkt dus overige kolommen te negeren. Opzich wel logisch gezien ik op stackoverflow al voorbeelden zag dat wanneer 1 kolom NULL is, ze al 2 indexes plaatste. Eén check waar die gevuld is en één waar niet:

code:

CREATE UNIQUE INDEX favorites_3col_uni_idx
ON favorites (user_id, menu_id, recipe_id)
WHERE menu_id IS NOT NULL;

CREATE UNIQUE INDEX favorites_2col_uni_idx
ON favorites (user_id, recipe_id)
WHERE menu_id IS NULL;

Maar dan zou ik dus iets van 9 indexes moeten plaatsen?

[ Voor 38% gewijzigd door Storm90 op 16-12-2014 17:43 ]

[MSalters]

Mag ik overigens zeggen dat "price_alt" een anti-pattern is? En "text" als type maakt het al helemaal verdacht. Misschien is het beter om je database op te schonen dan om complexe hacks toe te passen.

[Storm90]

Mag ik overigens zeggen dat "price_alt" een anti-pattern is? En "text" als type maakt het al helemaal verdacht. Misschien is het beter om je database op te schonen dan om complexe hacks toe te passen.

Dat mag, en om hier kort een reden voor te geven (gezien dit niet het onderwerp is van het topic ), de tabel bevat data van een scraper. Misschien was NoSQL dan een betere oplossing geweest, who knows, maar dit zorgt er in ieder geval voor dat we alternatieve prijzen als "bieden", "teab" e.d. ook op kunnen slaan.

[MSalters]

Kijk, dat is dan geen verrassing. Netter is het om in zo'n situatie een simpelere tabel te hebben met "scrape results" en een tweede tabel met succesvol geinterpreteerde resultaten. De scrape results kunnen uiteraard text zijn.

Het gevolg van die opsplitsing in twee tabellen is dus dat je niet zulke complexe constraints/indices nodig hebt op een enkele tabel - en daarmee is het dus wel feitelijk het onderwerp van dit topic.

[Storm90]

Kijk, dat is dan geen verrassing. Netter is het om in zo'n situatie een simpelere tabel te hebben met "scrape results" en een tweede tabel met succesvol geinterpreteerde resultaten. De scrape results kunnen uiteraard text zijn.

Het gevolg van die opsplitsing in twee tabellen is dus dat je niet zulke complexe constraints/indices nodig hebt op een enkele tabel - en daarmee is het dus wel feitelijk het onderwerp van dit topic.

Bedankt, maar het beantwoord mijn eerste vraag niet. We hebben over de achterliggende structuur lang nagedacht en onze huidige database structuur is een bewuste keuze. Ik ga hier niet ons compleet concept en de achterliggende gedachte bespreken. Mijn vraag is duidelijk: Storm90 in "NULL waardes in unique index PostgreSQL"

Super dat je probeert mee te denken, maar ik zou graag tot een oplossing willen komen op mijn eerste vraag Dus als er iemand is die een antwoord heeft dan hoor ik dat graag en mocht hier geen oplossing voor zijn, dan hoor ik dat natuurlijk ook graag

[NMe]

Die oplossing heb ik al genoemd. Dat je die niet mooi vindt omdat je dan tig verschillende indexen moet maken is jammer, maar dat is te wijten aan die achterliggende structuur waar je lang over hebt nagedacht.

[cariolive23]

Je kunt met COALESCE() een NULL value vervangen door een door jou gekozen default value. Wellicht dat dit een werkbare oplossing is voor jou.

Voorbeeldje:

SQL:

SELECT 
  COALESCE(NULL::int, 0) AS a,
  COALESCE(1::int, 0) AS b;

Je kunt dit uiteraard ook toepassen in je unique constraint.

Ps. Dit wordt ook genoemd in het topic op Stankexchange

[ Voor 8% gewijzigd door cariolive23 op 01-01-2015 22:41 ]

[Storm90]

Bedankt @cariolive23! Ik snapte jouw oplossing in eerste instantie niet dus heb de vraag nogmaals op stack geplaatst , waar ditmaal iemand met eenzelfde oplossing kwam. Na alle documentatie gelezen te hebben is dat inderdaad de enige en beste oplossing. Zo ziet mijn unique index (dus geen constraint, werd mij aangeraden index te gebruiken en schijnbaar heeft dit slechts effect op de manier dat de records worden geïndexeerd, ik zie dus niet echt een verschil maar goed) er nu uit:

SQL:

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

Werkt perfect Heb geprobeerd eenzelfde record opnieuw toe te voegen en dan treed er inderdaad een unique violation error op.

[ Voor 22% gewijzigd door Storm90 op 20-01-2015 14:28 ]

[Cartman!]

De enige en beste oplossing blijft om je model te wijzigen ipv een lelijke constraint te maken. Je gaat vast vaker jezelf klem zetten hiermee.

[Gomez12]

Tja, ik hoop dat je het met koeieletters gedocumenteerd (maar wie kijkt er in de documentatie als er iets raars optreed )

Want ergens ooit gaat er iemand iets afrekenen voor -1 euro en dat kan 9x goed gaan, maar de 10e keer gaat je unique constraint ervoor zorgen dat dat record niet toegevoegd gaat worden. En dan wordt het happy hunting, want 0 of -2 kan je bijna oneindig opgeven (of een willekeurig ander getal als -1) maar -1 die gaat "soms" een unique constraint opleveren.

Als je dit soort vuile oplossingen al wil gebruiken dan zou ik het zeker niet in de index stouwen, daar is hij echt onvindbaar maar dan nog liever gewoon een afspraak maken dat -999999,99 == NULL voor deze tabel en dat geldt bij input en output (op deze tabel).
Dat vereist wat aanpassingen (en dan kan je beter voor ander datamodel gaan maargoed) in je programmatuur maar het voorkomt een hele, hele, heleboel gescheld in de toekomst omdat niemand kan vinden waarom het bij magische waarde -1 niet werkt en bij de rest wel, waarop als het ooit gevonden wordt de index verwijderd/normaalgezet wordt waardoor een half jaar daarna er weer een hele, hele, heleboel gescheld gaat komen want de unique constraint die is niet meer werkend.

Dit is een leuke verborgen oplossing voor een hobbyist, maar zakelijk gezien schiet ik zo ongeveer je knieschijven eruit als jij vuile oplossingen ook nog eens uit het gezichtsveld gaat plaatsen, een vuile oplossing kan je zo af en toe toepassen maar dan wel in het zicht zodat iedereen weet dat het vuil is en mensen niet denken dat het een foutje was.