Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes14kdownloads
rangetypes.html435 linesDownload Raw Back to html
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>8.17. Range Types</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="rowtypes.html" title="8.16. Composite Types" /><link rel="next" href="domains.html" title="8.18. Domain Types" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">8.17. Range Types</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="rowtypes.html" title="8.16. Composite Types">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="datatype.html" title="Chapter 8. Data Types">Up</a></td><th width="60%" align="center">Chapter 8. Data Types</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="domains.html" title="8.18. Domain Types">Next</a></td></tr></table><hr /></div><div class="sect1" id="RANGETYPES"><div class="titlepage"><div><div><h2 class="title" style="clear: both">8.17. Range Types <a href="#RANGETYPES" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="rangetypes.html#RANGETYPES-BUILTIN">8.17.1. Built-in Range and Multirange Types</a></span></dt><dt><span class="sect2"><a href="rangetypes.html#RANGETYPES-EXAMPLES">8.17.2. Examples</a></span></dt><dt><span class="sect2"><a href="rangetypes.html#RANGETYPES-INCLUSIVITY">8.17.3. Inclusive and Exclusive Bounds</a></span></dt><dt><span class="sect2"><a href="rangetypes.html#RANGETYPES-INFINITE">8.17.4. Infinite (Unbounded) Ranges</a></span></dt><dt><span class="sect2"><a href="rangetypes.html#RANGETYPES-IO">8.17.5. Range Input/Output</a></span></dt><dt><span class="sect2"><a href="rangetypes.html#RANGETYPES-CONSTRUCT">8.17.6. Constructing Ranges and Multiranges</a></span></dt><dt><span class="sect2"><a href="rangetypes.html#RANGETYPES-DISCRETE">8.17.7. Discrete Range Types</a></span></dt><dt><span class="sect2"><a href="rangetypes.html#RANGETYPES-DEFINING">8.17.8. Defining New Range Types</a></span></dt><dt><span class="sect2"><a href="rangetypes.html#RANGETYPES-INDEXING">8.17.9. Indexing</a></span></dt><dt><span class="sect2"><a href="rangetypes.html#RANGETYPES-CONSTRAINT">8.17.10. Constraints on Ranges</a></span></dt></dl></div><a id="id-1.5.7.25.2" class="indexterm"></a><a id="id-1.5.7.25.3" class="indexterm"></a><p>3  Range types are data types representing a range of values of some4  element type (called the range's <em class="firstterm">subtype</em>).5  For instance, ranges6  of <code class="type">timestamp</code> might be used to represent the ranges of7  time that a meeting room is reserved. In this case the data type8  is <code class="type">tsrange</code> (short for <span class="quote">“<span class="quote">timestamp range</span>”</span>),9  and <code class="type">timestamp</code> is the subtype.  The subtype must have10  a total order so that it is well-defined whether element values are11  within, before, or after a range of values.12 </p><p>13  Range types are useful because they represent many element values in a14  single range value, and because concepts such as overlapping ranges can15  be expressed clearly. The use of time and date ranges for scheduling16  purposes is the clearest example; but price ranges, measurement17  ranges from an instrument, and so forth can also be useful.18 </p><p>19  Every range type has a corresponding multirange type. A multirange is20  an ordered list of non-contiguous, non-empty, non-null ranges. Most21  range operators also work on multiranges, and they have a few functions22  of their own.23 </p><div class="sect2" id="RANGETYPES-BUILTIN"><div class="titlepage"><div><div><h3 class="title">8.17.1. Built-in Range and Multirange Types <a href="#RANGETYPES-BUILTIN" class="id_link">#</a></h3></div></div></div><p>24  PostgreSQL comes with the following built-in range types:25  </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>26       <code class="type">int4range</code> — Range of <code class="type">integer</code>,27       <code class="type">int4multirange</code> — corresponding Multirange28      </p></li><li class="listitem"><p>29       <code class="type">int8range</code> — Range of <code class="type">bigint</code>,30       <code class="type">int8multirange</code> — corresponding Multirange31      </p></li><li class="listitem"><p>32       <code class="type">numrange</code> — Range of <code class="type">numeric</code>,33       <code class="type">nummultirange</code> — corresponding Multirange34      </p></li><li class="listitem"><p>35       <code class="type">tsrange</code> — Range of <code class="type">timestamp without time zone</code>,36       <code class="type">tsmultirange</code> — corresponding Multirange37      </p></li><li class="listitem"><p>38       <code class="type">tstzrange</code> — Range of <code class="type">timestamp with time zone</code>,39       <code class="type">tstzmultirange</code> — corresponding Multirange40      </p></li><li class="listitem"><p>41       <code class="type">daterange</code> — Range of <code class="type">date</code>,42       <code class="type">datemultirange</code> — corresponding Multirange43      </p></li></ul></div><p>44  In addition, you can define your own range types;45  see <a class="xref" href="sql-createtype.html" title="CREATE TYPE"><span class="refentrytitle">CREATE TYPE</span></a> for more information.46 </p></div><div class="sect2" id="RANGETYPES-EXAMPLES"><div class="titlepage"><div><div><h3 class="title">8.17.2. Examples <a href="#RANGETYPES-EXAMPLES" class="id_link">#</a></h3></div></div></div><p>47</p><pre class="programlisting">48CREATE TABLE reservation (room int, during tsrange);49INSERT INTO reservation VALUES50    (1108, '[2010-01-01 14:30, 2010-01-01 15:30)');51 52-- Containment53SELECT int4range(10, 20) @&gt; 3;54 55-- Overlaps56SELECT numrange(11.1, 22.2) &amp;&amp; numrange(20.0, 30.0);57 58-- Extract the upper bound59SELECT upper(int8range(15, 25));60 61-- Compute the intersection62SELECT int4range(10, 20) * int4range(15, 25);63 64-- Is the range empty?65SELECT isempty(numrange(1, 5));66</pre><p>67 68   See <a class="xref" href="functions-range.html#RANGE-OPERATORS-TABLE" title="Table 9.55. Range Operators">Table 9.55</a>69   and <a class="xref" href="functions-range.html#RANGE-FUNCTIONS-TABLE" title="Table 9.57. Range Functions">Table 9.57</a> for complete lists of70   operators and functions on range types.71  </p></div><div class="sect2" id="RANGETYPES-INCLUSIVITY"><div class="titlepage"><div><div><h3 class="title">8.17.3. Inclusive and Exclusive Bounds <a href="#RANGETYPES-INCLUSIVITY" class="id_link">#</a></h3></div></div></div><p>72   Every non-empty range has two bounds, the lower bound and the upper73   bound. All points between these values are included in the range. An74   inclusive bound means that the boundary point itself is included in75   the range as well, while an exclusive bound means that the boundary76   point is not included in the range.77  </p><p>78   In the text form of a range, an inclusive lower bound is represented by79   <span class="quote">“<span class="quote"><code class="literal">[</code></span>”</span> while an exclusive lower bound is80   represented by <span class="quote">“<span class="quote"><code class="literal">(</code></span>”</span>. Likewise, an inclusive upper bound is represented by81   <span class="quote">“<span class="quote"><code class="literal">]</code></span>”</span>, while an exclusive upper bound is82   represented by <span class="quote">“<span class="quote"><code class="literal">)</code></span>”</span>.83   (See <a class="xref" href="rangetypes.html#RANGETYPES-IO" title="8.17.5. Range Input/Output">Section 8.17.5</a> for more details.)84  </p><p>85   The functions <code class="literal">lower_inc</code>86   and <code class="literal">upper_inc</code> test the inclusivity of the lower87   and upper bounds of a range value, respectively.88  </p></div><div class="sect2" id="RANGETYPES-INFINITE"><div class="titlepage"><div><div><h3 class="title">8.17.4. Infinite (Unbounded) Ranges <a href="#RANGETYPES-INFINITE" class="id_link">#</a></h3></div></div></div><p>89   The lower bound of a range can be omitted, meaning that all90   values less than the upper bound are included in the range, e.g.,91   <code class="literal">(,3]</code>. Likewise, if the upper bound of the range92   is omitted, then all values greater than the lower bound are included93   in the range. If both lower and upper bounds are omitted, all values94   of the element type are considered to be in the range.  Specifying a95   missing bound as inclusive is automatically converted to exclusive,96   e.g., <code class="literal">[,]</code> is converted to <code class="literal">(,)</code>.97   You can think of these missing values as +/-infinity, but they are98   special range type values and are considered to be beyond any range99   element type's +/-infinity values.100  </p><p>101   Element types that have the notion of <span class="quote">“<span class="quote">infinity</span>”</span> can102   use them as explicit bound values.  For example, with timestamp103   ranges, <code class="literal">[today,infinity)</code> excludes the special104   <code class="type">timestamp</code> value <code class="literal">infinity</code>,105   while <code class="literal">[today,infinity]</code> include it, as does106   <code class="literal">[today,)</code> and <code class="literal">[today,]</code>.107  </p><p>108   The functions <code class="literal">lower_inf</code>109   and <code class="literal">upper_inf</code> test for infinite lower110   and upper bounds of a range, respectively.111  </p></div><div class="sect2" id="RANGETYPES-IO"><div class="titlepage"><div><div><h3 class="title">8.17.5. Range Input/Output <a href="#RANGETYPES-IO" class="id_link">#</a></h3></div></div></div><p>112   The input for a range value must follow one of the following patterns:113</p><pre class="synopsis">114(<em class="replaceable"><code>lower-bound</code></em>,<em class="replaceable"><code>upper-bound</code></em>)115(<em class="replaceable"><code>lower-bound</code></em>,<em class="replaceable"><code>upper-bound</code></em>]116[<em class="replaceable"><code>lower-bound</code></em>,<em class="replaceable"><code>upper-bound</code></em>)117[<em class="replaceable"><code>lower-bound</code></em>,<em class="replaceable"><code>upper-bound</code></em>]118empty119</pre><p>120   The parentheses or brackets indicate whether the lower and upper bounds121   are exclusive or inclusive, as described previously.122   Notice that the final pattern is <code class="literal">empty</code>, which123   represents an empty range (a range that contains no points).124  </p><p>125   The <em class="replaceable"><code>lower-bound</code></em> may be either a string126   that is valid input for the subtype, or empty to indicate no127   lower bound.  Likewise, <em class="replaceable"><code>upper-bound</code></em> may be128   either a string that is valid input for the subtype, or empty to129   indicate no upper bound.130  </p><p>131   Each bound value can be quoted using <code class="literal">"</code> (double quote)132   characters.  This is necessary if the bound value contains parentheses,133   brackets, commas, double quotes, or backslashes, since these characters134   would otherwise be taken as part of the range syntax.  To put a double135   quote or backslash in a quoted bound value, precede it with a136   backslash. (Also, a pair of double quotes within a double-quoted bound137   value is taken to represent a double quote character, analogously to the138   rules for single quotes in SQL literal strings.) Alternatively, you can139   avoid quoting and use backslash-escaping to protect all data characters140   that would otherwise be taken as range syntax.  Also, to write a bound141   value that is an empty string, write <code class="literal">""</code>, since writing142   nothing means an infinite bound.143  </p><p>144   Whitespace is allowed before and after the range value, but any whitespace145   between the parentheses or brackets is taken as part of the lower or upper146   bound value.  (Depending on the element type, it might or might not be147   significant.)148  </p><div class="note"><h3 class="title">Note</h3><p>149    These rules are very similar to those for writing field values in150    composite-type literals.  See <a class="xref" href="rowtypes.html#ROWTYPES-IO-SYNTAX" title="8.16.6. Composite Type Input and Output Syntax">Section 8.16.6</a> for151    additional commentary.152   </p></div><p>153  Examples:154</p><pre class="programlisting">155-- includes 3, does not include 7, and does include all points in between156SELECT '[3,7)'::int4range;157 158-- does not include either 3 or 7, but includes all points in between159SELECT '(3,7)'::int4range;160 161-- includes only the single point 4162SELECT '[4,4]'::int4range;163 164-- includes no points (and will be normalized to 'empty')165SELECT '[4,4)'::int4range;166</pre><p>167  </p><p>168   The input for a multirange is curly brackets (<code class="literal">{</code> and169   <code class="literal">}</code>) containing zero or more valid ranges,170   separated by commas. Whitespace is permitted around the brackets and171   commas. This is intended to be reminiscent of array syntax, although172   multiranges are much simpler: they have just one dimension and there is173   no need to quote their contents. (The bounds of their ranges may be174   quoted as above however.)175  </p><p>176  Examples:177</p><pre class="programlisting">178SELECT '{}'::int4multirange;179SELECT '{[3,7)}'::int4multirange;180SELECT '{[3,7), [8,9)}'::int4multirange;181</pre><p>182  </p></div><div class="sect2" id="RANGETYPES-CONSTRUCT"><div class="titlepage"><div><div><h3 class="title">8.17.6. Constructing Ranges and Multiranges <a href="#RANGETYPES-CONSTRUCT" class="id_link">#</a></h3></div></div></div><p>183   Each range type has a constructor function with the same name as the range184   type.  Using the constructor function is frequently more convenient than185   writing a range literal constant, since it avoids the need for extra186   quoting of the bound values.  The constructor function187   accepts two or three arguments.  The two-argument form constructs a range188   in standard form (lower bound inclusive, upper bound exclusive), while189   the three-argument form constructs a range with bounds of the form190   specified by the third argument.191   The third argument must be one of the strings192   <span class="quote">“<span class="quote"><code class="literal">()</code></span>”</span>,193   <span class="quote">“<span class="quote"><code class="literal">(]</code></span>”</span>,194   <span class="quote">“<span class="quote"><code class="literal">[)</code></span>”</span>, or195   <span class="quote">“<span class="quote"><code class="literal">[]</code></span>”</span>.196   For example:197 198</p><pre class="programlisting">199-- The full form is: lower bound, upper bound, and text argument indicating200-- inclusivity/exclusivity of bounds.201SELECT numrange(1.0, 14.0, '(]');202 203-- If the third argument is omitted, '[)' is assumed.204SELECT numrange(1.0, 14.0);205 206-- Although '(]' is specified here, on display the value will be converted to207-- canonical form, since int8range is a discrete range type (see below).208SELECT int8range(1, 14, '(]');209 210-- Using NULL for either bound causes the range to be unbounded on that side.211SELECT numrange(NULL, 2.2);212</pre><p>213  </p><p>214   Each range type also has a multirange constructor with the same name as the215   multirange type.  The constructor function takes zero or more arguments216   which are all ranges of the appropriate type.217   For example:218 219</p><pre class="programlisting">220SELECT nummultirange();221SELECT nummultirange(numrange(1.0, 14.0));222SELECT nummultirange(numrange(1.0, 14.0), numrange(20.0, 25.0));223</pre><p>224  </p></div><div class="sect2" id="RANGETYPES-DISCRETE"><div class="titlepage"><div><div><h3 class="title">8.17.7. Discrete Range Types <a href="#RANGETYPES-DISCRETE" class="id_link">#</a></h3></div></div></div><p>225   A discrete range is one whose element type has a well-defined226   <span class="quote">“<span class="quote">step</span>”</span>, such as <code class="type">integer</code> or <code class="type">date</code>.227   In these types two elements can be said to be adjacent, when there are228   no valid values between them.  This contrasts with continuous ranges,229   where it's always (or almost always) possible to identify other element230   values between two given values.  For example, a range over the231   <code class="type">numeric</code> type is continuous, as is a range over <code class="type">timestamp</code>.232   (Even though <code class="type">timestamp</code> has limited precision, and so could233   theoretically be treated as discrete, it's better to consider it continuous234   since the step size is normally not of interest.)235  </p><p>236   Another way to think about a discrete range type is that there is a clear237   idea of a <span class="quote">“<span class="quote">next</span>”</span> or <span class="quote">“<span class="quote">previous</span>”</span> value for each element value.238   Knowing that, it is possible to convert between inclusive and exclusive239   representations of a range's bounds, by choosing the next or previous240   element value instead of the one originally given.241   For example, in an integer range type <code class="literal">[4,8]</code> and242   <code class="literal">(3,9)</code> denote the same set of values; but this would not be so243   for a range over numeric.244  </p><p>245   A discrete range type should have a <em class="firstterm">canonicalization</em>246   function that is aware of the desired step size for the element type.247   The canonicalization function is charged with converting equivalent values248   of the range type to have identical representations, in particular249   consistently inclusive or exclusive bounds.250   If a canonicalization function is not specified, then ranges with different251   formatting will always be treated as unequal, even though they might252   represent the same set of values in reality.253  </p><p>254   The built-in range types <code class="type">int4range</code>, <code class="type">int8range</code>,255   and <code class="type">daterange</code> all use a canonical form that includes256   the lower bound and excludes the upper bound; that is,257   <code class="literal">[)</code>. User-defined range types can use other conventions,258   however.259  </p></div><div class="sect2" id="RANGETYPES-DEFINING"><div class="titlepage"><div><div><h3 class="title">8.17.8. Defining New Range Types <a href="#RANGETYPES-DEFINING" class="id_link">#</a></h3></div></div></div><p>260   Users can define their own range types. The most common reason to do261   this is to use ranges over subtypes not provided among the built-in262   range types.263   For example, to define a new range type of subtype <code class="type">float8</code>:264 265</p><pre class="programlisting">266CREATE TYPE floatrange AS RANGE (267    subtype = float8,268    subtype_diff = float8mi269);270 271SELECT '[1.234, 5.678]'::floatrange;272</pre><p>273 274   Because <code class="type">float8</code> has no meaningful275   <span class="quote">“<span class="quote">step</span>”</span>, we do not define a canonicalization276   function in this example.277  </p><p>278   When you define your own range you automatically get a corresponding279   multirange type.280  </p><p>281   Defining your own range type also allows you to specify a different282   subtype B-tree operator class or collation to use, so as to change the sort283   ordering that determines which values fall into a given range.284  </p><p>285   If the subtype is considered to have discrete rather than continuous286   values, the <code class="command">CREATE TYPE</code> command should specify a287   <code class="literal">canonical</code> function.288   The canonicalization function takes an input range value, and must return289   an equivalent range value that may have different bounds and formatting.290   The canonical output for two ranges that represent the same set of values,291   for example the integer ranges <code class="literal">[1, 7]</code> and <code class="literal">[1,292   8)</code>, must be identical.  It doesn't matter which representation293   you choose to be the canonical one, so long as two equivalent values with294   different formattings are always mapped to the same value with the same295   formatting.  In addition to adjusting the inclusive/exclusive bounds296   format, a canonicalization function might round off boundary values, in297   case the desired step size is larger than what the subtype is capable of298   storing.  For instance, a range type over <code class="type">timestamp</code> could be299   defined to have a step size of an hour, in which case the canonicalization300   function would need to round off bounds that weren't a multiple of an hour,301   or perhaps throw an error instead.302  </p><p>303   In addition, any range type that is meant to be used with GiST or SP-GiST304   indexes should define a subtype difference, or <code class="literal">subtype_diff</code>,305   function.  (The index will still work without <code class="literal">subtype_diff</code>,306   but it is likely to be considerably less efficient than if a difference307   function is provided.)  The subtype difference function takes two input308   values of the subtype, and returns their difference309   (i.e., <em class="replaceable"><code>X</code></em> minus <em class="replaceable"><code>Y</code></em>) represented as310   a <code class="type">float8</code> value.  In our example above, the311   function <code class="function">float8mi</code> that underlies the regular <code class="type">float8</code>312   minus operator can be used; but for any other subtype, some type313   conversion would be necessary.  Some creative thought about how to314   represent differences as numbers might be needed, too.  To the greatest315   extent possible, the <code class="literal">subtype_diff</code> function should agree with316   the sort ordering implied by the selected operator class and collation;317   that is, its result should be positive whenever its first argument is318   greater than its second according to the sort ordering.319  </p><p>320   A less-oversimplified example of a <code class="literal">subtype_diff</code> function is:321  </p><pre class="programlisting">322CREATE FUNCTION time_subtype_diff(x time, y time) RETURNS float8 AS323'SELECT EXTRACT(EPOCH FROM (x - y))' LANGUAGE sql STRICT IMMUTABLE;324 325CREATE TYPE timerange AS RANGE (326    subtype = time,327    subtype_diff = time_subtype_diff328);329 330SELECT '[11:10, 23:00]'::timerange;331</pre><p>332   See <a class="xref" href="sql-createtype.html" title="CREATE TYPE"><span class="refentrytitle">CREATE TYPE</span></a> for more information about creating333   range types.334  </p></div><div class="sect2" id="RANGETYPES-INDEXING"><div class="titlepage"><div><div><h3 class="title">8.17.9. Indexing <a href="#RANGETYPES-INDEXING" class="id_link">#</a></h3></div></div></div><a id="id-1.5.7.25.15.2" class="indexterm"></a><p>335   GiST and SP-GiST indexes can be created for table columns of range types.336   GiST indexes can be also created for table columns of multirange types.337   For instance, to create a GiST index:338</p><pre class="programlisting">339CREATE INDEX reservation_idx ON reservation USING GIST (during);340</pre><p>341   A GiST or SP-GiST index on ranges can accelerate queries involving these342   range operators:343   <code class="literal">=</code>,344   <code class="literal">&amp;&amp;</code>,345   <code class="literal">&lt;@</code>,346   <code class="literal">@&gt;</code>,347   <code class="literal">&lt;&lt;</code>,348   <code class="literal">&gt;&gt;</code>,349   <code class="literal">-|-</code>,350   <code class="literal">&amp;&lt;</code>, and351   <code class="literal">&amp;&gt;</code>.352   A GiST index on multiranges can accelerate queries involving the same353   set of multirange operators.354   A GiST index on ranges and GiST index on multiranges can also accelerate355   queries involving these cross-type range to multirange and multirange to356   range operators correspondingly:357   <code class="literal">&amp;&amp;</code>,358   <code class="literal">&lt;@</code>,359   <code class="literal">@&gt;</code>,360   <code class="literal">&lt;&lt;</code>,361   <code class="literal">&gt;&gt;</code>,362   <code class="literal">-|-</code>,363   <code class="literal">&amp;&lt;</code>, and364   <code class="literal">&amp;&gt;</code>.365   See <a class="xref" href="functions-range.html#RANGE-OPERATORS-TABLE" title="Table 9.55. Range Operators">Table 9.55</a> for more information.366  </p><p>367   In addition, B-tree and hash indexes can be created for table columns of368   range types.  For these index types, basically the only useful range369   operation is equality.  There is a B-tree sort ordering defined for range370   values, with corresponding <code class="literal">&lt;</code> and <code class="literal">&gt;</code> operators,371   but the ordering is rather arbitrary and not usually useful in the real372   world.  Range types' B-tree and hash support is primarily meant to373   allow sorting and hashing internally in queries, rather than creation of374   actual indexes.375  </p></div><div class="sect2" id="RANGETYPES-CONSTRAINT"><div class="titlepage"><div><div><h3 class="title">8.17.10. Constraints on Ranges <a href="#RANGETYPES-CONSTRAINT" class="id_link">#</a></h3></div></div></div><a id="id-1.5.7.25.16.2" class="indexterm"></a><p>376   While <code class="literal">UNIQUE</code> is a natural constraint for scalar377   values, it is usually unsuitable for range types. Instead, an378   exclusion constraint is often more appropriate379   (see <a class="link" href="sql-createtable.html#SQL-CREATETABLE-EXCLUDE">CREATE TABLE380   ... CONSTRAINT ... EXCLUDE</a>). Exclusion constraints allow the381   specification of constraints such as <span class="quote">“<span class="quote">non-overlapping</span>”</span> on a382   range type. For example:383 384</p><pre class="programlisting">385CREATE TABLE reservation (386    during tsrange,387    EXCLUDE USING GIST (during WITH &amp;&amp;)388);389</pre><p>390 391   That constraint will prevent any overlapping values from existing392   in the table at the same time:393 394</p><pre class="programlisting">395INSERT INTO reservation VALUES396    ('[2010-01-01 11:30, 2010-01-01 15:00)');397INSERT 0 1398 399INSERT INTO reservation VALUES400    ('[2010-01-01 14:45, 2010-01-01 15:45)');401ERROR:  conflicting key value violates exclusion constraint "reservation_during_excl"402DETAIL:  Key (during)=(["2010-01-01 14:45:00","2010-01-01 15:45:00")) conflicts403with existing key (during)=(["2010-01-01 11:30:00","2010-01-01 15:00:00")).404</pre><p>405  </p><p>406   You can use the <a class="link" href="btree-gist.html" title="F.9. btree_gist — GiST operator classes with B-tree behavior"><code class="literal">btree_gist</code></a>407   extension to define exclusion constraints on plain scalar data types, which408   can then be combined with range exclusions for maximum flexibility.  For409   example, after <code class="literal">btree_gist</code> is installed, the following410   constraint will reject overlapping ranges only if the meeting room numbers411   are equal:412 413</p><pre class="programlisting">414CREATE EXTENSION btree_gist;415CREATE TABLE room_reservation (416    room text,417    during tsrange,418    EXCLUDE USING GIST (room WITH =, during WITH &amp;&amp;)419);420 421INSERT INTO room_reservation VALUES422    ('123A', '[2010-01-01 14:00, 2010-01-01 15:00)');423INSERT 0 1424 425INSERT INTO room_reservation VALUES426    ('123A', '[2010-01-01 14:30, 2010-01-01 15:30)');427ERROR:  conflicting key value violates exclusion constraint "room_reservation_room_during_excl"428DETAIL:  Key (room, during)=(123A, ["2010-01-01 14:30:00","2010-01-01 15:30:00")) conflicts429with existing key (room, during)=(123A, ["2010-01-01 14:00:00","2010-01-01 15:00:00")).430 431INSERT INTO room_reservation VALUES432    ('123B', '[2010-01-01 14:30, 2010-01-01 15:30)');433INSERT 0 1434</pre><p>435  </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="rowtypes.html" title="8.16. Composite Types">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="datatype.html" title="Chapter 8. Data Types">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="domains.html" title="8.18. Domain Types">Next</a></td></tr><tr><td width="40%" align="left" valign="top">8.16. Composite Types </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 8.18. Domain Types</td></tr></table></div></body></html>
codekingpro/portable-devtools · Team Ai