Sophie

Sophie

distrib > Mageia > 6 > armv7hl > media > core-updates > by-pkgid > a8985c53237f42e0cfdfd17da0a53df3 > files > 578

postgresql9.4-docs-9.4.15-1.mga6.noarch.rpm

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>Range Types</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 9.4.15 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="Data Types"
HREF="datatype.html"><LINK
REL="PREVIOUS"
TITLE="Composite Types"
HREF="rowtypes.html"><LINK
REL="NEXT"
TITLE="Object Identifier Types"
HREF="datatype-oid.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=ISO-8859-1"><META
NAME="creation"
CONTENT="2017-11-10T00:43:05"></HEAD
><BODY
CLASS="SECT1"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="4"
ALIGN="center"
VALIGN="bottom"
><A
HREF="index.html"
>PostgreSQL 9.4.15 Documentation</A
></TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
TITLE="Composite Types"
HREF="rowtypes.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="datatype.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Chapter 8. Data Types</TD
><TD
WIDTH="20%"
ALIGN="right"
VALIGN="top"
><A
TITLE="Object Identifier Types"
HREF="datatype-oid.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="RANGETYPES"
>8.17. Range Types</A
></H1
><P
>  Range types are data types representing a range of values of some
  element type (called the range's <I
CLASS="FIRSTTERM"
>subtype</I
>).
  For instance, ranges
  of <TT
CLASS="TYPE"
>timestamp</TT
> might be used to represent the ranges of
  time that a meeting room is reserved. In this case the data type
  is <TT
CLASS="TYPE"
>tsrange</TT
> (short for <SPAN
CLASS="QUOTE"
>"timestamp range"</SPAN
>),
  and <TT
CLASS="TYPE"
>timestamp</TT
> is the subtype.  The subtype must have
  a total order so that it is well-defined whether element values are
  within, before, or after a range of values.
 </P
><P
>  Range types are useful because they represent many element values in a
  single range value, and because concepts such as overlapping ranges can
  be expressed clearly. The use of time and date ranges for scheduling
  purposes is the clearest example; but price ranges, measurement
  ranges from an instrument, and so forth can also be useful.
 </P
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="RANGETYPES-BUILTIN"
>8.17.1. Built-in Range Types</A
></H2
><P
>  PostgreSQL comes with the following built-in range types:
  <P
></P
></P><UL
><LI
><P
>       <TT
CLASS="TYPE"
>int4range</TT
> &mdash; Range of <TT
CLASS="TYPE"
>integer</TT
>
      </P
></LI
><LI
><P
>       <TT
CLASS="TYPE"
>int8range</TT
> &mdash; Range of <TT
CLASS="TYPE"
>bigint</TT
>
      </P
></LI
><LI
><P
>       <TT
CLASS="TYPE"
>numrange</TT
> &mdash; Range of <TT
CLASS="TYPE"
>numeric</TT
>
      </P
></LI
><LI
><P
>       <TT
CLASS="TYPE"
>tsrange</TT
> &mdash; Range of <TT
CLASS="TYPE"
>timestamp without time zone</TT
>
      </P
></LI
><LI
><P
>       <TT
CLASS="TYPE"
>tstzrange</TT
> &mdash; Range of <TT
CLASS="TYPE"
>timestamp with time zone</TT
>
      </P
></LI
><LI
><P
>       <TT
CLASS="TYPE"
>daterange</TT
> &mdash; Range of <TT
CLASS="TYPE"
>date</TT
>
      </P
></LI
></UL
><P>
  In addition, you can define your own range types;
  see <A
HREF="sql-createtype.html"
>CREATE TYPE</A
> for more information.
 </P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="RANGETYPES-EXAMPLES"
>8.17.2. Examples</A
></H2
><P
></P><PRE
CLASS="PROGRAMLISTING"
>CREATE TABLE reservation (room int, during tsrange);
INSERT INTO reservation VALUES
    (1108, '[2010-01-01 14:30, 2010-01-01 15:30)');

-- Containment
SELECT int4range(10, 20) @&#62; 3;

-- Overlaps
SELECT numrange(11.1, 22.2) &amp;&amp; numrange(20.0, 30.0);

-- Extract the upper bound
SELECT upper(int8range(15, 25));

-- Compute the intersection
SELECT int4range(10, 20) * int4range(15, 25);

-- Is the range empty?
SELECT isempty(numrange(1, 5));</PRE
><P>

   See <A
HREF="functions-range.html#RANGE-OPERATORS-TABLE"
>Table 9-47</A
>
   and <A
