Vorgestellt: RANGE-Typen und Exclusion-Constraints in PostgreSQL

Ich möchte an dieser Stelle mal auf ein tolles Feature in PostgreSQL 9.2 hinweisen: Range-Typen und Exclusion-Constraints. Diese Dinge gibt es so meines Wissens nach in keiner anderen der üblichen Datenbanken und ist damit wohl auch eher unbekannt.

Worum geht es: Mit Range-Typen kann man Bereiche von Daten abbilden. Natürlich kann man auch 2 Spalten definieren, nennen wir diese "von" und "bis". Als Datentyp z.B. INT oder DATE oder TIMESTAMP. Allerdings ist diese Darstellung unvollständig: sind die Werte inklusive oder exklusive? Interpretationssache.

In PostgreSQL kann man eine Spalte als RANGE definieren, die Range selbst wird so angegeben: '[1,5)'. In diesem Beispiel eine INT-Range, die von 1 inklusive (das [ steht für inklusive) bis 5 exklusive, mit dem ) markiert, reicht.


Exclusion-Constraints sind Constraints wie z.B. UNIQUE und diesem auch ähnlich. Aber ein Bereich 1-5 und ein anderer Bereich von 3-7 sind zwar UNIQUE, aber überschneiden sich. Ein Exclusion-Constraint erweitert als die Prüfung auf Gleichheit auf Prüfung auf 'Überlappung'.


Ein Beispiel: man stelle sich ein Hotel mit N Zimmern vor. Bei der Belegungsplanung will man natürlich verhindern, daß ein Zimmer doppelt (mehrfach) belegt wird, denn das gibt meist Ärger. Hier können wir beide oben genannten Feature sinnvoll einsetzen:

Code:

test=# create table hotel (zimmer int, belegt daterange, exclude using gist (zimmer with =, belegt with &&));
NOTICE:  CREATE TABLE / EXCLUDE will create implicit index "hotel_zimmer_belegt_excl" for table "hotel"
CREATE TABLE
Time: 13,338 ms
test=*# insert into hotel values (1, '[2013-01-01,2013-01-10)');
INSERT 0 1
Time: 0,415 ms
test=*# insert into hotel values (1, '[2013-01-08,2013-01-20)');
ERROR:  conflicting key value violates exclusion constraint "hotel_zimmer_belegt_excl"
DETAIL:  Key (zimmer, belegt)=(1, [2013-01-08,2013-01-20)) conflicts with existing key (zimmer, belegt)=(1, [2013-01-01,2013-01-10)).
Time: 0,383 ms

Das erste Select belegt Zimmer 1 vom 1. Januar bis zum 10. Das zweite Insert versucht, dieses Zimmer vom 8.1 bis zum 20.1. zu belegen -> FAIL!


Es gibt noch einige weitere sinnvolle Möglichkeiten, diese Features einzusetzen. Etwa für Preislisten, die je nach Menge (von 1-5 Stück, 5-10 Stück, ...) und Bestelldatum (Rabattaktion 5% vom---bis). Da Range-Typen auch Indexunterstützung haben und es dafür auch Operatoren wie z.B. Prüfung, ob Wert innerhalb/außerhalb einer Range liegt etc. lassen sich damit sehr einfach Dinge lösen, die ohne diese Features schwer bis unlösbar sind.

Cool, oder?


Andreas