1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
|
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!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 15.6 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</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>
Range types are data types representing a range of values of some
element type (called the range's <em class="firstterm">subtype</em>).
For instance, ranges
of <code class="type">timestamp</code> might be used to represent the ranges of
time that a meeting room is reserved. In this case the data type
is <code class="type">tsrange</code> (short for <span class="quote">“<span class="quote">timestamp range</span>”</span>),
and <code class="type">timestamp</code> 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><p>
Every range type has a corresponding multirange type. A multirange is
an ordered list of non-contiguous, non-empty, non-null ranges. Most
range operators also work on multiranges, and they have a few functions
of their own.
</p><div class="sect2" id="RANGETYPES-BUILTIN"><div class="titlepage"><div><div><h3 class="title">8.17.1. Built-in Range and Multirange Types</h3></div></div></div><p>
PostgreSQL comes with the following built-in range types:
</p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
<code class="type">int4range</code> — Range of <code class="type">integer</code>,
<code class="type">int4multirange</code> — corresponding Multirange
</p></li><li class="listitem"><p>
<code class="type">int8range</code> — Range of <code class="type">bigint</code>,
<code class="type">int8multirange</code> — corresponding Multirange
</p></li><li class="listitem"><p>
<code class="type">numrange</code> — Range of <code class="type">numeric</code>,
<code class="type">nummultirange</code> — corresponding Multirange
</p></li><li class="listitem"><p>
<code class="type">tsrange</code> — Range of <code class="type">timestamp without time zone</code>,
<code class="type">tsmultirange</code> — corresponding Multirange
</p></li><li class="listitem"><p>
<code class="type">tstzrange</code> — Range of <code class="type">timestamp with time zone</code>,
<code class="type">tstzmultirange</code> — corresponding Multirange
</p></li><li class="listitem"><p>
<code class="type">daterange</code> — Range of <code class="type">date</code>,
<code class="type">datemultirange</code> — corresponding Multirange
</p></li></ul></div><p>
In addition, you can define your own range types;
see <a class="xref" href="sql-createtype.html" title="CREATE TYPE"><span class="refentrytitle">CREATE TYPE</span></a> for more information.
</p></div><div class="sect2" id="RANGETYPES-EXAMPLES"><div class="titlepage"><div><div><h3 class="title">8.17.2. Examples</h3></div></div></div><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) @> 3;
-- Overlaps
SELECT numrange(11.1, 22.2) && 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 class="xref" href="functions-range.html#RANGE-OPERATORS-TABLE" title="Table 9.54. Range Operators">Table 9.54</a>
and <a class="xref" href="functions-range.html#RANGE-FUNCTIONS-TABLE" title="Table 9.56. Range Functions">Table 9.56</a> for complete lists of
operators and functions on range types.
</p></div><div class="sect2" id="RANGETYPES-INCLUSIVITY"><div class="titlepage"><div><div><h3 class="title">8.17.3. Inclusive and Exclusive Bounds</h3></div></div></div><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">“<span class="quote"><code class="literal">[</code></span>”</span> while an exclusive lower bound is
represented by <span class="quote">“<span class="quote"><code class="literal">(</code></span>”</span>. Likewise, an inclusive upper bound is represented by
<span class="quote">“<span class="quote"><code class="literal">]</code></span>”</span>, while an exclusive upper bound is
represented by <span class="quote">“<span class="quote"><code class="literal">)</code></span>”</span>.
(See <a class="xref" href="rangetypes.html#RANGETYPES-IO" title="8.17.5. Range Input/Output">Section 8.17.5</a> for more details.)
</p><p>
The functions <code class="literal">lower_inc</code>
and <code class="literal">upper_inc</code> test the inclusivity of the lower
and upper bounds of a range value, respectively.
</p></div><div class="sect2" id="RANGETYPES-INFINITE"><div class="titlepage"><div><div><h3 class="title">8.17.4. Infinite (Unbounded) Ranges</h3></div></div></div><p>
The lower bound of a range can be omitted, meaning that all
values less than the upper bound are included in the range, e.g.,
<code class="literal">(,3]</code>. Likewise, if the upper bound of the range
is omitted, then all values 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. Specifying a
missing bound as inclusive is automatically converted to exclusive,
e.g., <code class="literal">[,]</code> is converted to <code class="literal">(,)</code>.
You can think of these missing values as +/-infinity, but they are
special range type values and are considered to be beyond any range
element type's +/-infinity values.
</p><p>
Element types that have the notion of <span class="quote">“<span class="quote">infinity</span>”</span> can
use them as explicit bound values. For example, with timestamp
ranges, <code class="literal">[today,infinity)</code> excludes the special
<code class="type">timestamp</code> value <code class="literal">infinity</code>,
while <code class="literal">[today,infinity]</code> include it, as does
<code class="literal">[today,)</code> and <code class="literal">[today,]</code>.
</p><p>
The functions <code class="literal">lower_inf</code>
and <code class="literal">upper_inf</code> test for infinite lower
and upper bounds of a range, respectively.
</p></div><div class="sect2" id="RANGETYPES-IO"><div class="titlepage"><div><div><h3 class="title">8.17.5. Range Input/Output</h3></div></div></div><p>
The input for a range value must follow one of the following patterns:
</p><pre class="synopsis">
(<em class="replaceable"><code>lower-bound</code></em>,<em class="replaceable"><code>upper-bound</code></em>)
(<em class="replaceable"><code>lower-bound</code></em>,<em class="replaceable"><code>upper-bound</code></em>]
[<em class="replaceable"><code>lower-bound</code></em>,<em class="replaceable"><code>upper-bound</code></em>)
[<em class="replaceable"><code>lower-bound</code></em>,<em class="replaceable"><code>upper-bound</code></em>]
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 <code class="literal">empty</code>, which
represents an empty range (a range that contains no points).
</p><p>
The <em class="replaceable"><code>lower-bound</code></em> may be either a string
that is valid input for the subtype, or empty to indicate no
lower bound. Likewise, <em class="replaceable"><code>upper-bound</code></em> 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 <code class="literal">"</code> (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 <code class="literal">""</code>, 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"><h3 class="title">Note</h3><p>
These rules are very similar to those for writing field values in
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> for
additional commentary.
</p></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><p>
The input for a multirange is curly brackets (<code class="literal">{</code> and
<code class="literal">}</code>) containing zero or more valid ranges,
separated by commas. Whitespace is permitted around the brackets and
commas. This is intended to be reminiscent of array syntax, although
multiranges are much simpler: they have just one dimension and there is
no need to quote their contents. (The bounds of their ranges may be
quoted as above however.)
</p><p>
Examples:
</p><pre class="programlisting">
SELECT '{}'::int4multirange;
SELECT '{[3,7)}'::int4multirange;
SELECT '{[3,7), [8,9)}'::int4multirange;
</pre><p>
</p></div><div class="sect2" id="RANGETYPES-CONSTRUCT"><div class="titlepage"><div><div><h3 class="title">8.17.6. Constructing Ranges and Multiranges</h3></div></div></div><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">“<span class="quote"><code class="literal">()</code></span>”</span>,
<span class="quote">“<span class="quote"><code class="literal">(]</code></span>”</span>,
<span class="quote">“<span class="quote"><code class="literal">[)</code></span>”</span>, or
<span class="quote">“<span class="quote"><code class="literal">[]</code></span>”</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><p>
Each range type also has a multirange constructor with the same name as the
multirange type. The constructor function takes zero or more arguments
which are all ranges of the appropriate type.
For example:
</p><pre class="programlisting">
SELECT nummultirange();
SELECT nummultirange(numrange(1.0, 14.0));
SELECT nummultirange(numrange(1.0, 14.0), numrange(20.0, 25.0));
</pre><p>
</p></div><div class="sect2" id="RANGETYPES-DISCRETE"><div class="titlepage"><div><div><h3 class="title">8.17.7. Discrete Range Types</h3></div></div></div><p>
A discrete range is one whose element type has a well-defined
<span class="quote">“<span class="quote">step</span>”</span>, such as <code class="type">integer</code> or <code class="type">date</code>.
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
<code class="type">numeric</code> type is continuous, as is a range over <code class="type">timestamp</code>.
(Even though <code class="type">timestamp</code> 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">“<span class="quote">next</span>”</span> or <span class="quote">“<span class="quote">previous</span>”</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 <code class="literal">[4,8]</code> and
<code class="literal">(3,9)</code> 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 <em class="firstterm">canonicalization</em>
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 <code class="type">int4range</code>, <code class="type">int8range</code>,
and <code class="type">daterange</code> all use a canonical form that includes
the lower bound and excludes the upper bound; that is,
<code class="literal">[)</code>. User-defined range types can use other conventions,
however.
</p></div><div class="sect2" id="RANGETYPES-DEFINING"><div class="titlepage"><div><div><h3 class="title">8.17.8. Defining New Range Types</h3></div></div></div><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 <code class="type">float8</code>:
</p><pre class="programlisting">
CREATE TYPE floatrange AS RANGE (
subtype = float8,
subtype_diff = float8mi
);
SELECT '[1.234, 5.678]'::floatrange;
</pre><p>
Because <code class="type">float8</code> has no meaningful
<span class="quote">“<span class="quote">step</span>”</span>, we do not define a canonicalization
function in this example.
</p><p>
When you define your own range you automatically get a corresponding
multirange type.
</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>
If the subtype is considered to have discrete rather than continuous
values, the <code class="command">CREATE TYPE</code> command should specify a
<code class="literal">canonical</code> 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 <code class="literal">[1, 7]</code> and <code class="literal">[1,
8)</code>, 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 <code class="type">timestamp</code> 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>
In addition, any range type that is meant to be used with GiST or SP-GiST
indexes should define a subtype difference, or <code class="literal">subtype_diff</code>,
function. (The index will still work without <code class="literal">subtype_diff</code>,
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., <em class="replaceable"><code>X</code></em> minus <em class="replaceable"><code>Y</code></em>) represented as
a <code class="type">float8</code> value. In our example above, the
function <code class="function">float8mi</code> that underlies the regular <code class="type">float8</code>
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 <code class="literal">subtype_diff</code> 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>
A less-oversimplified example of a <code class="literal">subtype_diff</code> function is:
</p><pre class="programlisting">
CREATE FUNCTION time_subtype_diff(x time, y time) RETURNS float8 AS
'SELECT EXTRACT(EPOCH FROM (x - y))' LANGUAGE sql STRICT IMMUTABLE;
CREATE TYPE timerange AS RANGE (
subtype = time,
subtype_diff = time_subtype_diff
);
SELECT '[11:10, 23:00]'::timerange;
</pre><p>
See <a class="xref" href="sql-createtype.html" title="CREATE TYPE"><span class="refentrytitle">CREATE TYPE</span></a> for more information about creating
range types.
</p></div><div class="sect2" id="RANGETYPES-INDEXING"><div class="titlepage"><div><div><h3 class="title">8.17.9. Indexing</h3></div></div></div><a id="id-1.5.7.25.15.2" class="indexterm"></a><p>
GiST and SP-GiST indexes can be created for table columns of range types.
GiST indexes can be also created for table columns of multirange 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 on ranges can accelerate queries involving these
range operators:
<code class="literal">=</code>,
<code class="literal">&&</code>,
<code class="literal"><@</code>,
<code class="literal">@></code>,
<code class="literal"><<</code>,
<code class="literal">>></code>,
<code class="literal">-|-</code>,
<code class="literal">&<</code>, and
<code class="literal">&></code>.
A GiST index on multiranges can accelerate queries involving the same
set of multirange operators.
A GiST index on ranges and GiST index on multiranges can also accelerate
queries involving these cross-type range to multirange and multirange to
range operators correspondingly:
<code class="literal">&&</code>,
<code class="literal"><@</code>,
<code class="literal">@></code>,
<code class="literal"><<</code>,
<code class="literal">>></code>,
<code class="literal">-|-</code>,
<code class="literal">&<</code>, and
<code class="literal">&></code>.
See <a class="xref" href="functions-range.html#RANGE-OPERATORS-TABLE" title="Table 9.54. Range Operators">Table 9.54</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 <code class="literal"><</code> and <code class="literal">></code> 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" id="RANGETYPES-CONSTRAINT"><div class="titlepage"><div><div><h3 class="title">8.17.10. Constraints on Ranges</h3></div></div></div><a id="id-1.5.7.25.16.2" class="indexterm"></a><p>
While <code class="literal">UNIQUE</code> is a natural constraint for scalar
values, it is usually unsuitable for range types. Instead, an
exclusion constraint is often more appropriate
(see <a class="link" href="sql-createtable.html#SQL-CREATETABLE-EXCLUDE">CREATE TABLE
... CONSTRAINT ... EXCLUDE</a>). Exclusion constraints allow the
specification of constraints such as <span class="quote">“<span class="quote">non-overlapping</span>”</span> on a
range type. For example:
</p><pre class="programlisting">
CREATE TABLE reservation (
during tsrange,
EXCLUDE USING GIST (during WITH &&)
);
</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 class="link" href="btree-gist.html" title="F.9. btree_gist"><code class="literal">btree_gist</code></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 <code class="literal">btree_gist</code> 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 &&)
);
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 /><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 15.6 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 8.18. Domain Types</td></tr></table></div></body></html>
|