HREF="functions-range.html#RANGE-FUNCTIONS-TABLE"
>Table 9-48</A
> for complete lists of
   operators and functions on range types.
  </P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="RANGETYPES-INCLUSIVITY"
>8.17.3. Inclusive and Exclusive Bounds</A
></H2
><P
>   Every non-empty range has two bounds, the lower bound and the upper
   bound. All points between these values are included in the range. An
   inclusive bound means that the boundary point itself is included in
   the range as well, while an exclusive bound means that the boundary
   point is not included in the range.
  </P
><P
>   In the text form of a range, an inclusive lower bound is represented by
   <SPAN
CLASS="QUOTE"
>"<TT
CLASS="LITERAL"
>[</TT
>"</SPAN
> while an exclusive lower bound is
   represented by <SPAN
CLASS="QUOTE"
>"<TT
CLASS="LITERAL"
>(</TT
>"</SPAN
>. Likewise, an inclusive upper bound is represented by
   <SPAN
CLASS="QUOTE"
>"<TT
CLASS="LITERAL"
>]</TT
>"</SPAN
>, while an exclusive upper bound is
   represented by <SPAN
CLASS="QUOTE"
>"<TT
CLASS="LITERAL"
>)</TT
>"</SPAN
>.
   (See <A
HREF="rangetypes.html#RANGETYPES-IO"
>Section 8.17.5</A
> for more details.)
  </P
><P
>   The functions <TT
CLASS="LITERAL"
>lower_inc</TT
>
   and <TT
CLASS="LITERAL"
>upper_inc</TT
> test the inclusivity of the lower
   and upper bounds of a range value, respectively.
  </P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="RANGETYPES-INFINITE"
>8.17.4. Infinite (Unbounded) Ranges</A
></H2
><P
>   The lower bound of a range can be omitted, meaning that all points less
   than the upper bound are included in the range. Likewise, if the upper
   bound of the range is omitted, then all points greater than the lower bound
   are included in the range. If both lower and upper bounds are omitted, all
   values of the element type are considered to be in the range.
  </P
><P
>   This is equivalent to considering that the lower bound is <SPAN
CLASS="QUOTE"
>"minus
   infinity"</SPAN
>, or the upper bound is <SPAN
CLASS="QUOTE"
>"plus infinity"</SPAN
>,
   respectively.  But note that these infinite values are never values of
   the range's element type, and can never be part of the range.  (So there
   is no such thing as an inclusive infinite bound &mdash; if you try to
   write one, it will automatically be converted to an exclusive bound.)
  </P
><P
>   Also, some element types have a notion of <SPAN
CLASS="QUOTE"
>"infinity"</SPAN
>, but that
   is just another value so far as the range type mechanisms are concerned.
   For example, in timestamp ranges, <TT
CLASS="LITERAL"
>[today,]</TT
> means the same
   thing as <TT
CLASS="LITERAL"
>[today,)</TT
>.  But <TT
CLASS="LITERAL"
>[today,infinity]</TT
> means
   something different from <TT
CLASS="LITERAL"
>[today,infinity)</TT
> &mdash; the latter
   excludes the special <TT
CLASS="TYPE"
>timestamp</TT
> value <TT
CLASS="LITERAL"
>infinity</TT
>.
  </P
><P
>   The functions <TT
CLASS="LITERAL"
>lower_inf</TT
>
   and <TT
CLASS="LITERAL"
>upper_inf</TT
> test for infinite lower
   and upper bounds of a range, respectively.
  </P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="RANGETYPES-IO"
>8.17.5. Range Input/Output</A
></H2
><P
>   The input for a range value must follow one of the following patterns:
</P><PRE
CLASS="SYNOPSIS"
>(<TT
CLASS="REPLACEABLE"
><I
>lower-bound</I
></TT
>,<TT
CLASS="REPLACEABLE"
><I
>upper-bound</I
></TT
>)
(<TT
CLASS="REPLACEABLE"
><I
>lower-bound</I
></TT
>,<TT
CLASS="REPLACEABLE"
><I
>upper-bound</I
></TT
>]
[<TT
CLASS="REPLACEABLE"
><I
>lower-bound</I
></TT
>,<TT
CLASS="REPLACEABLE"
><I
>upper-bound</I
></TT
>)
[<TT
CLASS="REPLACEABLE"
><I
>lower-bound</I
></TT
>,<TT
CLASS="REPLACEABLE"
><I
>upper-bound</I
></TT
>]
empty</PRE
><P>
   The parentheses or brackets indicate whether the lower and upper bounds
   are exclusive or inclusive, as described previously.
   Notice that the final pattern is <TT
