On Tue, 2013-08-06 at 12:27 -0400, Tom Lane wrote:
> [email protected] writes:
> > In 8.17.10 "Constraints on Ranges" of PostgreSQL 9.2.4 Documentation, a
> > constraint will prevent the overlapping values in an example.
> > But in the example, range [2010-01-01 11:30, 2010-01-01 13:00) and range
> > [2010-01-01 14:45, 2010-01-01 15:45) do not overlap.
>
> No, but the second one overlaps with the range value shown in 8.17.2.
> So this is correct if it's understood as a continuation of that example.
> Still, it's kind of a long way from 8.17.2 to 8.17.10. It might be better
> if this section were recast to use a standalone example --- Jeff, what do
> you think?
That sounds reasonable, patch attached. Minor change, so if nobody has
any more suggestions I'll commit it tomorrow night.
Regards,
Jeff Davis
*** a/doc/src/sgml/rangetypes.sgml
--- b/doc/src/sgml/rangetypes.sgml
***************
*** 451,457 **** CREATE INDEX reservation_idx ON reservation USING gist (during);
range type. For example:
<programlisting>
! ALTER TABLE reservation ADD EXCLUDE USING gist (during WITH &&);
</programlisting>
That constraint will prevent any overlapping values from existing
--- 451,460 ----
range type. For example:
<programlisting>
! CREATE TABLE reservation (
! during tsrange,
! EXCLUDE USING gist (during WITH &&)
! );
</programlisting>
That constraint will prevent any overlapping values from existing
***************
*** 459,472 **** 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)');
INSERT 0 1
INSERT INTO reservation VALUES
! (1108, '[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 )).
</programlisting>
</para>
--- 462,475 ----
<programlisting>
INSERT INTO reservation VALUES
! ('[2010-01-01 11:30, 2010-01-01 15:00)');
INSERT 0 1
INSERT INTO reservation VALUES
! ('[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 11:30:00","2010-01-01 15:00:00")).
</programlisting>
</para>
***************
*** 479,484 **** with existing key (during)=([ 2010-01-01 14:30:00, 2010-01-01 15:30:00 )).
--- 482,488 ----
are equal:
<programlisting>
+ CREATE EXTENSION btree_gist;
CREATE TABLE room_reservation (
room text,
during tsrange,
***************
*** 492,499 **** 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 )).
INSERT INTO room_reservation VALUES
('123B', '[2010-01-01 14:30, 2010-01-01 15:30)');
--- 496,503 ----
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")).
INSERT INTO room_reservation VALUES
('123B', '[2010-01-01 14:30, 2010-01-01 15:30)');
--
Sent via pgsql-bugs mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
