2 <Title>Data Types</Title>
6 Describes the built-in data types available in <ProductName>Postgres</ProductName>.
11 <ProductName>Postgres</ProductName> has a rich set of native data types available to users.
12 Users may add new types to <ProductName>Postgres</ProductName> using the
13 <Command>define type</Command>
14 command described elsewhere.
17 In the context of data types, the following sections will discuss SQL standards
18 compliance, porting issues, and usage.
20 Some <ProductName>Postgres</ProductName> types correspond directly to SQL92-compatible types. In other
21 cases, data types defined by SQL92 syntax are mapped directly
22 into native <ProductName>Postgres</ProductName> types.
24 Many of the built-in types have obvious external formats. However, several
25 types are either unique to <ProductName>Postgres</ProductName>, such as open and closed paths, or have
26 several possibilities for formats, such as date and time types.
31 <TITLE><ProductName>Postgres</ProductName> Data Types</TITLE>
32 <TITLEABBREV>Data Types</TITLEABBREV>
36 <ENTRY><ProductName>Postgres</ProductName> Type</ENTRY>
37 <ENTRY><Acronym>SQL92</Acronym> or <Acronym>SQL3</Acronym> Type</ENTRY>
38 <ENTRY>Description</ENTRY>
44 <ENTRY>boolean</ENTRY>
45 <ENTRY>logical boolean (true/false)</ENTRY>
50 <ENTRY>rectangular box in 2D plane</ENTRY>
53 <ENTRY>char(n)</ENTRY>
54 <ENTRY>character(n)</ENTRY>
55 <ENTRY>fixed-length character string</ENTRY>
60 <ENTRY>circle in 2D plane</ENTRY>
65 <ENTRY>calendar date without time of day</ENTRY>
68 <ENTRY>float4/8</ENTRY>
69 <ENTRY>float(p)</ENTRY>
70 <ENTRY>floating-point number with precision p</ENTRY>
74 <ENTRY>real, double precision</ENTRY>
75 <ENTRY>double-precision floating-point number</ENTRY>
79 <ENTRY>smallint</ENTRY>
80 <ENTRY>signed two-byte integer</ENTRY>
84 <ENTRY>int, integer</ENTRY>
85 <ENTRY>signed 4-byte integer</ENTRY>
89 <ENTRY>decimal(p,s)</ENTRY>
90 <ENTRY>exact numeric for p <= 9, s = 0</ENTRY>
94 <ENTRY>numeric(p,s)</ENTRY>
95 <ENTRY>exact numeric for p == 9, s = 0</ENTRY>
100 <ENTRY>infinite line in 2D plane</ENTRY>
105 <ENTRY>line segment in 2D plane</ENTRY>
109 <ENTRY>decimal(9,2)</ENTRY>
110 <ENTRY>US-style currency</ENTRY>
115 <ENTRY>open and closed geometric path in 2D plane</ENTRY>
120 <ENTRY>geometric point in 2D plane</ENTRY>
123 <ENTRY>polygon</ENTRY>
125 <ENTRY>closed geometric path in 2D plane</ENTRY>
130 <ENTRY>time of day</ENTRY>
133 <ENTRY>timespan</ENTRY>
134 <ENTRY>interval</ENTRY>
135 <ENTRY>general-use time span</ENTRY>
138 <ENTRY>timestamp</ENTRY>
139 <ENTRY>timestamp with time zone</ENTRY>
140 <ENTRY>date/time</ENTRY>
143 <ENTRY>varchar(n)</ENTRY>
144 <ENTRY>character varying(n)</ENTRY>
145 <ENTRY>variable-length character string</ENTRY>
154 <TITLE><ProductName>Postgres</ProductName> Function Constants</TITLE>
155 <TITLEABBREV>Constants</TITLEABBREV>
159 <ENTRY><ProductName>Postgres</ProductName> Function</ENTRY>
160 <ENTRY><Acronym>SQL92</Acronym> Constant</ENTRY>
161 <ENTRY>Description</ENTRY>
166 <ENTRY>getpgusername()</ENTRY>
167 <ENTRY>current_user</ENTRY>
168 <ENTRY>user name in current session</ENTRY>
171 <ENTRY>date('now')</ENTRY>
172 <ENTRY>current_date</ENTRY>
173 <ENTRY>date of current transaction</ENTRY>
176 <ENTRY>time('now')</ENTRY>
177 <ENTRY>current_time</ENTRY>
178 <ENTRY>time of current transaction</ENTRY>
181 <ENTRY>timestamp('now')</ENTRY>
182 <ENTRY>current_timestamp</ENTRY>
183 <ENTRY>date and time of current transaction</ENTRY>
191 <ProductName>Postgres</ProductName> has features at the forefront of ORDBMS development. In addition to
192 SQL3 conformance, substantial portions of SQL92 are also supported.
193 Although we strive for SQL92 compliance, there are some cases in the standard
194 which are ill considered and which should not live through subsequent standards.
195 <ProductName>Postgres</ProductName> will not make great efforts to conform to these cases. However, these
196 cases tend to be little-used and obsure, and a typical user is not likely to
200 Although most of the input and output functions corresponding to the
201 base types (e.g., integers and floating point numbers) do some
202 error-checking, some are not particularly rigorous about it. More
203 importantly, few of the operators and functions (e.g.,
204 addition and multiplication) perform any error-checking at all.
205 Consequently, many of the numeric operators can (for example)
206 silently underflow or overflow.
210 Some of the input and output functions are not invertible. That is,
211 the result of an output function may lose precision when compared to
216 <Title>Numeric Types</Title>
219 Numeric types consist of two- and four-byte integers and four- and eight-byte
220 floating point numbers.
224 <TITLE><ProductName>Postgres</ProductName> Numeric Types</TITLE>
225 <TITLEABBREV>Numerics</TITLEABBREV>
229 <ENTRY>Numeric Type</ENTRY>
230 <ENTRY>Storage</ENTRY>
231 <ENTRY>Description</ENTRY>
238 <ENTRY>2 bytes</ENTRY>
239 <ENTRY>Fixed-precision</ENTRY>
240 <ENTRY>-32768 to +32767</ENTRY>
244 <ENTRY>4 bytes</ENTRY>
245 <ENTRY>Usual choice for fixed-precision</ENTRY>
246 <ENTRY>-2147483648 to +2147483647</ENTRY>
249 <ENTRY>float4</ENTRY>
250 <ENTRY>4 bytes</ENTRY>
251 <ENTRY>Variable-precision</ENTRY>
252 <ENTRY>7 decimal places</ENTRY>
255 <ENTRY>float8</ENTRY>
256 <ENTRY>8 bytes</ENTRY>
257 <ENTRY>Variable-precision</ENTRY>
258 <ENTRY>14 decimal places</ENTRY>
266 The <FirstTerm>exact numerics</FirstTerm> <Type>decimal</Type> and <Type>numeric</Type>
267 have fully implemented syntax but currently (<ProductName>Postgres</ProductName> v6.3)
268 support only a small range of precision and/or range values.
274 <Title>Monetary Type</Title>
277 The <Type>money</Type> type supports US-style currency with fixed decimal point representation.
278 If <ProductName>Postgres</ProductName> is compiled with USE_LOCALE then the money type
279 should use the monetary conventions defined for locale(7).
283 <TITLE><ProductName>Postgres</ProductName> Numeric Types</TITLE>
284 <TITLEABBREV>Numerics</TITLEABBREV>
288 <ENTRY>Monetary Type</ENTRY>
289 <ENTRY>Storage</ENTRY>
290 <ENTRY>Description</ENTRY>
297 <ENTRY>4 bytes</ENTRY>
298 <ENTRY>Fixed-precision</ENTRY>
299 <ENTRY>-21474836.48 to +21474836.47</ENTRY>
307 The <FirstTerm>numeric</FirstTerm>
308 should eventually replace the money type. It has a
309 fully implemented syntax but currently (<ProductName>Postgres</ProductName> v6.3)
310 support only a small range of precision and/or range values and cannot substitute for
317 <Title>Character Types</Title>
320 <Acronym>SQL92</Acronym> defines two primary character types: <Type>char</Type> and
321 <Type>varchar</Type>. <ProductName>Postgres</ProductName> supports these types, in
322 addition to the more general <Type>text</Type> type, which unlike <Type>varchar</Type>
323 does not require an upper
324 limit to be declared on the size of the field.
329 <TITLE><ProductName>Postgres</ProductName> Character Types</TITLE>
330 <TITLEABBREV>Characters</TITLEABBREV>
334 <ENTRY>Character Type</ENTRY>
335 <ENTRY>Storage</ENTRY>
336 <ENTRY>Recommendation</ENTRY>
337 <ENTRY>Description</ENTRY>
343 <ENTRY>1 byte</ENTRY>
344 <ENTRY>SQL92-compatible</ENTRY>
345 <ENTRY>Single character</ENTRY>
348 <ENTRY>char(n)</ENTRY>
349 <ENTRY>(4+n) bytes</ENTRY>
350 <ENTRY>SQL92-compatible</ENTRY>
351 <ENTRY>Fixed-length blank padded</ENTRY>
355 <ENTRY>(4+x) bytes</ENTRY>
356 <ENTRY>Best choice</ENTRY>
357 <ENTRY>Variable-length</ENTRY>
360 <ENTRY>varchar(n)</ENTRY>
361 <ENTRY>(4+n) bytes</ENTRY>
362 <ENTRY>SQL92-compatible</ENTRY>
363 <ENTRY>Variable-length with limit</ENTRY>
371 There is currently one other fixed-length character type. The <Type>name</Type> type
372 only has one purpose and that is to provide <ProductName>Postgres</ProductName> with a
373 special type to use for internal names. It is not intended for use by the general user.
374 It's length is currently defined as 32 chars but should be reference using NAMEDATALEN.
375 This is set at compile time and may change in any future release.
380 <TITLE><ProductName>Postgres</ProductName> Specialty Character Type</TITLE>
381 <TITLEABBREV>Specialty Characters</TITLEABBREV>
385 <ENTRY>Character Type</ENTRY>
386 <ENTRY>Storage</ENTRY>
387 <ENTRY>Description</ENTRY>
393 <ENTRY>32 bytes</ENTRY>
394 <ENTRY>Thirty-two character internal type</ENTRY>
404 <Title>Date/Time Types</Title>
407 There are two fundamental kinds of date and time measurements: clock time and time interval.
408 Both quantities have continuity and smoothness, as does time itself.
409 <ProductName>Postgres</ProductName> supplies two primary user-oriented date and time types,
410 <Type>datetime</Type> and timespan, as well as the related SQL92 types date and time.
414 Other date and time types are available
416 for historical reasons.
421 <TITLE><ProductName>Postgres</ProductName> Date/Time Types</TITLE>
422 <TITLEABBREV>Date/Time</TITLEABBREV>
426 <ENTRY>Date/Time Type</ENTRY>
427 <ENTRY>Storage</ENTRY>
428 <ENTRY>Recommendation</ENTRY>
429 <ENTRY>Description</ENTRY>
434 <ENTRY>abstime</ENTRY>
435 <ENTRY>4 bytes</ENTRY>
436 <ENTRY>original date and time</ENTRY>
437 <ENTRY>limited range</ENTRY>
441 <ENTRY>4 bytes</ENTRY>
442 <ENTRY>SQL92 type</ENTRY>
443 <ENTRY>wide range</ENTRY>
446 <ENTRY>datetime</ENTRY>
447 <ENTRY>8 bytes</ENTRY>
448 <ENTRY>best general date and time</ENTRY>
449 <ENTRY>wide range, high precision</ENTRY>
452 <ENTRY>interval</ENTRY>
453 <ENTRY>12 bytes</ENTRY>
454 <ENTRY>SQL92 type</ENTRY>
455 <ENTRY>equivalent to timespan</ENTRY>
458 <ENTRY>reltime</ENTRY>
459 <ENTRY>4 bytes</ENTRY>
460 <ENTRY>original time interval</ENTRY>
461 <ENTRY>limited range, low precision</ENTRY>
465 <ENTRY>4 bytes</ENTRY>
466 <ENTRY>SQL92 type</ENTRY>
467 <ENTRY>wide range</ENTRY>
470 <ENTRY>timespan</ENTRY>
471 <ENTRY>12 bytes</ENTRY>
472 <ENTRY>best general time interval</ENTRY>
473 <ENTRY>wide range, high precision</ENTRY>
476 <ENTRY>timestamp</ENTRY>
477 <ENTRY>4 bytes</ENTRY>
478 <ENTRY>SQL92 type</ENTRY>
479 <ENTRY>limited range</ENTRY>
488 <TITLE><ProductName>Postgres</ProductName> Date/Time Ranges</TITLE>
489 <TITLEABBREV>Ranges</TITLEABBREV>
493 <ENTRY>Date/Time Type</ENTRY>
494 <ENTRY>Earliest</ENTRY>
495 <ENTRY>Latest</ENTRY>
496 <ENTRY>Resolution</ENTRY>
501 <ENTRY>abstime</ENTRY>
502 <ENTRY>1901-12-14</ENTRY>
503 <ENTRY>2038-01-19</ENTRY>
508 <ENTRY>4713 BC</ENTRY>
509 <ENTRY>no limit</ENTRY>
513 <ENTRY>datetime</ENTRY>
514 <ENTRY>4713 BC</ENTRY>
515 <ENTRY>no limit</ENTRY>
516 <ENTRY>1 microsec to 14 digits</ENTRY>
519 <ENTRY>interval</ENTRY>
520 <ENTRY>no limit</ENTRY>
521 <ENTRY>no limit</ENTRY>
522 <ENTRY>1 microsec</ENTRY>
525 <ENTRY>reltime</ENTRY>
526 <ENTRY>-68 years</ENTRY>
527 <ENTRY>+68 years</ENTRY>
532 <ENTRY>00:00:00.00</ENTRY>
533 <ENTRY>23:59:59.99</ENTRY>
534 <ENTRY>1 microsec</ENTRY>
537 <ENTRY>timespan</ENTRY>
538 <ENTRY>no limit</ENTRY>
539 <ENTRY>no limit</ENTRY>
540 <ENTRY>1 microsec (14 digits)</ENTRY>
543 <ENTRY>timestamp</ENTRY>
544 <ENTRY>1901-12-14</ENTRY>
545 <ENTRY>2038-01-19</ENTRY>
554 <ProductName>Postgres</ProductName> endevours to be compatible with
555 <Acronym>SQL92</Acronym> definitions for typical usage.
556 The <Acronym>SQL92</Acronym> standard has an odd mix of date and
557 time types and capabilities. For example, although the date type does not have an associated time zone, the
558 time type can. The default time zone is specified as a constant offset from GMT/UTC;
559 however, time zones in the real world can have no meaning unless associated with a
561 as a time since the offset will vary through the year.
564 To obviate these difficulties, <ProductName>Postgres</ProductName> associates time zones
565 only with date and time
566 types which contain both date and time, and assumes local time for any type containing only
567 date or time. Further, time zone support is derived from the underlying operating system
568 time zone capabilities, and hence can handle daylight savings time and other expected behavior.
571 In future releases, the number of date/time types will decrease, with the current
572 implementation of <Type>datetime</Type> becoming <Type>timestamp</Type>, timespan becoming interval,
573 and (possibly) abstime
574 and reltime being deprecated in favor of <Type>timestamp</Type> and interval.
575 The more arcane features
576 of the date/time definitions from the <Acronym>SQL92</Acronym> standard are not likely to be pursued.
580 <Title>Date/Time Styles</Title>
583 Output formats can be set to one of four styles:
584 ISO-8601, SQL (Ingres), traditional
585 Postgres, and German.
588 <TITLE><ProductName>Postgres</ProductName> Date Styles</TITLE>
589 <TITLEABBREV>Styles</TITLEABBREV>
593 <ENTRY>Style Specification</ENTRY>
594 <ENTRY>Description</ENTRY>
595 <ENTRY>Example</ENTRY>
601 <ENTRY>ISO-8601 standard</ENTRY>
602 <ENTRY>1997-12-17 07:37:16-08</ENTRY>
606 <ENTRY>Traditional style</ENTRY>
607 <ENTRY>12/17/1997 07:37:16.00 PST</ENTRY>
610 <ENTRY>Postgres</ENTRY>
611 <ENTRY>Original style</ENTRY>
612 <ENTRY>Wed Dec 17 07:37:16 1997 PST</ENTRY>
615 <ENTRY>German</ENTRY>
616 <ENTRY>Regional style</ENTRY>
617 <ENTRY>17.12.1997 07:37:16.00 PST</ENTRY>
625 The SQL style has European and non-European (US) variants, which determines whether
626 month follows day or vica versa.
629 <TITLE><ProductName>Postgres</ProductName> Date Order Conventions</TITLE>
630 <TITLEABBREV>Order</TITLEABBREV>
634 <ENTRY>Style Specification</ENTRY>
635 <ENTRY>Description</ENTRY>
636 <ENTRY>Example</ENTRY>
641 <ENTRY>European</ENTRY>
642 <ENTRY>Regional convention</ENTRY>
643 <ENTRY>17/12/1997 15:37:16.00 MET</ENTRY>
646 <ENTRY>NonEuropean</ENTRY>
647 <ENTRY>Regional convention</ENTRY>
648 <ENTRY>12/17/1997 07:37:16.00 PST</ENTRY>
652 <ENTRY>Regional convention</ENTRY>
653 <ENTRY>12/17/1997 07:37:16.00 PST</ENTRY>
661 There are several ways to affect the appearance of date/time types:
663 <ItemizedList Mark="bullet" Spacing="compact">
666 The PGDATESTYLE environment variable used by the backend directly on postmaster startup.
671 The PGDATESTYLE environment variable used by the frontend libpq on session startup.
676 SET DateStyle <Acronym>SQL</Acronym> command.
682 For <ProductName>Postgres</ProductName> v6.3 (and earlier) the default date/time style is
683 "non-European traditional Postgres". In future releases, the default may become ISO-8601, which alleviates
684 date specification ambiguities and Y2K collation problems.
690 <Title>Time Zones</Title>
693 <ProductName>Postgres</ProductName> obtains time zone support from the underlying operating system.
694 All dates and times are stored internally in Universal Coordinated Time (UTC), alternately known as
695 Greenwich Mean Time (GMT). Times are converted to local time on the database server before being
696 sent to the client frontend, hence by default are in the server time zone.
699 There are several ways to affect the time zone behavior:
701 <ItemizedList Mark="bullet" Spacing="compact">
704 The TZ environment variable used by the backend directly
705 on postmaster startup as the default time zone.
710 The PGTZ environment variable set at the client used by libpq to send time zone information to the backend upon connection.
715 <Command>set timezone</Command> <Acronym>SQL</Acronym> sets the time zone for the session.
721 If an invalid time zone is specified,
722 the time zone becomes GMT (on most systems anyway).
727 <Title>Date/Time Input</Title>
730 General-use date and time is input using a wide range of
731 styles, including ISO-compatible, SQL-compatible, traditional
732 <ProductName>Postgres</ProductName>
733 and other permutations of date and time. In cases where interpretation
734 can be ambiguous (quite possible with many traditional styles of date specification)
735 <ProductName>Postgres</ProductName> uses a style setting to resolve the ambiguity.
739 Most date and time types share code for data input. For those types
740 the input can have any of a wide variety of styles. For numeric date representations,
741 European and US conventions can differ, and the proper interpretation is obtained
743 <Command>set datestyle</Command>
744 command before entering data.
745 Note that the style setting does not preclude use of various styles for input; it is
746 used primarily to determine the output style and to resolve ambiguities.
750 The special values `current',
751 `infinity' and `-infinity' are provided.
752 `infinity' specifies a time later than any other valid time, and
753 `-infinity' specifies a time earlier than any other valid time.
754 `current' indicates that the current time should be
755 substituted whenever this value appears in a computation.
762 and `epoch' can be used to specify
763 time values. `now' means the current transaction time, and differs from
764 `current' in that the current time is immediately substituted
765 for it. `epoch' means Jan 1 00:00:00 1970 GMT.
770 <TITLE><ProductName>Postgres</ProductName> Date/Time Special Constants</TITLE>
771 <TITLEABBREV>Constants</TITLEABBREV>
775 <ENTRY>Constant</ENTRY>
776 <ENTRY>Description</ENTRY>
781 <ENTRY>current</ENTRY>
782 <ENTRY>Current transaction time, deferred</ENTRY>
786 <ENTRY>1970-01-01 00:00:00+00 (Unix system time zero)</ENTRY>
789 <ENTRY>infinity</ENTRY>
790 <ENTRY>Later than other valid times</ENTRY>
793 <ENTRY>-infinity</ENTRY>
794 <ENTRY>Earlier than other valid times</ENTRY>
797 <ENTRY>invalid</ENTRY>
798 <ENTRY>Illegal entry</ENTRY>
802 <ENTRY>Current transaction time</ENTRY>
806 <ENTRY>Midnight today</ENTRY>
809 <ENTRY>tomorrow</ENTRY>
810 <ENTRY>Midnight tomorrow</ENTRY>
813 <ENTRY>yesterday</ENTRY>
814 <ENTRY>Midnight yesterday</ENTRY>
824 <Title>datetime</Title>
827 General-use date and time is input using a wide range of
828 styles, including ISO-compatible, SQL-compatible, traditional
829 <ProductName>Postgres</ProductName> (see section on "absolute time")
830 and other permutations of date and time. Output styles can be ISO-compatible,
831 SQL-compatible, or traditional <ProductName>Postgres</ProductName>, with the default set to be compatible
832 with <ProductName>Postgres</ProductName> v6.0.
836 <Type>datetime</Type> is specified using the following syntax:
839 Year-Month-Day [ Hour : Minute : Second ] [AD,BC] [ Timezone ]
840 YearMonthDay [ Hour : Minute : Second ] [AD,BC] [ Timezone ]
841 Month Day [ Hour : Minute : Second ] Year [AD,BC] [ Timezone ]
843 Year is 4013 BC, ..., very large
844 Month is Jan, Feb, ..., Dec or 1, 2, ..., 12
846 Hour is 00, 02, ..., 23
847 Minute is 00, 01, ..., 59
848 Second is 00, 01, ..., 59 (60 for leap second)
849 Timezone is 3 characters or ISO offset to GMT
853 Valid dates are from Nov 13 00:00:00 4013 BC GMT to far into the future.
854 Timezones are either three characters (e.g. "GMT" or "PST") or ISO-compatible
855 offsets to GMT (e.g. "-08" or "-08:00" when in Pacific Standard Time).
856 Dates are stored internally in Greenwich Mean Time. Input and output routines
857 translate time to the local time zone of the server.
861 <Title><Type>timespan</Type></Title>
864 General-use time span is input using a wide range of
865 syntaxes, including ISO-compatible, SQL-compatible, traditional
866 <ProductName>Postgres</ProductName> (see section on "relative time")
867 and other permutations of time span. Output formats can be ISO-compatible,
868 SQL-compatible, or traditional <ProductName>Postgres</ProductName>, with the default set to be <ProductName>Postgres</ProductName>-compatible.
869 Months and years are a "qualitative" time interval, and are stored separately
870 from the other "quantitative" time intervals such as day or hour. For date arithmetic,
871 the qualitative time units are instantiated in the context of the relevant date or time.
874 Time span is specified with the following syntax:
877 Quantity Unit [Quantity Unit...] [Direction]
878 @ Quantity Unit [Direction]
880 Quantity is ..., `-1', `0', `1', `2', ...
881 Unit is `second', `minute', `hour', `day', `week', `month', `year',
882 'decade', 'century', millenium', or abbreviations or plurals of these units.
889 <Title>abstime</Title>
892 Absolute time (abstime) is a limited-range (+/- 68 years) and limited-precision (1 sec)
893 date data type. <Type>datetime</Type> may be preferred, since it
894 covers a larger range with greater precision.
897 Absolute time is specified using the following syntax:
900 Month Day [ Hour : Minute : Second ] Year [ Timezone ]
902 Month is Jan, Feb, ..., Dec
904 Hour is 01, 02, ..., 24
905 Minute is 00, 01, ..., 59
906 Second is 00, 01, ..., 59
907 Year is 1901, 1902, ..., 2038
912 Valid dates are from Dec 13 20:45:53 1901 GMT to Jan 19 03:14:04
913 2038 GMT. As of Version 3.0, times are no longer read and written
914 using Greenwich Mean Time; the input and output routines default to
917 All special values allowed for <Type>datetime</Type> are also allowed for "absolute time".
923 <Title>reltime</Title>
926 Relative time <Type>reltime</Type> is a limited-range (+/- 68 years)
927 and limited-precision (1 sec) time span data type.
928 <Type>timespan</Type> should be preferred, since it
929 covers a larger range with greater precision and, more importantly, can distinguish between
930 relative units (months and years) and quantitative units (days, hours, etc). Instead, reltime
931 must force months to be exactly 30 days, so time arithmetic does not always work as expected.
932 For example, adding one reltime year to abstime today does not produce today's date one year from
933 now, but rather a date 360 days from today.
937 <Type>reltime</Type> shares input and output routines with the other time span types.
938 The section on <Type>timespan</Type> covers this in more detail.
944 <Title><Type>timestamp</Type></Title>
947 This is currently a limited-range absolute time which closely resembles the
949 data type. It shares the general input parser with the other date/time types.
950 In future releases this type will absorb the capabilities of the <Type>datetime</Type> type
951 and will move toward SQL92 compliance.
955 <Type>timestamp</Type> is specified using the same syntax as for <Type>datetime</Type>.
960 <Title><Type>interval</Type></Title>
963 <Type>interval</Type> is an <Acronym>SQL92</Acronym> data type which is
964 currently mapped to the <Type>timespan</Type> <ProductName>Postgres</ProductName>
970 <Title>tinterval</Title>
973 Time ranges are specified as:
976 [ 'abstime' 'abstime']
978 abstime is a time in the absolute time format.
981 Special abstime values such as
982 `current', `infinity' and `-infinity' can be used.
988 <Title>Boolean Type</Title>
991 <ProductName>Postgres</ProductName> supports <Type>bool</Type> as
992 the <Acronym>SQL3</Acronym> boolean type.
993 <Type>bool</Type> can have one of only two states: 'true' or 'false'. A third state, 'unknown', is not
994 implemented and is not suggested in <Acronym>SQL3</Acronym>; <Acronym>NULL</Acronym> is an
995 effective substitute. <Type>bool</Type> can be used in any boolean expression, and boolean expressions
996 always evaluate to a result compatible with this type.
999 <Type>bool</Type> uses 4 bytes of storage.
1003 <TABLE TOCENTRY="1">
1004 <TITLE><ProductName>Postgres</ProductName> Boolean Type</TITLE>
1005 <TITLEABBREV>Booleans</TITLEABBREV>
1009 <ENTRY>State</ENTRY>
1010 <ENTRY>Output</ENTRY>
1011 <ENTRY>Input</ENTRY>
1018 <ENTRY>TRUE, 't', 'true', 'y', 'yes', '1'</ENTRY>
1021 <ENTRY>False</ENTRY>
1023 <ENTRY>FALSE, 'f', 'false', 'n', 'no', '0'</ENTRY>
1032 <Title>Geometric Types</Title>
1035 Geometric types represent two-dimensional spatial objects. The most fundamental type,
1036 the point, forms the basis for all of the other types.
1040 <TABLE TOCENTRY="1">
1041 <TITLE><ProductName>Postgres</ProductName> Geometric Types</TITLE>
1042 <TITLEABBREV>Geometrics</TITLEABBREV>
1046 <ENTRY>Geometric Type</ENTRY>
1047 <ENTRY>Storage</ENTRY>
1048 <ENTRY>Representation</ENTRY>
1049 <ENTRY>Description</ENTRY>
1054 <ENTRY>point</ENTRY>
1055 <ENTRY>16 bytes</ENTRY>
1056 <ENTRY>(x,y)</ENTRY>
1057 <ENTRY>Point in space</ENTRY>
1061 <ENTRY>32 bytes</ENTRY>
1062 <ENTRY>((x1,y1),(x2,y2))</ENTRY>
1063 <ENTRY>Infinite line</ENTRY>
1067 <ENTRY>32 bytes</ENTRY>
1068 <ENTRY>((x1,y1),(x2,y2))</ENTRY>
1069 <ENTRY>Finite line segment</ENTRY>
1073 <ENTRY>32 bytes</ENTRY>
1074 <ENTRY>((x1,y1),(x2,y2))</ENTRY>
1075 <ENTRY>Rectangular box</ENTRY>
1079 <ENTRY>4+32n bytes</ENTRY>
1080 <ENTRY>((x1,y1),...)</ENTRY>
1081 <ENTRY>Closed path (similar to polygon)</ENTRY>
1085 <ENTRY>4+32n bytes</ENTRY>
1086 <ENTRY>[(x1,y1),...]</ENTRY>
1087 <ENTRY>Open path</ENTRY>
1090 <ENTRY>polygon</ENTRY>
1091 <ENTRY>4+32n bytes</ENTRY>
1092 <ENTRY>((x1,y1),...)</ENTRY>
1093 <ENTRY>Polygon (similar to closed path)</ENTRY>
1096 <ENTRY>circle</ENTRY>
1097 <ENTRY>24 bytes</ENTRY>
1098 <ENTRY><(x,y),r></ENTRY>
1099 <ENTRY>Circle (center and radius)</ENTRY>
1107 A rich set of functions and operators is available to perform various geometric
1108 operations such as scaling, translation, rotation, and determining intersections.
1112 <Title>Point</Title>
1115 Points are specified using the following syntax:
1121 x is the x-axis coordinate as a floating point number
1122 y is the y-axis coordinate as a floating point number
1128 <Title>Line Segment</Title>
1131 Line segments (lseg) are represented by pairs of points.
1135 lseg is specified using the following syntax:
1137 ( ( x1 , y1 ) , ( x2 , y2 ) )
1138 ( x1 , y1 ) , ( x2 , y2 )
1141 (x1,y1) and (x2,y2) are the endpoints of the segment
1150 Boxes are represented by pairs of points which are opposite
1155 box is specified using the following syntax:
1158 ( ( x1 , y1 ) , ( x2 , y2 ) )
1159 ( x1 , y1 ) , ( x2 , y2 )
1162 (x1,y1) and (x2,y2) are opposite corners
1165 Boxes are output using the first syntax.
1166 The corners are reordered on input to store
1167 the lower left corner first and the upper right corner last.
1168 Other corners of the box can be entered, but the lower
1169 left and upper right corners are determined from the input and stored.
1177 Paths are represented by connected sets of points. Paths can be "open", where
1178 the first and last points in the set are not connected, and "closed",
1179 where the first and last point are connected. Functions
1180 <Function>popen(p)</Function>
1182 <Function>pclose(p)</Function>
1183 are supplied to force a path to be open or closed, and functions
1184 <Function>isopen(p)</Function>
1186 <Function>isclosed(p)</Function>
1187 are supplied to select either type in a query.
1191 path is specified using the following syntax:
1194 ( ( x1 , y1 ) , ... , ( xn , yn ) )
1195 [ ( x1 , y1 ) , ... , ( xn , yn ) ]
1196 ( x1 , y1 ) , ... , ( xn , yn )
1197 ( x1 , y1 , ... , xn , yn )
1198 x1 , y1 , ... , xn , yn
1200 (x1,y1),...,(xn,yn) are points 1 through n
1201 a leading "[" indicates an open path
1202 a leading "(" indicates a closed path
1204 Paths are output using the first syntax.
1205 Note that <ProductName>Postgres</ProductName> versions prior to
1206 v6.1 used a format for paths which had a single leading parenthesis, a "closed" flag,
1207 an integer count of the number of points, then the list of points followed by a
1208 closing parenthesis. The built-in function <Function>upgradepath</Function> is supplied to convert
1209 paths dumped and reloaded from pre-v6.1 databases.
1214 <Title>Polygon</Title>
1217 Polygons are represented by sets of points. Polygons should probably be
1219 equivalent to closed paths, but are stored differently and have their own
1220 set of support routines.
1224 polygon is specified using the following syntax:
1227 ( ( x1 , y1 ) , ... , ( xn , yn ) )
1228 ( x1 , y1 ) , ... , ( xn , yn )
1229 ( x1 , y1 , ... , xn , yn )
1230 x1 , y1 , ... , xn , yn
1232 (x1,y1),...,(xn,yn) are points 1 through n
1235 Polygons are output using the first syntax.
1236 Note that <ProductName>Postgres</ProductName> versions prior to
1237 v6.1 used a format for polygons which had a single leading parenthesis, the list
1238 of x-axis coordinates, the list of y-axis coordinates, followed by a closing parenthesis.
1239 The built-in function <Function>upgradepoly</Function> is supplied to convert
1240 polygons dumped and reloaded from pre-v6.1 databases.
1245 <Title>Circle</Title>
1248 Circles are represented by a center point and a radius.
1252 circle is specified using the following syntax:
1260 (x,y) is the center of the circle
1261 r is the radius of the circle
1264 Circles are output using the first syntax.
1271 <Title>Operators</Title>
1274 <ProductName>Postgres</ProductName> provides a large number of built-in operators on system types.
1275 These operators are declared in the system catalog
1276 pg_operator. Every entry in pg_operator includes
1277 the name of the procedure that implements the operator and the
1278 class <Acronym>OIDs</Acronym> of the input and output types.
1281 To view all variations of the <Quote>||</Quote> string concatenation operator, try
1283 SELECT oprleft, oprright, oprresult, oprcode
1284 FROM pg_operator WHERE oprname = '||';
1286 oprleft|oprright|oprresult|oprcode
1287 -------+--------+---------+-------
1289 1042| 1042| 1042|textcat
1290 1043| 1043| 1043|textcat
1296 <TABLE TOCENTRY="1">
1297 <TITLE><ProductName>Postgres</ProductName> Operators</TITLE>
1298 <TITLEABBREV>Operators</TITLEABBREV>
1302 <ENTRY>Operator</ENTRY>
1303 <ENTRY>Description</ENTRY>
1304 <ENTRY>Usage</ENTRY>
1309 <ENTRY> < </ENTRY>
1310 <ENTRY>Less than?</ENTRY>
1311 <ENTRY>1 < 2</ENTRY>
1314 <ENTRY> <= </ENTRY>
1315 <ENTRY>Less than or equal to?</ENTRY>
1316 <ENTRY>1 <= 2</ENTRY>
1319 <ENTRY> <> </ENTRY>
1320 <ENTRY>Not equal?</ENTRY>
1321 <ENTRY>1 <> 2</ENTRY>
1325 <ENTRY>Equal?</ENTRY>
1326 <ENTRY>1 = 1</ENTRY>
1329 <ENTRY> > </ENTRY>
1330 <ENTRY>Greater than?</ENTRY>
1331 <ENTRY>2 > 1</ENTRY>
1334 <ENTRY> >= </ENTRY>
1335 <ENTRY>Greater than or equal to?</ENTRY>
1336 <ENTRY>2 >= 1</ENTRY>
1340 <ENTRY>Concatenate strings</ENTRY>
1341 <ENTRY>'Postgre' || 'SQL'</ENTRY>
1344 <ENTRY> !!= </ENTRY>
1345 <ENTRY>NOT IN</ENTRY>
1346 <ENTRY>3 !!= i</ENTRY>
1351 <ENTRY>'scrappy,marc,hermit' ~~ '%scrappy%'</ENTRY>
1354 <ENTRY> !~~ </ENTRY>
1355 <ENTRY>NOT LIKE</ENTRY>
1356 <ENTRY>'bruce' !~~ '%al%'</ENTRY>
1360 <ENTRY>Match (regex), case sensitive</ENTRY>
1361 <ENTRY>'thomas' ~ '*.thomas*.'</ENTRY>
1365 <ENTRY>Match (regex), case insensitive</ENTRY>
1366 <ENTRY>'thomas' ~* '*.Thomas*.'</ENTRY>
1370 <ENTRY>Does not match (regex), case sensitive</ENTRY>
1371 <ENTRY>'thomas' !~ '*.Thomas*.'</ENTRY>
1374 <ENTRY> !~* </ENTRY>
1375 <ENTRY>Does not match (regex), case insensitive</ENTRY>
1376 <ENTRY>'thomas' !~ '*.vadim*.'</ENTRY>
1384 <TABLE TOCENTRY="1">
1385 <TITLE><ProductName>Postgres</ProductName> Numerical Operators</TITLE>
1386 <TITLEABBREV>Operators</TITLEABBREV>
1390 <ENTRY>Operator</ENTRY>
1391 <ENTRY>Description</ENTRY>
1392 <ENTRY>Usage</ENTRY>
1398 <ENTRY>Factorial</ENTRY>
1403 <ENTRY>Factorial (left operator)</ENTRY>
1408 <ENTRY>Modulo</ENTRY>
1409 <ENTRY>5 % 4</ENTRY>
1413 <ENTRY>Truncate</ENTRY>
1414 <ENTRY>% 4.5</ENTRY>
1418 <ENTRY>Multiplication</ENTRY>
1419 <ENTRY>2 * 3</ENTRY>
1423 <ENTRY>Addition</ENTRY>
1424 <ENTRY>2 + 3</ENTRY>
1428 <ENTRY>Subtraction</ENTRY>
1429 <ENTRY>2 - 3</ENTRY>
1433 <ENTRY>Division</ENTRY>
1434 <ENTRY>4 / 2</ENTRY>
1438 <ENTRY>Natural Exponentiation</ENTRY>
1439 <ENTRY>: 3.0</ENTRY>
1443 <ENTRY>Natural Logarithm</ENTRY>
1444 <ENTRY>(; 5.0)</ENTRY>
1448 <ENTRY>Absolute value</ENTRY>
1449 <ENTRY>@ -5.0</ENTRY>
1453 <ENTRY>Exponentiation</ENTRY>
1454 <ENTRY>2.0 ^ 3.0</ENTRY>
1458 <ENTRY>Square root</ENTRY>
1459 <ENTRY>|/ 25.0</ENTRY>
1462 <ENTRY> ||/ </ENTRY>
1463 <ENTRY>Cube root</ENTRY>
1464 <ENTRY>||/ 27.0</ENTRY>
1472 <TABLE TOCENTRY="1">
1473 <TITLE><ProductName>Postgres</ProductName> Geometric Operators</TITLE>
1474 <TITLEABBREV>Operators</TITLEABBREV>
1478 <ENTRY>Operator</ENTRY>
1479 <ENTRY>Description</ENTRY>
1480 <ENTRY>Usage</ENTRY>
1486 <ENTRY>Translation</ENTRY>
1487 <ENTRY>'((0,0),(1,1))'::box + '(2.0,0)'::point</ENTRY>
1491 <ENTRY>Translation</ENTRY>
1492 <ENTRY>'((0,0),(1,1))'::box - '(2.0,0)'::point</ENTRY>
1496 <ENTRY>Scaling/rotation</ENTRY>
1497 <ENTRY>'((0,0),(1,1))'::box * '(2.0,0)'::point</ENTRY>
1501 <ENTRY>Scaling/rotation</ENTRY>
1502 <ENTRY>'((0,0),(2,2))'::box / '(2.0,0)'::point</ENTRY>
1506 <ENTRY>Intersection</ENTRY>
1507 <ENTRY>'((1,-1),(-1,1))' # '((1,1),(-1,-1))'</ENTRY>
1511 <ENTRY>Number of points in polygon</ENTRY>
1512 <ENTRY># '((1,0),(0,1),(-1,0))'</ENTRY>
1516 <ENTRY>Point of closest proximity</ENTRY>
1517 <ENTRY>'(0,0)'::point ## '((2,0),(0,2))'::lseg</ENTRY>
1520 <ENTRY> && </ENTRY>
1521 <ENTRY>Overlaps?</ENTRY>
1522 <ENTRY>'((0,0),(1,1))'::box && '((0,0),(2,2))'::box</ENTRY>
1525 <ENTRY> &< </ENTRY>
1526 <ENTRY>Overlaps to left?</ENTRY>
1527 <ENTRY>'((0,0),(1,1))'::box &< '((0,0),(2,2))'::box</ENTRY>
1530 <ENTRY> &> </ENTRY>
1531 <ENTRY>Overlaps to right?</ENTRY>
1532 <ENTRY>'((0,0),(3,3))'::box &> '((0,0),(2,2))'::box</ENTRY>
1535 <ENTRY> <-> </ENTRY>
1536 <ENTRY>Distance between</ENTRY>
1537 <ENTRY>'((0,0),1)'::circle <-> '((5,0),1)'::circle</ENTRY>
1540 <ENTRY> << </ENTRY>
1541 <ENTRY>Left of?</ENTRY>
1542 <ENTRY>'((0,0),1)'::circle << '((5,0),1)'::circle</ENTRY>
1545 <ENTRY> <^ </ENTRY>
1546 <ENTRY>Is below?</ENTRY>
1547 <ENTRY>'((0,0),1)'::circle <^ '((0,5),1)'::circle</ENTRY>
1550 <ENTRY> >> </ENTRY>
1551 <ENTRY>Is right of?</ENTRY>
1552 <ENTRY>'((5,0),1)'::circle >> '((0,0),1)'::circle</ENTRY>
1555 <ENTRY> >^ </ENTRY>
1556 <ENTRY>Is above?</ENTRY>
1557 <ENTRY>'((0,5),1)'::circle >^ '((0,0),1)'::circle</ENTRY>
1561 <ENTRY>Intersects or overlaps</ENTRY>
1562 <ENTRY>'((-1,0),(1,0))'::lseg ?# '((-2,-2),(2,2))'::box;</ENTRY>
1566 <ENTRY>Is horizontal?</ENTRY>
1567 <ENTRY>'(1,0)'::point ?- '(0,0)'::point</ENTRY>
1570 <ENTRY> ?-| </ENTRY>
1571 <ENTRY>Is perpendicular?</ENTRY>
1572 <ENTRY>'((0,0),(0,1))'::lseg ?-| '((0,0),(1,0))'::lseg</ENTRY>
1575 <ENTRY> @-@ </ENTRY>
1576 <ENTRY>Length or circumference</ENTRY>
1577 <ENTRY>@-@ '((0,0),(1,0))'::path</ENTRY>
1581 <ENTRY>Is vertical?</ENTRY>
1582 <ENTRY>'(0,1)'::point ?| '(0,0)'::point</ENTRY>
1585 <ENTRY> ?|| </ENTRY>
1586 <ENTRY>Is parallel?</ENTRY>
1587 <ENTRY>'((-1,0),(1,0))'::lseg ?|| '((-1,2),(1,2))'::lseg</ENTRY>
1591 <ENTRY>Contained or on</ENTRY>
1592 <ENTRY>'(1,1)'::point @ '((0,0),2)'::circle</ENTRY>
1596 <ENTRY>Center of</ENTRY>
1597 <ENTRY>@@ '((0,0),10)'::circle</ENTRY>
1601 <ENTRY>Same as</ENTRY>
1602 <ENTRY>'((0,0),(1,1))'::polygon ~= '((1,1),(0,0))'::polygon</ENTRY>
1610 The time interval data type <Type>tinterval</Type> is a legacy from the original
1611 date/time types and is not as well supported as the more modern types. There
1612 are several operators for this type.
1614 <TABLE TOCENTRY="1">
1615 <TITLE><ProductName>Postgres</ProductName> Time Interval Operators</TITLE>
1616 <TITLEABBREV>Operators</TITLEABBREV>
1620 <ENTRY>Operator</ENTRY>
1621 <ENTRY>Description</ENTRY>
1622 <ENTRY>Usage</ENTRY>
1627 <ENTRY> #< </ENTRY>
1628 <ENTRY>Interval less than?</ENTRY>
1632 <ENTRY> #<= </ENTRY>
1633 <ENTRY>Interval less than or equal to?</ENTRY>
1637 <ENTRY> #<> </ENTRY>
1638 <ENTRY>Interval not equal?</ENTRY>
1643 <ENTRY>Interval equal?</ENTRY>
1647 <ENTRY> #> </ENTRY>
1648 <ENTRY>Interval greater than?</ENTRY>
1652 <ENTRY> #>= </ENTRY>
1653 <ENTRY>Interval greater than or equal to?</ENTRY>
1657 <ENTRY> <#> </ENTRY>
1658 <ENTRY>Convert to time interval</ENTRY>
1662 <ENTRY> << </ENTRY>
1663 <ENTRY>Interval less than?</ENTRY>
1668 <ENTRY>Start of interval</ENTRY>
1673 <ENTRY>Same as</ENTRY>
1677 <ENTRY> <?> </ENTRY>
1678 <ENTRY>Time inside interval?</ENTRY>
1688 Users may invoke operators using the operator name, as in:
1691 select * from emp where salary < 40000;
1694 Alternatively, users may call the functions that implement the
1695 operators directly. In this case, the query above would be expressed
1698 select * from emp where int4lt(salary, 40000);
1702 <Application>psql</Application>
1703 has a <Command>\dd</Command> command to show these operators.
1707 <Title>Functions</Title>
1710 Many data types have functions available for conversion to other related types.
1711 In addition, there are some type-specific functions. Functions which are also
1712 available through operators are documented as operators only.
1716 Some functions defined for text are also available for char() and varchar().
1721 <Function>date_part</Function> and <Function>date_trunc</Function>
1722 functions, arguments can be
1723 `year', `month', `day', `hour', `minute', and `second',
1724 as well as the more specialized quantities
1725 `decade', `century', `millenium', `millisecond', and `microsecond'.
1726 <Function>date_part</Function> allows `dow'
1727 to return day of week and `epoch' to return seconds since 1970 (for <Type>datetime</Type>)
1728 or 'epoch' to return total elapsed seconds (for <Type>timespan</Type>).
1732 <TABLE TOCENTRY="1">
1733 <TITLE>Mathematical Functions</TITLE>
1737 <ENTRY>Function</ENTRY>
1738 <ENTRY>Returns</ENTRY>
1739 <ENTRY>Description</ENTRY>
1740 <ENTRY>Example</ENTRY>
1745 <ENTRY> float(int) </ENTRY>
1746 <ENTRY> float8 </ENTRY>
1747 <ENTRY> convert integer to floating point </ENTRY>
1748 <ENTRY> float(2) </ENTRY>
1751 <ENTRY> float4(int) </ENTRY>
1752 <ENTRY> float4 </ENTRY>
1753 <ENTRY> convert integer to floating point </ENTRY>
1754 <ENTRY> float4(2) </ENTRY>
1757 <ENTRY> int </ENTRY>
1758 <ENTRY> integer(float) </ENTRY>
1759 <ENTRY> convert floating point to integer </ENTRY>
1760 <ENTRY> integer(2.0) </ENTRY>
1768 Many of the string functions are available for text, varchar(), and char() types.
1769 At the moment, some functions are available only for the text type.
1771 <TABLE TOCENTRY="1">
1772 <TITLE>String Functions</TITLE>
1776 <ENTRY>Function</ENTRY>
1777 <ENTRY>Returns</ENTRY>
1778 <ENTRY>Description</ENTRY>
1779 <ENTRY>Example</ENTRY>
1784 <ENTRY> lower(text) </ENTRY>
1785 <ENTRY> text </ENTRY>
1786 <ENTRY> convert text to lower case </ENTRY>
1787 <ENTRY> lower('TOM') </ENTRY>
1790 <ENTRY> lpad(text,int,text) </ENTRY>
1791 <ENTRY> text </ENTRY>
1792 <ENTRY> left pad string to specified length </ENTRY>
1793 <ENTRY> lpad('hi',4,'??') </ENTRY>
1796 <ENTRY> ltrim(text,text) </ENTRY>
1797 <ENTRY> text </ENTRY>
1798 <ENTRY> left trim characters from text </ENTRY>
1799 <ENTRY> ltrim('xxxxtrim','x') </ENTRY>
1802 <ENTRY> position(text,text) </ENTRY>
1803 <ENTRY> text </ENTRY>
1804 <ENTRY> extract specified substring </ENTRY>
1805 <ENTRY> position('high','ig') </ENTRY>
1808 <ENTRY> rpad(text,int,text) </ENTRY>
1809 <ENTRY> text </ENTRY>
1810 <ENTRY> right pad string to specified length </ENTRY>
1811 <ENTRY> rpad('hi',4,'x') </ENTRY>
1814 <ENTRY> rtrim(text,text) </ENTRY>
1815 <ENTRY> text </ENTRY>
1816 <ENTRY> right trim characters from text </ENTRY>
1817 <ENTRY> rtrim('trimxxxx','x') </ENTRY>
1820 <ENTRY> substr(text,int[,int]) </ENTRY>
1821 <ENTRY> text </ENTRY>
1822 <ENTRY> extract specified substring </ENTRY>
1823 <ENTRY> substr('hi there',3,5) </ENTRY>
1826 <ENTRY> upper(text) </ENTRY>
1827 <ENTRY> text </ENTRY>
1828 <ENTRY> convert text to upper case </ENTRY>
1829 <ENTRY> upper('tom') </ENTRY>
1837 <TABLE TOCENTRY="1">
1838 <TITLE>Date/Time Functions</TITLE>
1842 <ENTRY>Function</ENTRY>
1843 <ENTRY>Returns</ENTRY>
1844 <ENTRY>Description</ENTRY>
1845 <ENTRY>Example</ENTRY>
1850 <ENTRY> isfinite(abstime) </ENTRY>
1851 <ENTRY> bool </ENTRY>
1852 <ENTRY> TRUE if this is a finite time </ENTRY>
1853 <ENTRY> isfinite('now'::abstime) </ENTRY>
1856 <ENTRY> datetime(abstime) </ENTRY>
1857 <ENTRY> datetime </ENTRY>
1858 <ENTRY> convert to datetime </ENTRY>
1859 <ENTRY> datetime('now'::abstime) </ENTRY>
1862 <ENTRY> datetime(date) </ENTRY>
1863 <ENTRY> datetime </ENTRY>
1864 <ENTRY> convert to datetime </ENTRY>
1865 <ENTRY> datetime('today'::date) </ENTRY>
1868 <ENTRY> datetime(date,time) </ENTRY>
1869 <ENTRY> datetime </ENTRY>
1870 <ENTRY> convert to datetime </ENTRY>
1871 <ENTRY> datetime('1998-02-24'::datetime, '23:07'::time); </ENTRY>
1874 <ENTRY> age(datetime,datetime) </ENTRY>
1875 <ENTRY> timespan </ENTRY>
1876 <ENTRY> span preserving months and years </ENTRY>
1877 <ENTRY> age('now','1957-06-13'::datetime) </ENTRY>
1880 <ENTRY> date_part(text,datetime) </ENTRY>
1881 <ENTRY> float8 </ENTRY>
1882 <ENTRY> specified portion of date field </ENTRY>
1883 <ENTRY> date_part('dow','now'::datetime) </ENTRY>
1886 <ENTRY> date_trunc(text,datetime) </ENTRY>
1887 <ENTRY> datetime </ENTRY>
1888 <ENTRY> truncate date at specified units </ENTRY>
1889 <ENTRY> date_trunc('month','now'::abstime) </ENTRY>
1892 <ENTRY> isfinite(datetime) </ENTRY>
1893 <ENTRY> bool </ENTRY>
1894 <ENTRY> TRUE if this is a finite time </ENTRY>
1895 <ENTRY> isfinite('now'::datetime) </ENTRY>
1898 <ENTRY> abstime(datetime) </ENTRY>
1899 <ENTRY> abstime </ENTRY>
1900 <ENTRY> convert to abstime </ENTRY>
1901 <ENTRY> abstime('now'::datetime) </ENTRY>
1904 <ENTRY> timespan(reltime) </ENTRY>
1905 <ENTRY> timespan </ENTRY>
1906 <ENTRY> convert to timespan </ENTRY>
1907 <ENTRY> timespan('4 hours'::reltime) </ENTRY>
1910 <ENTRY> datetime(date,time) </ENTRY>
1911 <ENTRY> datetime </ENTRY>
1912 <ENTRY> convert to datetime </ENTRY>
1913 <ENTRY> datetime('1998-02-25'::date,'06:41'::time) </ENTRY>
1916 <ENTRY> date_part(text,timespan) </ENTRY>
1917 <ENTRY> float8 </ENTRY>
1918 <ENTRY> specified portion of time field </ENTRY>
1919 <ENTRY> date_part('hour','4 hrs 3 mins'::timespan) </ENTRY>
1922 <ENTRY> isfinite(timespan) </ENTRY>
1923 <ENTRY> bool </ENTRY>
1924 <ENTRY> TRUE if this is a finite time </ENTRY>
1925 <ENTRY> isfinite('4 hrs'::timespan) </ENTRY>
1928 <ENTRY> reltime(timespan) </ENTRY>
1929 <ENTRY> reltime </ENTRY>
1930 <ENTRY> convert to reltime </ENTRY>
1931 <ENTRY> reltime('4 hrs'::timespan) </ENTRY>
1939 <TABLE TOCENTRY="1">
1940 <TITLE>Geometric Functions</TITLE>
1944 <ENTRY>Function</ENTRY>
1945 <ENTRY>Returns</ENTRY>
1946 <ENTRY>Description</ENTRY>
1947 <ENTRY>Example</ENTRY>
1952 <ENTRY> box(point,point) </ENTRY>
1953 <ENTRY> box </ENTRY>
1954 <ENTRY> convert points to box </ENTRY>
1955 <ENTRY> box('(0,0)'::point,'(1,1)'::point) </ENTRY>
1958 <ENTRY> area(box) </ENTRY>
1959 <ENTRY> float8 </ENTRY>
1960 <ENTRY> area of box </ENTRY>
1961 <ENTRY> area('((0,0),(1,1))'::box) </ENTRY>
1964 <ENTRY> isopen(path) </ENTRY>
1965 <ENTRY> bool </ENTRY>
1966 <ENTRY> TRUE if this is an open path </ENTRY>
1967 <ENTRY> isopen('[(0,0),(1,1),(2,0)]'::path) </ENTRY>
1970 <ENTRY> isclosed(path) </ENTRY>
1971 <ENTRY> bool </ENTRY>
1972 <ENTRY> TRUE if this is a closed path </ENTRY>
1973 <ENTRY> isclosed('((0,0),(1,1),(2,0))'::path) </ENTRY>
1976 <ENTRY> circle(point,float8) </ENTRY>
1977 <ENTRY> circle </ENTRY>
1978 <ENTRY> convert to circle </ENTRY>
1979 <ENTRY> circle('(0,0)'::point,2.0) </ENTRY>
1982 <ENTRY> polygon(npts,circle) </ENTRY>
1983 <ENTRY> polygon </ENTRY>
1984 <ENTRY> convert to polygon with npts points </ENTRY>
1985 <ENTRY> polygon(12,'((0,0),2.0)'::circle) </ENTRY>
1988 <ENTRY> center(circle) </ENTRY>
1989 <ENTRY> float8 </ENTRY>
1990 <ENTRY> center of object </ENTRY>
1991 <ENTRY> center('((0,0),2.0)'::circle) </ENTRY>
1994 <ENTRY> radius(circle) </ENTRY>
1995 <ENTRY> float8 </ENTRY>
1996 <ENTRY> radius of circle </ENTRY>
1997 <ENTRY> radius('((0,0),2.0)'::circle) </ENTRY>
2000 <ENTRY> diameter(circle) </ENTRY>
2001 <ENTRY> float8 </ENTRY>
2002 <ENTRY> diameter of circle </ENTRY>
2003 <ENTRY> diameter('((0,0),2.0)'::circle) </ENTRY>
2006 <ENTRY> area(circle) </ENTRY>
2007 <ENTRY> float8 </ENTRY>
2008 <ENTRY> area of circle </ENTRY>
2009 <ENTRY> area('((0,0),2.0)'::circle) </ENTRY>
2017 SQL92 defines functions with specific syntax. Some of these
2018 are implemented using other <ProductName>Postgres</ProductName> functions.
2022 <TABLE TOCENTRY="1">
2023 <TITLE><Acronym>SQL92</Acronym> Text Functions</TITLE>
2027 <ENTRY>Function</ENTRY>
2028 <ENTRY>Returns</ENTRY>
2029 <ENTRY>Description</ENTRY>
2030 <ENTRY>Example</ENTRY>
2035 <ENTRY> position(text in text) </ENTRY>
2036 <ENTRY> int4 </ENTRY>
2037 <ENTRY> extract specified substring </ENTRY>
2038 <ENTRY> position('o' in 'Tom') </ENTRY>
2041 <ENTRY> substring(text [from int] [for int]) </ENTRY>
2042 <ENTRY> text </ENTRY>
2043 <ENTRY> extract specified substring </ENTRY>
2044 <ENTRY> substring('Tom' from 2 for 2) </ENTRY>
2047 <ENTRY> trim([leading|trailing|both] [text] from text) </ENTRY>
2048 <ENTRY> text </ENTRY>
2049 <ENTRY> trim characters from text </ENTRY>
2050 <ENTRY> trim(both 'x' from 'xTomx') </ENTRY>