CLASS="LITERAL"
>empty</TT
>, which
   represents an empty range (a range that contains no points).
  </P
><P
>   The <TT
CLASS="REPLACEABLE"
><I
>lower-bound</I
></TT
> may be either a string
   that is valid input for the subtype, or empty to indicate no
   lower bound.  Likewise, <TT
CLASS="REPLACEABLE"
><I
>upper-bound</I
></TT
> may be
   either a string that is valid input for the subtype, or empty to
   indicate no upper bound.
  </P
><P
>   Each bound value can be quoted using <TT
CLASS="LITERAL"
>"</TT
> (double quote)
   characters.  This is necessary if the bound value contains parentheses,
   brackets, commas, double quotes, or backslashes, since these characters
   would otherwise be taken as part of the range syntax.  To put a double
   quote or backslash in a quoted bound value, precede it with a
   backslash. (Also, a pair of double quotes within a double-quoted bound
   value is taken to represent a double quote character, analogously to the
   rules for single quotes in SQL literal strings.) Alternatively, you can
   avoid quoting and use backslash-escaping to protect all data characters
   that would otherwise be taken as range syntax.  Also, to write a bound
   value that is an empty string, write <TT
CLASS="LITERAL"
>""</TT
>, since writing
   nothing means an infinite bound.
  </P
><P
>   Whitespace is allowed before and after the range value, but any whitespace
   between the parentheses or brackets is taken as part of the lower or upper
   bound value.  (Depending on the element type, it might or might not be
   significant.)
  </P
><DIV
CLASS="NOTE"
><BLOCKQUOTE
CLASS="NOTE"
><P
><B
>Note: </B
>    These rules are very similar to those for writing field values in
    composite-type literals.  See <A
HREF="rowtypes.html#ROWTYPES-IO-SYNTAX"
>Section 8.16.6</A
> for
    additional commentary.
   </P
></BLOCKQUOTE
></DIV
><P
>  Examples:
</P><PRE
CLASS="PROGRAMLISTING"
>-- includes 3, does not include 7, and does include all points in between
SELECT '[3,7)'::int4range;

-- does not include either 3 or 7, but includes all points in between
SELECT '(3,7)'::int4range;

-- includes only the single point 4
SELECT '[4,4]'::int4range;

-- includes no points (and will be normalized to 'empty')
SELECT '[4,4)'::int4range;</PRE
><P>
  </P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="RANGETYPES-CONSTRUCT"
>8.17.6. Constructing Ranges</A
></H2
><P
>   Each range type has a constructor function with the same name as the range
   type.  Using the constructor function is frequently more convenient than
   writing a range literal constant, since it avoids the need for extra
   quoting of the bound values.  The constructor function
   accepts two or three arguments.  The two-argument form constructs a range
   in standard form (lower bound inclusive, upper bound exclusive), while
   the three-argument form constructs a range with bounds of the form
   specified by the third argument.
   The third argument must be one of the strings
   <SPAN
CLASS="QUOTE"
>"<TT
CLASS="LITERAL"
>()</TT
>"</SPAN
>,
   <SPAN
CLASS="QUOTE"
>"<TT
CLASS="LITERAL"
>(]</TT
>"</SPAN
>,
   <SPAN
CLASS="QUOTE"
>"<TT
CLASS="LITERAL"
>[)</TT
>"</SPAN
>, or
   <SPAN
CLASS="QUOTE"
>"<TT
CLASS="LITERAL"
>[]</TT
>"</SPAN
>.
   For example:

</P><PRE
CLASS="PROGRAMLISTING"
>-- The full form is: lower bound, upper bound, and text argument indicating
-- inclusivity/exclusivity of bounds.
SELECT numrange(1.0, 14.0, '(]');

-- If the third argument is omitted, '[)' is assumed.
SELECT numrange(1.0, 14.0);

-- Although '(]' is specified here, on display the value will be converted to
-- canonical form, since int8range is a discrete range type (see below).
SELECT int8range(1, 14, '(]');

-- Using NULL for either bound causes the range to be unbounded on that side.
SELECT numrange(NULL, 2.2);</PRE
><P>
  </P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="RANGETYPES-DISCRETE"
>8.17.7. Discrete Range Types</A
></H2
><P
>   A discrete range is one whose element type has a well-defined
   <SPAN
CLASS="QUOTE"
>"step"</SPAN
>, such as <TT
CLASS="TYPE"
>integer</TT
> or <TT
CLASS="TYPE"
>date</TT
>.
   In these types two elements can be said to be adjacent, when there are
   no valid values between them.  This contrasts with continuous ranges,
   where it's always (or almost always) possible to identify other element
   values between two given values.  For example, a range over the
   <TT
