path: root/src/man/pgbuiltin.3

.\" This is -*-nroff-*-
.\" XXX standard disclaimer belongs here....
.\" $Header: /cvsroot/pgsql/src/man/Attic/pgbuiltin.3,v 1.8 1997/10/01 18:57:48 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
	int2	two-byte signed integer
	int28	array of 8 int2
	int4	four-byte signed integer
	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)
	regproc	registered procedure
	reltime	(relative) date and time span (duration)
	smgr	storage manager
	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
There are some data types defined by SQL/92 syntax which are mapped directly
into native Postgres types. Note that the "exact numerics"
.IR decimal
and
.IR numeric
have fully implemented syntax but currently (postgres v6.2) support only a limited 
range of the values allowed by SQL/92.

.SH "List of SQL/92 types"
.PP
.if n .ta 5 +15 +25 +40
.if t .ta 0.5i +1.5i +3.0i
.in 0
.nf
	\fBPOSTGRES Type\fP	\fBSQL/92 Type\fP	\fBMeaning\fP
	char(n)	character(n)	fixed-length character string
	varchar(n)	character varying(n)	variable-length character string
	float4/8	float(p)	floating-point number with precision p
	float8	double precision	double-precision floating-point number
	float8	real	double-precision floating-point number
	int2	smallint	signed two-byte integer
	int4	int	signed 4-byte integer
	int4	integer	signed 4-byte integer
	int4	decimal(p,s)	exact numeric for p <= 9, s = 0
	int4	numeric(p,s)	exact numeric for p == 9, s = 0
	timestamp	timestamp with time zone	date/time
	timespan	interval	general-use time span

.fi
.in
.PP
There are some constants and functions defined in SQL/92.
.SH "List of SQL/92 constants"
.PP
.if n .ta 5 +20 +40
.if t .ta 0.5i +1.5i +3.0i +4.0i
.in 0
.nf
	\fBSQL/92 Function\fP	\fBMeaning\fP
	current_date	date of current transaction
	current_time	time of current transaction
	current_timestamp	date and time of current transaction

.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"
Most date and time types share code for data input. For those types (
.IR datetime ,
.IR abstime ,
.IR timestamp ,
.IR timespan ,
.IR reltime ,
.IR date ,
and
.IR time )
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)).

In future releases, the number of date/time types will decrease, with the current
implementation of datetime becoming timestamp, timespan becoming interval,
and (possibly) abstime
and reltime being deprecated in favor of timestamp and interval.

.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 "TIMESTAMP"
This is currently a limited-range absolute time which closely resembles the
.IR abstime
data type. It shares the general input parser with the other date/time types.
In future releases this type will absorb the capabilities of the datetime type
and will move toward SQL92 compliance.

.PP
timestamp is specified using the same syntax as for datetime.

.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
    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
.fi
.PP
Paths are output using the first syntax.
Note that Postgres versions prior to
v6.1 used a format for paths which had a single leading parenthesis, a "closed" flag,
an integer count of the number of points, then the list of points followed by a
closing parenthesis. The built-in function upgradepath() is supplied to convert
paths dumped and reloaded from pre-v6.1 databases.

.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 , y1   , ... ,   xn , yn    
.sp
where
	(x1,y1),...,(xn,yn) are points 1 through n
.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.
	a single leading "(" indicates a v6.0-compatible format
( x1 , ... , xn , y1 , ... , yn )  
Note that Postgres versions prior to
v6.1 used a format for polygons which had a single leading parenthesis, the list
of x-axis coordinates, the list of y-axis coordinates, followed by a closing parenthesis.
The built-in function upgradepoly() is supplied to convert
polygons dumped and reloaded from pre-v6.1 databases.

.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

