diff options
Diffstat (limited to 'doc/src/sgml/html/rangetypes.html')
| -rw-r--r-- | doc/src/sgml/html/rangetypes.html | 435 |
1 files changed, 435 insertions, 0 deletions
diff --git a/doc/src/sgml/html/rangetypes.html b/doc/src/sgml/html/rangetypes.html new file mode 100644 index 0000000..0ad4a55 --- /dev/null +++ b/doc/src/sgml/html/rangetypes.html @@ -0,0 +1,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.5 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.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 8.18. Domain Types</td></tr></table></div></body></html>
\ No newline at end of file |