11.\" This is -*-nroff-*-
22.\" XXX standard disclaimer belongs here....
3- .\" $Header: /cvsroot/pgsql/src/man/Attic/pgbuiltin.3,v 1.4 1997/09/16 03:01:43 momjian Exp $
3+ .\" $Header: /cvsroot/pgsql/src/man/Attic/pgbuiltin.3,v 1.5 1997/09/27 04:37:33 thomas Exp $
44.TH PGBUILTIN INTRO 04/01/97 PostgreSQL PostgreSQL
55.SH "DESCRIPTION"
66This section describes the data types, functions and operators
@@ -37,12 +37,9 @@ described in this section.
3737dateANSI SQL date type
3838datetimegeneral-use date and time
3939filenamelarge object filename
40- intalias for int4
41- integeralias for int4
4240int2two-byte signed integer
4341int28array of 8 int2
4442int4four-byte signed integer
45- floatalias for float4
4643float4single-precision floating-point number
4744float8double-precision floating-point number
4845lseg2-dimensional line segment
@@ -57,11 +54,9 @@ described in this section.
5754point2-dimensional geometric point
5855polygon2-dimensional polygon (same as a closed path)
5956circle2-dimensional circle (center and radius)
60- realalias for float4
6157regprocregistered procedure
6258reltime(relative) date and time span (duration)
6359smgrstorage manager
64- smallintalias for int2
6560textvariable length array of characters
6661tidtuple identifier type
6762time ANSI SQL time type
@@ -71,6 +66,52 @@ described in this section.
7166varcharvariable-length characters
7267xidtransaction identifier type
7368
69+ .fi
70+ .in
71+ .PP
72+ There are some data types defined by SQL/92 syntax which are mapped directly
73+ into native Postgres types. Note that the "exact numerics"
74+ .IR decimal
75+ and
76+ .IR numeric
77+ have fully implemented syntax but currently (postgres v6.2) support only a limited
78+ range of the values allowed by SQL/92.
79+
80+ .SH "List of SQL/92 types"
81+ .PP
82+ .if n .ta 5 +15 +25 +40
83+ .if t .ta 0.5i +1.5i +3.0i
84+ .in 0
85+ .nf
86+ \fB POSTGRES Type \fP \fB SQL/92 Type \fP \fB Meaning \fP
87+ char(n)character(n)fixed-length character string
88+ varchar(n)character varying(n)variable-length character string
89+ float4/8float(p)floating-point number with precision p
90+ float8double precisiondouble-precision floating-point number
91+ float8realdouble-precision floating-point number
92+ int2smallintsigned two-byte integer
93+ int4intsigned 4-byte integer
94+ int4integersigned 4-byte integer
95+ int4decimal(p,s)exact numeric for p <= 9, s = 0
96+ int4numeric(p,s)exact numeric for p == 9, s = 0
97+ timestamptimestamp with time zonedate/time
98+ timespanintervalgeneral-use time span
99+
100+ .fi
101+ .in
102+ .PP
103+ There are some constants and functions defined in SQL/92.
104+ .SH "List of SQL/92 constants"
105+ .PP
106+ .if n .ta 5 +20 +40
107+ .if t .ta 0.5i +1.5i +3.0i +4.0i
108+ .in 0
109+ .nf
110+ \fB SQL/92 Function \fP \fB Meaning \fP
111+ current_datedate of current transaction
112+ current_timetime of current transaction
113+ current_timestampdate and time of current transaction
114+
74115.fi
75116.in
76117.PP
@@ -83,8 +124,11 @@ Most date and time types share code for data input. For those types (
83124.IR datetime ,
84125.IR abstime ,
85126.IR timestamp ,
86- .IR timespan and
87- .IR reltime )
127+ .IR timespan ,
128+ .IR reltime ,
129+ .IR date ,
130+ and
131+ .IR time )
88132the input can have any of a wide variety of styles. For numeric date representations,
89133European and US conventions can differ, and the proper interpretation is obtained
90134by using the
@@ -97,6 +141,11 @@ Postgres (see section on
97141with the SQL style having European and US variants (see
98142.IR set (l)).
99143
144+ In future releases, the number of date/time types will decrease, with the current
145+ implementation of datetime becoming timestamp, timespan becoming interval,
146+ and (possibly) abstime
147+ and reltime being deprecated in favor of timestamp and interval.
148+
100149.SH "DATETIME"
101150General-use date and time is input using a wide range of
102151styles, including ISO-compatible, SQL-compatible, traditional
@@ -531,7 +580,11 @@ tinterval
531580
532581.SH "FUNCTIONS"
533582Many data types have functions available for conversion to other related types.
534- In addition, there are some type-specific functions.
583+ In addition, there are some type-specific functions. Functions which are also
584+ available through operators are documented as operators only.
585+
586+ .PP
587+ Some functions defined for text are also available for char() and varchar().
535588.PP
536589For the
537590date_part() and date_trunc()
@@ -546,6 +599,23 @@ to return day of week and `epoch' to return seconds since 1970.
546599.nf
547600Functions:
548601
602+ integer
603+ float8 float(int) convert integer to floating point
604+ float4 float4(int) convert integer to floating point
605+
606+ float
607+ int integer(float) convert floating point to integer
608+
609+ text
610+ text lower(text) convert text to lower case
611+ text lpad(text,int,text) left pad string to specified length
612+ text ltrim(text,text) left trim characters from text
613+ text position(text,text) extract specified substring
614+ text rpad(text,int,text) right pad string to specified length
615+ text rtrim(text,text) right trim characters from text
616+ text substr(text,int[,int]) extract specified substring
617+ text upper(text) convert text to upper case
618+
549619abstime
550620bool isfinite(abstime) TRUE if this is a finite time
551621datetime datetime(abstime) convert to datetime
@@ -587,6 +657,22 @@ circle
587657float8 radius(circle) radius of circle
588658float8 diameter(circle) diameter of circle
589659float8 area(circle) area of circle
660+
661+ .fi
662+ .PP
663+ SQL/92 defines functions with specific syntax. Some of these
664+ are implemented using other Postgres functions.
665+
666+ .nf
667+ SQL/92 Functions:
668+
669+ text
670+ text position(text in text) extract specified substring
671+ text substring(text [from int] [for int])
672+ extract specified substring
673+ text trim([leading|trailing|both] [text] from text)
674+ trim characters from text
675+
590676.fi
591677
592678.SH "BINARY OPERATORS"
@@ -819,8 +905,8 @@ bool |lseg |line |?# |inter_sl
819905bool |lseg |line |@ |on_sl
820906bool |lseg |lseg |= |lseg_eq
821907bool |lseg |lseg |?# |lseg_intersect
822- bool |lseg |lseg |?-\| |lseg_perp
823- bool |lseg |lseg |?\|\| |lseg_parallel
908+ bool |lseg |lseg |?-| |lseg_perp
909+ bool |lseg |lseg |?|| |lseg_parallel
824910bool |money |money |< |cash_lt
825911bool |money |money |<= |cash_le
826912bool |money |money |<> |cash_ne
@@ -876,14 +962,14 @@ bool |point |box |@ |on_pb
876962bool |point |circle |@ |pt_contained_circle
877963bool |point |line |@ |on_pl
878964bool |point |lseg |@ |on_ps
879- bool |point |path |@ |pt_contained_path
880965bool |point |path |@ |on_ppath
966+ bool |point |path |@ |pt_contained_path
881967bool |point |point |<< |point_left
882968bool |point |point |<^ |point_below
883969bool |point |point |>> |point_right
884970bool |point |point |>^ |point_above
885971bool |point |point |?- |point_horiz
886- bool |point |point |?\| |point_vert
972+ bool |point |point |?| |point_vert
887973bool |point |point |~= |point_eq
888974bool |point |polygon |@ |pt_contained_poly
889975bool |polygon |point |~ |poly_contain_pt
@@ -939,8 +1025,14 @@ bool |tinterval |reltime |#> |intervallengt
9391025bool |tinterval |reltime |#>= |intervallenge
9401026bool |tinterval |tinterval |&& |intervalov
9411027bool |tinterval |tinterval |< |intervalct
1028+ bool |tinterval |tinterval |< |intervallt
9421029bool |tinterval |tinterval |<< |intervalct
1030+ bool |tinterval |tinterval |<= |intervalle
1031+ bool |tinterval |tinterval |<> |intervalne
9431032bool |tinterval |tinterval |= |intervaleq
1033+ bool |tinterval |tinterval |> |intervalgt
1034+ bool |tinterval |tinterval |>= |intervalge
1035+ bool |tinterval |tinterval |~= |intervalsame
9441036bool |varchar |text |!~ |textregexne
9451037bool |varchar |text |!~* |texticregexne
9461038bool |varchar |text |!~~ |textnlike
@@ -996,8 +1088,8 @@ float8 |lseg |box |<-> |dist_sb
9961088float8 |lseg |line |<-> |dist_sl
9971089float8 |lseg |lseg |<-> |lseg_distance
9981090float8 |path |path |<-> |path_distance
999- float8 |point |box |<-> |dist_pl
10001091float8 |point |box |<-> |dist_pb
1092+ float8 |point |box |<-> |dist_pl
10011093float8 |point |lseg |<-> |dist_ps
10021094float8 |point |path |<-> |dist_ppath
10031095float8 |point |point |<-> |point_distance
@@ -1035,8 +1127,18 @@ int4 |int4 |int4 |* |int4mul
10351127int4 |int4 |int4 |+ |int4pl
10361128int4 |int4 |int4 |- |int4mi
10371129int4 |int4 |int4 |/ |int4div
1038- money |money |float8 |* |cash_mul
1039- money |money |float8 |/ |cash_div
1130+ money |float4 |money |* |flt4_mul_cash
1131+ money |float8 |money |* |flt8_mul_cash
1132+ money |int2 |money |* |int2_mul_cash
1133+ money |int4 |money |* |int4_mul_cash
1134+ money |money |float4 |* |cash_mul_flt4
1135+ money |money |float4 |/ |cash_div_flt4
1136+ money |money |float8 |* |cash_mul_flt8
1137+ money |money |float8 |/ |cash_div_flt8
1138+ money |money |int2 |* |cash_mul_int2
1139+ money |money |int2 |/ |cash_div_int2
1140+ money |money |int4 |* |cash_mul_int4
1141+ money |money |int4 |/ |cash_div_int4
10401142money |money |money |+ |cash_pl
10411143money |money |money |- |cash_mi
10421144path |path |path |+ |path_add
@@ -1064,7 +1166,7 @@ timespan |timespan |timespan |+ |timespan_pl
10641166timespan |timespan |timespan |- |timespan_mi
10651167timespan |timespan |timespan |/ |timespan_div
10661168tinterval|abstime |abstime |<#> |mktinterval
1067- (446 rows)
1169+ (462 rows)
10681170
10691171.ec
10701172.fi
@@ -1089,28 +1191,28 @@ left_unary|operand |return_type
10891191----------+---------+-----------
10901192@@ |box |point
10911193@@ |circle |point
1092- @ |float4 |float4
10931194- |float4 |float4
1094- \| / |float8 |float8
1195+ @ |float4 |float4
1196+ - |float8 |float8
10951197@ |float8 |float8
1096- ; |float8 |float8
1097- : |float8 |float8
1198+ |/ |float8 |float8
1199+ ||/ |float8 |float8
10981200% |float8 |float8
1099- \|\| / |float8 |float8
1100- - |float8 |float8
1201+ : |float8 |float8
1202+ ; |float8 |float8
11011203- |int2 |int2
1102- - |int4 |int4
11031204!! |int4 |int4
1104- ?- |lseg |bool
1105- ?\| |lseg |bool
1205+ - |int4 |int4
11061206@@ |lseg |point
1107- ?? |path |float8
1207+ ?- |lseg |bool
1208+ ?| |lseg |bool
11081209# |path |int4
1210+ ?? |path |float8
11091211@@ |path |point
11101212@@ |polygon |point
11111213# |polygon |int4
11121214- |timespan |timespan
1113- \| |tinterval|abstime
1215+ | |tinterval|abstime
11141216(24 rows)
11151217
11161218.ec
@@ -1150,7 +1252,8 @@ This list was generated from the Postgres system catalogs with the query:
11501252
11511253.nf
11521254.eo
1153- SELECT a.aggname, t.typname
1255+ SELECT a.aggname AS aggname,
1256+ t.typname AS typname
11541257FROM pg_aggregate a, pg_type t
11551258WHERE a.aggbasetype = t.oid
11561259ORDER BY aggname, typname;