CLASS="TYPE"
>numeric</TT
> type is continuous, as is a range over <TT
CLASS="TYPE"
>timestamp</TT
>.
   (Even though <TT
CLASS="TYPE"
>timestamp</TT
> has limited precision, and so could
   theoretically be treated as discrete, it's better to consider it continuous
   since the step size is normally not of interest.)
  </P
><P
>   Another way to think about a discrete range type is that there is a clear
   idea of a <SPAN
CLASS="QUOTE"
>"next"</SPAN
> or <SPAN
CLASS="QUOTE"
>"previous"</SPAN
> value for each element value.
   Knowing that, it is possible to convert between inclusive and exclusive
   representations of a range's bounds, by choosing the next or previous
   element value instead of the one originally given.
   For example, in an integer range type <TT
CLASS="LITERAL"
>[4,8]</TT
> and
   <TT
CLASS="LITERAL"
>(3,9)</TT
> denote the same set of values; but this would not be so
   for a range over numeric.
  </P
><P
>   A discrete range type should have a <I
CLASS="FIRSTTERM"
>canonicalization</I
>
   function that is aware of the desired step size for the element type.
   The canonicalization function is charged with converting equivalent values
   of the range type to have identical representations, in particular
   consistently inclusive or exclusive bounds.
   If a canonicalization function is not specified, then ranges with different
   formatting will always be treated as unequal, even though they might
   represent the same set of values in reality.
  </P
><P
>   The built-in range types <TT
CLASS="TYPE"
>int4range</TT
>, <TT
CLASS="TYPE"
>int8range</TT
>,
   and <TT
CLASS="TYPE"
>daterange</TT
> all use a canonical form that includes
   the lower bound and excludes the upper bound; that is,
   <TT
CLASS="LITERAL"
>[)</TT
>. User-defined range types can use other conventions,
   however.
  </P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="RANGETYPES-DEFINING"
>8.17.8. Defining New Range Types</A
></H2
><P
>   Users can define their own range types. The most common reason to do
   this is to use ranges over subtypes not provided among the built-in
   range types.
   For example, to define a new range type of subtype <TT
CLASS="TYPE"
>float8</TT
>:

</P><PRE
CLASS="PROGRAMLISTING"
>CREATE TYPE floatrange AS RANGE (
    subtype = float8,
    subtype_diff = float8mi
);

SELECT '[1.234, 5.678]'::floatrange;</PRE
><P>

   Because <TT
CLASS="TYPE"
>float8</TT
> has no meaningful
   <SPAN
CLASS="QUOTE"
>"step"</SPAN
>, we do not define a canonicalization
   function in this example.
  </P
><P
>   If the subtype is considered to have discrete rather than continuous
   values, the <TT
CLASS="COMMAND"
>CREATE TYPE</TT
> command should specify a
   <TT
CLASS="LITERAL"
>canonical</TT
> function.
   The canonicalization function takes an input range value, and must return
   an equivalent range value that may have different bounds and formatting.
   The canonical output for two ranges that represent the same set of values,
   for example the integer ranges <TT
CLASS="LITERAL"
>[1, 7]</TT
> and <TT
CLASS="LITERAL"
>[1,
   8)</TT
>, must be identical.  It doesn't matter which representation
   you choose to be the canonical one, so long as two equivalent values with
   different formattings are always mapped to the same value with the same
   formatting.  In addition to adjusting the inclusive/exclusive bounds
   format, a canonicalization function might round off boundary values, in
   case the desired step size is larger than what the subtype is capable of
   storing.  For instance, a range type over <TT
CLASS="TYPE"
>timestamp</TT
> could be
   defined to have a step size of an hour, in which case the canonicalization
   function would need to round off bounds that weren't a multiple of an hour,
   or perhaps throw an error instead.
  </P
><P
>   Defining your own range type also allows you to specify a different
   subtype B-tree operator class or collation to use, so as to change the sort
   ordering that determines which values fall into a given range.
  </P
><P
>   In addition, any range type that is meant to be used with GiST or SP-GiST indexes
   should define a subtype difference, or <TT
CLASS="LITERAL"
>subtype_diff</TT
>, function.
   (the index will still work without <TT
CLASS="LITERAL"
>subtype_diff</TT
>, but it is
   likely to be considerably less efficient than if a difference function is
   provided.)  The subtype difference function takes two input values of the
   subtype, and returns their difference (i.e., <TT
CLASS="REPLACEABLE"
><I
>X</I
></TT
> minus
   <TT
CLASS="REPLACEABLE"
><I
>Y</I
></TT
>) represented as a <TT
CLASS="TYPE"
>float8</TT
> value.  In our example
   above, the function that underlies the regular <TT
