diff options
| author | Jeff Davis <jdavis@postgresql.org> | 2013-09-04 23:30:27 -0700 |
|---|---|---|
| committer | Jeff Davis <jdavis@postgresql.org> | 2013-09-04 23:30:27 -0700 |
| commit | be6fcb671e4291654391fba475f4b555d2da74f3 (patch) | |
| tree | 829ccb1301b22841fad6d0e2b180a9197294195e /doc/src | |
| parent | f5c2f5a8f6645a7da720a5f24efdbe8a1183a06c (diff) | |
| download | postgresql-be6fcb671e4291654391fba475f4b555d2da74f3.tar.gz postgresql-be6fcb671e4291654391fba475f4b555d2da74f3.zip | |
Improve Range Types and Exclusion Constraints example.
Make the examples self-contained to avoid confusion. Per bug report
8367 from KOIZUMI Satoru.
Diffstat (limited to 'doc/src')
| -rw-r--r-- | doc/src/sgml/rangetypes.sgml | 18 |
1 files 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)'); |