built-in(3pq)

DESCRIPTION

This section describes the data types, functions and operators available to users in as it is distributed.

BUILT-IN TYPES

This section describes both built-in data types. These Built-in types are are installed in every database.

Users may add new types to using the define type command described in this manual. User-defined types are not described in this section.

	POSTGRES Type	Meaning
	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

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.

ABSOLUTE TIME

Absolute time is specified using the following syntax: Month Day [ Hour : Minute : Second ] Year [ Timezone ] 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 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.

The special absolute time values “current”, “infinity” and “-infinity” are also 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.

The strings “now” 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.

RELATIVE TIME

Relative time is specified with the following syntax: @ Quantity Unit [Direction] 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''

(Note: Valid relative times are less than or equal to 68 years.) In addition, the special relative time “Undefined RelTime” is provided.

TIME RANGES

Time ranges are specified as: [ 'abstime' 'abstime'] where abstime is a time in the absolute time format. Special abstime values such as “current”, “infinity” and “-infinity” can be used.

OPERATORS

provides a large number of built-in operators on system types. These operators are declared in the system catalog “pg_operator”. Every entry in “pg_operator” includes the object ID of the procedure that implements the operator.

Users may invoke operators using the operator name, as in select * from emp where salary < 40000; Alternatively, users may call the functions that implement the operators directly. In this case, the query above would be expressed as 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.

BINARY OPERATORS

This list was generated from the system catalogs with the query 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; The list is sorted by the built-in type name of the first operand. The 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 types and so are not directly usable as C function prototypes.

Type Operator POSTGRES Function Prototype

Operation

abstime <> bool abstimene(abstime, abstime)

inequality

+ abstime timepl(abstime, reltime)

addition

− abstime timemi(abstime, reltime)

subtraction

<= bool abstimele(abstime, abstime)

less or equal

<?> bool ininterval(abstime, tinterval)

abstime in tinterval?

< bool abstimelt(abstime, abstime)

less than

= bool abstimeeq(abstime, abstime)

equality

>= bool abstimege(abstime, abstime)

greater or equal

> bool abstimegt(abstime, abstime)

greater than

bool = bool booleq(bool, bool)

equality

<> bool boolne(bool, bool)

inequality

box && bool box_overlap(box, box)

boxes overlap

&< bool box_overleft(box, box)

box A overlaps box B, but does not extend to right of box B

&> bool box_overright(box, box)

box A overlaps box B, but does not extend to left of box B

<< bool box_left(box, box)

A is left of B

<= bool box_le(box, box)

area less or equal

< bool box_lt(box, box)

area less than

= bool box_eq(box, box)

area equal

>= 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

~= 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

− bool charmi(char, char)

subtraction

/ bool chardiv(char, char)

division

<= bool charle(char, char)

less or equal

< bool charlt(char, char)

less than

= bool chareq(char, char)

equality

>= bool charge(char, char)

greater or equal

> bool chargt(char, char)

greater than

char2 <> bool char2ne(char2, char2)

inequality

!~ bool char2regexne(char2, text)

A does not match regular expression B, case-sensitive

!~* bool char2icregexne(char2, text)

A does not match regular expression B, case-insensitive

<= bool char2le(char2, char2)

less or equal

< bool char2lt(char2, char2)

less than

= bool char2eq(char2, char2)

equality

>= bool char2ge(char2, char2)

greater or equal

> bool char2gt(char2, char2)

greater than

~ bool char2regexeq(char2, text)

A matches regular expression B, case-sensitive.

~* bool char2icregexeq(char2, text)

A matches regular expression B, case-insensitive.

char4 <> bool char4ne(char4, char4)

inequality

!~ bool char4regexne(char4, text)

A does not match regular expression B, case-sensitive

!~* bool char4icregexne(char4, text)

A does not match regular expression B, case-insensitive

<= bool char4le(char4, char4)

less or equal

< bool char4lt(char4, char4)

less than

= bool char4eq(char4, char4)

equality

>= bool char4ge(char4, char4)

greater or equal

> bool char4gt(char4, char4)

greater than

~ bool char4regexeq(char4, text)

A matches regular expression B, case-sensitive

~* bool char4icregexeq(char4, text)

A matches regular expression B, case-insensitive

char8 <> bool char8ne(char8, char8)

inequality

!~ bool char8regexne(char8, text)

A does not match regular expression B, case sensitive

!~* bool char8icregexne(char8, text)

A does not match regular expression B, case insensitive

<= bool char8le(char8, char8)

less or equal

< bool char8lt(char8, char8)

less than

= bool char8eq(char8, char8)

equality

>= bool char8ge(char8, char8)

greater or equal

> bool char8gt(char8, char8)

greater than

~ bool char8regexeq(char8, text)

A matches regular expression B, case sensitive

~* bool char8icregexeq(char8, text)

A matches regular expression B, case insensitive

char16 <> bool char16ne(char16, char16)

inequality

!~ bool char16regexne(char16, text)

A does not match regular expression B, case sensitive

!~* bool char16icregexne(char16, text)

A does not match regular expression B, case insensitive

<= bool char16le(char16, char16)

less or equal

< bool char16lt(char16, char16)

less than

= bool char16eq(char16, char16)

equality

>= bool char16ge(char16, char16)

greater or equal

> bool char16gt(char16, char16)

greater than

~ bool char16regexeq(char16, text)

A matches regular expression B, case sensitive

~* bool char16regexeq(char16, text)

A matches regular expression B, case insensitive

float4 <> bool float4ne(float4, float4)

inequality

* float4 float4mul(float4, float4)

multiplication

+ float4 float4pl(float4, float4)