geometric
	@	A contained by (inside or on) B
	~	A contains (around or on) B
	@@	center of object
	<->	distance between A and B
	&&	objects 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 above B
	<^	A is below B

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	A same as B (equality)
	@	point inside (or on) path, box, circle, polygon

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
	>>	A is right of B
	>^	A is above B
	<^	A is below B
	\(eq	area equal
	<	area less than
	<\(eq	area less or equal
	>\(eq	area greater or equal
	>	area greater than
	~\(eq	A same as B (equality)
	@	A is contained in 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
	~\(eq	A same as B (equality)
	@	A is contained by 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 above B
	<^	A is below B
	~\(eq	A same as B (equality)
	@	A is contained by 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. Functions which are also
available through operators are documented as operators only.

.PP
Some functions defined for text are also available for char() and varchar().
.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() allows `dow'
to return day of week and `epoch' to return seconds since 1970 for datetime
and 'epoch' to return total elapsed seconds for timespan.

.nf
Functions:

integer
    float8   float(int)                convert integer to floating point
    float4   float4(int)               convert integer to floating point

float
    int      integer(float)            convert floating point to integer

text
    text     lower(text)               convert text to lower case
    text     lpad(text,int,text)       left pad string to specified length
    text     ltrim(text,text)          left trim characters from text
    text     position(text,text)       extract specified substring
    text     rpad(text,int,text)       right pad string to specified length
    text     rtrim(text,text)          right trim characters from text
    text     substr(text,int[,int])    extract specified substring
    text     upper(text)               convert text to upper case

abstime
	bool     isfinite(abstime)         TRUE if this is a finite time
	datetime datetime(abstime)         convert to datetime

date
	datetime datetime(date)            convert to datetime
	datetime datetime(date,time)       convert to datetime

datetime
	timespan age(datetime,datetime)    date difference preserving months and years
	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
	abstime  abstime(datetime)         convert to abstime

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
.PP
SQL/92 defines functions with specific syntax. Some of these
are implemented using other Postgres functions.

.nf
SQL/92 Functions:

text
    text     position(text in text)    extract specified substring
    text     substring(text [from int] [for int])
                                       extract specified substring
    text     trim([leading|trailing|both] [text] from text)
                                       trim characters from text

.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
.eo
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_below          
bool     |box       |box       |=      |box_eq             
bool     |box       |box       |>      |box_gt             
bool     |box       |box       |>=     |box_ge             
bool     |box       |box       |>>     |box_right          
bool     |box       |box       |>^     |box_above          
bool     |box       |box       |?#     |box_overlap        
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      |~      |char16regexeq      
bool     |char16    |text      |~*     |char16icregexeq    
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_overlap     
bool     |circle    |circle    |&<     |circle_overleft    
bool     |circle    |circle    |&>     |circle_overright   
bool     |circle    |circle    |<      |circle_lt          
bool     |circle    |circle    |<<     |circle_left        
bool     |circle    |circle    |<=     |circle_le          
bool     |circle    |circle    |<>     |circle_ne          
bool     |circle    |circle    |<^     |circle_below       
bool     |circle    |circle    |=      |circle_eq          
bool     |circle    |circle    |>      |circle_gt          
bool     |circle    |circle    |>=     |circle_ge          
bool     |circle    |circle    |>>     |circle_right       
bool     |circle    |circle    |>^     |circle_above       
bool     |circle    |circle    |@      |circle_contained   
bool     |circle    |circle    |~      |circle_contain     
bool     |circle    |circle    |~=     |circle_same        
bool     |circle    |point     |~      |circle_contain_pt  
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     |line      |box       |?#     |inter_lb           
bool     |lseg      |box       |?#     |inter_sb           
bool     |lseg      |box       |@      |on_sb              
bool     |lseg      |line      |?#     |inter_sl           
bool     |lseg      |line      |@      |on_sl              
bool     |lseg      |lseg      |=      |lseg_eq            
bool     |lseg      |lseg      |?#     |lseg_intersect     
bool     |lseg      |lseg      |?-|    |lseg_perp          
bool     |lseg      |lseg      |?||    |lseg_parallel      
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     |path      |path      |<      |path_n_lt          
bool     |path      |path      |<=     |path_n_le          
bool     |path      |path      |=      |path_n_eq          
bool     |path      |path      |>      |path_n_gt          
bool     |path      |path      |>=     |path_n_ge          
bool     |path      |path      |?#     |path_inter         
bool     |path      |point     |~      |path_contain_pt    
bool     |point     |box       |@      |on_pb              
bool     |point     |circle    |@      |pt_contained_circle
bool     |point     |line      |@      |on_pl              
bool     |point     |lseg      |@      |on_ps              
bool     |point     |path      |@      |on_ppath           
bool     |point     |path      |@      |pt_contained_path  
bool     |point     |point     |<<     |point_left         
bool     |point     |point     |<^     |point_below        
bool     |point     |point     |>>     |point_right        
bool     |point     |point     |>^     |point_above        
bool     |point     |point     |?-     |point_horiz        
bool     |point     |point     |?|     |point_vert         
bool     |point     |point     |~=     |point_eq           
bool     |point     |polygon   |@      |pt_contained_poly  
bool     |polygon   |point     |~      |poly_contain_pt    
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 |<      |intervallt         
bool     |tinterval |tinterval |<<     |intervalct         
bool     |tinterval |tinterval |<=     |intervalle         
bool     |tinterval |tinterval |<>     |intervalne         
bool     |tinterval |tinterval |=      |intervaleq         
bool     |tinterval |tinterval |>      |intervalgt         
bool     |tinterval |tinterval |>=     |intervalge         
bool     |tinterval |tinterval |~=     |intervalsame       
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          
box      |box       |box       |#      |box_intersect      
box      |box       |point     |*      |box_mul            
box      |box       |point     |+      |box_add            
box      |box       |point     |-      |box_sub            
box      |box       |point     |/      |box_div            
char     |char      |char      |*      |charmul            
char     |char      |char      |+      |charpl             
char     |char      |char      |-      |charmi             
char     |char      |char      |/      |chardiv            
circle   |circle    |point     |*      |circle_mul_pt      
circle   |circle    |point     |+      |circle_add_pt      
circle   |circle    |point     |-      |circle_sub_pt      
circle   |circle    |point     |/      |circle_div_pt      
date     |date      |int4      |+      |date_pli           
date     |date      |int4      |-      |date_mii           
datetime |datetime  |timespan  |+      |datetime_pl_span   
datetime |datetime  |timespan  |-      |datetime_mi_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   |circle    |polygon   |<->    |dist_cpoly         
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   |line      |box       |<->    |dist_lb            
float8   |line      |line      |<->    |line_distance      
float8   |lseg      |box       |<->    |dist_sb            
float8   |lseg      |line      |<->    |dist_sl            
float8   |lseg      |lseg      |<->    |lseg_distance      
float8   |path      |path      |<->    |path_distance      
float8   |point     |box       |<->    |dist_pl            
float8   |point     |box       |<->    |dist_pb            
float8   |point     |lseg      |<->    |dist_ps            
float8   |point     |path      |<->    |dist_ppath         
float8   |point     |point     |<->    |point_distance     
float8   |polygon   |polygon   |<->    |poly_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    |float4    |money     |*      |flt4_mul_cash      
money    |float8    |money     |*      |flt8_mul_cash      
money    |int2      |money     |*      |int2_mul_cash      
money    |int4      |money     |*      |int4_mul_cash      
money    |money     |float4    |*      |cash_mul_flt4      
money    |money     |float4    |/      |cash_div_flt4      
money    |money     |float8    |*      |cash_mul_flt8      
money    |money     |float8    |/      |cash_div_flt8      
money    |money     |int2      |*      |cash_mul_int2      
money    |money     |int2      |/      |cash_div_int2      
money    |money     |int4      |*      |cash_mul_int4      
money    |money     |int4      |/      |cash_div_int4      
money    |money     |money     |+      |cash_pl            
money    |money     |money     |-      |cash_mi            
path     |path      |path      |+      |path_add           
path     |path      |point     |*      |path_mul_pt        
path     |path      |point     |+      |path_add_pt        
path     |path      |point     |-      |path_sub_pt        
path     |path      |point     |/      |path_div_pt        
point    |line      |box       |##     |close_lb           
point    |lseg      |box       |##     |close_sb           
point    |lseg      |line      |##     |close_sl           
point    |lseg      |lseg      |#      |lseg_interpt       
point    |point     |box       |##     |close_pb           
point    |point     |line      |##     |close_pl           
point    |point     |lseg      |##     |close_ps           
point    |point     |point     |*      |point_mul          
point    |point     |point     |+      |point_add          
point    |point     |point     |-      |point_sub          
point    |point     |point     |/      |point_div          
polygon  |point     |circle    |<->    |dist_pc            
text     |bpchar    |bpchar    |||     |textcat            
text     |text      |text      |||     |textcat            
text     |varchar   |varchar   |||     |textcat            
timespan |datetime  |datetime  |-      |datetime_mi        
timespan |timespan  |timespan  |+      |timespan_pl        
timespan |timespan  |timespan  |-      |timespan_mi        
timespan |timespan  |timespan  |/      |timespan_div       
tinterval|abstime   |abstime   |<#>    |mktinterval        
(462 rows)
.ec
.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
.eo
SELECT   o.oprname AS left_unary, 
         t.typname AS operand,
         r.typname AS return_type
FROM     pg_operator o, pg_type t, pg_type r
WHERE    o.oprkind = 'l' AND          -- left unary
         o.oprright = t.oid AND
         o.oprresult = r.oid
ORDER BY operand;

left_unary|operand  |return_type
----------+---------+-----------
@@        |box      |point      
@@        |circle   |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       
?|        |lseg     |bool       
@@        |lseg     |point      
?-        |lseg     |bool       
??        |path     |float8     
#         |path     |int4       
@@        |path     |point      
@@        |polygon  |point      
#         |polygon  |int4       
-         |timespan |timespan   
|         |tinterval|abstime    
(24 rows)

.ec
.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
.eo
SELECT   o.oprname AS right_unary,
         t.typname AS operand,
         r.typname AS return_type
FROM     pg_operator o, pg_type t, pg_type r
WHERE    o.oprkind = 'r' AND          -- right unary
         o.oprleft = t.oid AND
         o.oprresult = r.oid
ORDER BY operand;

right_unary|operand|return_type
-----------+-------+-----------
%          |float8 |float8     
!          |int4   |int4       
(2 rows)

.ec
.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
.eo
SELECT   a.aggname AS aggname,
         t.typname AS 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   
avg    |timespan
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   
sum    |timespan
(30 rows)

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