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:
Anmerken muß ich, daß man dazu noch eine Extension erstellen muß:Code: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 &&)
Aber das ist seit 9.1 ja kein Problem mehr.Code:create extension btree_gist;
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:
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:Code: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)).
Das sind 66000 Datensätze, um mal ein paar anzuzeigen:Code: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;
Kurzer Test einer Abfrage:Code: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)
Und dazu ein Explain Analayse:Code: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)
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.Code: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)
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
--
https://www.xing.com/profile/Andreas_Kretschmer7ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)
Links: Tobias Bauer - IT- und Funkdienstleistungen - Bilderrahmen-kaufen.de - Der Onlineshop für Bilderrahmen & Fotorahmen - Funkforum
Lesezeichen