Version 9.2 wird wahrscheinlich einen komplett neuen Datentyp mitbringen: Range Types.
Die Grundarbeit daran begann Jeff Davis ( Temporal PostgreSQL Roadmap « Experimental Thoughts ) schon 2010, und es gab da bereits Patches, die einen ‘Temporal’ - Typ bereitstellten. PostgreSQL 9.0 brachten einen weiteren wichtigen Baustein, und zwar
Exclusion Constraints. Wie bei PostgreSQL typisch wurde die gute Idee von Jeff, einen von ihm ‘temporal’ genannten Datentyp bereitzustellen verallgemeinert zu den Range Types, und damit von Datum- bzw. Zeitbereichen auf numerische (INT, FLOAT) Bereiche erweitert.
Wozu braucht man das?
Man stelle sich einen Shop vor, der N Artikel hat. Zu bestimmten Zeitbereichen gibt es unterschiedliche Preise (vermutlich werden Kreuzfahrtreisen demnächst billiger …), wer große Mengen eines Artikels kauft bekommt Rabatt. Dies will man nicht nur effektiv speichern, sondern auch schnell abfragen und möglichst auch noch sicherstellen, daß es keine Überschneidungen gibt. Man stelle sich vor, man bucht im Hotel die Suite und bei Anreise stellt man fest: diese wurde leider doppelt gebucht.
Genug der Worte, laßt uns die Doku lesen: PostgreSQL: Documentation: Manuals: Range Types und das mal praktisch testen!
Ich habe dazu mal was vorbereitet, und zwar diese Tabelle:
test=*# \d preisliste
Table "public.preisliste"
Column | Type | Modifiers
---------+---------------+-----------
artikel | integer |
gueltig | daterange |
menge | int4range |
preis | numeric(10,3) |
Indexes:
"preisliste_artikel_gueltig_menge_excl" EXCLUDE USING gist (artikel WITH =, gueltig WITH &&, menge WITH &&)
Anmerken muß ich, daß man dazu noch eine Extension erstellen muß:
create extension btree_gist;
Aber das ist seit 9.1 ja kein Problem mehr.
Also, mit dieser Tabelle und dem Exclude-Constraint möchte ich sicherstellen, daß es zu einem Artikel (hier nur eine ID) bei einer bestimmten Menge und zu einem bestimmten Kaufdatum nur maximal einen definierten Preis gibt. Ein Test:
test=# INSERT INTO preisliste (artikel, gueltig, menge, preis) VALUES (1, '[2012-01-01,2012-01-31)','[0,10)',10);
INSERT 0 1
test=*# INSERT INTO preisliste (artikel, gueltig, menge, preis) VALUES (1, '[2012-01-01,2012-01-31)','[5,15)',15);
ERROR: conflicting key value violates exclusion constraint "preisliste_artikel_gueltig_menge_excl"
DETAIL: Key (artikel, gueltig, menge)=(1, [2012-01-01,2012-01-31), [5,15)) conflicts with existing key (artikel, gueltig, menge)=(1, [2012-01-01,2012-01-31), [0,10)).
Super, denn ich kann nicht für ein und denselben Artikel und Datumsbereich sich überlappende Rabattmengen definieren. Ich lösche die Tabelle und fülle sie mit einigen Testdaten. Hier nutze ich ein weiteres cooles Feature: writeable Common Table Expressions, und einen seltenen Fall, wo ein CROSS JOIN sinnvoll ist:
WITH
artikel AS (
SELECT
a
FROM
generate_series(1,500) a
),
gueltig AS (
SELECT
daterange(
('2012-01-01'::date + s * '1 month'::interval)::date,
('2012-01-01'::date + (s + 1) * '1 month'::interval)::date
) AS gueltig
FROM
generate_series(0,11) s
),
menge AS (
SELECT
int4range(0 + m * 10, 9 + m *10) AS m
FROM
generate_series(0,10)m
)
INSERT INTO
preisliste
SELECT
artikel.a,
gueltig.gueltig,
menge.m,
random()*1000
FROM
artikel
CROSS JOIN gueltig
CROSS JOIN menge;
Das sind 66000 Datensätze, um mal ein paar anzuzeigen:
test=*# select * from preisliste limit 20 offset 4750;
artikel | gueltig | menge | preis
---------+-------------------------+-----------+---------
36 | [2012-12-01,2013-01-01) | [90,99) | 85.753
36 | [2012-12-01,2013-01-01) | [100,109) | 89.641
37 | [2012-01-01,2012-02-01) | [0,9) | 776.763
37 | [2012-01-01,2012-02-01) | [10,19) | 324.941
37 | [2012-01-01,2012-02-01) | [20,29) | 363.077
37 | [2012-01-01,2012-02-01) | [30,39) | 255.377
37 | [2012-01-01,2012-02-01) | [40,49) | 66.375
37 | [2012-01-01,2012-02-01) | [50,59) | 972.578
37 | [2012-01-01,2012-02-01) | [60,69) | 396.493
37 | [2012-01-01,2012-02-01) | [70,79) | 123.560
37 | [2012-01-01,2012-02-01) | [80,89) | 180.430
37 | [2012-01-01,2012-02-01) | [90,99) | 94.849
37 | [2012-01-01,2012-02-01) | [100,109) | 305.953
37 | [2012-02-01,2012-03-01) | [0,9) | 265.531
37 | [2012-02-01,2012-03-01) | [10,19) | 822.822
37 | [2012-02-01,2012-03-01) | [20,29) | 332.631
37 | [2012-02-01,2012-03-01) | [30,39) | 926.855
37 | [2012-02-01,2012-03-01) | [40,49) | 531.492
37 | [2012-02-01,2012-03-01) | [50,59) | 891.744
37 | [2012-02-01,2012-03-01) | [60,69) | 467.688
(20 rows)
Kurzer Test einer Abfrage:
test=*# SELECT * FROM preisliste WHERE artikel = 37 AND '2012-02-25'::date <@ gueltig AND 53 <@ menge;
artikel | gueltig | menge | preis
---------+-------------------------+---------+---------
37 | [2012-02-01,2012-03-01) | [50,59) | 891.744
(1 row)
Und dazu ein Explain Analayse:
test=*# EXPLAIN ANALYSE SELECT * FROM preisliste WHERE artikel = 37 AND '2012-02-25'::date <@ gueltig AND 53 <@ menge;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using preisliste_artikel_gueltig_menge_excl on preisliste (cost=0.00..8.32 rows=1 width=74) (actual time=0.040..0.057 rows=1 loops=1)
Index Cond: ((artikel = 37) AND ('2012-02-25'::date <@ gueltig) AND (53 <@ menge))
Total runtime: 0.106 ms
(3 rows)
Fazit: wir haben Datentypen, um Bereiche von Werten definieren zu können, wir haben Constraints um sich überschneidende Bereiche verhindern zu können, wir haben eine Reihe von Operatoren auf solche Bereiche und wir haben effektive Indexe auf solchen Bereichen.
Ich bin echt mal wieder von PostgreSQL begeistert!
Und wer davon nicht begeistert ist, setze sich hin und entwerfe dasselbe ohne Range Types und ohne Exclusion Constraints!
Andreas