diff options
Diffstat (limited to 'src/man/built-in.3')
| -rw-r--r-- | src/man/built-in.3 | 215 |
1 files changed, 176 insertions, 39 deletions
diff --git a/src/man/built-in.3 b/src/man/built-in.3 index 3a667260b8..5f2cf8d456 100644 --- a/src/man/built-in.3 +++ b/src/man/built-in.3 @@ -1,17 +1,15 @@ .\" This is -*-nroff-*- .\" XXX standard disclaimer belongs here.... -.\" $Header: /cvsroot/pgsql/src/man/Attic/built-in.3,v 1.3 1996/12/11 00:27:02 momjian Exp $ -.TH BUILT-INS INTRO 11/05/95 PostgreSQL PostgreSQL +.\" $Header: /cvsroot/pgsql/src/man/Attic/built-in.3,v 1.4 1997/04/02 18:31:22 scrappy Exp $ +.TH BUILT-INS 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 "Built-in and System Types" .SH "BUILT-IN TYPES" -This section describes both +This section describes .BR built-in data types. -These Built-in types are -are installed in every database. +These Built-in types are installed in every database. .PP Users may add new types to Postgres using the .IR "define type" @@ -24,9 +22,9 @@ described in this section. .in 0 .nf \fBPOSTGRES Type\fP \fBMeaning\fP - abstime absolute date and time + abstime (absolute) limited-range date and time aclitem access control list item - bool boolean + bool boolean box 2-dimensional rectangle bpchar blank-padded characters bytea variable length array of bytes @@ -37,11 +35,12 @@ described in this section. 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 + int28 array of 8 int2 int4 four-byte signed integer float alias for float4 float4 single-precision floating-point number @@ -50,7 +49,7 @@ described in this section. name a multi-character type for storing system identifiers oid object identifier type oid8 array of 8 oid - oidchar16 oid and char16 composed + oidchar16 oid and char16 composed oidint2 oid and int2 composed oidint4 oid and int4 composed path variable-length array of lseg @@ -58,13 +57,15 @@ described in this section. polygon 2-dimensional polygon real alias for float4 regproc registered procedure - reltime relative date and time - smgr storage manager + 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 - tinterval time interval + 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 @@ -73,11 +74,94 @@ described in this section. .PP As a rule, the built-in types are all either (1) internal types, in which case the user should not worry about their external format, or -(2) have obvious formats. The exceptions to this rule are the three +(2) have obvious formats. The exceptions to this rule are the date and time types. + .SH "Syntax of date and time types" +.SH "DATETIME" +General-use date and time is input using a wide range of +syntaxes, 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 +All special values allowed for +.IR "absolute time" +are also allowed for +.IR "datetime". +The special values \*(lqcurrent\*(rq, +\*(lqinfinity\*(rq and \*(lq-infinity\*(rq are provided. +\*(lqinfinity\*(rq specifies a time later than any valid time, and +\*(lq-infinity\*(rq specifies a time earlier than any valid time. +\*(lqcurrent\*(rq indicates that the current time should be +substituted whenever this value appears in a computation. +.PP +The strings \*(lqnow\*(rq and \*(lqepoch\*(rq can be used to specify +time values. \*(lqnow\*(rq means the current time, and differs from +\*(lqcurrent\*(rq in that the current time is immediately substituted +for it. \*(lqepoch\*(rq 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 @@ -89,6 +173,7 @@ where 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 @@ -105,8 +190,19 @@ The strings \*(lqnow\*(rq and \*(lqepoch\*(rq can be used to specify time values. \*(lqnow\*(rq means the current time, and differs from \*(lqcurrent\*(rq in that the current time is immediately substituted for it. \*(lqepoch\*(rq means Jan 1 00:00:00 1970 GMT. + .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 @@ -124,6 +220,7 @@ In addition, the special relative time \*(lqUndefined RelTime\*(rq is provided. .SH "TIME RANGES" Time ranges are specified as: +.PP .nf [ 'abstime' 'abstime'] .fi @@ -131,6 +228,7 @@ 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 "Built-in operators and functions" .SH OPERATORS Postgres provides a large number of built-in operators on system types. @@ -151,31 +249,8 @@ select * from emp where int4lt(salary, 40000); 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" -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. +.SH "BINARY OPERATORS" .nf Operators: @@ -262,6 +337,68 @@ tinterval <?> 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. + +.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 + 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 +.fi + +.PP +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. result |left_type |right_type|operatr|func_name ---------+----------+----------+-------+--------------- |