1 .\" This is -*-nroff-*-
2 .\" XXX standard disclaimer belongs here....
3 .\" $Header: /cvsroot/pgsql/src/man/Attic/pgbuiltin.3,v 1.6 1997/10/01 17:05:16 thomas Exp $
4 .TH PGBUILTIN INTRO 04/01/97 PostgreSQL PostgreSQL
6 This section describes the data types, functions and operators
7 available to users in Postgres as it is distributed.
12 These Built-in types are installed in every database.
14 Users may add new types to Postgres using the
16 command described in this manual. User-defined types are not
17 described in this section.
18 .SH "List of built-in types"
21 .if t .ta 0.5i +1.5i +3.0i
24 \fBPOSTGRES Type\fP \fBMeaning\fP
25 abstime (absolute) limited-range date and time
26 aclitem access control list item
28 box 2-dimensional rectangle
29 bpchar blank-padded characters
30 bytea variable length array of bytes
32 char2 array of 2 characters
33 char4 array of 4 characters
34 char8 array of 8 characters
35 char16 array of 16 characters
36 cid command identifier type
37 date ANSI SQL date type
38 datetime general-use date and time
39 filename large object filename
40 int2 two-byte signed integer
42 int4 four-byte signed integer
43 float4 single-precision floating-point number
44 float8 double-precision floating-point number
45 lseg 2-dimensional line segment
46 money decimal type with fixed precision
47 name a multi-character type for storing system identifiers
48 oid object identifier type
50 oidchar16 oid and char16 composed
51 oidint2 oid and int2 composed
52 oidint4 oid and int4 composed
53 path open or closed line segments
54 point 2-dimensional geometric point
55 polygon 2-dimensional polygon (same as a closed path)
56 circle 2-dimensional circle (center and radius)
57 regproc registered procedure
58 reltime (relative) date and time span (duration)
60 text variable length array of characters
61 tid tuple identifier type
62 time ANSI SQL time type
63 timespan general-use time span (duration)
64 timestamp limited-range ISO-format date and time
65 tinterval time interval (start and stop abstime)
66 varchar variable-length characters
67 xid transaction identifier type
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"
77 have fully implemented syntax but currently (postgres v6.2) support only a limited
78 range of the values allowed by SQL/92.
80 .SH "List of SQL/92 types"
82 .if n .ta 5 +15 +25 +40
83 .if t .ta 0.5i +1.5i +3.0i
86 \fBPOSTGRES Type\fP \fBSQL/92 Type\fP \fBMeaning\fP
87 char(n) character(n) fixed-length character string
88 varchar(n) character varying(n) variable-length character string
89 float4/8 float(p) floating-point number with precision p
90 float8 double precision double-precision floating-point number
91 float8 real double-precision floating-point number
92 int2 smallint signed two-byte integer
93 int4 int signed 4-byte integer
94 int4 integer signed 4-byte integer
95 int4 decimal(p,s) exact numeric for p <= 9, s = 0
96 int4 numeric(p,s) exact numeric for p == 9, s = 0
97 timestamp timestamp with time zone date/time
98 timespan interval general-use time span
103 There are some constants and functions defined in SQL/92.
104 .SH "List of SQL/92 constants"
107 .if t .ta 0.5i +1.5i +3.0i +4.0i
110 \fBSQL/92 Function\fP \fBMeaning\fP
111 current_date date of current transaction
112 current_time time of current transaction
113 current_timestamp date and time of current transaction
118 Many of the built-in types have obvious external formats. However, several
119 types are either unique to Postgres, such as open and closed paths, or have
120 several possibilities for formats, such as date and time types.
122 .SH "Syntax of date and time types"
123 Most date and time types share code for data input. For those types (
132 the input can have any of a wide variety of styles. For numeric date representations,
133 European and US conventions can differ, and the proper interpretation is obtained
136 command before entering data.
137 Output formats can be set to one of three styles:
138 ISO-8601, SQL (traditional Oracle/Ingres), and traditional
139 Postgres (see section on
140 .IR "absolute time" )
141 with the SQL style having European and US variants (see
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.
150 General-use date and time is input using a wide range of
151 styles, including ISO-compatible, SQL-compatible, traditional
152 Postgres (see section on
154 and other permutations of date and time. Output styles can be ISO-compatible,
155 SQL-compatible, or traditional Postgres, with the default set to be compatible
158 datetime is specified using the following syntax:
161 Year-Month-Day [ Hour : Minute : Second ] [AD,BC] [ Timezone ]
163 YearMonthDay [ Hour : Minute : Second ] [AD,BC] [ Timezone ]
165 Month Day [ Hour : Minute : Second ] Year [AD,BC] [ Timezone ]
168 Year is 4013 BC, ..., very large
169 Month is Jan, Feb, ..., Dec or 1, 2, ..., 12
171 Hour is 00, 02, ..., 23
172 Minute is 00, 01, ..., 59
173 Second is 00, 01, ..., 59 (60 for leap second)
174 Timezone is 3 characters or ISO offset to GMT
177 Valid dates are from Nov 13 00:00:00 4013 BC GMT to far into the future.
178 Timezones are either three characters (e.g. "GMT" or "PST") or ISO-compatible
179 offsets to GMT (e.g. "-08" or "-08:00" when in Pacific Standard Time).
180 Dates are stored internally in Greenwich Mean Time. Input and output routines
181 translate time to the local time zone of the server.
183 The special values `current',
184 `infinity' and `-infinity' are provided.
185 `infinity' specifies a time later than any valid time, and
186 `-infinity' specifies a time earlier than any valid time.
187 `current' indicates that the current time should be
188 substituted whenever this value appears in a computation.
195 and `epoch' can be used to specify
196 time values. `now' means the current time, and differs from
197 `current' in that the current time is immediately substituted
198 for it. `epoch' means Jan 1 00:00:00 1970 GMT.
201 General-use time span is input using a wide range of
202 syntaxes, including ISO-compatible, SQL-compatible, traditional
203 Postgres (see section on
205 ) and other permutations of time span. Output formats can be ISO-compatible,
206 SQL-compatible, or traditional Postgres, with the default set to be Postgres-compatible.
207 Months and years are a "qualitative" time interval, and are stored separately
208 from the other "quantitative" time intervals such as day or hour. For date arithmetic,
209 the qualitative time units are instantiated in the context of the relevant date or time.
211 Time span is specified with the following syntax:
214 Quantity Unit [Quantity Unit...] [Direction]
216 @ Quantity Unit [Direction]
219 Quantity is ..., `-1', `0', `1', `2', ...
220 Unit is `second', `minute', `hour', `day', `week', `month', `year',
221 or abbreviations or plurals of these units.
225 Absolute time (abstime) is a limited-range (+/- 68 years) and limited-precision (1 sec)
228 may be preferred, since it
229 covers a larger range with greater precision.
231 Absolute time is specified using the following syntax:
234 Month Day [ Hour : Minute : Second ] Year [ Timezone ]
237 Month is Jan, Feb, ..., Dec
239 Hour is 01, 02, ..., 24
240 Minute is 00, 01, ..., 59
241 Second is 00, 01, ..., 59
242 Year is 1901, 1902, ..., 2038
245 Valid dates are from Dec 13 20:45:53 1901 GMT to Jan 19 03:14:04
246 2038 GMT. As of Version 3.0, times are no longer read and written
247 using Greenwich Mean Time; the input and output routines default to
250 All special values allowed for
256 Relative time (reltime) is a limited-range (+/- 68 years) and limited-precision (1 sec)
259 may be preferred, since it
260 covers a larger range with greater precision, allows multiple units
261 for an entry, and correctly handles qualitative time
262 units such as year and month. For reltime, only one quantity and unit is allowed
263 per entry, which can be inconvenient for complicated time spans.
265 Relative time is specified with the following syntax:
268 @ Quantity Unit [Direction]
271 Quantity is `1', `2', ...
272 Unit is ``second'', ``minute'', ``hour'', ``day'', ``week'',
273 ``month'' (30-days), or ``year'' (365-days),
274 or PLURAL of these units.
279 Valid relative times are less than or equal to 68 years.)
280 In addition, the special relative time \*(lqUndefined RelTime\*(rq is
284 This is currently a limited-range absolute time which closely resembles the
286 data type. It shares the general input parser with the other date/time types.
287 In future releases this type will absorb the capabilities of the datetime type
288 and will move toward SQL92 compliance.
291 timestamp is specified using the same syntax as for datetime.
294 Time ranges are specified as:
297 [ 'abstime' 'abstime']
301 is a time in the absolute time format. Special abstime values such as
302 \*(lqcurrent\*(rq, \*(lqinfinity\*(rq and \*(lq-infinity\*(rq can be used.
304 .SH "Syntax of geometric types"
306 Points are specified using the following syntax:
314 x is the x-axis coordinate as a floating point number
315 y is the y-axis coordinate as a floating point number
319 Line segments are represented by pairs of points.
321 lseg is specified using the following syntax:
324 ( ( x1 , y1 ) , ( x2 , y2 ) )
326 ( x1 , y1 ) , ( x2 , y2 )
331 (x1,y1) and (x2,y2) are the endpoints of the segment
335 Boxes are represented by pairs of points which are opposite
338 box is specified using the following syntax:
341 ( ( x1 , y1 ) , ( x2 , y2 ) )
343 ( x1 , y1 ) , ( x2 , y2 )
348 (x1,y1) and (x2,y2) are opposite corners
351 Boxes are output using the first syntax.
352 The corners are reordered on input to store
353 the lower left corner first and the upper right corner last.
354 Other corners of the box can be entered, but the lower
355 left and upper right corners are determined from the input and stored.
357 Paths are represented by sets of points. Paths can be "open", where
358 the first and last points in the set are not connected, and "closed",
359 where the first and last point are connected. Functions
363 are supplied to force a path to be open or closed, and functions
367 are supplied to select either type in a query.
369 path is specified using the following syntax:
372 ( ( x1 , y1 ) , ... , ( xn , yn ) )
374 [ ( x1 , y1 ) , ... , ( xn , yn ) ]
376 ( x1 , y1 ) , ... , ( xn , yn )
378 ( x1 , y1 , ... , xn , yn )
380 x1 , y1 , ... , xn , yn
383 (x1,y1),...,(xn,yn) are points 1 through n
384 a leading "[" indicates an open path
385 a leading "(" indicates a closed path
388 Paths are output using the first syntax.
389 Note that Postgres versions prior to
390 v6.1 used a format for paths which had a single leading parenthesis, a "closed" flag,
391 an integer count of the number of points, then the list of points followed by a
392 closing parenthesis. The built-in function upgradepath() is supplied to convert
393 paths dumped and reloaded from pre-v6.1 databases.
396 Polygons are represented by sets of points. Polygons should probably be
398 equivalent to closed paths, but are stored differently and have their own
399 set of support routines.
401 polygon is specified using the following syntax:
404 ( ( x1 , y1 ) , ... , ( xn , yn ) )
406 ( x1 , y1 ) , ... , ( xn , yn )
408 ( x1 , y1 , ... , xn , yn )
410 x1 , y1 , ... , xn , yn
413 (x1,y1),...,(xn,yn) are points 1 through n
416 Polygons are output using the first syntax.
417 The last format is supplied to be backward compatible with v6.0 and earlier
418 path formats and will not be supported in future versions of Postgres.
419 a single leading "(" indicates a v6.0-compatible format
420 ( x1 , ... , xn , y1 , ... , yn )
421 Note that Postgres versions prior to
422 v6.1 used a format for polygons which had a single leading parenthesis, the list
423 of x-axis coordinates, the list of y-axis coordinates, followed by a closing parenthesis.
424 The built-in function upgradepoly() is supplied to convert
425 polygons dumped and reloaded from pre-v6.1 databases.
428 Circles are represented by a center point and a radius.
430 circle is specified using the following syntax:
442 (x,y) is the center of the circle
443 r is the radius of the circle
446 Circles are output using the first syntax.
448 .SH "Built-in operators and functions"
450 Postgres provides a large number of built-in operators on system types.
451 These operators are declared in the system catalog
452 \*(lqpg_operator\*(rq. Every entry in \*(lqpg_operator\*(rq includes
453 the object ID of the procedure that implements the operator.
455 Users may invoke operators using the operator name, as in
457 select * from emp where salary < 40000;
459 Alternatively, users may call the functions that implement the
460 operators directly. In this case, the query above would be expressed
463 select * from emp where int4lt(salary, 40000);
465 The rest of this section provides a list of the built-in operators and
466 the functions that implement them. Binary operators are listed first,
467 followed by unary operators.
469 .SH "BINARY OPERATORS"
478 <\(eq greater or equal
479 >\(eq greater or equal
482 ~ A matches regular expression B, case-sensitive
483 !~ A does not match regular expression B, case-sensitive
484 ~* A matches regular expression B, case-insensitive.
485 !~* A does not match regular expression B, case-insensitive
486 ~~ A matches LIKE expression B, case-sensitive
487 !~~ A does not match LIKE expression B, case-sensitive
497 @ A contained by (inside or on) B
498 ~ A contains (around or on) B
500 <-> distance between A and B
502 &< A overlaps B, but does not extend to right of B
503 &> A overlaps B, but does not extend to left of B
511 % truncate to integer
514 : exponential function
515 ; natural logarithm (in psql, protect with parentheses)
522 ~\(eq A same as B (equality)
523 @ point inside (or on) path, box, circle, polygon
527 &< box A overlaps box B, but does not extend to right of box B
528 &> box A overlaps box B, but does not extend to left of box B
535 <\(eq area less or equal
536 >\(eq area greater or equal
538 ~\(eq A same as B (equality)
539 @ A is contained in B
545 &< A overlaps B but does not extend to right of B
546 &> A overlaps B but does not extend to left of B
549 ~\(eq A same as B (equality)
550 @ A is contained by B
555 &< A overlaps B but does not extend to right of B
556 &> A overlaps B but does not extend to left of B
561 ~\(eq A same as B (equality)
562 @ A is contained by B
566 #<\(eq interval length less or equal reltime
567 #<> interval length not equal to reltime.
568 #< interval length less than reltime
569 #\(eq interval length equal to reltime
570 #>\(eq interval length greater or equal reltime
571 #> interval length greater than reltime
575 <> interval bounded by two abstimes
576 <?> abstime in tinterval
578 <#> convert to interval
582 Many data types have functions available for conversion to other related types.
583 In addition, there are some type-specific functions. Functions which are also
584 available through operators are documented as operators only.
587 Some functions defined for text are also available for char() and varchar().
590 date_part() and date_trunc()
591 functions, arguments can be
592 `year', `month', `day', `hour', `minute', and `second',
593 as well as the more specialized quantities
594 `decade', `century', `millenium', `millisecond', and `microsecond'.
595 date_part() allows `dow'
596 to return day of week and `epoch' to return seconds since 1970 for datetime
597 and 'epoch' to return total elapsed seconds for timespan.
603 float8 float(int) convert integer to floating point
604 float4 float4(int) convert integer to floating point
607 int integer(float) convert floating point to integer
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
620 bool isfinite(abstime) TRUE if this is a finite time
621 datetime datetime(abstime) convert to datetime
624 datetime datetime(date) convert to datetime
625 datetime datetime(date,time) convert to datetime
628 timespan age(datetime,datetime) date difference preserving months and years
629 float8 date_part(text,datetime) specified portion of date field
630 datetime date_trunc(text,datetime) truncate date at specified units
631 bool isfinite(datetime) TRUE if this is a finite time
632 abstime abstime(datetime) convert to abstime
635 timespan timespan(reltime) convert to timespan
638 datetime datetime(date,time) convert to datetime
641 float8 date_part(text,timespan) specified portion of time field
642 bool isfinite(timespan) TRUE if this is a finite time
643 reltime reltime(timespan) convert to reltime
646 box box(point,point) convert points to box
647 float8 area(box) area of box
650 bool isopen(path) TRUE if this is an open path
651 bool isclosed(path) TRUE if this is a closed path
654 circle circle(point,float8) convert to circle
655 polygon polygon(npts,circle) convert to polygon with npts points
656 float8 center(circle) radius of circle
657 float8 radius(circle) radius of circle
658 float8 diameter(circle) diameter of circle
659 float8 area(circle) area of circle
663 SQL/92 defines functions with specific syntax. Some of these
664 are implemented using other Postgres functions.
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
678 .SH "BINARY OPERATORS"
679 This list was generated from the Postgres system catalogs with the
683 SELECT t0.typname AS result,
684 t1.typname AS left_type,
685 t2.typname AS right_type,
686 o.oprname AS operatr,
687 p.proname AS func_name
688 FROM pg_proc p, pg_type t0,
689 pg_type t1, pg_type t2,
691 WHERE p.prorettype = t0.oid AND
692 RegprocToOid(o.oprcode) = p.oid AND
694 o.oprleft = t1.oid AND
696 ORDER BY result, left_type, right_type, operatr;
699 These operations are cast in terms of SQL types and so are
701 directly usable as C function prototypes.
705 result |left_type |right_type|operatr|func_name
706 ---------+----------+----------+-------+-------------------
707 _aclitem |_aclitem |aclitem |+ |aclinsert
708 _aclitem |_aclitem |aclitem |- |aclremove
709 abstime |abstime |reltime |+ |timepl
710 abstime |abstime |reltime |- |timemi
711 bool |_abstime |_abstime |= |array_eq
712 bool |_aclitem |_aclitem |= |array_eq
713 bool |_aclitem |aclitem |~ |aclcontains
714 bool |_bool |_bool |= |array_eq
715 bool |_box |_box |= |array_eq
716 bool |_bytea |_bytea |= |array_eq
717 bool |_char |_char |= |array_eq
718 bool |_char16 |_char16 |= |array_eq
719 bool |_cid |_cid |= |array_eq
720 bool |_filename |_filename |= |array_eq
721 bool |_float4 |_float4 |= |array_eq
722 bool |_float8 |_float8 |= |array_eq
723 bool |_int2 |_int2 |= |array_eq
724 bool |_int28 |_int28 |= |array_eq
725 bool |_int4 |_int4 |= |array_eq
726 bool |_lseg |_lseg |= |array_eq
727 bool |_name |_name |= |array_eq
728 bool |_oid |_oid |= |array_eq
729 bool |_oid8 |_oid8 |= |array_eq
730 bool |_path |_path |= |array_eq
731 bool |_point |_point |= |array_eq
732 bool |_polygon |_polygon |= |array_eq
733 bool |_ref |_ref |= |array_eq
734 bool |_regproc |_regproc |= |array_eq
735 bool |_reltime |_reltime |= |array_eq
736 bool |_stub |_stub |= |array_eq
737 bool |_text |_text |= |array_eq
738 bool |_tid |_tid |= |array_eq
739 bool |_tinterval|_tinterval|= |array_eq
740 bool |_xid |_xid |= |array_eq
741 bool |abstime |abstime |< |abstimelt
742 bool |abstime |abstime |<= |abstimele
743 bool |abstime |abstime |<> |abstimene
744 bool |abstime |abstime |= |abstimeeq
745 bool |abstime |abstime |> |abstimegt
746 bool |abstime |abstime |>= |abstimege
747 bool |abstime |tinterval |<?> |ininterval
748 bool |bool |bool |< |boollt
749 bool |bool |bool |<> |boolne
750 bool |bool |bool |= |booleq
751 bool |bool |bool |> |boolgt
752 bool |box |box |&& |box_overlap
753 bool |box |box |&< |box_overleft
754 bool |box |box |&> |box_overright
755 bool |box |box |< |box_lt
756 bool |box |box |<< |box_left
757 bool |box |box |<= |box_le
758 bool |box |box |<^ |box_below
759 bool |box |box |= |box_eq
760 bool |box |box |> |box_gt
761 bool |box |box |>= |box_ge
762 bool |box |box |>> |box_right
763 bool |box |box |>^ |box_above
764 bool |box |box |?# |box_overlap
765 bool |box |box |@ |box_contained
766 bool |box |box |~ |box_contain
767 bool |box |box |~= |box_same
768 bool |bpchar |bpchar |< |bpcharlt
769 bool |bpchar |bpchar |<= |bpcharle
770 bool |bpchar |bpchar |<> |bpcharne
771 bool |bpchar |bpchar |= |bpchareq
772 bool |bpchar |bpchar |> |bpchargt
773 bool |bpchar |bpchar |>= |bpcharge
774 bool |bpchar |text |!~ |textregexne
775 bool |bpchar |text |!~* |texticregexne
776 bool |bpchar |text |!~~ |textnlike
777 bool |bpchar |text |~ |textregexeq
778 bool |bpchar |text |~* |texticregexeq
779 bool |bpchar |text |~~ |textlike
780 bool |char |char |< |charlt
781 bool |char |char |<= |charle
782 bool |char |char |<> |charne
783 bool |char |char |= |chareq
784 bool |char |char |> |chargt
785 bool |char |char |>= |charge
786 bool |char16 |char16 |< |char16lt
787 bool |char16 |char16 |<= |char16le
788 bool |char16 |char16 |<> |char16ne
789 bool |char16 |char16 |= |char16eq
790 bool |char16 |char16 |> |char16gt
791 bool |char16 |char16 |>= |char16ge
792 bool |char16 |text |!~ |char16regexne
793 bool |char16 |text |!~* |char16icregexne
794 bool |char16 |text |!~~ |char16nlike
795 bool |char16 |text |~ |char16regexeq
796 bool |char16 |text |~* |char16icregexeq
797 bool |char16 |text |~~ |char16like
798 bool |char2 |char2 |< |char2lt
799 bool |char2 |char2 |<= |char2le
800 bool |char2 |char2 |<> |char2ne
801 bool |char2 |char2 |= |char2eq
802 bool |char2 |char2 |> |char2gt
803 bool |char2 |char2 |>= |char2ge
804 bool |char2 |text |!~ |char2regexne
805 bool |char2 |text |!~* |char2icregexne
806 bool |char2 |text |!~~ |char2nlike
807 bool |char2 |text |~ |char2regexeq
808 bool |char2 |text |~* |char2icregexeq
809 bool |char2 |text |~~ |char2like
810 bool |char4 |char4 |< |char4lt
811 bool |char4 |char4 |<= |char4le
812 bool |char4 |char4 |<> |char4ne
813 bool |char4 |char4 |= |char4eq
814 bool |char4 |char4 |> |char4gt
815 bool |char4 |char4 |>= |char4ge
816 bool |char4 |text |!~ |char4regexne
817 bool |char4 |text |!~* |char4icregexne
818 bool |char4 |text |!~~ |char4nlike
819 bool |char4 |text |~ |char4regexeq
820 bool |char4 |text |~* |char4icregexeq
821 bool |char4 |text |~~ |char4like
822 bool |char8 |char8 |< |char8lt
823 bool |char8 |char8 |<= |char8le
824 bool |char8 |char8 |<> |char8ne
825 bool |char8 |char8 |= |char8eq
826 bool |char8 |char8 |> |char8gt
827 bool |char8 |char8 |>= |char8ge
828 bool |char8 |text |!~ |char8regexne
829 bool |char8 |text |!~* |char8icregexne
830 bool |char8 |text |!~~ |char8nlike
831 bool |char8 |text |~ |char8regexeq
832 bool |char8 |text |~* |char8icregexeq
833 bool |char8 |text |~~ |char8like
834 bool |circle |circle |&& |circle_overlap
835 bool |circle |circle |&< |circle_overleft
836 bool |circle |circle |&> |circle_overright
837 bool |circle |circle |< |circle_lt
838 bool |circle |circle |<< |circle_left
839 bool |circle |circle |<= |circle_le
840 bool |circle |circle |<> |circle_ne
841 bool |circle |circle |<^ |circle_below
842 bool |circle |circle |= |circle_eq
843 bool |circle |circle |> |circle_gt
844 bool |circle |circle |>= |circle_ge
845 bool |circle |circle |>> |circle_right
846 bool |circle |circle |>^ |circle_above
847 bool |circle |circle |@ |circle_contained
848 bool |circle |circle |~ |circle_contain
849 bool |circle |circle |~= |circle_same
850 bool |circle |point |~ |circle_contain_pt
851 bool |date |date |< |date_lt
852 bool |date |date |<= |date_le
853 bool |date |date |<> |date_ne
854 bool |date |date |= |date_eq
855 bool |date |date |> |date_gt
856 bool |date |date |>= |date_ge
857 bool |datetime |datetime |< |datetime_lt
858 bool |datetime |datetime |<= |datetime_le
859 bool |datetime |datetime |<> |datetime_ne
860 bool |datetime |datetime |= |datetime_eq
861 bool |datetime |datetime |> |datetime_gt
862 bool |datetime |datetime |>= |datetime_ge
863 bool |float4 |float4 |< |float4lt
864 bool |float4 |float4 |<= |float4le
865 bool |float4 |float4 |<> |float4ne
866 bool |float4 |float4 |= |float4eq
867 bool |float4 |float4 |> |float4gt
868 bool |float4 |float4 |>= |float4ge
869 bool |float4 |float8 |< |float48lt
870 bool |float4 |float8 |<= |float48le
871 bool |float4 |float8 |<> |float48ne
872 bool |float4 |float8 |= |float48eq
873 bool |float4 |float8 |> |float48gt
874 bool |float4 |float8 |>= |float48ge
875 bool |float8 |float4 |< |float84lt
876 bool |float8 |float4 |<= |float84le
877 bool |float8 |float4 |<> |float84ne
878 bool |float8 |float4 |= |float84eq
879 bool |float8 |float4 |> |float84gt
880 bool |float8 |float4 |>= |float84ge
881 bool |float8 |float8 |< |float8lt
882 bool |float8 |float8 |<= |float8le
883 bool |float8 |float8 |<> |float8ne
884 bool |float8 |float8 |= |float8eq
885 bool |float8 |float8 |> |float8gt
886 bool |float8 |float8 |>= |float8ge
887 bool |int2 |int2 |< |int2lt
888 bool |int2 |int2 |<= |int2le
889 bool |int2 |int2 |<> |int2ne
890 bool |int2 |int2 |= |int2eq
891 bool |int2 |int2 |> |int2gt
892 bool |int2 |int2 |>= |int2ge
893 bool |int4 |int4 |< |int4lt
894 bool |int4 |int4 |<= |int4le
895 bool |int4 |int4 |<> |int4ne
896 bool |int4 |int4 |= |int4eq
897 bool |int4 |int4 |> |int4gt
898 bool |int4 |int4 |>= |int4ge
899 bool |int4 |name |!!= |int4notin
900 bool |int4 |oid |= |int4eqoid
901 bool |line |box |?# |inter_lb
902 bool |lseg |box |?# |inter_sb
903 bool |lseg |box |@ |on_sb
904 bool |lseg |line |?# |inter_sl
905 bool |lseg |line |@ |on_sl
906 bool |lseg |lseg |= |lseg_eq
907 bool |lseg |lseg |?# |lseg_intersect
908 bool |lseg |lseg |?-| |lseg_perp
909 bool |lseg |lseg |?|| |lseg_parallel
910 bool |money |money |< |cash_lt
911 bool |money |money |<= |cash_le
912 bool |money |money |<> |cash_ne
913 bool |money |money |= |cash_eq
914 bool |money |money |> |cash_gt
915 bool |money |money |>= |cash_ge
916 bool |name |name |< |namelt
917 bool |name |name |<= |namele
918 bool |name |name |<> |namene
919 bool |name |name |= |nameeq
920 bool |name |name |> |namegt
921 bool |name |name |>= |namege
922 bool |name |text |!~ |nameregexne
923 bool |name |text |!~* |nameicregexne
924 bool |name |text |!~~ |namenlike
925 bool |name |text |~ |nameregexeq
926 bool |name |text |~* |nameicregexeq
927 bool |name |text |~~ |namelike
928 bool |oid |int4 |= |oideqint4
929 bool |oid |name |!!= |oidnotin
930 bool |oid |oid |< |int4lt
931 bool |oid |oid |<= |int4le
932 bool |oid |oid |<> |oidne
933 bool |oid |oid |= |oideq
934 bool |oid |oid |> |int4gt
935 bool |oid |oid |>= |int4ge
936 bool |oidint2 |oidint2 |< |oidint2lt
937 bool |oidint2 |oidint2 |<= |oidint2le
938 bool |oidint2 |oidint2 |<> |oidint2ne
939 bool |oidint2 |oidint2 |= |oidint2eq
940 bool |oidint2 |oidint2 |> |oidint2gt
941 bool |oidint2 |oidint2 |>= |oidint2ge
942 bool |oidint4 |oidint4 |< |oidint4lt
943 bool |oidint4 |oidint4 |<= |oidint4le
944 bool |oidint4 |oidint4 |<> |oidint4ne
945 bool |oidint4 |oidint4 |= |oidint4eq
946 bool |oidint4 |oidint4 |> |oidint4gt
947 bool |oidint4 |oidint4 |>= |oidint4ge
948 bool |oidname |oidname |< |oidnamelt
949 bool |oidname |oidname |<= |oidnamele
950 bool |oidname |oidname |<> |oidnamene
951 bool |oidname |oidname |= |oidnameeq
952 bool |oidname |oidname |> |oidnamegt
953 bool |oidname |oidname |>= |oidnamege
954 bool |path |path |< |path_n_lt
955 bool |path |path |<= |path_n_le
956 bool |path |path |= |path_n_eq
957 bool |path |path |> |path_n_gt
958 bool |path |path |>= |path_n_ge
959 bool |path |path |?# |path_inter
960 bool |path |point |~ |path_contain_pt
961 bool |point |box |@ |on_pb
962 bool |point |circle |@ |pt_contained_circle
963 bool |point |line |@ |on_pl
964 bool |point |lseg |@ |on_ps
965 bool |point |path |@ |on_ppath
966 bool |point |path |@ |pt_contained_path
967 bool |point |point |<< |point_left
968 bool |point |point |<^ |point_below
969 bool |point |point |>> |point_right
970 bool |point |point |>^ |point_above
971 bool |point |point |?- |point_horiz
972 bool |point |point |?| |point_vert
973 bool |point |point |~= |point_eq
974 bool |point |polygon |@ |pt_contained_poly
975 bool |polygon |point |~ |poly_contain_pt
976 bool |polygon |polygon |&& |poly_overlap
977 bool |polygon |polygon |&< |poly_overleft
978 bool |polygon |polygon |&> |poly_overright
979 bool |polygon |polygon |<< |poly_left
980 bool |polygon |polygon |>> |poly_right
981 bool |polygon |polygon |@ |poly_contained
982 bool |polygon |polygon |~ |poly_contain
983 bool |polygon |polygon |~= |poly_same
984 bool |reltime |reltime |< |reltimelt
985 bool |reltime |reltime |<= |reltimele
986 bool |reltime |reltime |<> |reltimene
987 bool |reltime |reltime |= |reltimeeq
988 bool |reltime |reltime |> |reltimegt
989 bool |reltime |reltime |>= |reltimege
990 bool |text |text |!~ |textregexne
991 bool |text |text |!~* |texticregexne
992 bool |text |text |!~~ |textnlike
993 bool |text |text |< |text_lt
994 bool |text |text |<= |text_le
995 bool |text |text |<> |textne
996 bool |text |text |= |texteq
997 bool |text |text |> |text_gt
998 bool |text |text |>= |text_ge
999 bool |text |text |~ |textregexeq
1000 bool |text |text |~* |texticregexeq
1001 bool |text |text |~~ |textlike
1002 bool |time |time |< |time_lt
1003 bool |time |time |<= |time_le
1004 bool |time |time |<> |time_ne
1005 bool |time |time |= |time_eq
1006 bool |time |time |> |time_gt
1007 bool |time |time |>= |time_ge
1008 bool |timespan |timespan |< |timespan_lt
1009 bool |timespan |timespan |<= |timespan_le
1010 bool |timespan |timespan |<> |timespan_ne
1011 bool |timespan |timespan |= |timespan_eq
1012 bool |timespan |timespan |> |timespan_gt
1013 bool |timespan |timespan |>= |timespan_ge
1014 bool |timestamp |timestamp |< |timestamplt
1015 bool |timestamp |timestamp |<= |timestample
1016 bool |timestamp |timestamp |<> |timestampne
1017 bool |timestamp |timestamp |= |timestampeq
1018 bool |timestamp |timestamp |> |timestampgt
1019 bool |timestamp |timestamp |>= |timestampge
1020 bool |tinterval |reltime |#< |intervallenlt
1021 bool |tinterval |reltime |#<= |intervallenle
1022 bool |tinterval |reltime |#<> |intervallenne
1023 bool |tinterval |reltime |#= |intervalleneq
1024 bool |tinterval |reltime |#> |intervallengt
1025 bool |tinterval |reltime |#>= |intervallenge
1026 bool |tinterval |tinterval |&& |intervalov
1027 bool |tinterval |tinterval |< |intervalct
1028 bool |tinterval |tinterval |< |intervallt
1029 bool |tinterval |tinterval |<< |intervalct
1030 bool |tinterval |tinterval |<= |intervalle
1031 bool |tinterval |tinterval |<> |intervalne
1032 bool |tinterval |tinterval |= |intervaleq
1033 bool |tinterval |tinterval |> |intervalgt
1034 bool |tinterval |tinterval |>= |intervalge
1035 bool |tinterval |tinterval |~= |intervalsame
1036 bool |varchar |text |!~ |textregexne
1037 bool |varchar |text |!~* |texticregexne
1038 bool |varchar |text |!~~ |textnlike
1039 bool |varchar |text |~ |textregexeq
1040 bool |varchar |text |~* |texticregexeq
1041 bool |varchar |text |~~ |textlike
1042 bool |varchar |varchar |< |varcharlt
1043 bool |varchar |varchar |<= |varcharle
1044 bool |varchar |varchar |<> |varcharne
1045 bool |varchar |varchar |= |varchareq
1046 bool |varchar |varchar |> |varchargt
1047 bool |varchar |varchar |>= |varcharge
1048 box |box |box |# |box_intersect
1049 box |box |point |* |box_mul
1050 box |box |point |+ |box_add
1051 box |box |point |- |box_sub
1052 box |box |point |/ |box_div
1053 char |char |char |* |charmul
1054 char |char |char |+ |charpl
1055 char |char |char |- |charmi
1056 char |char |char |/ |chardiv
1057 circle |circle |point |* |circle_mul_pt
1058 circle |circle |point |+ |circle_add_pt
1059 circle |circle |point |- |circle_sub_pt
1060 circle |circle |point |/ |circle_div_pt
1061 date |date |int4 |+ |date_pli
1062 date |date |int4 |- |date_mii
1063 datetime |datetime |timespan |+ |datetime_pl_span
1064 datetime |datetime |timespan |- |datetime_mi_span
1065 float4 |float4 |float4 |* |float4mul
1066 float4 |float4 |float4 |+ |float4pl
1067 float4 |float4 |float4 |- |float4mi
1068 float4 |float4 |float4 |/ |float4div
1069 float8 |box |box |<-> |box_distance
1070 float8 |circle |circle |<-> |circle_distance
1071 float8 |circle |polygon |<-> |dist_cpoly
1072 float8 |float4 |float8 |* |float48mul
1073 float8 |float4 |float8 |+ |float48pl
1074 float8 |float4 |float8 |- |float48mi
1075 float8 |float4 |float8 |/ |float48div
1076 float8 |float8 |float4 |* |float84mul
1077 float8 |float8 |float4 |+ |float84pl
1078 float8 |float8 |float4 |- |float84mi
1079 float8 |float8 |float4 |/ |float84div
1080 float8 |float8 |float8 |* |float8mul
1081 float8 |float8 |float8 |+ |float8pl
1082 float8 |float8 |float8 |- |float8mi
1083 float8 |float8 |float8 |/ |float8div
1084 float8 |float8 |float8 |^ |dpow
1085 float8 |line |box |<-> |dist_lb
1086 float8 |line |line |<-> |line_distance
1087 float8 |lseg |box |<-> |dist_sb
1088 float8 |lseg |line |<-> |dist_sl
1089 float8 |lseg |lseg |<-> |lseg_distance
1090 float8 |path |path |<-> |path_distance
1091 float8 |point |box |<-> |dist_pb
1092 float8 |point |box |<-> |dist_pl
1093 float8 |point |lseg |<-> |dist_ps
1094 float8 |point |path |<-> |dist_ppath
1095 float8 |point |point |<-> |point_distance
1096 float8 |polygon |polygon |<-> |poly_distance
1097 int2 |int2 |int2 |% |int2mod
1098 int2 |int2 |int2 |* |int2mul
1099 int2 |int2 |int2 |+ |int2pl
1100 int2 |int2 |int2 |- |int2mi
1101 int2 |int2 |int2 |/ |int2div
1102 int4 |date |date |- |date_mi
1103 int4 |int2 |int4 |% |int24mod
1104 int4 |int2 |int4 |* |int24mul
1105 int4 |int2 |int4 |+ |int24pl
1106 int4 |int2 |int4 |- |int24mi
1107 int4 |int2 |int4 |/ |int24div
1108 int4 |int2 |int4 |< |int24lt
1109 int4 |int2 |int4 |<= |int24le
1110 int4 |int2 |int4 |<> |int24ne
1111 int4 |int2 |int4 |= |int24eq
1112 int4 |int2 |int4 |> |int24gt
1113 int4 |int2 |int4 |>= |int24ge
1114 int4 |int4 |int2 |% |int42mod
1115 int4 |int4 |int2 |* |int42mul
1116 int4 |int4 |int2 |+ |int42pl
1117 int4 |int4 |int2 |- |int42mi
1118 int4 |int4 |int2 |/ |int42div
1119 int4 |int4 |int2 |< |int42lt
1120 int4 |int4 |int2 |<= |int42le
1121 int4 |int4 |int2 |<> |int42ne
1122 int4 |int4 |int2 |= |int42eq
1123 int4 |int4 |int2 |> |int42gt
1124 int4 |int4 |int2 |>= |int42ge
1125 int4 |int4 |int4 |% |int4mod
1126 int4 |int4 |int4 |* |int4mul
1127 int4 |int4 |int4 |+ |int4pl
1128 int4 |int4 |int4 |- |int4mi
1129 int4 |int4 |int4 |/ |int4div
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
1142 money |money |money |+ |cash_pl
1143 money |money |money |- |cash_mi
1144 path |path |path |+ |path_add
1145 path |path |point |* |path_mul_pt
1146 path |path |point |+ |path_add_pt
1147 path |path |point |- |path_sub_pt
1148 path |path |point |/ |path_div_pt
1149 point |line |box |## |close_lb
1150 point |lseg |box |## |close_sb
1151 point |lseg |line |## |close_sl
1152 point |lseg |lseg |# |lseg_interpt
1153 point |point |box |## |close_pb
1154 point |point |line |## |close_pl
1155 point |point |lseg |## |close_ps
1156 point |point |point |* |point_mul
1157 point |point |point |+ |point_add
1158 point |point |point |- |point_sub
1159 point |point |point |/ |point_div
1160 polygon |point |circle |<-> |dist_pc
1161 text |bpchar |bpchar ||| |textcat
1162 text |text |text ||| |textcat
1163 text |varchar |varchar ||| |textcat
1164 timespan |datetime |datetime |- |datetime_mi
1165 timespan |timespan |timespan |+ |timespan_pl
1166 timespan |timespan |timespan |- |timespan_mi
1167 timespan |timespan |timespan |/ |timespan_div
1168 tinterval|abstime |abstime |<#> |mktinterval
1173 .SH "LEFT UNARY OPERATORS"
1174 The table below gives the left unary operators that are
1175 registered in the system catalogs.
1177 This list was generated from the Postgres system catalogs with the query:
1181 SELECT o.oprname AS left_unary,
1182 t.typname AS operand,
1183 r.typname AS return_type
1184 FROM pg_operator o, pg_type t, pg_type r
1185 WHERE o.oprkind = 'l' AND -- left unary
1186 o.oprright = t.oid AND
1190 left_unary|operand |return_type
1191 ----------+---------+-----------
1214 - |timespan |timespan
1215 | |tinterval|abstime
1221 .SH "RIGHT UNARY OPERATORS"
1222 The table below gives the right unary operators that are
1223 registered in the system catalogs.
1225 This list was generated from the Postgres system catalogs with the query:
1229 SELECT o.oprname AS right_unary,
1230 t.typname AS operand,
1231 r.typname AS return_type
1232 FROM pg_operator o, pg_type t, pg_type r
1233 WHERE o.oprkind = 'r' AND -- right unary
1234 o.oprleft = t.oid AND
1238 right_unary|operand|return_type
1239 -----------+-------+-----------
1247 .SH "AGGREGATE FUNCTIONS"
1248 The table below gives the aggregate functions that are
1249 registered in the system catalogs.
1251 This list was generated from the Postgres system catalogs with the query:
1255 SELECT a.aggname AS aggname,
1256 t.typname AS typname
1257 FROM pg_aggregate a, pg_type t
1258 WHERE a.aggbasetype = t.oid
1259 ORDER BY aggname, typname;
1297 \fBcount\fR is also available, where \fBcount(*)\fR returns a count of all
1298 rows while \fBcount(column_name)\fR returns a count of all non-null fields
1299 in the specified column.
1306 For examples on specifying literals of built-in types, see
1310 Although most of the input and output functions corresponding to the
1311 base types (e.g., integers and floating point numbers) do some
1312 error-checking, some are not particularly rigorous about it. More
1313 importantly, few of the operators and functions (e.g.,
1314 addition and multiplication) perform any error-checking at all.
1315 Consequently, many of the numeric operators can (for example)
1316 silently underflow or overflow.
1318 Some of the input and output functions are not invertible. That is,
1319 the result of an output function may lose precision when compared to