From 4429f6a9e3e12bb4af6e3677fbc78cd80f160252 Mon Sep 17 00:00:00 2001 From: Heikki Linnakangas Date: Thu, 3 Nov 2011 13:16:28 +0200 Subject: Support range data types. Selectivity estimation functions are missing for some range type operators, which is a TODO. Jeff Davis --- doc/src/sgml/catalogs.sgml | 77 ++++++++ doc/src/sgml/datatype.sgml | 16 +- doc/src/sgml/extend.sgml | 22 ++- doc/src/sgml/filelist.sgml | 1 + doc/src/sgml/func.sgml | 304 +++++++++++++++++++++++++++++++ doc/src/sgml/plpgsql.sgml | 6 +- doc/src/sgml/rangetypes.sgml | 373 ++++++++++++++++++++++++++++++++++++++ doc/src/sgml/ref/create_type.sgml | 61 ++++++- doc/src/sgml/xfunc.sgml | 6 +- 9 files changed, 850 insertions(+), 16 deletions(-) create mode 100644 doc/src/sgml/rangetypes.sgml (limited to 'doc/src') diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index cfecaa6931a..2063812942f 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -218,6 +218,11 @@ functions and procedures + + pg_range + information about range types + + pg_rewrite query rewrite rules @@ -4594,6 +4599,78 @@ + + <structname>pg_range</structname> + + + pg_range + + + + The catalog pg_range stores information about range types. + + + + <structname>pg_range</> Columns + + + + + Name + Type + References + Description + + + + + + rngtypid + oid + pg_type.oid + The type that is a range type + + + + rngsubtype + oid + pg_type.oid + Subtype of this range type, e.g. integer is the subtype of int4range + + + + rngcollation + oid + pg_collation.oid + The collation used when comparing range boundaries + + + + rngsubopc + oid + pg_opclass.oid + The operator class used when comparing range boundaries + + + + rngcanonical + regproc + pg_proc.oid + A function to convert a range into its canonical form + + + + rngsubdiff + regproc + pg_proc.oid + A function to return the distance between two lower and upper bound, as a double precision. Used for GiST support + + + +
+ + + <structname>pg_rewrite</structname> diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml index e7b3098f28f..fe59a1c7763 100644 --- a/doc/src/sgml/datatype.sgml +++ b/doc/src/sgml/datatype.sgml @@ -4173,6 +4173,8 @@ SET xmloption TO { DOCUMENT | CONTENT }; &rowtypes; + &rangetypes; + Object Identifier Types @@ -4443,6 +4445,10 @@ SELECT * FROM pg_attribute anyenum + + anyrange + + void @@ -4519,6 +4525,13 @@ SELECT * FROM pg_attribute ). + + anyrange + Indicates that a function accepts any range data type + (see and + ). + + anynonarray Indicates that a function accepts any non-array data type @@ -4583,7 +4596,8 @@ SELECT * FROM pg_attribute only void and record as a result type (plus trigger when the function is used as a trigger). Some also support polymorphic functions using the types anyarray, - anyelement, anyenum, and anynonarray. + anyelement, anyenum, anyrange, and + anynonarray. diff --git a/doc/src/sgml/extend.sgml b/doc/src/sgml/extend.sgml index 7079db3ed3f..f3850b391e0 100644 --- a/doc/src/sgml/extend.sgml +++ b/doc/src/sgml/extend.sgml @@ -198,14 +198,15 @@ - Four pseudo-types of special interest are anyelement, - anyarray, anynonarray, and anyenum, - which are collectively called polymorphic types. - Any function declared using these types is said to be - a polymorphic function. A polymorphic function can - operate on many different data types, with the specific data type(s) - being determined by the data types actually passed to it in a particular - call. + Five pseudo-types of special interest are anyelement, + anyarray, anynonarray, anyenum, + and anyrange, which are collectively + called polymorphic types. Any function declared + using these types is said to be a polymorphic + function. A polymorphic function can operate on many + different data types, with the specific data type(s) being + determined by the data types actually passed to it in a + particular call. @@ -221,6 +222,11 @@ anyelement, the actual array type in the anyarray positions must be an array whose elements are the same type appearing in the anyelement positions. + Similarly, if there are positions declared anyrange + and others declared + anyelement, the actual range type in the + anyrange positions must be a range whose subtype is + the same type appearing in the anyelement positions. anynonarray is treated exactly the same as anyelement, but adds the additional constraint that the actual type must not be an array type. diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgml index ed39e0b6612..fb69415f800 100644 --- a/doc/src/sgml/filelist.sgml +++ b/doc/src/sgml/filelist.sgml @@ -25,6 +25,7 @@ + diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 2b8298c3e0c..f81bb9db977 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -10457,6 +10457,310 @@ SELECT NULLIF(value, '(none)') ... + + Range Functions and Operators + + + shows the operators + available for range types. + + + + Range Operators + + + + Operator + Description + Example + Result + + + + + = + equal + int4range(1,5) = '[1,4]'::int4range + t + + + + <> + not equal + numrange(1.1,2.2) <> numrange(1.1,2.3) + t + + + + < + less than + int4range(1,10) < int4range(2,3) + t + + + + > + greater than + int4range(1,10) > int4range(1,5) + t + + + + <= + less than or equal + numrange(1.1,2.2) <= numrange(1.1,2.2) + t + + + + >= + greater than or equal + numrange(1.1,2.2) >= numrange(1.1,2.0) + t + + + + @> + contains + '[2011-01-01,2011-03-01)'::tsrange @> '2011-01-10'::timestamp + t + + + + <@ + is contained by + int4range(2,4) <@ int4range(1,7) + t + + + + && + overlap (have points in common) + int8range(3,7) && int8range(4,12) + t + + + + << + strictly left of + int8range(1,10) << int8range(100,110) + t + + + + >> + strictly right of + int8range(50,60) >> int8range(20,30) + t + + + + &< + Does not extend to the right of? + int8range(1,20) &< int8range(18,20) + t + + + + &> + Does not extend to the left of? + int8range(7,20) &> int8range(5,10) + t + + + + -|- + adjacent? + numrange(1.1,2.2) -|- numrange(2.2,3.3) + t + + + + + + Union + numrange(5,15) + numrange(10,20) + [5,20) + + + + - + Difference + int8range(5,15) - int8range(10,20) + [5,10) + + + + * + Intersection + int8range(5,15) * int8range(10,20) + [10,15) + + + + !? + Is empty? + 'empty'::int4range !? + t + + + + ? + Is non-empty? + numrange(1.0,2.0)? + t + + + +
+ + + Range comparisons compare the lower bounds first, and only if + equal, compare the upper bounds. This is generally most useful for + B-tree indexes, rather than being useful comparisons by themselves. + + + + See for more details about range operator + behavior. + + + + shows the functions + available for use with range types. See + for more information and examples of the use of these functions. + + + + lower + + + upper + + + empty + + + non_empty + + + lower_inc + + + upper_inc + + + lower_inf + + + upper_inf + + + + Range Functions + + + + Function + Return Type + Description + Example + Result + + + + + + + lower(anyrange) + + + anyrange + lower bound of range + lower(numrange(1.1,2.2)) + 1.1 + + + + + upper(anyrange) + + + anyrange + upper bound of range + upper(numrange(1.1,2.2)) + 2.2 + + + + + empty(anyrange) + + + anyrange + is the range empty? + empty(numrange(1.1,2.2)) + false + + + + + non_empty(anyrange) + + + anyrange + is the range non-empty? + non_empty(numrange(1.1,2.2)) + true + + + + + lower_inc(anyrange) + + + anyrange + is the lower bound of the range inclusive? + lower_inc(numrange(1.1,2.2)) + true + + + + + upper_inc(anyrange) + + + anyrange + is the upper bound of the range inclusive? + upper_inc(numrange(1.1,2.2)) + false + + + + + lower_inf(anyrange) + + + anyrange + is the lower bound of the range infinite? + lower_inf('(,)'::daterange) + true + + + + + upper_inf(anyrange) + + + anyrange + is the upper bound of the range infinite? + upper_inf('(,)'::daterange) + true + + + +
+ + Aggregate Functions diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index 19c15ad26fc..f33cef55ed0 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -139,7 +139,7 @@ PL/pgSQL functions can also be declared to accept and return the polymorphic types anyelement, anyarray, anynonarray, - and anyenum. The actual + anyenum, and anyrange. The actual data types handled by a polymorphic function can vary from call to call, as discussed in . An example is shown in . @@ -500,8 +500,8 @@ $$ LANGUAGE plpgsql; When the return type of a PL/pgSQL function is declared as a polymorphic type (anyelement, - anyarray, anynonarray, or anyenum), - a special parameter $0 + anyarray, anynonarray, anyenum, + or anyrange), a special parameter $0 is created. Its data type is the actual return type of the function, as deduced from the actual input types (see ). diff --git a/doc/src/sgml/rangetypes.sgml b/doc/src/sgml/rangetypes.sgml new file mode 100644 index 00000000000..fc5896d8f42 --- /dev/null +++ b/doc/src/sgml/rangetypes.sgml @@ -0,0 +1,373 @@ + + + + Range Types + + + range type + + + + Range types are data types representing a range of values over some + sub-type with a total order. For instance, ranges + of timestamp might be used to represent the ranges of + time that a meeting room is reserved. In this case the data type + is tsrange (short for "timestamp range"), + and timestamp is the sub-type with a total order. + + + + Range types are useful because they represent many points in a + single value. The use of time and date ranges for scheduling + purposes is the clearest example; but price ranges, measurement + ranges from an instrument, etc., are also useful. + + + + Built-in Range Types + + PostgreSQL comes with the following built-in range types: + + + + INT4RANGE -- Range of INTEGER. This is a discrete range type, see . + + + + + INT8RANGE -- Range of BIGINT. This is a discrete range type, see . + + + + + NUMRANGE -- Range of NUMERIC. + + + + + TSRANGE -- Range of TIMESTAMP WITHOUT TIME ZONE. + + + + + TSTZRANGE -- Range of TIMESTAMP WITH TIME ZONE. + + + + + DATERANGE -- Range of DATE. This is a discrete range type, see . + + + + In addition, you can define your own; see for more information. + + + + + Examples + + +CREATE TABLE reservation ( during TSRANGE ); +INSERT INTO reservation VALUES + ( '[2010-01-01 14:30, 2010-01-01 15:30)' ); + +-- Containment +SELECT int4range(10, 20) @> 3; + +-- Overlaps +SELECT numrange(11.1, 22.2) && numrange(20.0, 30.0); + +-- Find the upper bound: +SELECT upper(int8range(15, 25)); + +-- Compute the intersection: +SELECT int4range(10, 20) * int4range(15, 25); + +-- Is the range non-empty? +SELECT numrange(1, 5)? ; + + + + See + and for complete lists of + functions and operators on range types. + + + + + Inclusive and Exclusive Bounds + + Every range has two bounds, the lower bound and the upper bound. All + points in between those 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. + + + An inclusive lower bound is represented by [ + while an exclusive lower bound is represented + by ( (see + and below). Likewise, an inclusive + upper bound is represented by ], while an + exclusive upper bound is represented by ). + + + Functions lower_inc + and upper_inc test the inclusivity of the lower + and upper bounds of a range, respectively. + + + + + Infinite (unbounded) Ranges + + The lower bound of a range can be omitted, meaning that all points + less (or equal to, if inclusive) than the upper bound are included + in the range. Likewise, if the upper bound of the range is omitted, + then all points greater than (or equal to, if omitted) the lower + bound are included in the range. If both lower and upper bounds are + omitted, all points are considered to be in the range. + + + Functions lower_inf + and upper_inf test the range for infinite lower + and upper bounds of a range, respectively. + + + + + Input/Output + + The input follows one of the following patterns: + +(lower-bound,upper-bound) +(lower-bound,upper-bound] +[lower-bound,upper-bound) +[lower-bound,upper-bound] +empty + + Notice that the final pattern is empty, which + represents an empty range (a range that contains no points). + + + The lower-bound may be either a string + that is valid input for the sub-type, or omitted (to indicate no + lower bound); and upper-bound may be + either a string that is valid input for the sub-type, or omitted (to + indicate no upper bound). + + + Either the lower-bound or + the upper-bound may be quoted + using "" (double quotation marks), which will allow + special characters such as ",". Within quotation + marks, "\" (backslash) serves as an escape + character. + + + The choice between the other input formats affects the inclusivity + of the bounds. See . + + + Examples: + +-- includes point 3, does not include point 7, and does include all points in between +select '[3,7)' + +-- does not include either 3 or 7, but includes all points in between +select '(3,7)' + +-- includes only the single point 4 +select '[4,4]' + + + + + + Constructing Ranges + + Each range type has a constructor by the same name. The constructor + accepts from zero to three arguments. The zero-argument form + constructs an empty range; the one-argument form constructs a + singleton range; the two-argument form constructs a range + in [ ) form; and the three-argument form + constructs a range in a form specified by the third argument. For + example: + +-- Three-argument form: lower bound, upper bound, and third argument indicating +-- inclusivity/exclusivity of bounds (if omitted, defaults to '[)'). +SELECT numrange(1.0, 14.0, '(]'); + +-- The int4range input will exclude the lower bound and include the upper bound; but the +-- resulting output will appear in the canonical form; see . +SELECT int8range(1, 14, '(]'); + +-- Single argument form constructs a singleton range; that is a range consisting of just +-- one point. +SELECT numrange(11.1); + +-- Zero-argument form constructs and empty range. +SELECT numrange(); + +-- Using NULL for a bound causes the range to be unbounded on that side; that is, negative +-- infinity for the lower bound or positive infinity for the upper bound. +SELECT numrange(NULL,2.2); + + + + + + Discrete Range Types + + Discrete ranges are those that have a + defined canonical function. Loosely speaking, a + discrete range has a sub-type with a well-defined "step"; + e.g. INTEGER or DATE. + + + The canonical function should take an input range + value, and return an equal range value that may have a different + formatting. For instance, the integer range [1, + 7] could be represented by the equal integer + range [1, 8). The two values are equal because + there are no points within the integer domain + between 7 and 8, so not + including the end point 8 is the same as + including the end point 7. The canonical output + for two values that are equal, like [1, 7] + and [1, 8), must be equal. It doesn't matter + which representation you choose to be the canonical one, as long as + two equal values with different formattings are always mapped to the + same value with the same formatting. If the canonical function is + not specified, then ranges with different formatting + (e.g. [1, 7] and [1, 8)) will + always be treated as unequal. + + + For types such as NUMRANGE, this is not possible, + because there are always points in between two + distinct NUMERIC values. + + + The built-in range + types INT4RANGE, INT8RANGE, + and DATERNAGE all use a canonical form that includes + the lower bound and excludes the upper bound; that is, [ + ). User-defined ranges can use other conventions, however. + + + + + Defining New Range Types + + Users can define their own range types. The most common reason to do + this is to use ranges where the subtype is not among the built-in + range types, e.g. a range of type FLOAT (or, if the + subtype itself is a user-defined type). + + + For example: to define a new range type of sub-type DOUBLE PRECISION: + +CREATE TYPE FLOATRANGE AS RANGE ( + SUBTYPE = DOUBLE PRECISION +); + +SELECT '[1.234, 5.678]'::floatrange; + + Because DOUBLE PRECISION has no meaningful "step", we + do not define a canonical + function. See for more + information. + + + Defining your own range type also allows you to specify a different + operator class or collation to use (which affects the points that + fall between the range boundaries), or a different canonicalization + function. + + + + + + range type + gist + + Indexing + + GiST indexes can be applied to a table containing a range type. For instance: + +CREATE INDEX reservation_idx ON reservation USING gist (during); + + This index may speed up queries + involving && + (overlaps), @> (contains), and all the boolean + operators found in this + table: . + + + + + + range type + exclude + + Constraints on Ranges + + While UNIQUE is a natural constraint for scalar + values, it is usually unsuitable for range types. Instead, an + exclusion constraint is often more appropriate + (see CREATE TABLE + ... CONSTRAINT ... EXCLUDE). Exclusion constraints allow the + specification of constraints such as "non-overlapping" on a range + type. For example: + +ALTER TABLE reservation + ADD EXCLUDE USING gist (during WITH &&); + + That constraint will prevent any overlapping values from existing + in the table at the same time: + +INSERT INTO reservation VALUES + ( '[2010-01-01 11:30, 2010-01-01 13:00)' ); +-- Result: INSERT 0 1 +INSERT INTO reservation VALUES + ( '[2010-01-01 14:45, 2010-01-01 15:45)' ); +-- Result: +-- ERROR: conflicting key value violates exclusion constraint "reservation_during_excl" +-- DETAIL: Key (during)=([ 2010-01-01 14:45:00, 2010-01-01 15:45:00 )) conflicts with +-- existing key (during)=([ 2010-01-01 14:30:00, 2010-01-01 15:30:00 )). + + + + Combine range types and exclusion constraints + with btree_gist for maximum + flexibility defining + constraints. After btree_gist is installed, the + following constraint will prevent overlapping ranges only if the + meeting room numbers are equal: + + +CREATE TABLE room_reservation +( + room TEXT, + during TSRANGE, + EXCLUDE USING gist (room WITH =, during WITH &&) +); + +INSERT INTO room_reservation VALUES + ( '123A', '[2010-01-01 14:00, 2010-01-01 15:00)' ); +-- Result: INSERT 0 1 +INSERT INTO room_reservation VALUES + ( '123A', '[2010-01-01 14:30, 2010-01-01 15:30)' ); +-- Result: +-- ERROR: conflicting key value violates exclusion constraint "room_reservation_room_during_excl" +-- DETAIL: Key (room, during)=(123A, [ 2010-01-01 14:30:00, 2010-01-01 15:30:00 )) conflicts with +-- existing key (room, during)=(123A, [ 2010-01-01 14:00:00, 2010-01-01 15:00:00 )). +INSERT INTO room_reservation VALUES + ( '123B', '[2010-01-01 14:30, 2010-01-01 15:30)' ); +-- Result: INSERT 0 1 + + + + + diff --git a/doc/src/sgml/ref/create_type.sgml b/doc/src/sgml/ref/create_type.sgml index ea45fadae69..ebcd461bd91 100644 --- a/doc/src/sgml/ref/create_type.sgml +++ b/doc/src/sgml/ref/create_type.sgml @@ -27,6 +27,15 @@ CREATE TYPE name AS CREATE TYPE name AS ENUM ( [ 'label' [, ... ] ] ) +CREATE TYPE name AS RANGE ( + SUBTYPE = subtype, + [ , SUBTYPE_OPCLASS = subtype_operator_class ] + [ , SUBTYPE_DIFF = subtype_diff_function ] + [ , CANONICAL = canonical_function ] + [ , ANALYZE = analyze_function ] + [ , COLLATION = collation ] +) + CREATE TYPE name ( INPUT = input_function, OUTPUT = output_function @@ -98,11 +107,61 @@ CREATE TYPE name + + Range Types + + + The third form of CREATE TYPE creates a new + range type, as described in . + + + + The subtype parameter + can be any type with an associated btree opclass (uses the type's + default btree operator class unless specified with + subtype_operator_class). + + + + The subtype_diff + function takes two values of type + subtype as argument, and + returns the distance between the two values as + double precision. This function is used for GiST indexing + (see for more information), and should be provided + for efficiency. + + + + The canonical + function takes an argument and returns a value, both of the same + type being defined. This is used to convert the range value to a + canonical form, when applicable. See + for more information. To define + a canonical function, + you must first create a shell type, which is a + placeholder type that has no properties except a name and an + owner. This is done by issuing the command CREATE TYPE + name, with no additional parameters. + + + + The analyze + function is the same as for creating a base type. + + + + The collation option + specifies the collation used when determining the total order for + the range. + + + Base Types - The third form of CREATE TYPE creates a new base type + The fourth form of CREATE TYPE creates a new base type (scalar type). To create a new base type, you must be a superuser. (This restriction is made because an erroneous type definition could confuse or even crash the server.) diff --git a/doc/src/sgml/xfunc.sgml b/doc/src/sgml/xfunc.sgml index 34e2cc29150..70643122046 100644 --- a/doc/src/sgml/xfunc.sgml +++ b/doc/src/sgml/xfunc.sgml @@ -997,8 +997,8 @@ $$ LANGUAGE SQL; SQL functions can be declared to accept and return the polymorphic types anyelement, - anyarray, anynonarray, and - anyenum. See anyarray, anynonarray, + anyenum, and anyrange. See for a more detailed explanation of polymorphic functions. Here is a polymorphic function make_array that builds up an array @@ -3046,7 +3046,7 @@ CREATE OR REPLACE FUNCTION retcomposite(IN integer, IN integer, C-language functions can be declared to accept and return the polymorphic types anyelement, anyarray, anynonarray, - and anyenum. + anyenum, and anyrange. See for a more detailed explanation of polymorphic functions. When function arguments or return types are defined as polymorphic types, the function author cannot know -- cgit v1.2.3