CLASS="TYPE"
>float8</TT
> minus
   operator can be used; but for any other subtype, some type conversion would
   be necessary.  Some creative thought about how to represent differences as
   numbers might be needed, too.  To the greatest extent possible, the
   <TT
CLASS="LITERAL"
>subtype_diff</TT
> function should agree with the sort ordering
   implied by the selected operator class and collation; that is, its result
   should be positive whenever its first argument is greater than its second
   according to the sort ordering.
  </P
><P
>   See <A
HREF="sql-createtype.html"
>CREATE TYPE</A
> for more information about creating
   range types.
  </P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="RANGETYPES-INDEXING"
>8.17.9. Indexing</A
></H2
><P
>   GiST and SP-GiST indexes can be created for table columns of range types.
   For instance, to create a GiST index:
</P><PRE
CLASS="PROGRAMLISTING"
>CREATE INDEX reservation_idx ON reservation USING gist (during);</PRE
><P>
   A GiST or SP-GiST index can accelerate queries involving these range operators:
   <TT
CLASS="LITERAL"
>=</TT
>,
   <TT
CLASS="LITERAL"
>&amp;&amp;</TT
>,
   <TT
CLASS="LITERAL"
>&lt;@</TT
>,
   <TT
CLASS="LITERAL"
>@&gt;</TT
>,
   <TT
CLASS="LITERAL"
>&lt;&lt;</TT
>,
   <TT
CLASS="LITERAL"
>&gt;&gt;</TT
>,
   <TT
CLASS="LITERAL"
>-|-</TT
>,
   <TT
CLASS="LITERAL"
>&amp;&lt;</TT
>, and
   <TT
CLASS="LITERAL"
>&amp;&gt;</TT
>
   (see <A
HREF="functions-range.html#RANGE-OPERATORS-TABLE"
>Table 9-47</A
> for more information).
  </P
><P
>   In addition, B-tree and hash indexes can be created for table columns of
   range types.  For these index types, basically the only useful range
   operation is equality.  There is a B-tree sort ordering defined for range
   values, with corresponding <TT
CLASS="LITERAL"
>&lt;</TT
> and <TT
CLASS="LITERAL"
>&gt;</TT
> operators,
   but the ordering is rather arbitrary and not usually useful in the real
   world.  Range types' B-tree and hash support is primarily meant to
   allow sorting and hashing internally in queries, rather than creation of
   actual indexes.
  </P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="RANGETYPES-CONSTRAINT"
>8.17.10. Constraints on Ranges</A
></H2
><P
>   While <TT
CLASS="LITERAL"
>UNIQUE</TT
> is a natural constraint for scalar
   values, it is usually unsuitable for range types. Instead, an
   exclusion constraint is often more appropriate
   (see <A
HREF="sql-createtable.html#SQL-CREATETABLE-EXCLUDE"
>CREATE TABLE
   ... CONSTRAINT ... EXCLUDE</A
>). Exclusion constraints allow the
   specification of constraints such as <SPAN
CLASS="QUOTE"
>"non-overlapping"</SPAN
> on a
   range type. For example:

</P><PRE
CLASS="PROGRAMLISTING"
>CREATE TABLE reservation (
    during tsrange,
    EXCLUDE USING gist (during WITH &amp;&amp;)
);</PRE
><P>

   That constraint will prevent any overlapping values from existing
   in the table at the same time:

</P><PRE
CLASS="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")).</PRE
><P>
  </P
><P
>   You can use the <A
HREF="btree-gist.html"
><TT
CLASS="LITERAL"
>btree_gist</TT
></A
>
   extension to define exclusion constraints on plain scalar data types, which
   can then be combined with range exclusions for maximum flexibility.  For
   example, after <TT
CLASS="LITERAL"
>btree_gist</TT
> is installed, the following
   constraint will reject overlapping ranges only if the meeting room numbers
   are equal:

</P><PRE
CLASS="PROGRAMLISTING"
>CREATE EXTENSION btree_gist;
CREATE TABLE room_reservation (
    room text,
    during tsrange,
    EXCLUDE USING gist (room WITH =, during WITH &amp;&amp;)
);

INSERT INTO room_reservation VALUES
    ('123A', '[2010-01-01 14:00, 2010-01-01 15:00)');
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)');
INSERT 0 1</PRE
><P>
  </P
></DIV
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="rowtypes.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="datatype-oid.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>Composite Types</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="datatype.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>Object Identifier Types</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>