codekingpro/portable-devtools
114k
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) @> 3;54 55-- Overlaps56SELECT numrange(11.1, 22.2) && 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">&&</code>,345 <code class="literal"><@</code>,346 <code class="literal">@></code>,347 <code class="literal"><<</code>,348 <code class="literal">>></code>,349 <code class="literal">-|-</code>,350 <code class="literal">&<</code>, and351 <code class="literal">&></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">&&</code>,358 <code class="literal"><@</code>,359 <code class="literal">@></code>,360 <code class="literal"><<</code>,361 <code class="literal">>></code>,362 <code class="literal">-|-</code>,363 <code class="literal">&<</code>, and364 <code class="literal">&></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"><</code> and <code class="literal">></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 &&)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 &&)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>