diff options
Diffstat (limited to 'src/man/pgbuiltin.3')
| -rw-r--r-- | src/man/pgbuiltin.3 | 1105 |
1 files changed, 1105 insertions, 0 deletions
diff --git a/src/man/pgbuiltin.3 b/src/man/pgbuiltin.3 new file mode 100644 index 00000000000..b4dd6c04156 --- /dev/null +++ b/src/man/pgbuiltin.3 @@ -0,0 +1,1105 @@ +.\" This is -*-nroff-*- +.\" XXX standard disclaimer belongs here.... +.\" $Header: /cvsroot/pgsql/src/man/Attic/pgbuiltin.3,v 1.1 1997/08/26 17:39:32 momjian Exp $ +.TH PGBUILTIN INTRO 04/01/97 PostgreSQL PostgreSQL +.SH "DESCRIPTION" +This section describes the data types, functions and operators +available to users in Postgres as it is distributed. +.SH "PGBUILTIN TYPES" +This section describes +.BR pgbuiltin +data types. +These Built-in types are installed in every database. +.PP +Users may add new types to Postgres using the +.IR "define type" +command described in this manual. User-defined types are not +described in this section. +.SH "List of built-in types" +.PP +.if n .ta 5 +15 +40 +.if t .ta 0.5i +1.5i +3.0i +.in 0 +.nf + \fBPOSTGRES Type\fP \fBMeaning\fP + abstime (absolute) limited-range date and time + aclitem access control list item + bool boolean + box 2-dimensional rectangle + bpchar blank-padded characters + bytea variable length array of bytes + char character + char2 array of 2 characters + char4 array of 4 characters + char8 array of 8 characters + char16 array of 16 characters + cid command identifier type + date ANSI SQL date type + datetime general-use date and time + filename large object filename + int alias for int4 + integer alias for int4 + int2 two-byte signed integer + int28 array of 8 int2 + int4 four-byte signed integer + float alias for float4 + float4 single-precision floating-point number + float8 double-precision floating-point number + lseg 2-dimensional line segment + money decimal type with fixed precision + name a multi-character type for storing system identifiers + oid object identifier type + oid8 array of 8 oid + oidchar16 oid and char16 composed + oidint2 oid and int2 composed + oidint4 oid and int4 composed + path open or closed line segments + point 2-dimensional geometric point + polygon 2-dimensional polygon (same as a closed path) + circle 2-dimensional circle (center and radius) + real alias for float4 + regproc registered procedure + reltime (relative) date and time span (duration) + smgr storage manager + smallint alias for int2 + text variable length array of characters + tid tuple identifier type + time ANSI SQL time type + timespan general-use time span (duration) + timestamp limited-range ISO-format date and time + tinterval time interval (start and stop abstime) + varchar variable-length characters + xid transaction identifier type + +.fi +.in +.PP +Many of the built-in types have obvious external formats. However, several +types are either unique to Postgres, such as open and closed paths, or have +several possibilities for formats, such as date and time types. + +.SH "Syntax of date and time types" +Some date and time types share code for data input. For those types (currently +.IR datetime +and +.IR abstime +, and +.IR timespan +and +.IR reltime ) +the input can have any of a wide variety of styles. For numeric date representations, +European and US conventions can differ, and the proper interpretation is obtained +by using the +.IR set (l) +command before entering data. +Output formats can be set to one of three styles: +ISO-8601, SQL (traditional Oracle/Ingres), and traditional +Postgres (see section on +.IR "absolute time" ) +with the SQL style having European and US variants (see +.IR set (l)). + +.SH "DATETIME" +General-use date and time is input using a wide range of +styles, including ISO-compatible, SQL-compatible, traditional +Postgres (see section on +.IR "absolute time") +and other permutations of date and time. Output styles can be ISO-compatible, +SQL-compatible, or traditional Postgres, with the default set to be compatible +with Postgres v6.0. +.PP +datetime is specified using the following syntax: +.PP +.nf +Year-Month-Day [ Hour : Minute : Second ] [AD,BC] [ Timezone ] +.nf + YearMonthDay [ Hour : Minute : Second ] [AD,BC] [ Timezone ] +.nf + Month Day [ Hour : Minute : Second ] Year [AD,BC] [ Timezone ] +.sp +where + Year is 4013 BC, ..., very large + Month is Jan, Feb, ..., Dec or 1, 2, ..., 12 + Day is 1, 2, ..., 31 + Hour is 00, 02, ..., 23 + Minute is 00, 01, ..., 59 + Second is 00, 01, ..., 59 (60 for leap second) + Timezone is 3 characters or ISO offset to GMT +.fi +.PP +Valid dates are from Nov 13 00:00:00 4013 BC GMT to far into the future. +Timezones are either three characters (e.g. "GMT" or "PST") or ISO-compatible +offsets to GMT (e.g. "-08" or "-08:00" when in Pacific Standard Time). +Dates are stored internally in Greenwich Mean Time. Input and output routines +translate time to the local time zone of the server. +.PP +The special values `current', +`infinity' and `-infinity' are provided. +`infinity' specifies a time later than any valid time, and +`-infinity' specifies a time earlier than any valid time. +`current' indicates that the current time should be +substituted whenever this value appears in a computation. +.PP +The strings +`now', +`today', +`yesterday', +`tomorrow', +and `epoch' can be used to specify +time values. `now' means the current time, and differs from +`current' in that the current time is immediately substituted +for it. `epoch' means Jan 1 00:00:00 1970 GMT. + +.SH "TIMESPAN" +General-use time span is input using a wide range of +syntaxes, including ISO-compatible, SQL-compatible, traditional +Postgres (see section on +.IR "relative time" +) and other permutations of time span. Output formats can be ISO-compatible, +SQL-compatible, or traditional Postgres, with the default set to be Postgres-compatible. +Months and years are a "qualitative" time interval, and are stored separately +from the other "quantitative" time intervals such as day or hour. For date arithmetic, +the qualitative time units are instantiated in the context of the relevant date or time. +.PP +Time span is specified with the following syntax: +.PP +.nf + Quantity Unit [Quantity Unit...] [Direction] +.nf +@ Quantity Unit [Direction] +.sp +where + Quantity is ..., `-1', `0', `1', `2', ... + Unit is `second', `minute', `hour', `day', `week', `month', `year', + or abbreviations or plurals of these units. + Direction is `ago'. +.fi +.SH "ABSOLUTE TIME" +Absolute time (abstime) is a limited-range (+/- 68 years) and limited-precision (1 sec) +date data type. +.IR "datetime" +may be preferred, since it +covers a larger range with greater precision. +.PP +Absolute time is specified using the following syntax: +.PP +.nf +Month Day [ Hour : Minute : Second ] Year [ Timezone ] +.sp +where + Month is Jan, Feb, ..., Dec + Day is 1, 2, ..., 31 + Hour is 01, 02, ..., 24 + Minute is 00, 01, ..., 59 + Second is 00, 01, ..., 59 + Year is 1901, 1902, ..., 2038 +.fi +.PP +Valid dates are from Dec 13 20:45:53 1901 GMT to Jan 19 03:14:04 +2038 GMT. As of Version 3.0, times are no longer read and written +using Greenwich Mean Time; the input and output routines default to +the local time zone. +.PP +All special values allowed for +.IR "datetime" +are also allowed for +.IR "absolute time". + +.SH "RELATIVE TIME" +Relative time (reltime) is a limited-range (+/- 68 years) and limited-precision (1 sec) +time span data type. +.IR "timespan" +may be preferred, since it +covers a larger range with greater precision, allows multiple units +for an entry, and correctly handles qualitative time +units such as year and month. For reltime, only one quantity and unit is allowed +per entry, which can be inconvenient for complicated time spans. +.PP +Relative time is specified with the following syntax: +.PP +.nf +@ Quantity Unit [Direction] +.sp +where + Quantity is `1', `2', ... + Unit is ``second'', ``minute'', ``hour'', ``day'', ``week'', + ``month'' (30-days), or ``year'' (365-days), + or PLURAL of these units. + Direction is ``ago'' +.fi +.PP +.RB ( Note : +Valid relative times are less than or equal to 68 years.) +In addition, the special relative time \*(lqUndefined RelTime\*(rq is +provided. +.SH "TIME RANGES" +Time ranges are specified as: +.PP +.nf +[ 'abstime' 'abstime'] +.fi +where +.IR abstime +is a time in the absolute time format. Special abstime values such as +\*(lqcurrent\*(rq, \*(lqinfinity\*(rq and \*(lq-infinity\*(rq can be used. + +.SH "Syntax of geometric types" +.SH "POINT" +Points are specified using the following syntax: +.PP +.nf +( x , y ) +.nf + x , y +.sp +where + x is the x-axis coordinate as a floating point number + y is the y-axis coordinate as a floating point number +.fi +.PP +.SH "LSEG" +Line segments are represented by pairs of points. +.PP +lseg is specified using the following syntax: +.PP +.nf +( ( x1 , y1 ) , ( x2 , y2 ) ) +.nf + ( x1 , y1 ) , ( x2 , y2 ) +.nf + x1 , y1 , x2 , y2 +.sp +where + (x1,y1) and (x2,y2) are the endpoints of the segment +.fi +.PP +.SH "BOX" +Boxes are represented by pairs of points which are opposite +corners of the box. +.PP +box is specified using the following syntax: +.PP +.nf +( ( x1 , y1 ) , ( x2 , y2 ) ) +.nf + ( x1 , y1 ) , ( x2 , y2 ) +.nf + x1 , y1 , x2 , y2 +.sp +where + (x1,y1) and (x2,y2) are opposite corners +.fi +.PP +Boxes are output using the first syntax. +The corners are reordered on input to store +the lower left corner first and the upper right corner last. +Other corners of the box can be entered, but the lower +left and upper right corners are determined from the input and stored. +.SH "PATH" +Paths are represented by sets of points. Paths can be "open", where +the first and last points in the set are not connected, and "closed", +where the first and last point are connected. Functions +.IR popen(p) +and +.IR pclose(p) +are supplied to force a path to be open or closed, and functions +.IR isopen(p) +and +.IR isclosed(p) +are supplied to select either type in a query. +.PP +path is specified using the following syntax: +.PP +.nf +( ( x1 , y1 ) , ... , ( xn , yn ) ) +.nf +[ ( x1 , y1 ) , ... , ( xn , yn ) ] +.nf + ( x1 , y1 ) , ... , ( xn , yn ) +.nf + x1 , y1 , ... , xn , yn +.nf +( closed, n, x1 , y1 , ... , xn , yn ) +.sp +where + (x1,y1),...,(xn,yn) are points 1 through n + a leading "[" indicates an open path + a leading "(" indicates a closed path + a single leading "(" indicates a v6.0-compatible format + closed is an integer flag which is zero for an open path + n is the number of points in the path +.fi +.PP +Paths are output using the first syntax. +The last format is supplied to be backward compatible with v6.0 and earlier +path formats and will not be supported in future versions of Postgres. +.SH "POLYGON" +Polygons are represented by sets of points. Polygons should probably be +considered +equivalent to closed paths, but are stored differently and have their own +set of support routines. +.PP +polygon is specified using the following syntax: +.PP +.nf +( ( x1 , y1 ) , ... , ( xn , yn ) ) +.nf + ( x1 , y1 ) , ... , ( xn , yn ) +.nf + x1 , y1 , ... , xn , yn +.nf +( x1 , ... , xn , y1 , ... , yn ) +.sp +where + (x1,y1),...,(xn,yn) are points 1 through n + a single leading "(" indicates a v6.0-compatible format +.fi +.PP +Polygons are output using the first syntax. +The last format is supplied to be backward compatible with v6.0 and earlier +path formats and will not be supported in future versions of Postgres. +.SH "CIRCLE" +Circles are represented by a center point and a radius. +.PP +circle is specified using the following syntax: +.PP +.nf +< ( x , y ) , r > +.nf +( ( x , y ) , r ) +.nf + ( x , y ) , r +.nf + x , y , r +.sp +where + (x,y) is the center of the circle + r is the radius of the circle +.fi +.PP +Circles are output using the first syntax. + +.SH "Built-in operators and functions" +.SH OPERATORS +Postgres provides a large number of built-in operators on system types. +These operators are declared in the system catalog +\*(lqpg_operator\*(rq. Every entry in \*(lqpg_operator\*(rq includes +the object ID of the procedure that implements the operator. +.PP +Users may invoke operators using the operator name, as in +.nf +select * from emp where salary < 40000; +.fi +Alternatively, users may call the functions that implement the +operators directly. In this case, the query above would be expressed +as +.nf +select * from emp where int4lt(salary, 40000); +.fi +The rest of this section provides a list of the built-in operators and +the functions that implement them. Binary operators are listed first, +followed by unary operators. + +.SH "BINARY OPERATORS" + +.nf +Operators: + +general + <\(eq less or equal + <> inequality + < less than + <\(eq greater or equal + >\(eq greater or equal + > greater than + \(eq equality + ~ A matches regular expression B, case-sensitive + !~ A does not match regular expression B, case-sensitive + ~* A matches regular expression B, case-insensitive. + !~* A does not match regular expression B, case-insensitive + ~~ A matches LIKE expression B, case-sensitive + !~~ A does not match LIKE expression B, case-sensitive + + + addition + \(mi subtraction + * multiplication + / division + % modulus + @ absolute value + <===> distance between + +float8 + ^ exponentiation + % truncate to integer + |/ square root + ||/ cube root + : exponential function + ; natural logarithm (in psql, protect with parentheses) + +point + !< A is left of B + !> A is right of B + !^ A is above B + !| A is below B + \(eq|\(eq equality + ===> point inside box + ===` point on path + +box + && boxes overlap + &< box A overlaps box B, but does not extend to right of box B + &> box A overlaps box B, but does not extend to left of box B + << A is left of B + \(eq area equal + < area less than + <\(eq area less or equal + >\(eq area greater or equal + > area greater than + >> A is right of B + @ A is contained in B + ~\(eq box equality + ~= A same as B + ~ A contains B + @@ center of box + +polygon + && polygons overlap + &< A overlaps B but does not extend to right of B + &> A overlaps B but does not extend to left of B + << A is left of B + >> A is right of B + @ A is contained by B + ~\(eq equality + ~= A same as B + ~ A contains B + +circle + && circles overlap + &< A overlaps B but does not extend to right of B + &> A overlaps B but does not extend to left of B + << A is left of B + >> A is right of B + @ A is contained by B + ~\(eq equality + ~= A same as B + ~ A contains B + +tinterval + #<\(eq interval length less or equal reltime + #<> interval length not equal to reltime. + #< interval length less than reltime + #\(eq interval length equal to reltime + #>\(eq interval length greater or equal reltime + #> interval length greater than reltime + && intervals overlap + << A contains B + \(eq equality + <> interval bounded by two abstimes + <?> abstime in tinterval + | start of interval + <#> convert to interval +.fi + +.SH "FUNCTIONS" +Many data types have functions available for conversion to other related types. +In addition, there are some type-specific functions. +.PP +For the +date_part() and date_trunc() +functions, arguments can be +`year', `month', `day', `hour', `minute', and `second', +as well as the more specialized quantities +`decade', `century', `millenium', `millisecond', and `microsecond'. +date_part() also allows +`dow' +to return day of week and `epoch' to return seconds since 1970. + +.nf +Functions: + +abstime + datetime datetime(abstime) convert to datetime + bool isfinite(abstime) TRUE if this is a finite time + +date + datetime datetime(date) convert to datetime + datetime datetime(date,time) convert to datetime + +datetime + abstime abstime(datetime) convert to abstime + float8 date_part(text,datetime) specified portion of date field + datetime date_trunc(text,datetime) truncate date at specified units + bool isfinite(datetime) TRUE if this is a finite time + +reltime + timespan timespan(reltime) convert to timespan + +time + datetime datetime(date,time) convert to datetime + +timespan + float8 date_part(text,timespan) specified portion of time field + bool isfinite(timespan) TRUE if this is a finite time + reltime reltime(timespan) convert to reltime + +box + box box(point,point) convert points to box + float8 area(box) area of box + +path + bool isopen(path) TRUE if this is an open path + bool isclosed(path) TRUE if this is a closed path + +circle + circle circle(point,float8) convert to circle + polygon polygon(npts,circle) convert to polygon with npts points + float8 center(circle) radius of circle + float8 radius(circle) radius of circle + float8 diameter(circle) diameter of circle + float8 area(circle) area of circle +.fi + +.SH "BINARY OPERATORS" +This list was generated from the Postgres system catalogs with the +query: + +.nf +SELECT t0.typname AS result, + t1.typname AS left_type, + t2.typname AS right_type, + o.oprname AS operatr, + p.proname AS func_name +FROM pg_proc p, pg_type t0, + pg_type t1, pg_type t2, + pg_operator o +WHERE p.prorettype = t0.oid AND + RegprocToOid(o.oprcode) = p.oid AND + p.pronargs = 2 AND + o.oprleft = t1.oid AND + o.oprright = t2.oid +ORDER BY result, left_type, right_type, operatr; +.fi + +These operations are cast in terms of SQL types and so are +.BR not +directly usable as C function prototypes. + +.nf +result |left_type |right_type|operatr|func_name +---------+----------+----------+-------+----------------- +_aclitem |_aclitem |aclitem |+ |aclinsert +_aclitem |_aclitem |aclitem |- |aclremove +abstime |abstime |reltime |+ |timepl +abstime |abstime |reltime |- |timemi +bool |_abstime |_abstime |= |array_eq +bool |_aclitem |_aclitem |= |array_eq +bool |_aclitem |aclitem |~ |aclcontains +bool |_bool |_bool |= |array_eq +bool |_box |_box |= |array_eq +bool |_bytea |_bytea |= |array_eq +bool |_char |_char |= |array_eq +bool |_char16 |_char16 |= |array_eq +bool |_cid |_cid |= |array_eq +bool |_filename |_filename |= |array_eq +bool |_float4 |_float4 |= |array_eq +bool |_float8 |_float8 |= |array_eq +bool |_int2 |_int2 |= |array_eq +bool |_int28 |_int28 |= |array_eq +bool |_int4 |_int4 |= |array_eq +bool |_lseg |_lseg |= |array_eq +bool |_name |_name |= |array_eq +bool |_oid |_oid |= |array_eq +bool |_oid8 |_oid8 |= |array_eq +bool |_path |_path |= |array_eq +bool |_point |_point |= |array_eq +bool |_polygon |_polygon |= |array_eq +bool |_ref |_ref |= |array_eq +bool |_regproc |_regproc |= |array_eq +bool |_reltime |_reltime |= |array_eq +bool |_stub |_stub |= |array_eq +bool |_text |_text |= |array_eq +bool |_tid |_tid |= |array_eq +bool |_tinterval|_tinterval|= |array_eq +bool |_xid |_xid |= |array_eq +bool |abstime |abstime |< |abstimelt +bool |abstime |abstime |<= |abstimele +bool |abstime |abstime |<> |abstimene +bool |abstime |abstime |= |abstimeeq +bool |abstime |abstime |> |abstimegt +bool |abstime |abstime |>= |abstimege +bool |abstime |tinterval |<?> |ininterval +bool |bool |bool |< |boollt +bool |bool |bool |<> |boolne +bool |bool |bool |= |booleq +bool |bool |bool |> |boolgt +bool |box |box |&& |box_overlap +bool |box |box |&< |box_overleft +bool |box |box |&> |box_overright +bool |box |box |< |box_lt +bool |box |box |<< |box_left +bool |box |box |<= |box_le +bool |box |box |= |box_eq +bool |box |box |> |box_gt +bool |box |box |>= |box_ge +bool |box |box |>> |box_right +bool |box |box |@ |box_contained +bool |box |box |~ |box_contain +bool |box |box |~= |box_same +bool |bpchar |bpchar |< |bpcharlt +bool |bpchar |bpchar |<= |bpcharle +bool |bpchar |bpchar |<> |bpcharne +bool |bpchar |bpchar |= |bpchareq +bool |bpchar |bpchar |> |bpchargt +bool |bpchar |bpchar |>= |bpcharge +bool |bpchar |text |!~ |textregexne +bool |bpchar |text |!~* |texticregexne +bool |bpchar |text |!~~ |textnlike +bool |bpchar |text |~ |textregexeq +bool |bpchar |text |~* |texticregexeq +bool |bpchar |text |~~ |textlike +bool |char |char |< |charlt +bool |char |char |<= |charle +bool |char |char |<> |charne +bool |char |char |= |chareq +bool |char |char |> |chargt +bool |char |char |>= |charge +bool |char16 |char16 |< |char16lt +bool |char16 |char16 |<= |char16le +bool |char16 |char16 |<> |char16ne +bool |char16 |char16 |= |char16eq +bool |char16 |char16 |> |char16gt +bool |char16 |char16 |>= |char16ge +bool |char16 |text |!~ |char16regexne +bool |char16 |text |!~* |char16icregexne +bool |char16 |text |!~~ |char16nlike +bool |char16 |text |!~~ |char16nlike +bool |char16 |text |~ |char16regexeq +bool |char16 |text |~* |char16icregexeq +bool |char16 |text |~~ |char16like +bool |char16 |text |~~ |char16like +bool |char2 |char2 |< |char2lt +bool |char2 |char2 |<= |char2le +bool |char2 |char2 |<> |char2ne +bool |char2 |char2 |= |char2eq +bool |char2 |char2 |> |char2gt +bool |char2 |char2 |>= |char2ge +bool |char2 |text |!~ |char2regexne +bool |char2 |text |!~* |char2icregexne +bool |char2 |text |!~~ |char2nlike +bool |char2 |text |~ |char2regexeq +bool |char2 |text |~* |char2icregexeq +bool |char2 |text |~~ |char2like +bool |char4 |char4 |< |char4lt +bool |char4 |char4 |<= |char4le +bool |char4 |char4 |<> |char4ne +bool |char4 |char4 |= |char4eq +bool |char4 |char4 |> |char4gt +bool |char4 |char4 |>= |char4ge +bool |char4 |text |!~ |char4regexne +bool |char4 |text |!~* |char4icregexne +bool |char4 |text |!~~ |char4nlike +bool |char4 |text |~ |char4regexeq +bool |char4 |text |~* |char4icregexeq +bool |char4 |text |~~ |char4like +bool |char8 |char8 |< |char8lt +bool |char8 |char8 |<= |char8le +bool |char8 |char8 |<> |char8ne +bool |char8 |char8 |= |char8eq +bool |char8 |char8 |> |char8gt +bool |char8 |char8 |>= |char8ge +bool |char8 |text |!~ |char8regexne +bool |char8 |text |!~* |char8icregexne +bool |char8 |text |!~~ |char8nlike +bool |char8 |text |~ |char8regexeq +bool |char8 |text |~* |char8icregexeq +bool |char8 |text |~~ |char8like +bool |circle |circle |!^ |circle_above +bool |circle |circle |!\| |circle_below +bool |circle |circle |&& |circle_overlap +bool |circle |circle |&< |circle_overleft +bool |circle |circle |&> |circle_overright +bool |circle |circle |< |circle_eq +bool |circle |circle |<< |circle_left +bool |circle |circle |<= |circle_eq +bool |circle |circle |<> |circle_ne +bool |circle |circle |= |circle_eq +bool |circle |circle |> |circle_eq +bool |circle |circle |>= |circle_eq +bool |circle |circle |>> |circle_right +bool |circle |circle |@ |circle_contained +bool |circle |circle |~ |circle_contain +bool |circle |circle |~= |circle_same +bool |date |date |< |date_lt +bool |date |date |<= |date_le +bool |date |date |<> |date_ne +bool |date |date |= |date_eq +bool |date |date |> |date_gt +bool |date |date |>= |date_ge +bool |datetime |datetime |< |datetime_lt +bool |datetime |datetime |<= |datetime_le +bool |datetime |datetime |<> |datetime_ne +bool |datetime |datetime |= |datetime_eq +bool |datetime |datetime |> |datetime_gt +bool |datetime |datetime |>= |datetime_ge +bool |float4 |float4 |< |float4lt +bool |float4 |float4 |<= |float4le +bool |float4 |float4 |<> |float4ne +bool |float4 |float4 |= |float4eq +bool |float4 |float4 |> |float4gt +bool |float4 |float4 |>= |float4ge +bool |float4 |float8 |< |float48lt +bool |float4 |float8 |<= |float48le +bool |float4 |float8 |<> |float48ne +bool |float4 |float8 |= |float48eq +bool |float4 |float8 |> |float48gt +bool |float4 |float8 |>= |float48ge +bool |float8 |float4 |< |float84lt +bool |float8 |float4 |<= |float84le +bool |float8 |float4 |<> |float84ne +bool |float8 |float4 |= |float84eq +bool |float8 |float4 |> |float84gt +bool |float8 |float4 |>= |float84ge +bool |float8 |float8 |< |float8lt +bool |float8 |float8 |<= |float8le +bool |float8 |float8 |<> |float8ne +bool |float8 |float8 |= |float8eq +bool |float8 |float8 |> |float8gt +bool |float8 |float8 |>= |float8ge +bool |int2 |int2 |< |int2lt +bool |int2 |int2 |<= |int2le +bool |int2 |int2 |<> |int2ne +bool |int2 |int2 |= |int2eq +bool |int2 |int2 |> |int2gt +bool |int2 |int2 |>= |int2ge +bool |int4 |int4 |< |int4lt +bool |int4 |int4 |<= |int4le +bool |int4 |int4 |<> |int4ne +bool |int4 |int4 |= |int4eq +bool |int4 |int4 |> |int4gt +bool |int4 |int4 |>= |int4ge +bool |int4 |name |!!= |int4notin +bool |int4 |oid |= |int4eqoid +bool |money |money |< |cash_lt +bool |money |money |<= |cash_le +bool |money |money |<> |cash_ne +bool |money |money |= |cash_eq +bool |money |money |> |cash_gt +bool |money |money |>= |cash_ge +bool |name |name |< |namelt +bool |name |name |<= |namele +bool |name |name |<> |namene +bool |name |name |= |nameeq +bool |name |name |> |namegt +bool |name |name |>= |namege +bool |name |text |!~ |nameregexne +bool |name |text |!~* |nameicregexne +bool |name |text |!~~ |namenlike +bool |name |text |~ |nameregexeq +bool |name |text |~* |nameicregexeq +bool |name |text |~~ |namelike +bool |oid |int4 |= |oideqint4 +bool |oid |name |!!= |oidnotin +bool |oid |oid |< |int4lt +bool |oid |oid |<= |int4le +bool |oid |oid |<> |oidne +bool |oid |oid |= |oideq +bool |oid |oid |> |int4gt +bool |oid |oid |>= |int4ge +bool |oidint2 |oidint2 |< |oidint2lt +bool |oidint2 |oidint2 |<= |oidint2le +bool |oidint2 |oidint2 |<> |oidint2ne +bool |oidint2 |oidint2 |= |oidint2eq +bool |oidint2 |oidint2 |> |oidint2gt +bool |oidint2 |oidint2 |>= |oidint2ge +bool |oidint4 |oidint4 |< |oidint4lt +bool |oidint4 |oidint4 |<= |oidint4le +bool |oidint4 |oidint4 |<> |oidint4ne +bool |oidint4 |oidint4 |= |oidint4eq +bool |oidint4 |oidint4 |> |oidint4gt +bool |oidint4 |oidint4 |>= |oidint4ge +bool |oidname |oidname |< |oidnamelt +bool |oidname |oidname |<= |oidnamele +bool |oidname |oidname |<> |oidnamene +bool |oidname |oidname |= |oidnameeq +bool |oidname |oidname |> |oidnamegt +bool |oidname |oidname |>= |oidnamege +bool |point |box |===> |on_pb +bool |point |path |===` |on_ppath +bool |point |point |!< |point_left +bool |point |point |!> |point_right +bool |point |point |!^ |point_above +bool |point |point |!\| |point_below +bool |point |point |=\|= |point_eq +bool |polygon |polygon |&& |poly_overlap +bool |polygon |polygon |&< |poly_overleft +bool |polygon |polygon |&> |poly_overright +bool |polygon |polygon |<< |poly_left +bool |polygon |polygon |>> |poly_right +bool |polygon |polygon |@ |poly_contained +bool |polygon |polygon |~ |poly_contain +bool |polygon |polygon |~= |poly_same +bool |reltime |reltime |< |reltimelt +bool |reltime |reltime |<= |reltimele +bool |reltime |reltime |<> |reltimene +bool |reltime |reltime |= |reltimeeq +bool |reltime |reltime |> |reltimegt +bool |reltime |reltime |>= |reltimege +bool |text |text |!~ |textregexne +bool |text |text |!~* |texticregexne +bool |text |text |!~~ |textnlike +bool |text |text |< |text_lt +bool |text |text |<= |text_le +bool |text |text |<> |textne +bool |text |text |= |texteq +bool |text |text |> |text_gt +bool |text |text |>= |text_ge +bool |text |text |~ |textregexeq +bool |text |text |~* |texticregexeq +bool |text |text |~~ |textlike +bool |time |time |< |time_lt +bool |time |time |<= |time_le +bool |time |time |<> |time_ne +bool |time |time |= |time_eq +bool |time |time |> |time_gt +bool |time |time |>= |time_ge +bool |timespan |timespan |< |timespan_lt +bool |timespan |timespan |<= |timespan_le +bool |timespan |timespan |<> |timespan_ne +bool |timespan |timespan |= |timespan_eq +bool |timespan |timespan |> |timespan_gt +bool |timespan |timespan |>= |timespan_ge +bool |timestamp |timestamp |< |timestamplt +bool |timestamp |timestamp |<= |timestample +bool |timestamp |timestamp |<> |timestampne +bool |timestamp |timestamp |= |timestampeq +bool |timestamp |timestamp |> |timestampgt +bool |timestamp |timestamp |>= |timestampge +bool |tinterval |reltime |#< |intervallenlt +bool |tinterval |reltime |#<= |intervallenle +bool |tinterval |reltime |#<> |intervallenne +bool |tinterval |reltime |#= |intervalleneq +bool |tinterval |reltime |#> |intervallengt +bool |tinterval |reltime |#>= |intervallenge +bool |tinterval |tinterval |&& |intervalov +bool |tinterval |tinterval |<< |intervalct +bool |tinterval |tinterval |= |intervaleq +bool |varchar |text |!~ |textregexne +bool |varchar |text |!~* |texticregexne +bool |varchar |text |!~~ |textnlike +bool |varchar |text |~ |textregexeq +bool |varchar |text |~* |texticregexeq +bool |varchar |text |~~ |textlike +bool |varchar |varchar |< |varcharlt +bool |varchar |varchar |<= |varcharle +bool |varchar |varchar |<> |varcharne +bool |varchar |varchar |= |varchareq +bool |varchar |varchar |> |varchargt +bool |varchar |varchar |>= |varcharge +char |char |char |* |charmul +char |char |char |+ |charpl +char |char |char |- |charmi +char |char |char |/ |chardiv +date |date |int4 |+ |date_pli +date |date |int4 |- |date_mii +datetime |datetime |timespan |+ |datetime_add_span +datetime |datetime |timespan |- |datetime_sub_span +float4 |float4 |float4 |* |float4mul +float4 |float4 |float4 |+ |float4pl +float4 |float4 |float4 |- |float4mi +float4 |float4 |float4 |/ |float4div +float8 |box |box |<===> |box_distance +float8 |circle |circle |<===> |circle_distance +float8 |float4 |float8 |* |float48mul +float8 |float4 |float8 |+ |float48pl +float8 |float4 |float8 |- |float48mi +float8 |float4 |float8 |/ |float48div +float8 |float8 |float4 |* |float84mul +float8 |float8 |float4 |+ |float84pl +float8 |float8 |float4 |- |float84mi +float8 |float8 |float4 |/ |float84div +float8 |float8 |float8 |* |float8mul +float8 |float8 |float8 |+ |float8pl +float8 |float8 |float8 |- |float8mi +float8 |float8 |float8 |/ |float8div +float8 |float8 |float8 |^ |dpow +float8 |lseg |box |<===> |dist_sb +float8 |lseg |lseg |<===> |lseg_distance +float8 |path |path |<===> |path_distance +float8 |point |box |<===> |dist_pl +float8 |point |box |<===> |dist_ps +float8 |point |box |<===> |dist_pb +float8 |point |lseg |<===> |dist_ps +float8 |point |path |<===> |dist_ppth +float8 |point |point |<===> |point_distance +int2 |int2 |int2 |% |int2mod +int2 |int2 |int2 |* |int2mul +int2 |int2 |int2 |+ |int2pl +int2 |int2 |int2 |- |int2mi +int2 |int2 |int2 |/ |int2div +int4 |date |date |- |date_mi +int4 |int2 |int4 |% |int24mod +int4 |int2 |int4 |* |int24mul +int4 |int2 |int4 |+ |int24pl +int4 |int2 |int4 |- |int24mi +int4 |int2 |int4 |/ |int24div +int4 |int2 |int4 |< |int24lt +int4 |int2 |int4 |<= |int24le +int4 |int2 |int4 |<> |int24ne +int4 |int2 |int4 |= |int24eq +int4 |int2 |int4 |> |int24gt +int4 |int2 |int4 |>= |int24ge +int4 |int4 |int2 |% |int42mod +int4 |int4 |int2 |* |int42mul +int4 |int4 |int2 |+ |int42pl +int4 |int4 |int2 |- |int42mi +int4 |int4 |int2 |/ |int42div +int4 |int4 |int2 |< |int42lt +int4 |int4 |int2 |<= |int42le +int4 |int4 |int2 |<> |int42ne +int4 |int4 |int2 |= |int42eq +int4 |int4 |int2 |> |int42gt +int4 |int4 |int2 |>= |int42ge +int4 |int4 |int4 |% |int4mod +int4 |int4 |int4 |* |int4mul +int4 |int4 |int4 |+ |int4pl +int4 |int4 |int4 |- |int4mi +int4 |int4 |int4 |/ |int4div +money |money |float8 |* |cash_mul +money |money |float8 |/ |cash_div +money |money |money |+ |cash_pl +money |money |money |- |cash_mi +timespan |datetime |datetime |- |datetime_sub +timespan |timespan |timespan |+ |timespan_add +timespan |timespan |timespan |- |timespan_sub +tinterval|abstime |abstime |<#> |mktinterval + + +.fi +.SH "LEFT UNARY OPERATORS" +The table below gives the left unary operators that are +registered in the system catalogs. + +This list was generated from the Postgres system catalogs with the query: + +.nf +SELECT o.oprname AS left_unary, + right.typname AS operand, + result.typname AS return_type +FROM pg_operator o, pg_type right, pg_type result +WHERE o.oprkind = 'l' AND -- left unary + o.oprright = right.oid AND + o.oprresult = result.oid +ORDER BY operand; + +left_unary|operand |return_type +----------+---------+----------- +@@ |box |point +@ |float4 |float4 +- |float4 |float4 +|/ |float8 |float8 +@ |float8 |float8 +; |float8 |float8 +: |float8 |float8 +% |float8 |float8 +||/ |float8 |float8 +- |float8 |float8 +- |int2 |int2 +!! |int4 |int4 +- |int4 |int4 +# |polygon |int4 +- |timespan |timespan +| |tinterval|abstime + +.fi +.in +.SH "RIGHT UNARY OPERATORS" +The table below gives the right unary operators that are +registered in the system catalogs. + +This list was generated from the Postgres system catalogs with the query: + +.nf +SELECT o.oprname AS right_unary, + left.typname AS operand, + result.typname AS return_type +FROM pg_operator o, pg_type left, pg_type result +WHERE o.oprkind = 'r' AND -- right unary + o.oprleft = left.oid AND + o.oprresult = result.oid +ORDER BY operand; + +right_unary|operand|return_type +-----------+-------+----------- +% |float8 |float8 +! |int4 |int4 + +.fi +.in +.SH "AGGREGATE FUNCTIONS" +The table below gives the aggregate functions that are +registered in the system catalogs. + +This list was generated from the Postgres system catalogs with the query: + +.nf +SELECT a.aggname, t.typname +FROM pg_aggregate a, pg_type t +WHERE a.aggbasetype = t.oid +ORDER BY aggname, typname; + +aggname|typname +-------+-------- +avg |float4 +avg |float8 +avg |int2 +avg |int4 +avg |money +max |abstime +max |date +max |datetime +max |float4 +max |float8 +max |int2 +max |int4 +max |money +max |timespan +min |abstime +min |date +min |datetime +min |float4 +min |float8 +min |int2 +min |int4 +min |money +min |timespan +sum |float4 +sum |float8 +sum |int2 +sum |int4 +sum |money + +.fi +\fBcount\fR is also available, where \fBcount(*)\fR returns a count of all +rows while \fBcount(column_name)\fR returns a count of all non-null fields +in the specified column. + +.in +.SH "SEE ALSO" +.IR set (l), +.IR show (l), +.IR reset (l). +For examples on specifying literals of built-in types, see +.IR SQL (l). +.SH BUGS +.PP +Although most of the input and output functions corresponding to the +base types (e.g., integers and floating point numbers) do some +error-checking, some are not particularly rigorous about it. More +importantly, few of the operators and functions (e.g., +addition and multiplication) perform any error-checking at all. +Consequently, many of the numeric operators can (for example) +silently underflow or overflow. +.PP +Some of the input and output functions are not invertible. That is, +the result of an output function may lose precision when compared to +the original input. |