.lf 1 built-in.3pqsrc .\" This is -*-nroff-*- .\" XXX standard disclaimer belongs here.... .\" $Header: /usr/local/devel/pglite/cvs/src/doc/ref/built-in.3pqsrc,v 1.1 1996/02/24 01:16:21 jolly Exp $ .TH INTRODUCTION BUILT-INS 11/05/95 Postgres95 Postgres95 .XA 0 "Section 3 \(em What comes with Postgres95 (BUILT-INS)" .BH "SECTION 3 \(em WHAT COMES WITH Postgres95 (BUILT-INS)" .SH "DESCRIPTION" This section describes the data types, functions and operators available to users in Postgres95 as it is distributed. .XA 1 "Built-in and System Types" .SH "BUILT-IN TYPES" This section describes both .BR built-in data types. These Built-in types are are installed in every database. .PP Users may add new types to Postgres95 using the .IR "define type" command described in this manual. User-defined types are not described in this section. .XA 2 "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 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 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 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 variable-length array of lseg point 2-dimensional geometric point polygon 2-dimensional polygon real alias for float4 regproc registered procedure reltime relative date and time 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 varchar variable-length characters xid transaction identifier type .fi .in .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 time types. .XA 2 "Syntax of date and time types" .SP "DATE AND TIME" BUILT-INS 02/18/94 .SH "ABSOLUTE TIME" Absolute time is specified using the following syntax: .nf Month Day [ Hour : Minute : Second ] Year [ Timezone ] .fi .(l 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 .)l 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 The special absolute time values \*(lqcurrent\*(rq, \*(lqinfinity\*(rq and \*(lq-infinity\*(rq are also 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 "RELATIVE TIME" Relative time is specified with the following syntax: .nf @ Quantity Unit [Direction] .fi .(l 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'' .)l .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: .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. .XA 1 "Built-in operators and functions" .SP OPERATORS BUILT-INS 02/18/94 .SH OPERATORS Postgres95 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. .XA 2 "Binary operators" .SH "BINARY OPERATORS" This list was generated from the Postgres95 system catalogs with the query .nf select t1.typname as argtype, o.oprname, t0.typname, p.proname, t1.typname as 1type, t2.typname as rtype 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; .fi The list is sorted by the built-in type name of the first operand. The .IR "function prototype" column gives the return type, function name, and argument types for the procedure that implements the operator. Note that these function prototypes are cast in terms of SQL types and so are .BR not directly usable as C function prototypes. .ie t \{ .lf 1 sql/binary.tbl .\" This is -*-nroff-*- .TS center expand; l l c l c l l lw(2i). \fBType\fP \fBOperator\fP \fBPOSTGRES Function Prototype\fP \fBOperation\fP _ abstime <> bool abstimene(abstime, abstime) inequality + abstime timepl(abstime, reltime) addition \(mi abstime timemi(abstime, reltime) subtraction <\(eq bool abstimele(abstime, abstime) less or equal bool ininterval(abstime, tinterval) abstime in tinterval? < bool abstimelt(abstime, abstime) less than \(eq bool abstimeeq(abstime, abstime) equality >\(eq bool abstimege(abstime, abstime) greater or equal > bool abstimegt(abstime, abstime) greater than _ bool \(eq bool booleq(bool, bool) equality <> bool boolne(bool, bool) inequality _ box && bool box_overlap(box, box) boxes overlap &< bool box_overleft(box, box) T{ box A overlaps box B, but does not extend to right of box B T} &> bool box_overright(box, box) T{ box A overlaps box B, but does not extend to left of box B T} << bool box_left(box, box) A is left of B <\(eq bool box_le(box, box) area less or equal < bool box_lt(box, box) area less than \(eq bool box_eq(box, box) area equal >\(eq bool box_ge(box, box) area greater or equal >> bool box_right(box, box) A is right of B > bool box_gt(box, box) area greater than @ bool box_contained(box, box) A is contained in B ~\(eq bool box_same(box, box) box equality ~ bool box_contain(box, box) A contains B _ char <> bool charne(char, char) inequality * bool charmul(char, char) multiplication + bool charpl(char, char) addition \(mi bool charmi(char, char) subtraction / bool chardiv(char, char) division <\(eq bool charle(char, char) less or equal < bool charlt(char, char) less than \(eq bool chareq(char, char) equality >\(eq bool charge(char, char) greater or equal > bool chargt(char, char) greater than _ char2 <> bool char2ne(char2, char2) inequality !~ bool char2regexne(char2, text) T{ A does not match regular expression B, case-sensitive T} !~* bool char2icregexne(char2, text) T{ A does not match regular expression B, case-insensitive T} <\(eq bool char2le(char2, char2) less or equal < bool char2lt(char2, char2) less than \(eq bool char2eq(char2, char2) equality >\(eq bool char2ge(char2, char2) greater or equal > bool char2gt(char2, char2) greater than ~ bool char2regexeq(char2, text) T{ A matches regular expression B, case-sensitive. T} ~* bool char2icregexeq(char2, text) T{ A matches regular expression B, case-insensitive. T} _ char4 <> bool char4ne(char4, char4) inequality !~ bool char4regexne(char4, text) T{ A does not match regular expression B, case-sensitive T} !~* bool char4icregexne(char4, text) T{ A does not match regular expression B, case-insensitive T} <\(eq bool char4le(char4, char4) less or equal < bool char4lt(char4, char4) less than \(eq bool char4eq(char4, char4) equality >\(eq bool char4ge(char4, char4) greater or equal > bool char4gt(char4, char4) greater than ~ bool char4regexeq(char4, text) T{ A matches regular expression B, case-sensitive T} ~* bool char4icregexeq(char4, text) T{ A matches regular expression B, case-insensitive T} _ char8 <> bool char8ne(char8, char8) inequality !~ bool char8regexne(char8, text) T{ A does not match regular expression B, case sensitive T} !~* bool char8icregexne(char8, text) T{ A does not match regular expression B, case insensitive T} <\(eq bool char8le(char8, char8) less or equal < bool char8lt(char8, char8) less than \(eq bool char8eq(char8, char8) equality >\(eq bool char8ge(char8, char8) greater or equal > bool char8gt(char8, char8) greater than ~ bool char8regexeq(char8, text) T{ A matches regular expression B, case sensitive T} ~* bool char8icregexeq(char8, text) T{ A matches regular expression B, case insensitive T} _ char16 <> bool char16ne(char16, char16) inequality !~ bool char16regexne(char16, text) T{ A does not match regular expression B, case sensitive T} !~* bool char16icregexne(char16, text) T{ A does not match regular expression B, case insensitive T} <\(eq bool char16le(char16, char16) less or equal < bool char16lt(char16, char16) less than \(eq bool char16eq(char16, char16) equality >\(eq bool char16ge(char16, char16) greater or equal > bool char16gt(char16, char16) greater than ~ bool char16regexeq(char16, text) T{ A matches regular expression B, case sensitive T} ~* bool char16regexeq(char16, text) T{ A matches regular expression B, case insensitive T} _ float4 <> bool float4ne(float4, float4) inequality * float4 float4mul(float4, float4) multiplication + float4 float4pl(float4, float4) addition \(mi float4 float4mi(float4, float4) subtraction / float4 float4div(float4, float4) division <\(eq bool float4le(float4, float4) less or equal < bool float4lt(float4, float4) less than \(eq bool float4eq(float4, float4) equality >\(eq bool float4ge(float4, float4) greater or equal > bool float4gt(float4, float4) greater than _ float8 <> bool float8ne(float8, float8) inequality * float8 float8mul(float8, float8) multiplication + float8 float8pl(float8, float8) addition \(mi float8 float8mi(float8, float8) subtraction / float8 float8div(float8, float8) division <\(eq bool float8le(float8, float8) less or equal < bool float8lt(float8, float8) less than1 \(eq bool float8eq(float8, float8) equality >\(eq bool float8ge(float8, float8) greater or equal > bool float8gt(float8, float8) greater than ^ float8 dpow(float8, float8) exponentiation _ int2 <> bool int2ne(int2, int2) inequality <> int4 int24ne(int2, int4) inequality % int2 int2mod(int2, int2) modulus % int4 int24mod(int2, int4) modulus * int2 int2mul(int2, int2) multiplication * int4 int24mul(int2, int4) multiplication + int2 int2pl(int2, int2) addition + int4 int24pl(int2, int4) addition \(mi int2 int2mi(int2, int2) subtraction \(mi int4 int24mi(int2, int4) subtraction / int2 int2div(int2, int2) division / int4 int24div(int2, int4) division <\(eq bool int2le(int2, int2) less or equal <\(eq bool int24le(int2, int4) less or equal < bool int2lt(int2, int2) less than < bool int24lt(int2, int4) less than \(eq bool int2eq(int2, int2) equality \(eq bool int24eq(int2, int4) equality >\(eq bool int2ge(int2, int2) greater or equal >\(eq bool int24ge(int2, int4) greater or equal > bool int2gt(int2, int2) greater than > bool int24gt(int2, int4) greater than _ int4 <> bool int4ne(int4, int4) inequality <> bool int42ne(int4, int2) inequality % int4 int42mod(int4, int2) modulus % int4 int4mod(int4, int4) modulus * int4 int42mul(int4, int2) multiplication * int4 int4mul(int4, int4) multiplication + int4 int42pl(int4, int2) addition + int4 int4pl(int4, int4) addition \(mi int4 int42mi(int4, int2) subtraction \(mi int4 int4mi(int4, int4) subtraction / int4 int42div(int4, int2) division / int4 int4div(int4, int4) division <\(eq bool int4le(int4, int4) less or equal <\(eq bool int42le(int4, int2) less or equal < bool int4lt(int4, int4) less than < bool int42lt(int4, int2) less than \(eq bool int4eq(int4, int4) equality \(eq bool int42eq(int4, int2) equality \(eq bool int4eqoid(int4, oid) equality >\(eq bool int4ge(int4, int4) greater or equal >\(eq bool int42ge(int4, int2) greater or equal > bool int4gt(int4, int4) greater than > bool int42lt(int4, int2) less than - name <> bool namene(name, name) inequality !~ bool nameregexne(name, text) T{ A does not match regular expression B, case sensitive T} !~* bool nameicregexne(name, text) T{ A does not match regular expression B, case insensitive T} <\(eq bool namele(name, name) less or equal < bool namelt(name, name) less than \(eq bool nameeq(name, name) equality >\(eq bool namege(name, name) greater or equal > bool namegt(name, name) greater than ~ bool nameregexeq(name, text) T{ A matches regular expression B, case sensitive T} ~* bool nameregexeq(name, text) T{ A matches regular expression B, case insensitive T} _ oid <> bool oidne(oid, oid) inequality <> bool oidne(oid, regproc) inequality <\(eq bool oidle(oid, oid) less or equal < bool oidlt(oid, oid) less than \(eq bool oideq(oid, oid) equality \(eq bool oideqint4(oid, int4) equality \(eq bool oideq(oid, regproc) equality >\(eq bool oidge(oid, oid) greater or equal > bool oidgt(oid, oid) greater than _ oidname <> bool oidnamene(oidname, oidname) inequality < bool oidnamelt(oidname, oidname) less than <\(eq bool oidnamele(oidname, oidname) less or equal \(eq bool oidnameeq(oidname, oidname) equality > bool oidnamegt(oidname, oidname) greater than >\(eq bool oidnamege(oidname, oidname) greater or equal _ oidint2 <> bool oidint2ne(oidint2, oidint2) inequality < bool oidint2lt(oidint2, oidint2) less than <\(eq bool oidint2le(oidint2, oidint2) less or equal \(eq bool oidint2eq(oidint2, oidint2) equality > bool oidint2gt(oidint2, oidint2) greater than >\(eq bool oidint2ge(oidint2, oidint2) greater or equal _ oidint4 <> bool oidint4ne(oidint4, oidint4) inequality < bool oidint4lt(oidint4, oidint4) less than <\(eq bool oidint4le(oidint4, oidint4) less or equal \(eq bool oidint4eq(oidint4, oidint4) equality > bool oidint4gt(oidint4, oidint4) greater than >\(eq bool oidint4ge(oidint4, oidint4) greater or equal _ point !< bool point_left(point, point) A is left of B !> bool point_right(point, point) A is right of B !^ bool point_above(point, point) A is above B !| bool point_below(point, point) A is below B \(eq|\(eq bool point_eq(point, point) equality ===> bool on_pb(point, box) point inside box ===` bool on_ppath(point, path) point on path <===> int4 pointdist(point, point) distance between points _ polygon && bool poly_overlap(polygon, polygon) polygons overlap &< bool poly_overleft(polygon, polygon) T{ A overlaps B but does not extend to right of B T} &> bool poly_overright(polygon, polygon) T{ A overlaps B but does not extend to left of B T} << bool poly_left(polygon, polygon) A is left of B >> bool poly_right(polygon, polygon) A is right of B @ bool poly_contained(polygon, polygon) A is contained by B ~\(eq bool poly_same(polygon, polygon) equality ~ bool poly_contain(polygon, polygon) A contains B _ reltime <> bool reltimene(reltime, reltime) inequality <\(eq bool reltimele(reltime, reltime) less or equal < bool reltimelt(reltime, reltime) less than \(eq bool reltimeeq(reltime, reltime) equality >\(eq bool reltimege(reltime, reltime) greater or equal > bool reltimegt(reltime, reltime) greater than _ text <> bool textne(text, text) inequality !~ bool textregexne(text, text) T{ A does not contain the regular expression B, case sensitive T} !~* bool texticregexne(text, text) T{ A does not contain the regular expression B, case insensitive T} <\(eq bool text_le(text, text) less or equal < bool text_lt(text, text) less than \(eq bool texteq(text, text) equality >\(eq bool text_ge(text, text) greater or equal > bool text_gt(text, text) greater than ~ bool textregexeq(text, text) T{ A contains the regular expression B. case sensitive T} ~* bool texticregexeq(text, text) T{ A contains the regular expression B. case insensitive T} _ tinterval #<> bool intervallenne(tinterval, reltime) T{ interval length not equal to reltime. T} #<\(eq bool intervallenle(tinterval, reltime) T{ interval length less or equal reltime T} #< bool intervallenlt(tinterval, reltime) T{ interval length less than reltime T} #\(eq bool intervalleneq(tinterval, reltime) T{ interval length equal to reltime T} #>\(eq bool intervallenge(tinterval, reltime) T{ interval length greater or equal reltime T} #> bool intervallengt(tinterval, reltime) T{ interval length greater than reltime T} && bool intervalov(tinterval, tinterval) intervals overlap << bool intervalct(tinterval, tinterval) A contains B \(eq bool intervaleq(tinterval, tinterval) equality <> tinterval mktinterval(abstime, abstime) T{ interval bounded by two abstimes T} .TE .lf 186 built-in.3pqsrc \} .el \{ .lf 1 obj/binary.nr .ta +11 +10 +39 .na .TP 60 \fBType\fP \fBOperator\fP \fBPOSTGRES Function Prototype\fP \fBOperation\fP .TP 60 abstime <> bool abstimene(abstime, abstime) inequality .TP 60 + abstime timepl(abstime, reltime) addition .TP 60 \(mi abstime timemi(abstime, reltime) subtraction .TP 60 <\(eq bool abstimele(abstime, abstime) less or equal .TP 60 bool ininterval(abstime, tinterval) abstime in tinterval? .TP 60 < bool abstimelt(abstime, abstime) less than .TP 60 \(eq bool abstimeeq(abstime, abstime) equality .TP 60 >\(eq bool abstimege(abstime, abstime) greater or equal .TP 60 > bool abstimegt(abstime, abstime) greater than .TP 60 bool \(eq bool booleq(bool, bool) equality .TP 60 <> bool boolne(bool, bool) inequality .TP 60 box && bool box_overlap(box, box) boxes overlap .TP 60 &< bool box_overleft(box, box) box A overlaps box B, but does not extend to right of box B .TP 60 &> bool box_overright(box, box) box A overlaps box B, but does not extend to left of box B .TP 60 << bool box_left(box, box) A is left of B .TP 60 <\(eq bool box_le(box, box) area less or equal .TP 60 < bool box_lt(box, box) area less than .TP 60 \(eq bool box_eq(box, box) area equal .TP 60 >\(eq bool box_ge(box, box) area greater or equal .TP 60 >> bool box_right(box, box) A is right of B .TP 60 > bool box_gt(box, box) area greater than .TP 60 @ bool box_contained(box, box) A is contained in B .TP 60 ~\(eq bool box_same(box, box) box equality .TP 60 ~ bool box_contain(box, box) A contains B .TP 60 char <> bool charne(char, char) inequality .TP 60 * bool charmul(char, char) multiplication .TP 60 + bool charpl(char, char) addition .TP 60 \(mi bool charmi(char, char) subtraction .TP 60 / bool chardiv(char, char) division .TP 60 <\(eq bool charle(char, char) less or equal .TP 60 < bool charlt(char, char) less than .TP 60 \(eq bool chareq(char, char) equality .TP 60 >\(eq bool charge(char, char) greater or equal .TP 60 > bool chargt(char, char) greater than .TP 60 char2 <> bool char2ne(char2, char2) inequality .TP 60 !~ bool char2regexne(char2, text) A does not match regular expression B, case-sensitive .TP 60 !~* bool char2icregexne(char2, text) A does not match regular expression B, case-insensitive .TP 60 <\(eq bool char2le(char2, char2) less or equal .TP 60 < bool char2lt(char2, char2) less than .TP 60 \(eq bool char2eq(char2, char2) equality .TP 60 >\(eq bool char2ge(char2, char2) greater or equal .TP 60 > bool char2gt(char2, char2) greater than .TP 60 ~ bool char2regexeq(char2, text) A matches regular expression B, case-sensitive. .TP 60 ~* bool char2icregexeq(char2, text) A matches regular expression B, case-insensitive. .TP 60 char4 <> bool char4ne(char4, char4) inequality .TP 60 !~ bool char4regexne(char4, text) A does not match regular expression B, case-sensitive .TP 60 !~* bool char4icregexne(char4, text) A does not match regular expression B, case-insensitive .TP 60 <\(eq bool char4le(char4, char4) less or equal .TP 60 < bool char4lt(char4, char4) less than .TP 60 \(eq bool char4eq(char4, char4) equality .TP 60 >\(eq bool char4ge(char4, char4) greater or equal .TP 60 > bool char4gt(char4, char4) greater than .TP 60 ~ bool char4regexeq(char4, text) A matches regular expression B, case-sensitive .TP 60 ~* bool char4icregexeq(char4, text) A matches regular expression B, case-insensitive .TP 60 char8 <> bool char8ne(char8, char8) inequality .TP 60 !~ bool char8regexne(char8, text) A does not match regular expression B, case sensitive .TP 60 !~* bool char8icregexne(char8, text) A does not match regular expression B, case insensitive .TP 60 <\(eq bool char8le(char8, char8) less or equal .TP 60 < bool char8lt(char8, char8) less than .TP 60 \(eq bool char8eq(char8, char8) equality .TP 60 >\(eq bool char8ge(char8, char8) greater or equal .TP 60 > bool char8gt(char8, char8) greater than .TP 60 ~ bool char8regexeq(char8, text) A matches regular expression B, case sensitive .TP 60 ~* bool char8icregexeq(char8, text) A matches regular expression B, case insensitive .TP 60 char16 <> bool char16ne(char16, char16) inequality .TP 60 !~ bool char16regexne(char16, text) A does not match regular expression B, case sensitive .TP 60 !~* bool char16icregexne(char16, text) A does not match regular expression B, case insensitive .TP 60 <\(eq bool char16le(char16, char16) less or equal .TP 60 < bool char16lt(char16, char16) less than .TP 60 \(eq bool char16eq(char16, char16) equality .TP 60 >\(eq bool char16ge(char16, char16) greater or equal .TP 60 > bool char16gt(char16, char16) greater than .TP 60 ~ bool char16regexeq(char16, text) A matches regular expression B, case sensitive .TP 60 ~* bool char16regexeq(char16, text) A matches regular expression B, case insensitive .TP 60 float4 <> bool float4ne(float4, float4) inequality .TP 60 * float4 float4mul(float4, float4) multiplication .TP 60 + float4 float4pl(float4, float4) addition .TP 60 \(mi float4 float4mi(float4, float4) subtraction .TP 60 / float4 float4div(float4, float4) division .TP 60 <\(eq bool float4le(float4, float4) less or equal .TP 60 < bool float4lt(float4, float4) less than .TP 60 \(eq bool float4eq(float4, float4) equality .TP 60 >\(eq bool float4ge(float4, float4) greater or equal .TP 60 > bool float4gt(float4, float4) greater than .TP 60 float8 <> bool float8ne(float8, float8) inequality .TP 60 * float8 float8mul(float8, float8) multiplication .TP 60 + float8 float8pl(float8, float8) addition .TP 60 \(mi float8 float8mi(float8, float8) subtraction .TP 60 / float8 float8div(float8, float8) division .TP 60 <\(eq bool float8le(float8, float8) less or equal .TP 60 < bool float8lt(float8, float8) less than1 .TP 60 \(eq bool float8eq(float8, float8) equality .TP 60 >\(eq bool float8ge(float8, float8) greater or equal .TP 60 > bool float8gt(float8, float8) greater than .TP 60 ^ float8 dpow(float8, float8) exponentiation .TP 60 int2 <> bool int2ne(int2, int2) inequality .TP 60 <> int4 int24ne(int2, int4) inequality .TP 60 % int2 int2mod(int2, int2) modulus .TP 60 % int4 int24mod(int2, int4) modulus .TP 60 * int2 int2mul(int2, int2) multiplication .TP 60 * int4 int24mul(int2, int4) multiplication .TP 60 + int2 int2pl(int2, int2) addition .TP 60 + int4 int24pl(int2, int4) addition .TP 60 \(mi int2 int2mi(int2, int2) subtraction .TP 60 \(mi int4 int24mi(int2, int4) subtraction .TP 60 / int2 int2div(int2, int2) division .TP 60 / int4 int24div(int2, int4) division .TP 60 <\(eq bool int2le(int2, int2) less or equal .TP 60 <\(eq bool int24le(int2, int4) less or equal .TP 60 < bool int2lt(int2, int2) less than .TP 60 < bool int24lt(int2, int4) less than .TP 60 \(eq bool int2eq(int2, int2) equality .TP 60 \(eq bool int24eq(int2, int4) equality .TP 60 >\(eq bool int2ge(int2, int2) greater or equal .TP 60 >\(eq bool int24ge(int2, int4) greater or equal .TP 60 > bool int2gt(int2, int2) greater than .TP 60 > bool int24gt(int2, int4) greater than .TP 60 int4 <> bool int4ne(int4, int4) inequality .TP 60 <> bool int42ne(int4, int2) inequality .TP 60 % int4 int42mod(int4, int2) modulus .TP 60 % int4 int4mod(int4, int4) modulus .TP 60 * int4 int42mul(int4, int2) multiplication .TP 60 * int4 int4mul(int4, int4) multiplication .TP 60 + int4 int42pl(int4, int2) addition .TP 60 + int4 int4pl(int4, int4) addition .TP 60 \(mi int4 int42mi(int4, int2) subtraction .TP 60 \(mi int4 int4mi(int4, int4) subtraction .TP 60 / int4 int42div(int4, int2) division .TP 60 / int4 int4div(int4, int4) division .TP 60 <\(eq bool int4le(int4, int4) less or equal .TP 60 <\(eq bool int42le(int4, int2) less or equal .TP 60 < bool int4lt(int4, int4) less than .TP 60 < bool int42lt(int4, int2) less than .TP 60 \(eq bool int4eq(int4, int4) equality .TP 60 \(eq bool int42eq(int4, int2) equality .TP 60 \(eq bool int4eqoid(int4, oid) equality .TP 60 >\(eq bool int4ge(int4, int4) greater or equal .TP 60 >\(eq bool int42ge(int4, int2) greater or equal .TP 60 > bool int4gt(int4, int4) greater than .TP 60 > bool int42lt(int4, int2) less than .TP 60 name <> bool namene(name, name) inequality .TP 60 !~ bool nameregexne(name, text) A does not match regular expression B, case sensitive .TP 60 !~* bool nameicregexne(name, text) A does not match regular expression B, case insensitive .TP 60 <\(eq bool namele(name, name) less or equal .TP 60 < bool namelt(name, name) less than .TP 60 \(eq bool nameeq(name, name) equality .TP 60 >\(eq bool namege(name, name) greater or equal .TP 60 > bool namegt(name, name) greater than .TP 60 ~ bool nameregexeq(name, text) A matches regular expression B, case sensitive .TP 60 ~* bool nameregexeq(name, text) A matches regular expression B, case insensitive .TP 60 oid <> bool oidne(oid, oid) inequality .TP 60 <> bool oidne(oid, regproc) inequality .TP 60 <\(eq bool oidle(oid, oid) less or equal .TP 60 < bool oidlt(oid, oid) less than .TP 60 \(eq bool oideq(oid, oid) equality .TP 60 \(eq bool oideqint4(oid, int4) equality .TP 60 \(eq bool oideq(oid, regproc) equality .TP 60 >\(eq bool oidge(oid, oid) greater or equal .TP 60 > bool oidgt(oid, oid) greater than .TP 60 oidname <> bool oidnamene(oidname, oidname) inequality .TP 60 < bool oidnamelt(oidname, oidname) less than .TP 60 <\(eq bool oidnamele(oidname, oidname) less or equal .TP 60 \(eq bool oidnameeq(oidname, oidname) equality .TP 60 > bool oidnamegt(oidname, oidname) greater than .TP 60 >\(eq bool oidnamege(oidname, oidname) greater or equal .TP 60 oidint2 <> bool oidint2ne(oidint2, oidint2) inequality .TP 60 < bool oidint2lt(oidint2, oidint2) less than .TP 60 <\(eq bool oidint2le(oidint2, oidint2) less or equal .TP 60 \(eq bool oidint2eq(oidint2, oidint2) equality .TP 60 > bool oidint2gt(oidint2, oidint2) greater than .TP 60 >\(eq bool oidint2ge(oidint2, oidint2) greater or equal .TP 60 oidint4 <> bool oidint4ne(oidint4, oidint4) inequality .TP 60 < bool oidint4lt(oidint4, oidint4) less than .TP 60 <\(eq bool oidint4le(oidint4, oidint4) less or equal .TP 60 \(eq bool oidint4eq(oidint4, oidint4) equality .TP 60 > bool oidint4gt(oidint4, oidint4) greater than .TP 60 >\(eq bool oidint4ge(oidint4, oidint4) greater or equal .TP 60 point !< bool point_left(point, point) A is left of B .TP 60 !> bool point_right(point, point) A is right of B .TP 60 !^ bool point_above(point, point) A is above B .TP 60 !| bool point_below(point, point) A is below B .TP 60 \(eq|\(eq bool point_eq(point, point) equality .TP 60 ===> bool on_pb(point, box) point inside box .TP 60 ===` bool on_ppath(point, path) point on path .TP 60 <===> int4 pointdist(point, point) distance between points .TP 60 polygon && bool poly_overlap(polygon, polygon) polygons overlap .TP 60 &< bool poly_overleft(polygon, polygon) A overlaps B but does not extend to right of B .TP 60 &> bool poly_overright(polygon, polygon) A overlaps B but does not extend to left of B .TP 60 << bool poly_left(polygon, polygon) A is left of B .TP 60 >> bool poly_right(polygon, polygon) A is right of B .TP 60 @ bool poly_contained(polygon, polygon) A is contained by B .TP 60 ~\(eq bool poly_same(polygon, polygon) equality .TP 60 ~ bool poly_contain(polygon, polygon) A contains B .TP 60 reltime <> bool reltimene(reltime, reltime) inequality .TP 60 <\(eq bool reltimele(reltime, reltime) less or equal .TP 60 < bool reltimelt(reltime, reltime) less than .TP 60 \(eq bool reltimeeq(reltime, reltime) equality .TP 60 >\(eq bool reltimege(reltime, reltime) greater or equal .TP 60 > bool reltimegt(reltime, reltime) greater than .TP 60 text <> bool textne(text, text) inequality .TP 60 !~ bool textregexne(text, text) A does not contain the regular expression B, case sensitive .TP 60 !~* bool texticregexne(text, text) A does not contain the regular expression B, case insensitive .TP 60 <\(eq bool text_le(text, text) less or equal .TP 60 < bool text_lt(text, text) less than .TP 60 \(eq bool texteq(text, text) equality .TP 60 >\(eq bool text_ge(text, text) greater or equal .TP 60 > bool text_gt(text, text) greater than .TP 60 ~ bool textregexeq(text, text) A contains the regular expression B. case sensitive .TP 60 ~* bool texticregexeq(text, text) A contains the regular expression B. case insensitive .TP 60 tinterval #<> bool intervallenne(tinterval, reltime) interval length not equal to reltime. .TP 60 #<\(eq bool intervallenle(tinterval, reltime) interval length less or equal reltime .TP 60 #< bool intervallenlt(tinterval, reltime) interval length less than reltime .TP 60 #\(eq bool intervalleneq(tinterval, reltime) interval length equal to reltime .TP 60 #>\(eq bool intervallenge(tinterval, reltime) interval length greater or equal reltime .TP 60 #> bool intervallengt(tinterval, reltime) interval length greater than reltime .TP 60 && bool intervalov(tinterval, tinterval) intervals overlap .TP 60 << bool intervalct(tinterval, tinterval) A contains B .TP 60 \(eq bool intervaleq(tinterval, tinterval) equality .TP 60 <> tinterval mktinterval(abstime, abstime) interval bounded by two abstimes .ad b .lf 189 built-in.3pqsrc \} .XA 2 "Unary operators" .SH "UNARY OPERATORS" The tables below give right and left unary operators. Left unary operators have the operator precede the operand; right unary operators have the operator follow the operand. .PP .SS "Right Unary Operators" .ie t \{ .lf 1 sql/right_unary.tbl .\" This is -*-nroff-*- .TS center expand; l l c l c l l lw(2i). \fBType\fP \fBOperator\fP \fBPOSTGRES Function Prototype\fP \fBOperation\fP _ float8 % float8 dround(float8) round to nearest integer .TE .lf 199 built-in.3pqsrc \} .el \{ .lf 1 obj/right_unary.nr .ta +11 +10 +39 .na .TP 60 \fBType\fP \fBOperator\fP \fBPOSTGRES Function Prototype\fP \fBOperation\fP .TP 60 float8 % float8 dround(float8) round to nearest integer .ad b .lf 202 built-in.3pqsrc \} .PP .SS "Left Unary Operators" .\" we want soelim to include the .nr table but not the .tbl table .ie t \{ .lf 1 sql/left_unary.tbl .\" This is -*-nroff-*- .TS center expand; l l c l c l l lw(2i). \fBType\fP \fBOperator\fP \fBPOSTGRES Function Prototype\fP \fBOperation\fP _ box @@ point box_center(box) center of box _ float4 @ float4 float4abs(float4) absolute value _ float8 @ float8 float8abs(float8) absolute value % float8 dtrunc(float8) truncate to integer |/ float8 dsqrt(float8) square root ||/ float8 dcbrt(float8) cube root : float8 dexp(float8) exponential function ; float8 dlog1(float8) natural logarithm _ tinterval | abstime intervalstart(tinterval) start of interval .TE .lf 208 built-in.3pqsrc \} .el \{ .lf 1 obj/left_unary.nr .ta +11 +10 +39 .na .TP 60 \fBType\fP \fBOperator\fP \fBPOSTGRES Function Prototype\fP \fBOperation\fP .TP 60 box @@ point box_center(box) center of box .TP 60 float4 @ float4 float4abs(float4) absolute value .TP 60 float8 @ float8 float8abs(float8) absolute value .TP 60 % float8 dtrunc(float8) truncate to integer .TP 60 |/ float8 dsqrt(float8) square root .TP 60 ||/ float8 dcbrt(float8) cube root .TP 60 : float8 dexp(float8) exponential function .TP 60 ; float8 dlog1(float8) natural logarithm .TP 60 tinterval | abstime intervalstart(tinterval) start of interval .ad b .lf 211 built-in.3pqsrc \} .XA 1 "Built-in aggregate functions" .SH "AGGREGATE FUNCTIONS" The table below gives the aggregate functions that are registered in the system catalogs. They operate on int2, int4, float4, and float8 types. .PP .if n .ta 5 +15 .if t .ta 0.5i +1.5i .in 0 .nf \fBName\fP \fBOperation\fP arg average sum sum max maximum min minimum count count .fi .in .SH "SEE ALSO" For examples on specifying literals of built-in types, see .IR SQL(sql). .SH BUGS .PP Although most of the input and output functions correponding to the base types (e.g., integers and floating point numbers) do some error-checking, none of them are particularly rigorous about it. More importantly, almost none of the operators and functions (e.g., addition and multiplication) perform any error-checking at all. Consequently, many of the numeric operations will (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.