postgres.git / summary / log / commit / refs
commit be6fcb671e4291654391fba475f4b555d2da74f3
Author: Jeff Davis <jdavis@postgresql.org>
Date: Thu Sep 05 06:30:27 2013 +0000
First released in: 9.4.0, 9.2.5, 9.3.1
Improve Range Types and Exclusion Constraints example.
Make the examples self-contained to avoid confusion. Per bug report
8367 from KOIZUMI Satoru.
doc/src/sgml/rangetypes.sgml | 18 +++++++++++-------
1 file changed, 11 insertions(+), 7 deletions(-)
diff --git a/doc/src/sgml/rangetypes.sgml b/doc/src/sgml/rangetypes.sgml
index 8dabc833e9e..d1125618b4a 100644
--- a/doc/src/sgml/rangetypes.sgml
+++ b/doc/src/sgml/rangetypes.sgml
@@ -451,7 +451,10 @@ CREATE INDEX reservation_idx ON reservation USING gist (during);
range type. For example:
<programlisting>
-ALTER TABLE reservation ADD EXCLUDE USING gist (during WITH &&);
+CREATE TABLE reservation (
+ during tsrange,
+ EXCLUDE USING gist (during WITH &&)
+);
</programlisting>
That constraint will prevent any overlapping values from existing
@@ -459,14 +462,14 @@ ALTER TABLE reservation ADD EXCLUDE USING gist (during WITH &&);
<programlisting>
INSERT INTO reservation VALUES
- (1108, '[2010-01-01 11:30, 2010-01-01 13:00)');
+ ('[2010-01-01 11:30, 2010-01-01 15:00)');
INSERT 0 1
INSERT INTO reservation VALUES
- (1108, '[2010-01-01 14:45, 2010-01-01 15:45)');
+ ('[2010-01-01 14:45, 2010-01-01 15:45)');
ERROR: conflicting key value violates exclusion constraint "reservation_during_excl"
-DETAIL: Key (during)=([ 2010-01-01 14:45:00, 2010-01-01 15:45:00 )) conflicts
-with existing key (during)=([ 2010-01-01 14:30:00, 2010-01-01 15:30:00 )).
+DETAIL: Key (during)=(["2010-01-01 14:45:00","2010-01-01 15:45:00")) conflicts
+with existing key (during)=(["2010-01-01 11:30:00","2010-01-01 15:00:00")).
</programlisting>
</para>
@@ -479,6 +482,7 @@ with existing key (during)=([ 2010-01-01 14:30:00, 2010-01-01 15:30:00 )).
are equal:
<programlisting>
+CREATE EXTENSION btree_gist;
CREATE TABLE room_reservation (
room text,
during tsrange,
@@ -492,8 +496,8 @@ INSERT 0 1
INSERT INTO room_reservation VALUES
('123A', '[2010-01-01 14:30, 2010-01-01 15:30)');
ERROR: conflicting key value violates exclusion constraint "room_reservation_room_during_excl"
-DETAIL: Key (room, during)=(123A, [ 2010-01-01 14:30:00, 2010-01-01 15:30:00 )) conflicts with
-existing key (room, during)=(123A, [ 2010-01-01 14:00:00, 2010-01-01 15:00:00 )).
+DETAIL: Key (room, during)=(123A, ["2010-01-01 14:30:00","2010-01-01 15:30:00")) conflicts
+with existing key (room, during)=(123A, ["2010-01-01 14:00:00","2010-01-01 15:00:00")).
INSERT INTO room_reservation VALUES
('123B', '[2010-01-01 14:30, 2010-01-01 15:30)');
[parent: f5c2f5a8f664]