{"Entry":{"collection":"type","key":"datemultirange","name":"datemultirange","aliases":[],"metadata":{"aliases":[],"category":"Multiranges","content_hash":"68bec7e17ae9f012ed4780484da0c59d406defd23df8e3b6ceb9679b617dd7f3","imported_at":"2026-09-30T00:40:35.666712+08:00","name":"datemultirange","name_zh":"","slug":"datemultirange","summary":"multirange of dates"}},"Definition":{"Collection":"type","Key":"datemultirange","SourceDatabase":"center","Version":"18","SourceTable":"data_type","SourceKey":"datemultirange","SourceRevision":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","Facts":{"aliases":[],"casts":[{"castcontext":"e","castfunc":"datemultirange(daterange)","castmethod":"f","castsource":"daterange","casttarget":"datemultirange"}],"catalog":{"array_type_name":"_datemultirange","array_type_oid":"6155","descr":"multirange of dates","oid":"4535","typacl":"_null_","typalign":"i","typanalyze":"multirange_typanalyze","typarray":"0","typbasetype":"0","typbyval":"f","typcategory":"R","typcollation":"0","typdefault":"_null_","typdefaultbin":"_null_","typdelim":"','","typelem":"0","typinput":"multirange_in","typisdefined":"t","typispreferred":"f","typlen":"-1","typmodin":"-","typmodout":"-","typname":"datemultirange","typnamespace":"pg_catalog","typndims":"0","typnotnull":"f","typoutput":"multirange_out","typowner":"POSTGRES","typreceive":"multirange_recv","typrelid":"0","typsend":"multirange_send","typstorage":"x","typsubscript":"-","typtype":"m","typtypmod":"-1"},"comparison_data":{"aliases":[],"casts":[{"castcontext":"e","castfunc":"datemultirange(daterange)","castmethod":"f","castsource":"daterange","casttarget":"datemultirange"}],"catalog":{"array_type_name":"_datemultirange","typacl":"_null_","typalign":"i","typanalyze":"multirange_typanalyze","typarray":"_datemultirange","typbasetype":"0","typbyval":"f","typcategory":"R","typcollation":"0","typdefault":"_null_","typdefaultbin":"_null_","typdelim":",","typelem":"0","typinput":"multirange_in","typisdefined":"t","typispreferred":"f","typlen":"-1","typmodin":"-","typmodout":"-","typname":"datemultirange","typndims":"0","typnotnull":"f","typoutput":"multirange_out","typreceive":"multirange_recv","typrelid":"0","typsend":"multirange_send","typstorage":"x","typsubscript":"-","typtype":"m","typtypmod":"-1"},"facts":[{"label":"Catalog name","value":"pg_catalog.datemultirange"},{"label":"Declared length","value":"Variable length (varlena)"},{"label":"Input function","value":"multirange_in"},{"label":"Output function","value":"multirange_out"},{"label":"Storage strategy","value":"extended"},{"label":"Type OID","value":"4535"},{"label":"Type kind","value":"Multirange"}],"operator_classes":[],"operators":[],"ranges":[{"rngcanonical":"daterange_canonical","rngcollation":"0","rngmultitypid":"datemultirange","rngsubdiff":"daterange_subdiff","rngsubopc":"btree/date_ops","rngsubtype":"date","rngtypid":"daterange"}]},"comparison_hash":"d6697973692d45a751950d16445232652955d5b62d83946817aaf68f194bd39b","coverage":"source inventory; exact declared input types for operator classes","description":["multirange of dates"],"facts":[{"label":"Catalog name","value":"pg_catalog.datemultirange"},{"label":"Type OID","value":"4535"},{"label":"Type kind","value":"Multirange"},{"label":"Declared length","value":"Variable length (varlena)"},{"label":"Storage strategy","value":"extended"},{"label":"Input function","value":"multirange_in"},{"label":"Output function","value":"multirange_out"}],"manual_documentation":"dedicated family chapter","manual_html":"\u003cdiv class=\"sect1\" id=\"RANGETYPES\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch2 class=\"title\"\u003e8.17. Range Types \u003c/h2\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\n\u003cp\u003eRange types are data types representing a range of values of some element type (called the range's \u003cem class=\"firstterm\"\u003esubtype\u003c/em\u003e). For instance, ranges of \u003ccode class=\"type\"\u003etimestamp\u003c/code\u003e might be used to represent the ranges of time that a meeting room is reserved. In this case the data type is \u003ccode class=\"type\"\u003etsrange\u003c/code\u003e (short for \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003etimestamp range\u003c/span\u003e”\u003c/span\u003e), and \u003ccode class=\"type\"\u003etimestamp\u003c/code\u003e 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.\u003c/p\u003e\n\u003cp\u003eRange 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.\u003c/p\u003e\n\u003cp\u003eEvery 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.\u003c/p\u003e\n\u003cdiv class=\"sect2\" id=\"RANGETYPES-BUILTIN\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.17.1. Built-in Range and Multirange Types \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003ePostgreSQL comes with the following built-in range types:\u003c/p\u003e\n\u003cdiv class=\"itemizedlist\"\u003e\n\u003cul class=\"itemizedlist\"\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003e\u003ccode class=\"type\"\u003eint4range\u003c/code\u003e — Range of \u003ccode class=\"type\"\u003einteger\u003c/code\u003e, \u003ccode class=\"type\"\u003eint4multirange\u003c/code\u003e — corresponding Multirange\u003c/p\u003e\n\u003c/li\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003e\u003ccode class=\"type\"\u003eint8range\u003c/code\u003e — Range of \u003ccode class=\"type\"\u003ebigint\u003c/code\u003e, \u003ccode class=\"type\"\u003eint8multirange\u003c/code\u003e — corresponding Multirange\u003c/p\u003e\n\u003c/li\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003e\u003ccode class=\"type\"\u003enumrange\u003c/code\u003e — Range of \u003ccode class=\"type\"\u003enumeric\u003c/code\u003e, \u003ccode class=\"type\"\u003enummultirange\u003c/code\u003e — corresponding Multirange\u003c/p\u003e\n\u003c/li\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003e\u003ccode class=\"type\"\u003etsrange\u003c/code\u003e — Range of \u003ccode class=\"type\"\u003etimestamp without time zone\u003c/code\u003e, \u003ccode class=\"type\"\u003etsmultirange\u003c/code\u003e — corresponding Multirange\u003c/p\u003e\n\u003c/li\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003e\u003ccode class=\"type\"\u003etstzrange\u003c/code\u003e — Range of \u003ccode class=\"type\"\u003etimestamp with time zone\u003c/code\u003e, \u003ccode class=\"type\"\u003etstzmultirange\u003c/code\u003e — corresponding Multirange\u003c/p\u003e\n\u003c/li\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003e\u003ccode class=\"type\"\u003edaterange\u003c/code\u003e — Range of \u003ccode class=\"type\"\u003edate\u003c/code\u003e, \u003ccode class=\"type\"\u003edatemultirange\u003c/code\u003e — corresponding Multirange\u003c/p\u003e\n\u003c/li\u003e\n\u003c/ul\u003e\n\u003c/div\u003e\n\u003cp\u003eIn addition, you can define your own range types; see \u003ca class=\"xref\" href=\"/docs/18/sql-createtype.html\" title=\"CREATE TYPE\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE TYPE\u003c/span\u003e\u003c/a\u003e for more information.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"RANGETYPES-EXAMPLES\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.17.2. Examples \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cpre class=\"programlisting\"\u003eCREATE TABLE reservation (room int, during tsrange);\nINSERT INTO reservation VALUES\n    (1108, '[2010-01-01 14:30, 2010-01-01 15:30)');\n\n-- Containment\nSELECT int4range(10, 20) @\u0026gt; 3;\n\n-- Overlaps\nSELECT numrange(11.1, 22.2) \u0026amp;\u0026amp; numrange(20.0, 30.0);\n\n-- Extract the upper bound\nSELECT upper(int8range(15, 25));\n\n-- Compute the intersection\nSELECT int4range(10, 20) * int4range(15, 25);\n\n-- Is the range empty?\nSELECT isempty(numrange(1, 5));\n\u003c/pre\u003e\n\u003cp\u003eSee \u003ca class=\"xref\" href=\"/docs/18/functions-range.html#RANGE-OPERATORS-TABLE\" title=\"Table 9.58. Range Operators\"\u003eTable 9.58\u003c/a\u003e and \u003ca class=\"xref\" href=\"/docs/18/functions-range.html#RANGE-FUNCTIONS-TABLE\" title=\"Table 9.60. Range Functions\"\u003eTable 9.60\u003c/a\u003e for complete lists of operators and functions on range types.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"RANGETYPES-INCLUSIVITY\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.17.3. Inclusive and Exclusive Bounds \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eEvery 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.\u003c/p\u003e\n\u003cp\u003eIn the text form of a range, an inclusive lower bound is represented by \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003e\u003ccode class=\"literal\"\u003e[\u003c/code\u003e\u003c/span\u003e”\u003c/span\u003e while an exclusive lower bound is represented by \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003e\u003ccode class=\"literal\"\u003e(\u003c/code\u003e\u003c/span\u003e”\u003c/span\u003e. Likewise, an inclusive upper bound is represented by \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003e\u003ccode class=\"literal\"\u003e]\u003c/code\u003e\u003c/span\u003e”\u003c/span\u003e, while an exclusive upper bound is represented by \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003e\u003ccode class=\"literal\"\u003e)\u003c/code\u003e\u003c/span\u003e”\u003c/span\u003e. (See \u003ca class=\"xref\" href=\"/docs/18/rangetypes.html#RANGETYPES-IO\" title=\"8.17.5. Range Input/Output\"\u003eSection 8.17.5\u003c/a\u003e for more details.)\u003c/p\u003e\n\u003cp\u003eThe functions \u003ccode class=\"literal\"\u003elower_inc\u003c/code\u003e and \u003ccode class=\"literal\"\u003eupper_inc\u003c/code\u003e test the inclusivity of the lower and upper bounds of a range value, respectively.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"RANGETYPES-INFINITE\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.17.4. Infinite (Unbounded) Ranges \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eThe lower bound of a range can be omitted, meaning that all values less than the upper bound are included in the range, e.g., \u003ccode class=\"literal\"\u003e(,3]\u003c/code\u003e. 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., \u003ccode class=\"literal\"\u003e[,]\u003c/code\u003e is converted to \u003ccode class=\"literal\"\u003e(,)\u003c/code\u003e. 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.\u003c/p\u003e\n\u003cp\u003eElement types that have the notion of \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003einfinity\u003c/span\u003e”\u003c/span\u003e can use them as explicit bound values. For example, with timestamp ranges, \u003ccode class=\"literal\"\u003e[today,infinity)\u003c/code\u003e excludes the special \u003ccode class=\"type\"\u003etimestamp\u003c/code\u003e value \u003ccode class=\"literal\"\u003einfinity\u003c/code\u003e, while \u003ccode class=\"literal\"\u003e[today,infinity]\u003c/code\u003e include it, as does \u003ccode class=\"literal\"\u003e[today,)\u003c/code\u003e and \u003ccode class=\"literal\"\u003e[today,]\u003c/code\u003e.\u003c/p\u003e\n\u003cp\u003eThe functions \u003ccode class=\"literal\"\u003elower_inf\u003c/code\u003e and \u003ccode class=\"literal\"\u003eupper_inf\u003c/code\u003e test for infinite lower and upper bounds of a range, respectively.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"RANGETYPES-IO\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.17.5. Range Input/Output \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eThe input for a range value must follow one of the following patterns:\u003c/p\u003e\n\u003cpre class=\"synopsis\"\u003e(\u003cem class=\"replaceable\"\u003e\u003ccode\u003elower-bound\u003c/code\u003e\u003c/em\u003e,\u003cem class=\"replaceable\"\u003e\u003ccode\u003eupper-bound\u003c/code\u003e\u003c/em\u003e)\n(\u003cem class=\"replaceable\"\u003e\u003ccode\u003elower-bound\u003c/code\u003e\u003c/em\u003e,\u003cem class=\"replaceable\"\u003e\u003ccode\u003eupper-bound\u003c/code\u003e\u003c/em\u003e]\n[\u003cem class=\"replaceable\"\u003e\u003ccode\u003elower-bound\u003c/code\u003e\u003c/em\u003e,\u003cem class=\"replaceable\"\u003e\u003ccode\u003eupper-bound\u003c/code\u003e\u003c/em\u003e)\n[\u003cem class=\"replaceable\"\u003e\u003ccode\u003elower-bound\u003c/code\u003e\u003c/em\u003e,\u003cem class=\"replaceable\"\u003e\u003ccode\u003eupper-bound\u003c/code\u003e\u003c/em\u003e]\nempty\n\u003c/pre\u003e\n\u003cp\u003eThe parentheses or brackets indicate whether the lower and upper bounds are exclusive or inclusive, as described previously. Notice that the final pattern is \u003ccode class=\"literal\"\u003eempty\u003c/code\u003e, which represents an empty range (a range that contains no points).\u003c/p\u003e\n\u003cp\u003eThe \u003cem class=\"replaceable\"\u003e\u003ccode\u003elower-bound\u003c/code\u003e\u003c/em\u003e may be either a string that is valid input for the subtype, or empty to indicate no lower bound. Likewise, \u003cem class=\"replaceable\"\u003e\u003ccode\u003eupper-bound\u003c/code\u003e\u003c/em\u003e may be either a string that is valid input for the subtype, or empty to indicate no upper bound.\u003c/p\u003e\n\u003cp\u003eEach bound value can be quoted using \u003ccode class=\"literal\"\u003e\"\u003c/code\u003e (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 \u003ccode class=\"literal\"\u003e\"\"\u003c/code\u003e, since writing nothing means an infinite bound.\u003c/p\u003e\n\u003cp\u003eWhitespace 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.)\u003c/p\u003e\n\u003cdiv class=\"note\"\u003e\n\u003ch3 class=\"title\"\u003eNote\u003c/h3\u003e\n\u003cp\u003eThese rules are very similar to those for writing field values in composite-type literals. See \u003ca class=\"xref\" href=\"/docs/18/rowtypes.html#ROWTYPES-IO-SYNTAX\" title=\"8.16.6. Composite Type Input and Output Syntax\"\u003eSection 8.16.6\u003c/a\u003e for additional commentary.\u003c/p\u003e\n\u003c/div\u003e\n\u003cp\u003eExamples:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003e-- includes 3, does not include 7, and does include all points in between\nSELECT '[3,7)'::int4range;\n\n-- does not include either 3 or 7, but includes all points in between\nSELECT '(3,7)'::int4range;\n\n-- includes only the single point 4\nSELECT '[4,4]'::int4range;\n\n-- includes no points (and will be normalized to 'empty')\nSELECT '[4,4)'::int4range;\n\u003c/pre\u003e\n\u003cp\u003eThe input for a multirange is curly brackets (\u003ccode class=\"literal\"\u003e{\u003c/code\u003e and \u003ccode class=\"literal\"\u003e}\u003c/code\u003e) 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.)\u003c/p\u003e\n\u003cp\u003eExamples:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT '{}'::int4multirange;\nSELECT '{[3,7)}'::int4multirange;\nSELECT '{[3,7), [8,9)}'::int4multirange;\n\u003c/pre\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"RANGETYPES-CONSTRUCT\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.17.6. Constructing Ranges and Multiranges \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eEach 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 \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003e\u003ccode class=\"literal\"\u003e()\u003c/code\u003e\u003c/span\u003e”\u003c/span\u003e, \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003e\u003ccode class=\"literal\"\u003e(]\u003c/code\u003e\u003c/span\u003e”\u003c/span\u003e, \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003e\u003ccode class=\"literal\"\u003e[)\u003c/code\u003e\u003c/span\u003e”\u003c/span\u003e, or \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003e\u003ccode class=\"literal\"\u003e[]\u003c/code\u003e\u003c/span\u003e”\u003c/span\u003e. For example:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003e-- The full form is: lower bound, upper bound, and text argument indicating\n-- inclusivity/exclusivity of bounds.\nSELECT numrange(1.0, 14.0, '(]');\n\n-- If the third argument is omitted, '[)' is assumed.\nSELECT numrange(1.0, 14.0);\n\n-- Although '(]' is specified here, on display the value will be converted to\n-- canonical form, since int8range is a discrete range type (see below).\nSELECT int8range(1, 14, '(]');\n\n-- Using NULL for either bound causes the range to be unbounded on that side.\nSELECT numrange(NULL, 2.2);\n\u003c/pre\u003e\n\u003cp\u003eEach 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:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT nummultirange();\nSELECT nummultirange(numrange(1.0, 14.0));\nSELECT nummultirange(numrange(1.0, 14.0), numrange(20.0, 25.0));\n\u003c/pre\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"RANGETYPES-DISCRETE\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.17.7. Discrete Range Types \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eA discrete range is one whose element type has a well-defined \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003estep\u003c/span\u003e”\u003c/span\u003e, such as \u003ccode class=\"type\"\u003einteger\u003c/code\u003e or \u003ccode class=\"type\"\u003edate\u003c/code\u003e. 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 \u003ccode class=\"type\"\u003enumeric\u003c/code\u003e type is continuous, as is a range over \u003ccode class=\"type\"\u003etimestamp\u003c/code\u003e. (Even though \u003ccode class=\"type\"\u003etimestamp\u003c/code\u003e 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.)\u003c/p\u003e\n\u003cp\u003eAnother way to think about a discrete range type is that there is a clear idea of a \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003enext\u003c/span\u003e”\u003c/span\u003e or \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003eprevious\u003c/span\u003e”\u003c/span\u003e 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 \u003ccode class=\"literal\"\u003e[4,8]\u003c/code\u003e and \u003ccode class=\"literal\"\u003e(3,9)\u003c/code\u003e denote the same set of values; but this would not be so for a range over numeric.\u003c/p\u003e\n\u003cp\u003eA discrete range type should have a \u003cem class=\"firstterm\"\u003ecanonicalization\u003c/em\u003e 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.\u003c/p\u003e\n\u003cp\u003eThe built-in range types \u003ccode class=\"type\"\u003eint4range\u003c/code\u003e, \u003ccode class=\"type\"\u003eint8range\u003c/code\u003e, and \u003ccode class=\"type\"\u003edaterange\u003c/code\u003e all use a canonical form that includes the lower bound and excludes the upper bound; that is, \u003ccode class=\"literal\"\u003e[)\u003c/code\u003e. User-defined range types can use other conventions, however.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"RANGETYPES-DEFINING\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.17.8. Defining New Range Types \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eUsers 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 \u003ccode class=\"type\"\u003efloat8\u003c/code\u003e:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eCREATE TYPE floatrange AS RANGE (\n    subtype = float8,\n    subtype_diff = float8mi\n);\n\nSELECT '[1.234, 5.678]'::floatrange;\n\u003c/pre\u003e\n\u003cp\u003eBecause \u003ccode class=\"type\"\u003efloat8\u003c/code\u003e has no meaningful \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003estep\u003c/span\u003e”\u003c/span\u003e, we do not define a canonicalization function in this example.\u003c/p\u003e\n\u003cp\u003eWhen you define your own range you automatically get a corresponding multirange type.\u003c/p\u003e\n\u003cp\u003eDefining 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.\u003c/p\u003e\n\u003cp\u003eIf the subtype is considered to have discrete rather than continuous values, the \u003ccode class=\"command\"\u003eCREATE TYPE\u003c/code\u003e command should specify a \u003ccode class=\"literal\"\u003ecanonical\u003c/code\u003e 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 \u003ccode class=\"literal\"\u003e[1, 7]\u003c/code\u003e and \u003ccode class=\"literal\"\u003e[1, 8)\u003c/code\u003e, 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 \u003ccode class=\"type\"\u003etimestamp\u003c/code\u003e 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.\u003c/p\u003e\n\u003cp\u003eIn addition, any range type that is meant to be used with GiST or SP-GiST indexes should define a subtype difference, or \u003ccode class=\"literal\"\u003esubtype_diff\u003c/code\u003e, function. (The index will still work without \u003ccode class=\"literal\"\u003esubtype_diff\u003c/code\u003e, 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., \u003cem class=\"replaceable\"\u003e\u003ccode\u003eX\u003c/code\u003e\u003c/em\u003e minus \u003cem class=\"replaceable\"\u003e\u003ccode\u003eY\u003c/code\u003e\u003c/em\u003e) represented as a \u003ccode class=\"type\"\u003efloat8\u003c/code\u003e value. In our example above, the function \u003ccode class=\"function\"\u003efloat8mi\u003c/code\u003e that underlies the regular \u003ccode class=\"type\"\u003efloat8\u003c/code\u003e 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 \u003ccode class=\"literal\"\u003esubtype_diff\u003c/code\u003e 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.\u003c/p\u003e\n\u003cp\u003eA less-oversimplified example of a \u003ccode class=\"literal\"\u003esubtype_diff\u003c/code\u003e function is:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eCREATE FUNCTION time_subtype_diff(x time, y time) RETURNS float8 AS\n'SELECT EXTRACT(EPOCH FROM (x - y))' LANGUAGE sql STRICT IMMUTABLE;\n\nCREATE TYPE timerange AS RANGE (\n    subtype = time,\n    subtype_diff = time_subtype_diff\n);\n\nSELECT '[11:10, 23:00]'::timerange;\n\u003c/pre\u003e\n\u003cp\u003eSee \u003ca class=\"xref\" href=\"/docs/18/sql-createtype.html\" title=\"CREATE TYPE\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE TYPE\u003c/span\u003e\u003c/a\u003e for more information about creating range types.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"RANGETYPES-INDEXING\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.17.9. Indexing \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eGiST 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:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eCREATE INDEX reservation_idx ON reservation USING GIST (during);\n\u003c/pre\u003e\n\u003cp\u003eA GiST or SP-GiST index on ranges can accelerate queries involving these range operators: \u003ccode class=\"literal\"\u003e=\u003c/code\u003e, \u003ccode class=\"literal\"\u003e\u0026amp;\u0026amp;\u003c/code\u003e, \u003ccode class=\"literal\"\u003e\u0026lt;@\u003c/code\u003e, \u003ccode class=\"literal\"\u003e@\u0026gt;\u003c/code\u003e, \u003ccode class=\"literal\"\u003e\u0026lt;\u0026lt;\u003c/code\u003e, \u003ccode class=\"literal\"\u003e\u0026gt;\u0026gt;\u003c/code\u003e, \u003ccode class=\"literal\"\u003e-|-\u003c/code\u003e, \u003ccode class=\"literal\"\u003e\u0026amp;\u0026lt;\u003c/code\u003e, and \u003ccode class=\"literal\"\u003e\u0026amp;\u0026gt;\u003c/code\u003e. 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: \u003ccode class=\"literal\"\u003e\u0026amp;\u0026amp;\u003c/code\u003e, \u003ccode class=\"literal\"\u003e\u0026lt;@\u003c/code\u003e, \u003ccode class=\"literal\"\u003e@\u0026gt;\u003c/code\u003e, \u003ccode class=\"literal\"\u003e\u0026lt;\u0026lt;\u003c/code\u003e, \u003ccode class=\"literal\"\u003e\u0026gt;\u0026gt;\u003c/code\u003e, \u003ccode class=\"literal\"\u003e-|-\u003c/code\u003e, \u003ccode class=\"literal\"\u003e\u0026amp;\u0026lt;\u003c/code\u003e, and \u003ccode class=\"literal\"\u003e\u0026amp;\u0026gt;\u003c/code\u003e. See \u003ca class=\"xref\" href=\"/docs/18/functions-range.html#RANGE-OPERATORS-TABLE\" title=\"Table 9.58. Range Operators\"\u003eTable 9.58\u003c/a\u003e for more information.\u003c/p\u003e\n\u003cp\u003eIn 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 \u003ccode class=\"literal\"\u003e\u0026lt;\u003c/code\u003e and \u003ccode class=\"literal\"\u003e\u0026gt;\u003c/code\u003e 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.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"RANGETYPES-CONSTRAINT\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.17.10. Constraints on Ranges \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eWhile \u003ccode class=\"literal\"\u003eUNIQUE\u003c/code\u003e is a natural constraint for scalar values, it is usually unsuitable for range types. Instead, an exclusion constraint is often more appropriate (see \u003ca class=\"link\" href=\"/docs/18/sql-createtable.html#SQL-CREATETABLE-EXCLUDE\"\u003eCREATE TABLE ... CONSTRAINT ... EXCLUDE\u003c/a\u003e). Exclusion constraints allow the specification of constraints such as \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003enon-overlapping\u003c/span\u003e”\u003c/span\u003e on a range type. For example:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eCREATE TABLE reservation (\n    during tsrange,\n    EXCLUDE USING GIST (during WITH \u0026amp;\u0026amp;)\n);\n\u003c/pre\u003e\n\u003cp\u003eThat constraint will prevent any overlapping values from existing in the table at the same time:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eINSERT INTO reservation VALUES\n    ('[2010-01-01 11:30, 2010-01-01 15:00)');\nINSERT 0 1\n\nINSERT INTO reservation VALUES\n    ('[2010-01-01 14:45, 2010-01-01 15:45)');\nERROR:  conflicting key value violates exclusion constraint \"reservation_during_excl\"\nDETAIL:  Key (during)=([\"2010-01-01 14:45:00\",\"2010-01-01 15:45:00\")) conflicts\nwith existing key (during)=([\"2010-01-01 11:30:00\",\"2010-01-01 15:00:00\")).\n\u003c/pre\u003e\n\u003cp\u003eYou can use the \u003ca class=\"link\" href=\"/docs/18/btree-gist.html\" title=\"F.8. btree_gist — GiST operator classes with B-tree behavior\"\u003e\u003ccode class=\"literal\"\u003ebtree_gist\u003c/code\u003e\u003c/a\u003e extension to define exclusion constraints on plain scalar data types, which can then be combined with range exclusions for maximum flexibility. For example, after \u003ccode class=\"literal\"\u003ebtree_gist\u003c/code\u003e is installed, the following constraint will reject overlapping ranges only if the meeting room numbers are equal:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eCREATE EXTENSION btree_gist;\nCREATE TABLE room_reservation (\n    room text,\n    during tsrange,\n    EXCLUDE USING GIST (room WITH =, during WITH \u0026amp;\u0026amp;)\n);\n\nINSERT INTO room_reservation VALUES\n    ('123A', '[2010-01-01 14:00, 2010-01-01 15:00)');\nINSERT 0 1\n\nINSERT INTO room_reservation VALUES\n    ('123A', '[2010-01-01 14:30, 2010-01-01 15:30)');\nERROR:  conflicting key value violates exclusion constraint \"room_reservation_room_during_excl\"\nDETAIL:  Key (room, during)=(123A, [\"2010-01-01 14:30:00\",\"2010-01-01 15:30:00\")) conflicts\nwith existing key (room, during)=(123A, [\"2010-01-01 14:00:00\",\"2010-01-01 15:00:00\")).\n\nINSERT INTO room_reservation VALUES\n    ('123B', '[2010-01-01 14:30, 2010-01-01 15:30)');\nINSERT 0 1\n\u003c/pre\u003e\n\u003c/div\u003e\n\u003c/div\u003e","manual_path":"rangetypes.html","operator_classes":[],"operators":[],"ranges":[{"rngcanonical":"daterange_canonical","rngcollation":"0","rngmultitypid":"datemultirange","rngsubdiff":"daterange_subdiff","rngsubopc":"btree/date_ops","rngsubtype":"date","rngtypid":"daterange"}],"related":[{"label":"pg_type catalog","url":"/wiki/catalog/pg_type/?v=18"},{"label":"pg_cast catalog","url":"/wiki/catalog/pg_cast/?v=18"},{"label":"pg_operator catalog","url":"/wiki/catalog/pg_operator/?v=18"},{"label":"pg_opclass catalog","url":"/wiki/catalog/pg_opclass/?v=18"},{"label":"Arrays","url":"/wiki/type/arrays/?v=18"}],"release":{"channel":"stable","label":"18.6","major":"18","manual_sha256":{"arrays.html":"0e1d5c5a7b4a949d4439995f44f74ad4264e050d16eb9ad2a850fbaee04032df","catalog-pg-type.html":"ea9c9313bab8e92a7da18ebc50c7ff1f908dd80999a7e0ca3bbc82ba626c58d4","datatype-binary.html":"0c03ec47b76e37888a3da7f5dcfd340128a5d356642a85185cc9ebe17856a904","datatype-bit.html":"c49124aad561636c18080b9e09572fde7785a6b9eae86f5a33283f0fd6774c37","datatype-boolean.html":"b633f663e6c8276e7056287640c413aa79dbc15d5353cee0d41653d58d376956","datatype-character.html":"c73eebe413ea709a7792e4cfc3cfe9fb68cb496ebe83c3ebaa9d09f8c40d0f73","datatype-datetime.html":"e366275d8b13845bfa4ef0d68faa093e5f3ecb708a1ec870cbcbf7e425b85699","datatype-enum.html":"cab38e53222e30cc94b24de4b33634fd22d7c93f58ebcf35f646c9b8da78fae8","datatype-geometric.html":"0eb3053cfe7d6b0a4b5592c5d7af8d97cae527e87f063c5793413bcba5c14cd9","datatype-json.html":"650162a05b660146b500a205d523374d00aee117a6a6240d30262be51b6e33e5","datatype-money.html":"8b29c14b90679683ac5c472b59a0a7e2853f32cc7db43fab417b2697a6879964","datatype-net-types.html":"96846e641cc37727ddcd8b8a1aa0dbe33cd9787760a2b4efb0dcb9403d5be63f","datatype-numeric.html":"b74619f7ac2f1ca9f85c48073df78c33dd30e464e61baba6008634534edcb500","datatype-oid.html":"8b3360302225f08ae72716ce1804596de95b568e5d31f788cb4597427b28cc38","datatype-pg-lsn.html":"339172f5107cc7eb7139bec911277647219e92a532cebd0f3da0a5dacf8195af","datatype-pseudo.html":"c7cf0b8214304bd8702c95f2142671ae5f0d631f83717f9c07d9e1f2579961b4","datatype-textsearch.html":"4b15e05be8b49a71b28aea22f2c46d0dbf639a91e7b4fe129a4c737c6daad47c","datatype-uuid.html":"292b307b3223c6182bdba687e7f69e6dca9038430143ad79d56327f6ed4c3246","datatype-xml.html":"061fc2295b7b69be704f0a741b8decd10ad721059f871fb0243c669bb54d7822","datatype.html":"e581f67c74e42006289638c8659e9b338adbfd7f068bd8bf2b3be3880fcd2254","domains.html":"82d486973ccc35d14276627a67ac76b434452f23f9ed4d8aa3fbb01fdf285fc2","functions-info.html":"78ac80bf81da2e4f4a33f3b850faa86f0f58ec54a083d6c31da557907de0df87","rangetypes.html":"e4960abc7ce8e51d794f94f1e57b099d30b7f75dd7f2b23a960eef91f6291c33","rowtypes.html":"74f99e029c3a9edfd3e18810ba3c8cb66d3aada91eb67b86476560871e6617e9","sql-createdomain.html":"e94f927196de1b0ea1ce35cd4603c1f6f544439adcc0edf1c4222358e832245b"},"ref":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2","revision":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","source_files":{"doc/src/sgml/datatype.sgml":"86328daa77e20d81d222376ec0306d9841a17104e436c08d138eb763aefb3700","src/include/catalog/pg_am.dat":"b3cb86b102a42fb0024cbd99779c9897afb40d12a71f5cc5a0e5aee7ff2f9969","src/include/catalog/pg_am.h":"3426799df799f32163fcc0765f43dbdb5e88e626acf31284fef9fc6014b69d8e","src/include/catalog/pg_cast.dat":"97911281ca2c81917394ccb2ec13367e37c6801fd461d5e1a3d2f28b911b46b2","src/include/catalog/pg_cast.h":"de585c7df687d698e7d1d100dce8a794ac9da52414ea48169191019178c8f053","src/include/catalog/pg_opclass.dat":"4ee7d3619a6aa106e1c0db55de903931c7d11b1c60ba053a022519c55f8ee668","src/include/catalog/pg_opclass.h":"9c218537806c8ef0398302faaca7f91315dfc1198c48706cebffb479b68defaf","src/include/catalog/pg_operator.dat":"5d35b9b2ef5f9797cc815263927ef8a12367fedace53e828b4e8b320e2a7f703","src/include/catalog/pg_operator.h":"621b18cfffe102413d79b746ce7a10f77fc60fdd097bac34c22b31070d3ee3c4","src/include/catalog/pg_opfamily.dat":"3b694879027b858f2ecf1fa1152b7e30c6e15a216ba99c2384291c30e9d52311","src/include/catalog/pg_opfamily.h":"e1f5fc8aebd3042df847455bf66bf755f768c2aa8a9b03d22f7ec8563eba59c0","src/include/catalog/pg_proc.dat":"1f934ce80d460159dda137714374a35f9896315ea2476709cbb4da13b38ef95a","src/include/catalog/pg_proc.h":"f7d59f07c5b95e2f3c7141762a0f576e8a3af7581ba54c27537f4d500472bea5","src/include/catalog/pg_range.dat":"5c2271f8e89e9378d1887204e5785060b77405b5f0ff3e6c719250c9c1dcfcc4","src/include/catalog/pg_range.h":"45546d952b5181f989bd9234721897fc2b37005c8e49ce2ddc156f26974c3ca4","src/include/catalog/pg_type.dat":"5f5887b75677cba2d4a1a0cfeb355df5ed91f85d385fac88bd8d7c605b3578f9","src/include/catalog/pg_type.h":"8fb198749fd82b6c1818a3c18455f226f66116fcfeb3d4959c65ce3fda22802e"},"source_sha256":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"},"sections":[],"signature":"datemultirange","sources":[{"label":"PostgreSQL 18 English manual","path":"rangetypes.html","sha256":"e4960abc7ce8e51d794f94f1e57b099d30b7f75dd7f2b23a960eef91f6291c33","url":"/docs/18/rangetypes.html"},{"label":"Matching PostgreSQL source archive","sha256":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","url":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2"}]},"ManualEvidence":{"manual_path":"rangetypes.html","release":{"channel":"stable","label":"18.6","major":"18","manual_sha256":{"arrays.html":"0e1d5c5a7b4a949d4439995f44f74ad4264e050d16eb9ad2a850fbaee04032df","catalog-pg-type.html":"ea9c9313bab8e92a7da18ebc50c7ff1f908dd80999a7e0ca3bbc82ba626c58d4","datatype-binary.html":"0c03ec47b76e37888a3da7f5dcfd340128a5d356642a85185cc9ebe17856a904","datatype-bit.html":"c49124aad561636c18080b9e09572fde7785a6b9eae86f5a33283f0fd6774c37","datatype-boolean.html":"b633f663e6c8276e7056287640c413aa79dbc15d5353cee0d41653d58d376956","datatype-character.html":"c73eebe413ea709a7792e4cfc3cfe9fb68cb496ebe83c3ebaa9d09f8c40d0f73","datatype-datetime.html":"e366275d8b13845bfa4ef0d68faa093e5f3ecb708a1ec870cbcbf7e425b85699","datatype-enum.html":"cab38e53222e30cc94b24de4b33634fd22d7c93f58ebcf35f646c9b8da78fae8","datatype-geometric.html":"0eb3053cfe7d6b0a4b5592c5d7af8d97cae527e87f063c5793413bcba5c14cd9","datatype-json.html":"650162a05b660146b500a205d523374d00aee117a6a6240d30262be51b6e33e5","datatype-money.html":"8b29c14b90679683ac5c472b59a0a7e2853f32cc7db43fab417b2697a6879964","datatype-net-types.html":"96846e641cc37727ddcd8b8a1aa0dbe33cd9787760a2b4efb0dcb9403d5be63f","datatype-numeric.html":"b74619f7ac2f1ca9f85c48073df78c33dd30e464e61baba6008634534edcb500","datatype-oid.html":"8b3360302225f08ae72716ce1804596de95b568e5d31f788cb4597427b28cc38","datatype-pg-lsn.html":"339172f5107cc7eb7139bec911277647219e92a532cebd0f3da0a5dacf8195af","datatype-pseudo.html":"c7cf0b8214304bd8702c95f2142671ae5f0d631f83717f9c07d9e1f2579961b4","datatype-textsearch.html":"4b15e05be8b49a71b28aea22f2c46d0dbf639a91e7b4fe129a4c737c6daad47c","datatype-uuid.html":"292b307b3223c6182bdba687e7f69e6dca9038430143ad79d56327f6ed4c3246","datatype-xml.html":"061fc2295b7b69be704f0a741b8decd10ad721059f871fb0243c669bb54d7822","datatype.html":"e581f67c74e42006289638c8659e9b338adbfd7f068bd8bf2b3be3880fcd2254","domains.html":"82d486973ccc35d14276627a67ac76b434452f23f9ed4d8aa3fbb01fdf285fc2","functions-info.html":"78ac80bf81da2e4f4a33f3b850faa86f0f58ec54a083d6c31da557907de0df87","rangetypes.html":"e4960abc7ce8e51d794f94f1e57b099d30b7f75dd7f2b23a960eef91f6291c33","rowtypes.html":"74f99e029c3a9edfd3e18810ba3c8cb66d3aada91eb67b86476560871e6617e9","sql-createdomain.html":"e94f927196de1b0ea1ce35cd4603c1f6f544439adcc0edf1c4222358e832245b"},"ref":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2","revision":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","source_files":{"doc/src/sgml/datatype.sgml":"86328daa77e20d81d222376ec0306d9841a17104e436c08d138eb763aefb3700","src/include/catalog/pg_am.dat":"b3cb86b102a42fb0024cbd99779c9897afb40d12a71f5cc5a0e5aee7ff2f9969","src/include/catalog/pg_am.h":"3426799df799f32163fcc0765f43dbdb5e88e626acf31284fef9fc6014b69d8e","src/include/catalog/pg_cast.dat":"97911281ca2c81917394ccb2ec13367e37c6801fd461d5e1a3d2f28b911b46b2","src/include/catalog/pg_cast.h":"de585c7df687d698e7d1d100dce8a794ac9da52414ea48169191019178c8f053","src/include/catalog/pg_opclass.dat":"4ee7d3619a6aa106e1c0db55de903931c7d11b1c60ba053a022519c55f8ee668","src/include/catalog/pg_opclass.h":"9c218537806c8ef0398302faaca7f91315dfc1198c48706cebffb479b68defaf","src/include/catalog/pg_operator.dat":"5d35b9b2ef5f9797cc815263927ef8a12367fedace53e828b4e8b320e2a7f703","src/include/catalog/pg_operator.h":"621b18cfffe102413d79b746ce7a10f77fc60fdd097bac34c22b31070d3ee3c4","src/include/catalog/pg_opfamily.dat":"3b694879027b858f2ecf1fa1152b7e30c6e15a216ba99c2384291c30e9d52311","src/include/catalog/pg_opfamily.h":"e1f5fc8aebd3042df847455bf66bf755f768c2aa8a9b03d22f7ec8563eba59c0","src/include/catalog/pg_proc.dat":"1f934ce80d460159dda137714374a35f9896315ea2476709cbb4da13b38ef95a","src/include/catalog/pg_proc.h":"f7d59f07c5b95e2f3c7141762a0f576e8a3af7581ba54c27537f4d500472bea5","src/include/catalog/pg_range.dat":"5c2271f8e89e9378d1887204e5785060b77405b5f0ff3e6c719250c9c1dcfcc4","src/include/catalog/pg_range.h":"45546d952b5181f989bd9234721897fc2b37005c8e49ce2ddc156f26974c3ca4","src/include/catalog/pg_type.dat":"5f5887b75677cba2d4a1a0cfeb355df5ed91f85d385fac88bd8d7c605b3578f9","src/include/catalog/pg_type.h":"8fb198749fd82b6c1818a3c18455f226f66116fcfeb3d4959c65ce3fda22802e"},"source_sha256":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"},"sources":[{"label":"PostgreSQL 18 English manual","path":"rangetypes.html","sha256":"e4960abc7ce8e51d794f94f1e57b099d30b7f75dd7f2b23a960eef91f6291c33","url":"/docs/18/rangetypes.html"},{"label":"Matching PostgreSQL source archive","sha256":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","url":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2"}]},"MeasuredEvidence":{}},"Text":{"Collection":"type","Key":"datemultirange","SourceDatabase":"center","Version":"18","Locale":"en","Title":"datemultirange","Summary":"multirange of dates","BodyHTML":"\u003cdiv id=\"RANGETYPES\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch2\u003e8.17. Range Types \u003c/h2\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\n\u003cp\u003eRange types are data types representing a range of values of some element type (called the range\u0026#39;s \u003cem\u003esubtype\u003c/em\u003e). For instance, ranges of \u003ccode\u003etimestamp\u003c/code\u003e might be used to represent the ranges of time that a meeting room is reserved. In this case the data type is \u003ccode\u003etsrange\u003c/code\u003e (short for \u003cspan\u003e“\u003cspan\u003etimestamp range\u003c/span\u003e”\u003c/span\u003e), and \u003ccode\u003etimestamp\u003c/code\u003e 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.\u003c/p\u003e\n\u003cp\u003eRange 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.\u003c/p\u003e\n\u003cp\u003eEvery 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.\u003c/p\u003e\n\u003cdiv id=\"RANGETYPES-BUILTIN\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3\u003e8.17.1. Built-in Range and Multirange Types \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003ePostgreSQL comes with the following built-in range types:\u003c/p\u003e\n\u003cdiv\u003e\n\u003cul\u003e\n\u003cli\u003e\n\u003cp\u003e\u003ccode\u003eint4range\u003c/code\u003e — Range of \u003ccode\u003einteger\u003c/code\u003e, \u003ccode\u003eint4multirange\u003c/code\u003e — corresponding Multirange\u003c/p\u003e\n\u003c/li\u003e\n\u003cli\u003e\n\u003cp\u003e\u003ccode\u003eint8range\u003c/code\u003e — Range of \u003ccode\u003ebigint\u003c/code\u003e, \u003ccode\u003eint8multirange\u003c/code\u003e — corresponding Multirange\u003c/p\u003e\n\u003c/li\u003e\n\u003cli\u003e\n\u003cp\u003e\u003ccode\u003enumrange\u003c/code\u003e — Range of \u003ccode\u003enumeric\u003c/code\u003e, \u003ccode\u003enummultirange\u003c/code\u003e — corresponding Multirange\u003c/p\u003e\n\u003c/li\u003e\n\u003cli\u003e\n\u003cp\u003e\u003ccode\u003etsrange\u003c/code\u003e — Range of \u003ccode\u003etimestamp without time zone\u003c/code\u003e, \u003ccode\u003etsmultirange\u003c/code\u003e — corresponding Multirange\u003c/p\u003e\n\u003c/li\u003e\n\u003cli\u003e\n\u003cp\u003e\u003ccode\u003etstzrange\u003c/code\u003e — Range of \u003ccode\u003etimestamp with time zone\u003c/code\u003e, \u003ccode\u003etstzmultirange\u003c/code\u003e — corresponding Multirange\u003c/p\u003e\n\u003c/li\u003e\n\u003cli\u003e\n\u003cp\u003e\u003ccode\u003edaterange\u003c/code\u003e — Range of \u003ccode\u003edate\u003c/code\u003e, \u003ccode\u003edatemultirange\u003c/code\u003e — corresponding Multirange\u003c/p\u003e\n\u003c/li\u003e\n\u003c/ul\u003e\n\u003c/div\u003e\n\u003cp\u003eIn addition, you can define your own range types; see \u003ca href=\"/docs/18/sql-createtype.html\" title=\"CREATE TYPE\" rel=\"nofollow\"\u003e\u003cspan\u003eCREATE TYPE\u003c/span\u003e\u003c/a\u003e for more information.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv id=\"RANGETYPES-EXAMPLES\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3\u003e8.17.2. Examples \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cpre\u003eCREATE TABLE reservation (room int, during tsrange);\nINSERT INTO reservation VALUES\n    (1108, \u0026#39;[2010-01-01 14:30, 2010-01-01 15:30)\u0026#39;);\n\n-- Containment\nSELECT int4range(10, 20) @\u0026gt; 3;\n\n-- Overlaps\nSELECT numrange(11.1, 22.2) \u0026amp;\u0026amp; numrange(20.0, 30.0);\n\n-- Extract the upper bound\nSELECT upper(int8range(15, 25));\n\n-- Compute the intersection\nSELECT int4range(10, 20) * int4range(15, 25);\n\n-- Is the range empty?\nSELECT isempty(numrange(1, 5));\n\u003c/pre\u003e\n\u003cp\u003eSee \u003ca href=\"/docs/18/functions-range.html#RANGE-OPERATORS-TABLE\" rel=\"nofollow\"\u003eTable 9.58\u003c/a\u003e and \u003ca href=\"/docs/18/functions-range.html#RANGE-FUNCTIONS-TABLE\" rel=\"nofollow\"\u003eTable 9.60\u003c/a\u003e for complete lists of operators and functions on range types.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv id=\"RANGETYPES-INCLUSIVITY\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3\u003e8.17.3. Inclusive and Exclusive Bounds \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eEvery 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.\u003c/p\u003e\n\u003cp\u003eIn the text form of a range, an inclusive lower bound is represented by \u003cspan\u003e“\u003cspan\u003e\u003ccode\u003e[\u003c/code\u003e\u003c/span\u003e”\u003c/span\u003e while an exclusive lower bound is represented by \u003cspan\u003e“\u003cspan\u003e\u003ccode\u003e(\u003c/code\u003e\u003c/span\u003e”\u003c/span\u003e. Likewise, an inclusive upper bound is represented by \u003cspan\u003e“\u003cspan\u003e\u003ccode\u003e]\u003c/code\u003e\u003c/span\u003e”\u003c/span\u003e, while an exclusive upper bound is represented by \u003cspan\u003e“\u003cspan\u003e\u003ccode\u003e)\u003c/code\u003e\u003c/span\u003e”\u003c/span\u003e. (See \u003ca href=\"/docs/18/rangetypes.html#RANGETYPES-IO\" rel=\"nofollow\"\u003eSection 8.17.5\u003c/a\u003e for more details.)\u003c/p\u003e\n\u003cp\u003eThe functions \u003ccode\u003elower_inc\u003c/code\u003e and \u003ccode\u003eupper_inc\u003c/code\u003e test the inclusivity of the lower and upper bounds of a range value, respectively.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv id=\"RANGETYPES-INFINITE\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3\u003e8.17.4. Infinite (Unbounded) Ranges \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eThe lower bound of a range can be omitted, meaning that all values less than the upper bound are included in the range, e.g., \u003ccode\u003e(,3]\u003c/code\u003e. 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., \u003ccode\u003e[,]\u003c/code\u003e is converted to \u003ccode\u003e(,)\u003c/code\u003e. 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\u0026#39;s +/-infinity values.\u003c/p\u003e\n\u003cp\u003eElement types that have the notion of \u003cspan\u003e“\u003cspan\u003einfinity\u003c/span\u003e”\u003c/span\u003e can use them as explicit bound values. For example, with timestamp ranges, \u003ccode\u003e[today,infinity)\u003c/code\u003e excludes the special \u003ccode\u003etimestamp\u003c/code\u003e value \u003ccode\u003einfinity\u003c/code\u003e, while \u003ccode\u003e[today,infinity]\u003c/code\u003e include it, as does \u003ccode\u003e[today,)\u003c/code\u003e and \u003ccode\u003e[today,]\u003c/code\u003e.\u003c/p\u003e\n\u003cp\u003eThe functions \u003ccode\u003elower_inf\u003c/code\u003e and \u003ccode\u003eupper_inf\u003c/code\u003e test for infinite lower and upper bounds of a range, respectively.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv id=\"RANGETYPES-IO\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3\u003e8.17.5. Range Input/Output \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eThe input for a range value must follow one of the following patterns:\u003c/p\u003e\n\u003cpre\u003e(\u003cem\u003e\u003ccode\u003elower-bound\u003c/code\u003e\u003c/em\u003e,\u003cem\u003e\u003ccode\u003eupper-bound\u003c/code\u003e\u003c/em\u003e)\n(\u003cem\u003e\u003ccode\u003elower-bound\u003c/code\u003e\u003c/em\u003e,\u003cem\u003e\u003ccode\u003eupper-bound\u003c/code\u003e\u003c/em\u003e]\n[\u003cem\u003e\u003ccode\u003elower-bound\u003c/code\u003e\u003c/em\u003e,\u003cem\u003e\u003ccode\u003eupper-bound\u003c/code\u003e\u003c/em\u003e)\n[\u003cem\u003e\u003ccode\u003elower-bound\u003c/code\u003e\u003c/em\u003e,\u003cem\u003e\u003ccode\u003eupper-bound\u003c/code\u003e\u003c/em\u003e]\nempty\n\u003c/pre\u003e\n\u003cp\u003eThe parentheses or brackets indicate whether the lower and upper bounds are exclusive or inclusive, as described previously. Notice that the final pattern is \u003ccode\u003eempty\u003c/code\u003e, which represents an empty range (a range that contains no points).\u003c/p\u003e\n\u003cp\u003eThe \u003cem\u003e\u003ccode\u003elower-bound\u003c/code\u003e\u003c/em\u003e may be either a string that is valid input for the subtype, or empty to indicate no lower bound. Likewise, \u003cem\u003e\u003ccode\u003eupper-bound\u003c/code\u003e\u003c/em\u003e may be either a string that is valid input for the subtype, or empty to indicate no upper bound.\u003c/p\u003e\n\u003cp\u003eEach bound value can be quoted using \u003ccode\u003e\u0026#34;\u003c/code\u003e (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 \u003ccode\u003e\u0026#34;\u0026#34;\u003c/code\u003e, since writing nothing means an infinite bound.\u003c/p\u003e\n\u003cp\u003eWhitespace 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.)\u003c/p\u003e\n\u003cdiv\u003e\n\u003ch3\u003eNote\u003c/h3\u003e\n\u003cp\u003eThese rules are very similar to those for writing field values in composite-type literals. See \u003ca href=\"/docs/18/rowtypes.html#ROWTYPES-IO-SYNTAX\" rel=\"nofollow\"\u003eSection 8.16.6\u003c/a\u003e for additional commentary.\u003c/p\u003e\n\u003c/div\u003e\n\u003cp\u003eExamples:\u003c/p\u003e\n\u003cpre\u003e-- includes 3, does not include 7, and does include all points in between\nSELECT \u0026#39;[3,7)\u0026#39;::int4range;\n\n-- does not include either 3 or 7, but includes all points in between\nSELECT \u0026#39;(3,7)\u0026#39;::int4range;\n\n-- includes only the single point 4\nSELECT \u0026#39;[4,4]\u0026#39;::int4range;\n\n-- includes no points (and will be normalized to \u0026#39;empty\u0026#39;)\nSELECT \u0026#39;[4,4)\u0026#39;::int4range;\n\u003c/pre\u003e\n\u003cp\u003eThe input for a multirange is curly brackets (\u003ccode\u003e{\u003c/code\u003e and \u003ccode\u003e}\u003c/code\u003e) 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.)\u003c/p\u003e\n\u003cp\u003eExamples:\u003c/p\u003e\n\u003cpre\u003eSELECT \u0026#39;{}\u0026#39;::int4multirange;\nSELECT \u0026#39;{[3,7)}\u0026#39;::int4multirange;\nSELECT \u0026#39;{[3,7), [8,9)}\u0026#39;::int4multirange;\n\u003c/pre\u003e\n\u003c/div\u003e\n\u003cdiv id=\"RANGETYPES-CONSTRUCT\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3\u003e8.17.6. Constructing Ranges and Multiranges \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eEach 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 \u003cspan\u003e“\u003cspan\u003e\u003ccode\u003e()\u003c/code\u003e\u003c/span\u003e”\u003c/span\u003e, \u003cspan\u003e“\u003cspan\u003e\u003ccode\u003e(]\u003c/code\u003e\u003c/span\u003e”\u003c/span\u003e, \u003cspan\u003e“\u003cspan\u003e\u003ccode\u003e[)\u003c/code\u003e\u003c/span\u003e”\u003c/span\u003e, or \u003cspan\u003e“\u003cspan\u003e\u003ccode\u003e[]\u003c/code\u003e\u003c/span\u003e”\u003c/span\u003e. For example:\u003c/p\u003e\n\u003cpre\u003e-- The full form is: lower bound, upper bound, and text argument indicating\n-- inclusivity/exclusivity of bounds.\nSELECT numrange(1.0, 14.0, \u0026#39;(]\u0026#39;);\n\n-- If the third argument is omitted, \u0026#39;[)\u0026#39; is assumed.\nSELECT numrange(1.0, 14.0);\n\n-- Although \u0026#39;(]\u0026#39; is specified here, on display the value will be converted to\n-- canonical form, since int8range is a discrete range type (see below).\nSELECT int8range(1, 14, \u0026#39;(]\u0026#39;);\n\n-- Using NULL for either bound causes the range to be unbounded on that side.\nSELECT numrange(NULL, 2.2);\n\u003c/pre\u003e\n\u003cp\u003eEach 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:\u003c/p\u003e\n\u003cpre\u003eSELECT nummultirange();\nSELECT nummultirange(numrange(1.0, 14.0));\nSELECT nummultirange(numrange(1.0, 14.0), numrange(20.0, 25.0));\n\u003c/pre\u003e\n\u003c/div\u003e\n\u003cdiv id=\"RANGETYPES-DISCRETE\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3\u003e8.17.7. Discrete Range Types \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eA discrete range is one whose element type has a well-defined \u003cspan\u003e“\u003cspan\u003estep\u003c/span\u003e”\u003c/span\u003e, such as \u003ccode\u003einteger\u003c/code\u003e or \u003ccode\u003edate\u003c/code\u003e. 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\u0026#39;s always (or almost always) possible to identify other element values between two given values. For example, a range over the \u003ccode\u003enumeric\u003c/code\u003e type is continuous, as is a range over \u003ccode\u003etimestamp\u003c/code\u003e. (Even though \u003ccode\u003etimestamp\u003c/code\u003e has limited precision, and so could theoretically be treated as discrete, it\u0026#39;s better to consider it continuous since the step size is normally not of interest.)\u003c/p\u003e\n\u003cp\u003eAnother way to think about a discrete range type is that there is a clear idea of a \u003cspan\u003e“\u003cspan\u003enext\u003c/span\u003e”\u003c/span\u003e or \u003cspan\u003e“\u003cspan\u003eprevious\u003c/span\u003e”\u003c/span\u003e value for each element value. Knowing that, it is possible to convert between inclusive and exclusive representations of a range\u0026#39;s bounds, by choosing the next or previous element value instead of the one originally given. For example, in an integer range type \u003ccode\u003e[4,8]\u003c/code\u003e and \u003ccode\u003e(3,9)\u003c/code\u003e denote the same set of values; but this would not be so for a range over numeric.\u003c/p\u003e\n\u003cp\u003eA discrete range type should have a \u003cem\u003ecanonicalization\u003c/em\u003e 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.\u003c/p\u003e\n\u003cp\u003eThe built-in range types \u003ccode\u003eint4range\u003c/code\u003e, \u003ccode\u003eint8range\u003c/code\u003e, and \u003ccode\u003edaterange\u003c/code\u003e all use a canonical form that includes the lower bound and excludes the upper bound; that is, \u003ccode\u003e[)\u003c/code\u003e. User-defined range types can use other conventions, however.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv id=\"RANGETYPES-DEFINING\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3\u003e8.17.8. Defining New Range Types \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eUsers 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 \u003ccode\u003efloat8\u003c/code\u003e:\u003c/p\u003e\n\u003cpre\u003eCREATE TYPE floatrange AS RANGE (\n    subtype = float8,\n    subtype_diff = float8mi\n);\n\nSELECT \u0026#39;[1.234, 5.678]\u0026#39;::floatrange;\n\u003c/pre\u003e\n\u003cp\u003eBecause \u003ccode\u003efloat8\u003c/code\u003e has no meaningful \u003cspan\u003e“\u003cspan\u003estep\u003c/span\u003e”\u003c/span\u003e, we do not define a canonicalization function in this example.\u003c/p\u003e\n\u003cp\u003eWhen you define your own range you automatically get a corresponding multirange type.\u003c/p\u003e\n\u003cp\u003eDefining 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.\u003c/p\u003e\n\u003cp\u003eIf the subtype is considered to have discrete rather than continuous values, the \u003ccode\u003eCREATE TYPE\u003c/code\u003e command should specify a \u003ccode\u003ecanonical\u003c/code\u003e 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 \u003ccode\u003e[1, 7]\u003c/code\u003e and \u003ccode\u003e[1, 8)\u003c/code\u003e, must be identical. It doesn\u0026#39;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 \u003ccode\u003etimestamp\u003c/code\u003e 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\u0026#39;t a multiple of an hour, or perhaps throw an error instead.\u003c/p\u003e\n\u003cp\u003eIn addition, any range type that is meant to be used with GiST or SP-GiST indexes should define a subtype difference, or \u003ccode\u003esubtype_diff\u003c/code\u003e, function. (The index will still work without \u003ccode\u003esubtype_diff\u003c/code\u003e, 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., \u003cem\u003e\u003ccode\u003eX\u003c/code\u003e\u003c/em\u003e minus \u003cem\u003e\u003ccode\u003eY\u003c/code\u003e\u003c/em\u003e) represented as a \u003ccode\u003efloat8\u003c/code\u003e value. In our example above, the function \u003ccode\u003efloat8mi\u003c/code\u003e that underlies the regular \u003ccode\u003efloat8\u003c/code\u003e 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 \u003ccode\u003esubtype_diff\u003c/code\u003e 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.\u003c/p\u003e\n\u003cp\u003eA less-oversimplified example of a \u003ccode\u003esubtype_diff\u003c/code\u003e function is:\u003c/p\u003e\n\u003cpre\u003eCREATE FUNCTION time_subtype_diff(x time, y time) RETURNS float8 AS\n\u0026#39;SELECT EXTRACT(EPOCH FROM (x - y))\u0026#39; LANGUAGE sql STRICT IMMUTABLE;\n\nCREATE TYPE timerange AS RANGE (\n    subtype = time,\n    subtype_diff = time_subtype_diff\n);\n\nSELECT \u0026#39;[11:10, 23:00]\u0026#39;::timerange;\n\u003c/pre\u003e\n\u003cp\u003eSee \u003ca href=\"/docs/18/sql-createtype.html\" title=\"CREATE TYPE\" rel=\"nofollow\"\u003e\u003cspan\u003eCREATE TYPE\u003c/span\u003e\u003c/a\u003e for more information about creating range types.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv id=\"RANGETYPES-INDEXING\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3\u003e8.17.9. Indexing \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eGiST 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:\u003c/p\u003e\n\u003cpre\u003eCREATE INDEX reservation_idx ON reservation USING GIST (during);\n\u003c/pre\u003e\n\u003cp\u003eA GiST or SP-GiST index on ranges can accelerate queries involving these range operators: \u003ccode\u003e=\u003c/code\u003e, \u003ccode\u003e\u0026amp;\u0026amp;\u003c/code\u003e, \u003ccode\u003e\u0026lt;@\u003c/code\u003e, \u003ccode\u003e@\u0026gt;\u003c/code\u003e, \u003ccode\u003e\u0026lt;\u0026lt;\u003c/code\u003e, \u003ccode\u003e\u0026gt;\u0026gt;\u003c/code\u003e, \u003ccode\u003e-|-\u003c/code\u003e, \u003ccode\u003e\u0026amp;\u0026lt;\u003c/code\u003e, and \u003ccode\u003e\u0026amp;\u0026gt;\u003c/code\u003e. 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: \u003ccode\u003e\u0026amp;\u0026amp;\u003c/code\u003e, \u003ccode\u003e\u0026lt;@\u003c/code\u003e, \u003ccode\u003e@\u0026gt;\u003c/code\u003e, \u003ccode\u003e\u0026lt;\u0026lt;\u003c/code\u003e, \u003ccode\u003e\u0026gt;\u0026gt;\u003c/code\u003e, \u003ccode\u003e-|-\u003c/code\u003e, \u003ccode\u003e\u0026amp;\u0026lt;\u003c/code\u003e, and \u003ccode\u003e\u0026amp;\u0026gt;\u003c/code\u003e. See \u003ca href=\"/docs/18/functions-range.html#RANGE-OPERATORS-TABLE\" rel=\"nofollow\"\u003eTable 9.58\u003c/a\u003e for more information.\u003c/p\u003e\n\u003cp\u003eIn 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 \u003ccode\u003e\u0026lt;\u003c/code\u003e and \u003ccode\u003e\u0026gt;\u003c/code\u003e operators, but the ordering is rather arbitrary and not usually useful in the real world. Range types\u0026#39; B-tree and hash support is primarily meant to allow sorting and hashing internally in queries, rather than creation of actual indexes.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv id=\"RANGETYPES-CONSTRAINT\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3\u003e8.17.10. Constraints on Ranges \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eWhile \u003ccode\u003eUNIQUE\u003c/code\u003e is a natural constraint for scalar values, it is usually unsuitable for range types. Instead, an exclusion constraint is often more appropriate (see \u003ca href=\"/docs/18/sql-createtable.html#SQL-CREATETABLE-EXCLUDE\" rel=\"nofollow\"\u003eCREATE TABLE ... CONSTRAINT ... EXCLUDE\u003c/a\u003e). Exclusion constraints allow the specification of constraints such as \u003cspan\u003e“\u003cspan\u003enon-overlapping\u003c/span\u003e”\u003c/span\u003e on a range type. For example:\u003c/p\u003e\n\u003cpre\u003eCREATE TABLE reservation (\n    during tsrange,\n    EXCLUDE USING GIST (during WITH \u0026amp;\u0026amp;)\n);\n\u003c/pre\u003e\n\u003cp\u003eThat constraint will prevent any overlapping values from existing in the table at the same time:\u003c/p\u003e\n\u003cpre\u003eINSERT INTO reservation VALUES\n    (\u0026#39;[2010-01-01 11:30, 2010-01-01 15:00)\u0026#39;);\nINSERT 0 1\n\nINSERT INTO reservation VALUES\n    (\u0026#39;[2010-01-01 14:45, 2010-01-01 15:45)\u0026#39;);\nERROR:  conflicting key value violates exclusion constraint \u0026#34;reservation_during_excl\u0026#34;\nDETAIL:  Key (during)=([\u0026#34;2010-01-01 14:45:00\u0026#34;,\u0026#34;2010-01-01 15:45:00\u0026#34;)) conflicts\nwith existing key (during)=([\u0026#34;2010-01-01 11:30:00\u0026#34;,\u0026#34;2010-01-01 15:00:00\u0026#34;)).\n\u003c/pre\u003e\n\u003cp\u003eYou can use the \u003ca href=\"/docs/18/btree-gist.html\" rel=\"nofollow\"\u003e\u003ccode\u003ebtree_gist\u003c/code\u003e\u003c/a\u003e extension to define exclusion constraints on plain scalar data types, which can then be combined with range exclusions for maximum flexibility. For example, after \u003ccode\u003ebtree_gist\u003c/code\u003e is installed, the following constraint will reject overlapping ranges only if the meeting room numbers are equal:\u003c/p\u003e\n\u003cpre\u003eCREATE EXTENSION btree_gist;\nCREATE TABLE room_reservation (\n    room text,\n    during tsrange,\n    EXCLUDE USING GIST (room WITH =, during WITH \u0026amp;\u0026amp;)\n);\n\nINSERT INTO room_reservation VALUES\n    (\u0026#39;123A\u0026#39;, \u0026#39;[2010-01-01 14:00, 2010-01-01 15:00)\u0026#39;);\nINSERT 0 1\n\nINSERT INTO room_reservation VALUES\n    (\u0026#39;123A\u0026#39;, \u0026#39;[2010-01-01 14:30, 2010-01-01 15:30)\u0026#39;);\nERROR:  conflicting key value violates exclusion constraint \u0026#34;room_reservation_room_during_excl\u0026#34;\nDETAIL:  Key (room, during)=(123A, [\u0026#34;2010-01-01 14:30:00\u0026#34;,\u0026#34;2010-01-01 15:30:00\u0026#34;)) conflicts\nwith existing key (room, during)=(123A, [\u0026#34;2010-01-01 14:00:00\u0026#34;,\u0026#34;2010-01-01 15:00:00\u0026#34;)).\n\nINSERT INTO room_reservation VALUES\n    (\u0026#39;123B\u0026#39;, \u0026#39;[2010-01-01 14:30, 2010-01-01 15:30)\u0026#39;);\nINSERT 0 1\n\u003c/pre\u003e\n\u003c/div\u003e\n\u003c/div\u003e","SourceRevision":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","ContentHash":"169402df164294271958e5e7f1570ec402885588d976db1252fb0164b0ec8bf8","Payload":{"description":["multirange of dates"],"manual_html":"\u003cdiv class=\"sect1\" id=\"RANGETYPES\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch2 class=\"title\"\u003e8.17. Range Types \u003c/h2\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\n\u003cp\u003eRange types are data types representing a range of values of some element type (called the range's \u003cem class=\"firstterm\"\u003esubtype\u003c/em\u003e). For instance, ranges of \u003ccode class=\"type\"\u003etimestamp\u003c/code\u003e might be used to represent the ranges of time that a meeting room is reserved. In this case the data type is \u003ccode class=\"type\"\u003etsrange\u003c/code\u003e (short for \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003etimestamp range\u003c/span\u003e”\u003c/span\u003e), and \u003ccode class=\"type\"\u003etimestamp\u003c/code\u003e 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.\u003c/p\u003e\n\u003cp\u003eRange 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.\u003c/p\u003e\n\u003cp\u003eEvery 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.\u003c/p\u003e\n\u003cdiv class=\"sect2\" id=\"RANGETYPES-BUILTIN\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.17.1. Built-in Range and Multirange Types \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003ePostgreSQL comes with the following built-in range types:\u003c/p\u003e\n\u003cdiv class=\"itemizedlist\"\u003e\n\u003cul class=\"itemizedlist\"\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003e\u003ccode class=\"type\"\u003eint4range\u003c/code\u003e — Range of \u003ccode class=\"type\"\u003einteger\u003c/code\u003e, \u003ccode class=\"type\"\u003eint4multirange\u003c/code\u003e — corresponding Multirange\u003c/p\u003e\n\u003c/li\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003e\u003ccode class=\"type\"\u003eint8range\u003c/code\u003e — Range of \u003ccode class=\"type\"\u003ebigint\u003c/code\u003e, \u003ccode class=\"type\"\u003eint8multirange\u003c/code\u003e — corresponding Multirange\u003c/p\u003e\n\u003c/li\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003e\u003ccode class=\"type\"\u003enumrange\u003c/code\u003e — Range of \u003ccode class=\"type\"\u003enumeric\u003c/code\u003e, \u003ccode class=\"type\"\u003enummultirange\u003c/code\u003e — corresponding Multirange\u003c/p\u003e\n\u003c/li\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003e\u003ccode class=\"type\"\u003etsrange\u003c/code\u003e — Range of \u003ccode class=\"type\"\u003etimestamp without time zone\u003c/code\u003e, \u003ccode class=\"type\"\u003etsmultirange\u003c/code\u003e — corresponding Multirange\u003c/p\u003e\n\u003c/li\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003e\u003ccode class=\"type\"\u003etstzrange\u003c/code\u003e — Range of \u003ccode class=\"type\"\u003etimestamp with time zone\u003c/code\u003e, \u003ccode class=\"type\"\u003etstzmultirange\u003c/code\u003e — corresponding Multirange\u003c/p\u003e\n\u003c/li\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003e\u003ccode class=\"type\"\u003edaterange\u003c/code\u003e — Range of \u003ccode class=\"type\"\u003edate\u003c/code\u003e, \u003ccode class=\"type\"\u003edatemultirange\u003c/code\u003e — corresponding Multirange\u003c/p\u003e\n\u003c/li\u003e\n\u003c/ul\u003e\n\u003c/div\u003e\n\u003cp\u003eIn addition, you can define your own range types; see \u003ca class=\"xref\" href=\"/docs/18/sql-createtype.html\" title=\"CREATE TYPE\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE TYPE\u003c/span\u003e\u003c/a\u003e for more information.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"RANGETYPES-EXAMPLES\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.17.2. Examples \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cpre class=\"programlisting\"\u003eCREATE TABLE reservation (room int, during tsrange);\nINSERT INTO reservation VALUES\n    (1108, '[2010-01-01 14:30, 2010-01-01 15:30)');\n\n-- Containment\nSELECT int4range(10, 20) @\u0026gt; 3;\n\n-- Overlaps\nSELECT numrange(11.1, 22.2) \u0026amp;\u0026amp; numrange(20.0, 30.0);\n\n-- Extract the upper bound\nSELECT upper(int8range(15, 25));\n\n-- Compute the intersection\nSELECT int4range(10, 20) * int4range(15, 25);\n\n-- Is the range empty?\nSELECT isempty(numrange(1, 5));\n\u003c/pre\u003e\n\u003cp\u003eSee \u003ca class=\"xref\" href=\"/docs/18/functions-range.html#RANGE-OPERATORS-TABLE\" title=\"Table 9.58. Range Operators\"\u003eTable 9.58\u003c/a\u003e and \u003ca class=\"xref\" href=\"/docs/18/functions-range.html#RANGE-FUNCTIONS-TABLE\" title=\"Table 9.60. Range Functions\"\u003eTable 9.60\u003c/a\u003e for complete lists of operators and functions on range types.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"RANGETYPES-INCLUSIVITY\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.17.3. Inclusive and Exclusive Bounds \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eEvery 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.\u003c/p\u003e\n\u003cp\u003eIn the text form of a range, an inclusive lower bound is represented by \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003e\u003ccode class=\"literal\"\u003e[\u003c/code\u003e\u003c/span\u003e”\u003c/span\u003e while an exclusive lower bound is represented by \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003e\u003ccode class=\"literal\"\u003e(\u003c/code\u003e\u003c/span\u003e”\u003c/span\u003e. Likewise, an inclusive upper bound is represented by \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003e\u003ccode class=\"literal\"\u003e]\u003c/code\u003e\u003c/span\u003e”\u003c/span\u003e, while an exclusive upper bound is represented by \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003e\u003ccode class=\"literal\"\u003e)\u003c/code\u003e\u003c/span\u003e”\u003c/span\u003e. (See \u003ca class=\"xref\" href=\"/docs/18/rangetypes.html#RANGETYPES-IO\" title=\"8.17.5. Range Input/Output\"\u003eSection 8.17.5\u003c/a\u003e for more details.)\u003c/p\u003e\n\u003cp\u003eThe functions \u003ccode class=\"literal\"\u003elower_inc\u003c/code\u003e and \u003ccode class=\"literal\"\u003eupper_inc\u003c/code\u003e test the inclusivity of the lower and upper bounds of a range value, respectively.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"RANGETYPES-INFINITE\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.17.4. Infinite (Unbounded) Ranges \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eThe lower bound of a range can be omitted, meaning that all values less than the upper bound are included in the range, e.g., \u003ccode class=\"literal\"\u003e(,3]\u003c/code\u003e. 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., \u003ccode class=\"literal\"\u003e[,]\u003c/code\u003e is converted to \u003ccode class=\"literal\"\u003e(,)\u003c/code\u003e. 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.\u003c/p\u003e\n\u003cp\u003eElement types that have the notion of \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003einfinity\u003c/span\u003e”\u003c/span\u003e can use them as explicit bound values. For example, with timestamp ranges, \u003ccode class=\"literal\"\u003e[today,infinity)\u003c/code\u003e excludes the special \u003ccode class=\"type\"\u003etimestamp\u003c/code\u003e value \u003ccode class=\"literal\"\u003einfinity\u003c/code\u003e, while \u003ccode class=\"literal\"\u003e[today,infinity]\u003c/code\u003e include it, as does \u003ccode class=\"literal\"\u003e[today,)\u003c/code\u003e and \u003ccode class=\"literal\"\u003e[today,]\u003c/code\u003e.\u003c/p\u003e\n\u003cp\u003eThe functions \u003ccode class=\"literal\"\u003elower_inf\u003c/code\u003e and \u003ccode class=\"literal\"\u003eupper_inf\u003c/code\u003e test for infinite lower and upper bounds of a range, respectively.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"RANGETYPES-IO\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.17.5. Range Input/Output \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eThe input for a range value must follow one of the following patterns:\u003c/p\u003e\n\u003cpre class=\"synopsis\"\u003e(\u003cem class=\"replaceable\"\u003e\u003ccode\u003elower-bound\u003c/code\u003e\u003c/em\u003e,\u003cem class=\"replaceable\"\u003e\u003ccode\u003eupper-bound\u003c/code\u003e\u003c/em\u003e)\n(\u003cem class=\"replaceable\"\u003e\u003ccode\u003elower-bound\u003c/code\u003e\u003c/em\u003e,\u003cem class=\"replaceable\"\u003e\u003ccode\u003eupper-bound\u003c/code\u003e\u003c/em\u003e]\n[\u003cem class=\"replaceable\"\u003e\u003ccode\u003elower-bound\u003c/code\u003e\u003c/em\u003e,\u003cem class=\"replaceable\"\u003e\u003ccode\u003eupper-bound\u003c/code\u003e\u003c/em\u003e)\n[\u003cem class=\"replaceable\"\u003e\u003ccode\u003elower-bound\u003c/code\u003e\u003c/em\u003e,\u003cem class=\"replaceable\"\u003e\u003ccode\u003eupper-bound\u003c/code\u003e\u003c/em\u003e]\nempty\n\u003c/pre\u003e\n\u003cp\u003eThe parentheses or brackets indicate whether the lower and upper bounds are exclusive or inclusive, as described previously. Notice that the final pattern is \u003ccode class=\"literal\"\u003eempty\u003c/code\u003e, which represents an empty range (a range that contains no points).\u003c/p\u003e\n\u003cp\u003eThe \u003cem class=\"replaceable\"\u003e\u003ccode\u003elower-bound\u003c/code\u003e\u003c/em\u003e may be either a string that is valid input for the subtype, or empty to indicate no lower bound. Likewise, \u003cem class=\"replaceable\"\u003e\u003ccode\u003eupper-bound\u003c/code\u003e\u003c/em\u003e may be either a string that is valid input for the subtype, or empty to indicate no upper bound.\u003c/p\u003e\n\u003cp\u003eEach bound value can be quoted using \u003ccode class=\"literal\"\u003e\"\u003c/code\u003e (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 \u003ccode class=\"literal\"\u003e\"\"\u003c/code\u003e, since writing nothing means an infinite bound.\u003c/p\u003e\n\u003cp\u003eWhitespace 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.)\u003c/p\u003e\n\u003cdiv class=\"note\"\u003e\n\u003ch3 class=\"title\"\u003eNote\u003c/h3\u003e\n\u003cp\u003eThese rules are very similar to those for writing field values in composite-type literals. See \u003ca class=\"xref\" href=\"/docs/18/rowtypes.html#ROWTYPES-IO-SYNTAX\" title=\"8.16.6. Composite Type Input and Output Syntax\"\u003eSection 8.16.6\u003c/a\u003e for additional commentary.\u003c/p\u003e\n\u003c/div\u003e\n\u003cp\u003eExamples:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003e-- includes 3, does not include 7, and does include all points in between\nSELECT '[3,7)'::int4range;\n\n-- does not include either 3 or 7, but includes all points in between\nSELECT '(3,7)'::int4range;\n\n-- includes only the single point 4\nSELECT '[4,4]'::int4range;\n\n-- includes no points (and will be normalized to 'empty')\nSELECT '[4,4)'::int4range;\n\u003c/pre\u003e\n\u003cp\u003eThe input for a multirange is curly brackets (\u003ccode class=\"literal\"\u003e{\u003c/code\u003e and \u003ccode class=\"literal\"\u003e}\u003c/code\u003e) 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.)\u003c/p\u003e\n\u003cp\u003eExamples:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT '{}'::int4multirange;\nSELECT '{[3,7)}'::int4multirange;\nSELECT '{[3,7), [8,9)}'::int4multirange;\n\u003c/pre\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"RANGETYPES-CONSTRUCT\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.17.6. Constructing Ranges and Multiranges \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eEach 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 \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003e\u003ccode class=\"literal\"\u003e()\u003c/code\u003e\u003c/span\u003e”\u003c/span\u003e, \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003e\u003ccode class=\"literal\"\u003e(]\u003c/code\u003e\u003c/span\u003e”\u003c/span\u003e, \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003e\u003ccode class=\"literal\"\u003e[)\u003c/code\u003e\u003c/span\u003e”\u003c/span\u003e, or \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003e\u003ccode class=\"literal\"\u003e[]\u003c/code\u003e\u003c/span\u003e”\u003c/span\u003e. For example:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003e-- The full form is: lower bound, upper bound, and text argument indicating\n-- inclusivity/exclusivity of bounds.\nSELECT numrange(1.0, 14.0, '(]');\n\n-- If the third argument is omitted, '[)' is assumed.\nSELECT numrange(1.0, 14.0);\n\n-- Although '(]' is specified here, on display the value will be converted to\n-- canonical form, since int8range is a discrete range type (see below).\nSELECT int8range(1, 14, '(]');\n\n-- Using NULL for either bound causes the range to be unbounded on that side.\nSELECT numrange(NULL, 2.2);\n\u003c/pre\u003e\n\u003cp\u003eEach 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:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT nummultirange();\nSELECT nummultirange(numrange(1.0, 14.0));\nSELECT nummultirange(numrange(1.0, 14.0), numrange(20.0, 25.0));\n\u003c/pre\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"RANGETYPES-DISCRETE\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.17.7. Discrete Range Types \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eA discrete range is one whose element type has a well-defined \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003estep\u003c/span\u003e”\u003c/span\u003e, such as \u003ccode class=\"type\"\u003einteger\u003c/code\u003e or \u003ccode class=\"type\"\u003edate\u003c/code\u003e. 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 \u003ccode class=\"type\"\u003enumeric\u003c/code\u003e type is continuous, as is a range over \u003ccode class=\"type\"\u003etimestamp\u003c/code\u003e. (Even though \u003ccode class=\"type\"\u003etimestamp\u003c/code\u003e 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.)\u003c/p\u003e\n\u003cp\u003eAnother way to think about a discrete range type is that there is a clear idea of a \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003enext\u003c/span\u003e”\u003c/span\u003e or \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003eprevious\u003c/span\u003e”\u003c/span\u003e 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 \u003ccode class=\"literal\"\u003e[4,8]\u003c/code\u003e and \u003ccode class=\"literal\"\u003e(3,9)\u003c/code\u003e denote the same set of values; but this would not be so for a range over numeric.\u003c/p\u003e\n\u003cp\u003eA discrete range type should have a \u003cem class=\"firstterm\"\u003ecanonicalization\u003c/em\u003e 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.\u003c/p\u003e\n\u003cp\u003eThe built-in range types \u003ccode class=\"type\"\u003eint4range\u003c/code\u003e, \u003ccode class=\"type\"\u003eint8range\u003c/code\u003e, and \u003ccode class=\"type\"\u003edaterange\u003c/code\u003e all use a canonical form that includes the lower bound and excludes the upper bound; that is, \u003ccode class=\"literal\"\u003e[)\u003c/code\u003e. User-defined range types can use other conventions, however.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"RANGETYPES-DEFINING\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.17.8. Defining New Range Types \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eUsers 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 \u003ccode class=\"type\"\u003efloat8\u003c/code\u003e:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eCREATE TYPE floatrange AS RANGE (\n    subtype = float8,\n    subtype_diff = float8mi\n);\n\nSELECT '[1.234, 5.678]'::floatrange;\n\u003c/pre\u003e\n\u003cp\u003eBecause \u003ccode class=\"type\"\u003efloat8\u003c/code\u003e has no meaningful \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003estep\u003c/span\u003e”\u003c/span\u003e, we do not define a canonicalization function in this example.\u003c/p\u003e\n\u003cp\u003eWhen you define your own range you automatically get a corresponding multirange type.\u003c/p\u003e\n\u003cp\u003eDefining 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.\u003c/p\u003e\n\u003cp\u003eIf the subtype is considered to have discrete rather than continuous values, the \u003ccode class=\"command\"\u003eCREATE TYPE\u003c/code\u003e command should specify a \u003ccode class=\"literal\"\u003ecanonical\u003c/code\u003e 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 \u003ccode class=\"literal\"\u003e[1, 7]\u003c/code\u003e and \u003ccode class=\"literal\"\u003e[1, 8)\u003c/code\u003e, 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 \u003ccode class=\"type\"\u003etimestamp\u003c/code\u003e 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.\u003c/p\u003e\n\u003cp\u003eIn addition, any range type that is meant to be used with GiST or SP-GiST indexes should define a subtype difference, or \u003ccode class=\"literal\"\u003esubtype_diff\u003c/code\u003e, function. (The index will still work without \u003ccode class=\"literal\"\u003esubtype_diff\u003c/code\u003e, 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., \u003cem class=\"replaceable\"\u003e\u003ccode\u003eX\u003c/code\u003e\u003c/em\u003e minus \u003cem class=\"replaceable\"\u003e\u003ccode\u003eY\u003c/code\u003e\u003c/em\u003e) represented as a \u003ccode class=\"type\"\u003efloat8\u003c/code\u003e value. In our example above, the function \u003ccode class=\"function\"\u003efloat8mi\u003c/code\u003e that underlies the regular \u003ccode class=\"type\"\u003efloat8\u003c/code\u003e 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 \u003ccode class=\"literal\"\u003esubtype_diff\u003c/code\u003e 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.\u003c/p\u003e\n\u003cp\u003eA less-oversimplified example of a \u003ccode class=\"literal\"\u003esubtype_diff\u003c/code\u003e function is:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eCREATE FUNCTION time_subtype_diff(x time, y time) RETURNS float8 AS\n'SELECT EXTRACT(EPOCH FROM (x - y))' LANGUAGE sql STRICT IMMUTABLE;\n\nCREATE TYPE timerange AS RANGE (\n    subtype = time,\n    subtype_diff = time_subtype_diff\n);\n\nSELECT '[11:10, 23:00]'::timerange;\n\u003c/pre\u003e\n\u003cp\u003eSee \u003ca class=\"xref\" href=\"/docs/18/sql-createtype.html\" title=\"CREATE TYPE\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE TYPE\u003c/span\u003e\u003c/a\u003e for more information about creating range types.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"RANGETYPES-INDEXING\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.17.9. Indexing \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eGiST 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:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eCREATE INDEX reservation_idx ON reservation USING GIST (during);\n\u003c/pre\u003e\n\u003cp\u003eA GiST or SP-GiST index on ranges can accelerate queries involving these range operators: \u003ccode class=\"literal\"\u003e=\u003c/code\u003e, \u003ccode class=\"literal\"\u003e\u0026amp;\u0026amp;\u003c/code\u003e, \u003ccode class=\"literal\"\u003e\u0026lt;@\u003c/code\u003e, \u003ccode class=\"literal\"\u003e@\u0026gt;\u003c/code\u003e, \u003ccode class=\"literal\"\u003e\u0026lt;\u0026lt;\u003c/code\u003e, \u003ccode class=\"literal\"\u003e\u0026gt;\u0026gt;\u003c/code\u003e, \u003ccode class=\"literal\"\u003e-|-\u003c/code\u003e, \u003ccode class=\"literal\"\u003e\u0026amp;\u0026lt;\u003c/code\u003e, and \u003ccode class=\"literal\"\u003e\u0026amp;\u0026gt;\u003c/code\u003e. 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: \u003ccode class=\"literal\"\u003e\u0026amp;\u0026amp;\u003c/code\u003e, \u003ccode class=\"literal\"\u003e\u0026lt;@\u003c/code\u003e, \u003ccode class=\"literal\"\u003e@\u0026gt;\u003c/code\u003e, \u003ccode class=\"literal\"\u003e\u0026lt;\u0026lt;\u003c/code\u003e, \u003ccode class=\"literal\"\u003e\u0026gt;\u0026gt;\u003c/code\u003e, \u003ccode class=\"literal\"\u003e-|-\u003c/code\u003e, \u003ccode class=\"literal\"\u003e\u0026amp;\u0026lt;\u003c/code\u003e, and \u003ccode class=\"literal\"\u003e\u0026amp;\u0026gt;\u003c/code\u003e. See \u003ca class=\"xref\" href=\"/docs/18/functions-range.html#RANGE-OPERATORS-TABLE\" title=\"Table 9.58. Range Operators\"\u003eTable 9.58\u003c/a\u003e for more information.\u003c/p\u003e\n\u003cp\u003eIn 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 \u003ccode class=\"literal\"\u003e\u0026lt;\u003c/code\u003e and \u003ccode class=\"literal\"\u003e\u0026gt;\u003c/code\u003e 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.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"RANGETYPES-CONSTRAINT\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.17.10. Constraints on Ranges \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eWhile \u003ccode class=\"literal\"\u003eUNIQUE\u003c/code\u003e is a natural constraint for scalar values, it is usually unsuitable for range types. Instead, an exclusion constraint is often more appropriate (see \u003ca class=\"link\" href=\"/docs/18/sql-createtable.html#SQL-CREATETABLE-EXCLUDE\"\u003eCREATE TABLE ... CONSTRAINT ... EXCLUDE\u003c/a\u003e). Exclusion constraints allow the specification of constraints such as \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003enon-overlapping\u003c/span\u003e”\u003c/span\u003e on a range type. For example:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eCREATE TABLE reservation (\n    during tsrange,\n    EXCLUDE USING GIST (during WITH \u0026amp;\u0026amp;)\n);\n\u003c/pre\u003e\n\u003cp\u003eThat constraint will prevent any overlapping values from existing in the table at the same time:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eINSERT INTO reservation VALUES\n    ('[2010-01-01 11:30, 2010-01-01 15:00)');\nINSERT 0 1\n\nINSERT INTO reservation VALUES\n    ('[2010-01-01 14:45, 2010-01-01 15:45)');\nERROR:  conflicting key value violates exclusion constraint \"reservation_during_excl\"\nDETAIL:  Key (during)=([\"2010-01-01 14:45:00\",\"2010-01-01 15:45:00\")) conflicts\nwith existing key (during)=([\"2010-01-01 11:30:00\",\"2010-01-01 15:00:00\")).\n\u003c/pre\u003e\n\u003cp\u003eYou can use the \u003ca class=\"link\" href=\"/docs/18/btree-gist.html\" title=\"F.8. btree_gist — GiST operator classes with B-tree behavior\"\u003e\u003ccode class=\"literal\"\u003ebtree_gist\u003c/code\u003e\u003c/a\u003e extension to define exclusion constraints on plain scalar data types, which can then be combined with range exclusions for maximum flexibility. For example, after \u003ccode class=\"literal\"\u003ebtree_gist\u003c/code\u003e is installed, the following constraint will reject overlapping ranges only if the meeting room numbers are equal:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eCREATE EXTENSION btree_gist;\nCREATE TABLE room_reservation (\n    room text,\n    during tsrange,\n    EXCLUDE USING GIST (room WITH =, during WITH \u0026amp;\u0026amp;)\n);\n\nINSERT INTO room_reservation VALUES\n    ('123A', '[2010-01-01 14:00, 2010-01-01 15:00)');\nINSERT 0 1\n\nINSERT INTO room_reservation VALUES\n    ('123A', '[2010-01-01 14:30, 2010-01-01 15:30)');\nERROR:  conflicting key value violates exclusion constraint \"room_reservation_room_during_excl\"\nDETAIL:  Key (room, during)=(123A, [\"2010-01-01 14:30:00\",\"2010-01-01 15:30:00\")) conflicts\nwith existing key (room, during)=(123A, [\"2010-01-01 14:00:00\",\"2010-01-01 15:00:00\")).\n\nINSERT INTO room_reservation VALUES\n    ('123B', '[2010-01-01 14:30, 2010-01-01 15:30)');\nINSERT 0 1\n\u003c/pre\u003e\n\u003c/div\u003e\n\u003c/div\u003e","related":[{"label":"pg_type catalog","url":"/wiki/catalog/pg_type/?v=18"},{"label":"pg_cast catalog","url":"/wiki/catalog/pg_cast/?v=18"},{"label":"pg_operator catalog","url":"/wiki/catalog/pg_operator/?v=18"},{"label":"pg_opclass catalog","url":"/wiki/catalog/pg_opclass/?v=18"},{"label":"Arrays","url":"/wiki/type/arrays/?v=18"}],"sections":[]}},"RequestedLocale":"zh-Hans","Fallback":true,"Versions":["14","15","16","17","18","19","20"],"Locales":["en"],"Signatures":null,"Spellings":null,"SQLState":null,"Evidence":null}