addition

− float4 float4mi(float4, float4)

subtraction

/ float4 float4div(float4, float4)

division

<= bool float4le(float4, float4)

less or equal

< bool float4lt(float4, float4)

less than

= bool float4eq(float4, float4)

equality

>= 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

− float8 float8mi(float8, float8)

subtraction

/ float8 float8div(float8, float8)

division

<= bool float8le(float8, float8)

less or equal

< bool float8lt(float8, float8)

less than1

= bool float8eq(float8, float8)

equality

>= 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

− int2 int2mi(int2, int2)

subtraction

− int4 int24mi(int2, int4)

subtraction

/ int2 int2div(int2, int2)

division

/ int4 int24div(int2, int4)

division

<= bool int2le(int2, int2)

less or equal

<= bool int24le(int2, int4)

less or equal

< bool int2lt(int2, int2)

less than

< bool int24lt(int2, int4)

less than

= bool int2eq(int2, int2)

equality

= bool int24eq(int2, int4)

equality

>= bool int2ge(int2, int2)

greater or equal

>= 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

− int4 int42mi(int4, int2)

subtraction

− int4 int4mi(int4, int4)

subtraction

/ int4 int42div(int4, int2)

division

/ int4 int4div(int4, int4)

division

<= bool int4le(int4, int4)

less or equal

<= bool int42le(int4, int2)

less or equal

< bool int4lt(int4, int4)

less than

< bool int42lt(int4, int2)

less than

= bool int4eq(int4, int4)

equality

= bool int42eq(int4, int2)

equality

= bool int4eqoid(int4, oid)

equality

>= bool int4ge(int4, int4)

greater or equal

>= 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)

A does not match regular expression B, case sensitive

!~* bool nameicregexne(name, text)

A does not match regular expression B, case insensitive

<= bool namele(name, name)

less or equal

< bool namelt(name, name)

less than

= bool nameeq(name, name)

equality

>= bool namege(name, name)

greater or equal

> bool namegt(name, name)

greater than

~ bool nameregexeq(name, text)

A matches regular expression B, case sensitive

~* bool nameregexeq(name, text)

A matches regular expression B, case insensitive

oid <> bool oidne(oid, oid)

inequality

<> bool oidne(oid, regproc)

inequality

<= bool oidle(oid, oid)

less or equal

< bool oidlt(oid, oid)

less than

= bool oideq(oid, oid)

equality

= bool oideqint4(oid, int4)

equality

= bool oideq(oid, regproc)

equality

>= 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

<= bool oidnamele(oidname, oidname)

less or equal

= bool oidnameeq(oidname, oidname)

equality

> bool oidnamegt(oidname, oidname)

greater than

>= bool oidnamege(oidname, oidname)

greater or equal

oidint2 <> bool oidint2ne(oidint2, oidint2)

inequality

< bool oidint2lt(oidint2, oidint2)

less than

<= bool oidint2le(oidint2, oidint2)

less or equal

= bool oidint2eq(oidint2, oidint2)

equality

> bool oidint2gt(oidint2, oidint2)

greater than

>= bool oidint2ge(oidint2, oidint2)

greater or equal

oidint4 <> bool oidint4ne(oidint4, oidint4)

inequality

< bool oidint4lt(oidint4, oidint4)

less than

<= bool oidint4le(oidint4, oidint4)

less or equal

= bool oidint4eq(oidint4, oidint4)

equality

> bool oidint4gt(oidint4, oidint4)

greater than

>= 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

=|= 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)

A overlaps B but does not extend to right of B

&> bool poly_overright(polygon, polygon)

A overlaps B but does not extend to left of B

<< 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

~= bool poly_same(polygon, polygon)

equality

~ bool poly_contain(polygon, polygon)

A contains B

reltime <> bool reltimene(reltime, reltime)

inequality

<= bool reltimele(reltime, reltime)

less or equal

< bool reltimelt(reltime, reltime)

less than

= bool reltimeeq(reltime, reltime)

equality

>= bool reltimege(reltime, reltime)

greater or equal

> bool reltimegt(reltime, reltime)

greater than

text <> bool textne(text, text)

inequality

!~ bool textregexne(text, text)

A does not contain the regular expression B, case sensitive

!~* bool texticregexne(text, text)

A does not contain the regular expression B, case insensitive

<= bool text_le(text, text)

less or equal

< bool text_lt(text, text)

less than

= bool texteq(text, text)

equality

>= bool text_ge(text, text)

greater or equal

> bool text_gt(text, text)

greater than

~ bool textregexeq(text, text)

A contains the regular expression B. case sensitive

~* bool texticregexeq(text, text)

A contains the regular expression B. case insensitive

tinterval #<> bool intervallenne(tinterval, reltime)

interval length not equal to reltime.

#<= bool intervallenle(tinterval, reltime)

interval length less or equal reltime

#< bool intervallenlt(tinterval, reltime)

interval length less than reltime

#= bool intervalleneq(tinterval, reltime)

interval length equal to reltime

#>= bool intervallenge(tinterval, reltime)

interval length greater or equal reltime

#> bool intervallengt(tinterval, reltime)

interval length greater than reltime

&& bool intervalov(tinterval, tinterval)

intervals overlap

<< bool intervalct(tinterval, tinterval)

A contains B

= bool intervaleq(tinterval, tinterval)

equality

<> tinterval mktinterval(abstime, abstime)

interval bounded by two abstimes

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.

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.

	Name	Operation	
	arg	average
	sum	sum
	max	maximum
	min	minimum
	count	count

SEE ALSO

For examples on specifying literals of built-in types, see SQL(commands).

BUGS

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.

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.