1 <chapter id="datatype">
2 <title>Data Types</title>
6 Describes the built-in data types available in
7 <productname>Postgres</productname>.
12 <productname>Postgres</productname> has a rich set of native data
13 types available to users.
14 Users may add new types to <productname>Postgres</productname> using the
15 <command>DEFINE TYPE</command>
16 command described elsewhere.
20 In the context of data types, the following sections will discuss
21 <acronym>SQL</acronym> standards compliance, porting issues, and usage.
23 Some <productname>Postgres</productname> types correspond directly to
24 <acronym>SQL92</acronym>-compatible types. In other
25 cases, data types defined by <acronym>SQL92</acronym> syntax are mapped directly
26 into native <productname>Postgres</productname> types.
28 Many of the built-in types have obvious external formats. However, several
29 types are either unique to <productname>Postgres</productname>,
30 such as open and closed paths, or have
31 several possibilities for formats, such as the date and time types.
36 <title><productname>Postgres</productname> Data Types</title>
37 <titleabbrev>Data Types</titleabbrev>
41 <entry><productname>Postgres</productname> Type</entry>
42 <entry><acronym>SQL92</acronym> or <acronym>SQL3</acronym> Type</entry>
43 <entry>Description</entry>
49 <entry>boolean</entry>
50 <entry>logical boolean (true/false)</entry>
55 <entry>rectangular box in 2D plane</entry>
58 <entry>char(n)</entry>
59 <entry>character(n)</entry>
60 <entry>fixed-length character string</entry>
65 <entry>IP version 4 network or host address</entry>
70 <entry>circle in 2D plane</entry>
75 <entry>calendar date without time of day</entry>
78 <entry>float4/8</entry>
79 <entry>float(p)</entry>
80 <entry>floating-point number with precision p</entry>
84 <entry>real, double precision</entry>
85 <entry>double-precision floating-point number</entry>
90 <entry>IP version 4 network or host address</entry>
94 <entry>smallint</entry>
95 <entry>signed two-byte integer</entry>
99 <entry>int, integer</entry>
100 <entry>signed 4-byte integer</entry>
104 <entry>decimal(p,s)</entry>
105 <entry>exact numeric for p <= 9, s = 0</entry>
109 <entry>numeric(p,s)</entry>
110 <entry>exact numeric for p == 9, s = 0</entry>
115 <entry>signed 8-byte integer</entry>
120 <entry>infinite line in 2D plane</entry>
125 <entry>line segment in 2D plane</entry>
129 <entry>decimal(9,2)</entry>
130 <entry>US-style currency</entry>
135 <entry>open and closed geometric path in 2D plane</entry>
140 <entry>geometric point in 2D plane</entry>
143 <entry>polygon</entry>
145 <entry>closed geometric path in 2D plane</entry>
148 <entry>serial</entry>
150 <entry>unique id for indexing and cross-reference</entry>
155 <entry>time of day</entry>
158 <entry>timespan</entry>
159 <entry>interval</entry>
160 <entry>general-use time span</entry>
163 <entry>timestamp</entry>
164 <entry>timestamp with time zone</entry>
165 <entry>date/time</entry>
168 <entry>varchar(n)</entry>
169 <entry>character varying(n)</entry>
170 <entry>variable-length character string</entry>
180 The <type>cidr</type> and <type>inet</type> types are designed to handle any IP type
181 but only ipv4 is handled in the current implementation.
182 Everything here that talks about ipv4 will apply to ipv6 in a future release.</para>
187 <title><productname>Postgres</productname> Function Constants</title>
188 <titleabbrev>Constants</titleabbrev>
192 <entry><productname>Postgres</productname> Function</entry>
193 <entry><acronym>SQL92</acronym> Constant</entry>
194 <entry>Description</entry>
199 <entry>getpgusername()</entry>
200 <entry>current_user</entry>
201 <entry>user name in current session</entry>
204 <entry>date('now')</entry>
205 <entry>current_date</entry>
206 <entry>date of current transaction</entry>
209 <entry>time('now')</entry>
210 <entry>current_time</entry>
211 <entry>time of current transaction</entry>
214 <entry>timestamp('now')</entry>
215 <entry>current_timestamp</entry>
216 <entry>date and time of current transaction</entry>
224 <productname>Postgres</productname> has features at the forefront of
225 <acronym>ORDBMS</acronym> development. In addition to
226 <acronym>SQL3</acronym> conformance, substantial portions
227 of <acronym>SQL92</acronym> are also supported.
228 Although we strive for <acronym>SQL92</acronym> compliance,
229 there are some aspects of the standard
230 which are ill considered and which should not live through subsequent standards.
231 <productname>Postgres</productname> will not make great efforts to
232 conform to these features; however, these tend to apply in little-used
233 or obsure cases, and a typical user is not likely to run into them.</para>
236 Most of the input and output functions corresponding to the
237 base types (e.g., integers and floating point numbers) do some
239 Some of the operators and functions (e.g.,
240 addition and multiplication) do not perform run-time error-checking in the
241 interests of improving execution speed.
242 On some systems, for example, the numeric operators for some data types may
243 silently underflow or overflow.
247 Note that some of the input and output functions are not invertible. That is,
248 the result of an output function may lose precision when compared to
253 The original <productname>Postgres</productname> v4.2 code received from
254 Berkeley rounded all double precision floating point results to six digits for
255 output. Starting with v6.1, floating point numbers are allowed to retain
256 most of the intrinsic precision of the type (typically 15 digits for doubles,
257 6 digits for 4-byte floats).
258 Other types with underlying floating point fields (e.g. geometric
259 types) carry similar precision.</para>
264 <title>Numeric Types</title>
267 Numeric types consist of two- and four-byte integers and four- and eight-byte
268 floating point numbers.</para>
272 <title><productname>Postgres</productname> Numeric Types</title>
273 <titleabbrev>Numerics</titleabbrev>
277 <entry>Numeric Type</entry>
278 <entry>Storage</entry>
279 <entry>Description</entry>
285 <entry>float4</entry>
286 <entry>4 bytes</entry>
287 <entry>Variable-precision</entry>
288 <entry>6 decimal places</entry>
291 <entry>float8</entry>
292 <entry>8 bytes</entry>
293 <entry>Variable-precision</entry>
294 <entry>15 decimal places</entry>
298 <entry>2 bytes</entry>
299 <entry>Fixed-precision</entry>
300 <entry>-32768 to +32767</entry>
304 <entry>4 bytes</entry>
305 <entry>Usual choice for fixed-precision</entry>
306 <entry>-2147483648 to +2147483647</entry>
310 <entry>8 bytes</entry>
311 <entry>Very large range fixed-precision</entry>
312 <entry>+/- > 18 decimal places</entry>
315 <entry>serial</entry>
316 <entry>4 bytes</entry>
317 <entry>Identifer or cross-reference</entry>
318 <entry>0 to +2147483647</entry>
326 The numeric types have a full set of corresponding arithmetic operators and
327 functions. Refer to <xref endterm="math-opers" linkend="math-opers">
328 and <xref endterm="math-funcs" linkend="math-funcs"> for more information.
332 The <type>serial</type> type is a special-case type constructed by
333 <productname>Postgres</productname> from other existing components.
334 It is typically used to create unique identifiers for table entries.
335 In the current implementation, specifying
338 CREATE TABLE <replaceable class="parameter">tablename</replaceable> (<replaceable class="parameter">colname</replaceable> SERIAL);
341 is equivalent to specifying:
344 CREATE SEQUENCE <replaceable class="parameter">tablename</replaceable>_<replaceable class="parameter">colname</replaceable>_seq;
345 CREATE TABLE <replaceable class="parameter">tablename</replaceable>
346 (<replaceable class="parameter">colname</replaceable> INT4 DEFAULT nextval('<replaceable class="parameter">tablename</replaceable>_<replaceable class="parameter">colname</replaceable>_seq');
347 CREATE UNIQUE INDEX <replaceable class="parameter">tablename</replaceable>_<replaceable class="parameter">colname</replaceable>_key on <replaceable class="parameter">tablename</replaceable> (<replaceable class="parameter">colname</replaceable>);
352 The implicit sequence created for the <type>serial</type> type will
353 <emphasis>not</emphasis> be automatically removed when the table is dropped.
354 So, the following commands executed in order will likely fail:
357 CREATE TABLE <replaceable class="parameter">tablename</replaceable> (<replaceable class="parameter">colname</replaceable> SERIAL);
358 DROP TABLE <replaceable class="parameter">tablename</replaceable>;
359 CREATE TABLE <replaceable class="parameter">tablename</replaceable> (<replaceable class="parameter">colname</replaceable> SERIAL);
362 The sequence will remain in the database until explicitly dropped using
363 <command>DROP SEQUENCE</command>.</para>
368 The <firstterm>exact numerics</firstterm> <type>decimal</type> and
370 have fully implemented syntax but currently
371 (<productname>Postgres</productname> v6.4)
372 support only a small range of precision and/or range values.
373 The <type>int8</type> type may not be available on all platforms since
374 it relies on compiler support for this.
380 <title>Monetary Type</title>
383 The <type>money</type> type supports US-style currency with
384 fixed decimal point representation.
385 If <productname>Postgres</productname> is compiled with USE_LOCALE
386 then the money type should use the monetary conventions defined for
387 <citetitle>locale(7)</citetitle>.
392 <title><productname>Postgres</productname> Monetary Types</title>
393 <titleabbrev>Money</titleabbrev>
397 <entry>Monetary Type</entry>
398 <entry>Storage</entry>
399 <entry>Description</entry>
406 <entry>4 bytes</entry>
407 <entry>Fixed-precision</entry>
408 <entry>-21474836.48 to +21474836.47</entry>
417 should eventually replace the money type. It has a
418 fully implemented syntax but currently
419 (<productname>Postgres</productname> v6.4)
420 support only a small range of precision and/or range values
421 and cannot adequately substitute for the money type.
427 <title>Character Types</title>
430 <acronym>SQL92</acronym> defines two primary character types:
431 <type>char</type> and <type>varchar</type>.
432 <productname>Postgres</productname> supports these types, in
433 addition to the more general <type>text</type> type,
434 which unlike <type>varchar</type>
435 does not require an upper
436 limit to be declared on the size of the field.
441 <title><productname>Postgres</productname> Character Types</title>
442 <titleabbrev>Characters</titleabbrev>
446 <entry>Character Type</entry>
447 <entry>Storage</entry>
448 <entry>Recommendation</entry>
449 <entry>Description</entry>
455 <entry>1 byte</entry>
456 <entry><acronym>SQL92</acronym>-compatible</entry>
457 <entry>Single character</entry>
460 <entry>char(n)</entry>
461 <entry>(4+n) bytes</entry>
462 <entry><acronym>SQL92</acronym>-compatible</entry>
463 <entry>Fixed-length blank padded</entry>
467 <entry>(4+x) bytes</entry>
468 <entry>Best choice</entry>
469 <entry>Variable-length</entry>
472 <entry>varchar(n)</entry>
473 <entry>(4+n) bytes</entry>
474 <entry><acronym>SQL92</acronym>-compatible</entry>
475 <entry>Variable-length with limit</entry>
483 There is one other fixed-length character type.
484 The <type>name</type> type
485 only has one purpose and that is to provide
486 <productname>Postgres</productname> with a
487 special type to use for internal names.
488 It is not intended for use by the general user.
489 It's length is currently defined as 32 chars
490 but should be reference using NAMEDATALEN.
491 This is set at compile time and may change in a future release.
496 <title><productname>Postgres</productname> Specialty Character Type</title>
497 <titleabbrev>Specialty Characters</titleabbrev>
501 <entry>Character Type</entry>
502 <entry>Storage</entry>
503 <entry>Description</entry>
509 <entry>32 bytes</entry>
510 <entry>Thirty-two character internal type</entry>
520 <title>Date/Time Types</title>
523 There are two fundamental kinds of date and time measurements
524 provided by <productname>Postgres</productname>:
525 absolute clock times and relative time intervals.
526 Both kinds of time measurements should demonstrate both
527 continuity and smoothness.
531 <productname>Postgres</productname> supplies two primary user-oriented
533 <type>datetime</type> and <type>timespan</type>, as well as
534 the related <acronym>SQL92</acronym> types <type>timestamp</type>,
535 <type>interval</type>,
536 <type>date</type> and <type>time</type>.
540 In a future release, <type>datetime</type> and <type>timespan</type> are likely
541 to merge with the <acronym>SQL92</acronym> types <type>timestamp</type>,
542 <type>interval</type>.
543 Other date and time types are also available, mostly
544 for historical reasons.
549 <title><productname>Postgres</productname> Date/Time Types</title>
550 <titleabbrev>Date/Time</titleabbrev>
554 <entry>Date/Time Type</entry>
555 <entry>Storage</entry>
556 <entry>Recommendation</entry>
557 <entry>Description</entry>
562 <entry>abstime</entry>
563 <entry>4 bytes</entry>
564 <entry>original date and time</entry>
565 <entry>limited range</entry>
569 <entry>4 bytes</entry>
570 <entry><acronym>SQL92</acronym> type</entry>
571 <entry>wide range</entry>
574 <entry>datetime</entry>
575 <entry>8 bytes</entry>
576 <entry>best general date and time</entry>
577 <entry>wide range, high precision</entry>
580 <entry>interval</entry>
581 <entry>12 bytes</entry>
582 <entry><acronym>SQL92</acronym> type</entry>
583 <entry>equivalent to timespan</entry>
586 <entry>reltime</entry>
587 <entry>4 bytes</entry>
588 <entry>original time interval</entry>
589 <entry>limited range, low precision</entry>
593 <entry>4 bytes</entry>
594 <entry><acronym>SQL92</acronym> type</entry>
595 <entry>wide range</entry>
598 <entry>timespan</entry>
599 <entry>12 bytes</entry>
600 <entry>best general time interval</entry>
601 <entry>wide range, high precision</entry>
604 <entry>timestamp</entry>
605 <entry>4 bytes</entry>
606 <entry><acronym>SQL92</acronym> type</entry>
607 <entry>limited range</entry>
613 <type>timestamp</type> is currently implemented separately from
614 <type>datetime</type>, although they share input and output routines.
619 <title><productname>Postgres</productname> Date/Time Ranges</title>
620 <titleabbrev>Ranges</titleabbrev>
624 <entry>Date/Time Type</entry>
625 <entry>Earliest</entry>
626 <entry>Latest</entry>
627 <entry>Resolution</entry>
632 <entry>abstime</entry>
633 <entry>1901-12-14</entry>
634 <entry>2038-01-19</entry>
639 <entry>4713 BC</entry>
640 <entry>32767 AD</entry>
644 <entry>datetime</entry>
645 <entry>4713 BC</entry>
646 <entry>1465001 AD</entry>
647 <entry>1 microsec to 14 digits</entry>
650 <entry>interval</entry>
651 <entry>-178000000 years</entry>
652 <entry>178000000 years</entry>
653 <entry>1 microsec</entry>
656 <entry>reltime</entry>
657 <entry>-68 years</entry>
658 <entry>+68 years</entry>
663 <entry>00:00:00.00</entry>
664 <entry>23:59:59.99</entry>
665 <entry>1 microsec</entry>
668 <entry>timespan</entry>
669 <entry>-178000000 years</entry>
670 <entry>178000000 years</entry>
671 <entry>1 microsec (14 digits)</entry>
674 <entry>timestamp</entry>
675 <entry>1901-12-14</entry>
676 <entry>2038-01-19</entry>
685 <title>SQL92 Conventions</title>
688 <productname>Postgres</productname> endeavors to be compatible with
689 <acronym>SQL92</acronym> definitions for typical usage.
690 However, the <acronym>SQL92</acronym> standard has an odd mix of date and
691 time types and capabilities. Two obvious problems are:
696 Although the <type>date</type> type
697 does not have an associated time zone, the
698 <type>time</type> type can or does.
704 The default time zone is specified as a constant integer offset
711 Time zones in the real world can have no meaning unless
712 associated with a date as well as a time
713 since the offset may vary through the year with daylight savings
718 To address these difficulties, <productname>Postgres</productname>
719 associates time zones only with date and time
720 types which contain both date and time,
721 and assumes local time for any type containing only
722 date or time. Further, time zone support is derived from
723 the underlying operating system
724 time zone capabilities, and hence can handle daylight savings time
725 and other expected behavior.
729 In future releases, the number of date/time types will decrease,
730 with the current implementation of
731 <type>datetime</type> becoming <type>timestamp</type>,
732 <type>timespan</type> becoming <type>interval</type>,
733 and (possibly) <type>abstime</type> and <type>reltime</type>
734 being deprecated in favor of <type>timestamp</type> and <type>interval</type>.
735 The more arcane features of the date/time definitions from
736 the <acronym>SQL92</acronym> standard are not likely to be pursued.
741 <title>Date/Time Styles</title>
744 Output formats can be set to one of four styles:
745 ISO-8601, <acronym>SQL</acronym> (Ingres), traditional
746 Postgres, and German.
749 <title><productname>Postgres</productname> Date Styles</title>
750 <titleabbrev>Styles</titleabbrev>
754 <entry>Style Specification</entry>
755 <entry>Description</entry>
756 <entry>Example</entry>
762 <entry>ISO-8601 standard</entry>
763 <entry>1997-12-17 07:37:16-08</entry>
766 <entry><acronym>SQL</acronym></entry>
767 <entry>Traditional style</entry>
768 <entry>12/17/1997 07:37:16.00 PST</entry>
771 <entry>Postgres</entry>
772 <entry>Original style</entry>
773 <entry>Wed Dec 17 07:37:16 1997 PST</entry>
776 <entry>German</entry>
777 <entry>Regional style</entry>
778 <entry>17.12.1997 07:37:16.00 PST</entry>
786 The <acronym>SQL</acronym> style has European and non-European (US) variants,
787 which determines whether month follows day or vica versa.
790 <title><productname>Postgres</productname> Date Order Conventions</title>
791 <titleabbrev>Order</titleabbrev>
795 <entry>Style Specification</entry>
796 <entry>Description</entry>
797 <entry>Example</entry>
802 <entry>European</entry>
803 <entry>Regional convention</entry>
804 <entry>17/12/1997 15:37:16.00 MET</entry>
807 <entry>NonEuropean</entry>
808 <entry>Regional convention</entry>
809 <entry>12/17/1997 07:37:16.00 PST</entry>
813 <entry>Regional convention</entry>
814 <entry>12/17/1997 07:37:16.00 PST</entry>
822 There are several ways to affect the appearance of date/time types:
824 <itemizedlist spacing="compact" mark="bullet">
827 The PGDATESTYLE environment variable used by the backend directly
828 on postmaster startup.
833 The PGDATESTYLE environment variable used by the frontend libpq
839 <command>SET DATESTYLE</command> <acronym>SQL</acronym> command.
846 For <productname>Postgres</productname> v6.4 (and earlier)
847 the default date/time style is
848 "non-European traditional Postgres".
849 In future releases, the default may become "ISO" (compatible with ISO-8601),
850 which alleviates date specification ambiguities and Y2K collation problems.
856 <title>Calendar</title>
859 <productname>Postgres</productname> uses Julian dates
860 for all date/time calculations. They have the nice property of correctly
861 predicting/calculating any date more recent than 4713BC
862 to far into the future, using the assumption that the length of the
863 year is 365.2425 days.
867 Date conventions before the 19th century make for interesting reading,
868 but are not consistant enough to warrant coding into a date/time handler.
873 <title>Time Zones</title>
876 <productname>Postgres</productname> obtains time zone support
877 from the underlying operating system for dates between 1902 and
878 2038 (near the typical date limits for Unix-style
879 systems). Outside of this range, all dates are assumed to be
880 specified and used in Universal Coordinated Time (UTC).
884 All dates and times are stored internally in Universal UTC,
885 alternately known as Greenwich Mean Time (GMT).
886 Times are converted to local time on the database server before being
887 sent to the client frontend, hence by default are in the server
892 There are several ways to affect the time zone behavior:
894 <itemizedlist spacing="compact" mark="bullet">
897 The TZ environment variable used by the backend directly
898 on postmaster startup as the default time zone.
903 The PGTZ environment variable set at the client used by libpq
904 to send time zone information to the backend upon connection.
909 The <acronym>SQL</acronym> command <command>SET TIME ZONE</command>
910 sets the time zone for the session.
913 </itemizedlist></para>
916 If an invalid time zone is specified,
917 the time zone becomes GMT (on most systems anyway).
922 <title>Date/Time Input</title>
925 General-use date and time is input using a wide range of
926 styles, including ISO-compatible, <acronym>SQL</acronym>-compatible,
927 traditional <productname>Postgres</productname>
928 and other permutations of date and time. In cases where interpretation
929 can be ambiguous (quite possible with many traditional styles of date
930 specification) <productname>Postgres</productname> uses a style setting
931 to resolve the ambiguity.
935 Most date and time types share code for data input. For those types
936 the input can have any of a wide variety of styles. For numeric date
938 European and US conventions can differ, and the proper interpretation
939 is obtained by using the <command>SET DATESTYLE</command>
940 command before entering data.
941 Note that the style setting does not preclude use of various styles for input;
942 it is used primarily to determine the output style and to resolve ambiguities.
946 The special values <literal>current</literal>,
947 <literal>infinity</literal> and <literal>-infinity</literal> are provided.
948 <literal>infinity</literal> specifies a time later than any other valid time, and
949 <literal>-infinity</literal> specifies a time earlier than any other valid time.
950 <literal>current</literal> indicates that the current time should be
951 substituted whenever this value appears in a computation.
956 <literal>now</literal>,
957 <literal>today</literal>,
958 <literal>yesterday</literal>,
959 <literal>tomorrow</literal>,
960 and <literal>epoch</literal>
961 can be used to specify time values.
962 <literal>now</literal>
963 means the current transaction time, and differs from
964 <literal>current</literal>
965 in that the current time is immediately substituted for it.
966 <literal>epoch</literal> means <literal>Jan 1 00:00:00 1970 GMT</literal>.
971 <title><productname>Postgres</productname> Date/Time Special Constants</title>
972 <titleabbrev>Constants</titleabbrev>
976 <entry>Constant</entry>
977 <entry>Description</entry>
982 <entry>current</entry>
983 <entry>Current transaction time, deferred</entry>
987 <entry>1970-01-01 00:00:00+00 (Unix system time zero)</entry>
990 <entry>infinity</entry>
991 <entry>Later than other valid times</entry>
994 <entry>-infinity</entry>
995 <entry>Earlier than other valid times</entry>
998 <entry>invalid</entry>
999 <entry>Illegal entry</entry>
1003 <entry>Current transaction time</entry>
1006 <entry>today</entry>
1007 <entry>Midnight today</entry>
1010 <entry>tomorrow</entry>
1011 <entry>Midnight tomorrow</entry>
1014 <entry>yesterday</entry>
1015 <entry>Midnight yesterday</entry>
1023 <table tocentry="1">
1024 <title><productname>Postgres</productname> Date Input</title>
1025 <titleabbrev>Date Inputs</titleabbrev>
1029 <entry>Example</entry>
1030 <entry>Description</entry>
1035 <entry>January 8, 1999</entry>
1036 <entry>Unambiguous text month</entry>
1039 <entry>1999-01-08</entry>
1040 <entry>ISO-8601</entry>
1043 <entry>1/8/1999</entry>
1044 <entry>US; read as August 1 in European mode</entry>
1047 <entry>8/1/1999</entry>
1048 <entry>European; read as August 1 in US mode</entry>
1051 <entry>1/18/1999</entry>
1052 <entry>US; read as January 18 in any mode</entry>
1055 <entry>1999.008</entry>
1056 <entry>Year and day of year</entry>
1059 <entry>19990108</entry>
1060 <entry>ISO-8601 year, month, day</entry>
1063 <entry>990108</entry>
1064 <entry>ISO-8601 year, month, day</entry>
1067 <entry>1999.008</entry>
1068 <entry>Year and day of year</entry>
1071 <entry>99008</entry>
1072 <entry>Year and day of year</entry>
1075 <entry>January 8, 99 BC</entry>
1076 <entry>Year 99 before the Christian Era</entry>
1084 <table tocentry="1">
1085 <title><productname>Postgres</productname> Month Abbreviations</title>
1086 <titleabbrev>Month Abbreviations</titleabbrev>
1090 <entry>Month</entry>
1091 <entry>Abbreviations</entry>
1096 <entry>April</entry>
1100 <entry>August</entry>
1104 <entry>December</entry>
1108 <entry>February</entry>
1112 <entry>January</entry>
1124 <entry>March</entry>
1128 <entry>November</entry>
1132 <entry>October</entry>
1136 <entry>September</entry>
1137 <entry>Sep, Sept</entry>
1145 The month <literal>May</literal> has no explicit abbreviation, for obvious reasons.
1151 <table tocentry="1">
1152 <title><productname>Postgres</productname> Day of Week Abbreviations</title>
1153 <titleabbrev>Day of Week Abbreviations</titleabbrev>
1158 <entry>Abbreviation</entry>
1163 <entry>Sunday</entry>
1167 <entry>Monday</entry>
1171 <entry>Tuesday</entry>
1172 <entry>Tue, Tues</entry>
1175 <entry>Wednesday</entry>
1176 <entry>Wed, Weds</entry>
1179 <entry>Thursday</entry>
1180 <entry>Thu, Thur, Thurs</entry>
1183 <entry>Friday</entry>
1187 <entry>Saturday</entry>
1196 <table tocentry="1">
1197 <title><productname>Postgres</productname> Time Input</title>
1198 <titleabbrev>Time Inputs</titleabbrev>
1202 <entry>Example</entry>
1203 <entry>Description</entry>
1208 <entry>04:05:06.789</entry>
1209 <entry>ISO-8601, with all time fields</entry>
1212 <entry>04:05:06</entry>
1213 <entry>ISO-8601</entry>
1216 <entry>04:05</entry>
1217 <entry>ISO-8601</entry>
1220 <entry>040506</entry>
1221 <entry>ISO-8601</entry>
1224 <entry>04:05 AM</entry>
1225 <entry>Same as 04:05; AM does not affect value</entry>
1228 <entry>04:05 PM</entry>
1229 <entry>Same as 16:05; input hour must be <= 12</entry>
1233 <entry>Same as 00:00:00</entry>
1237 <entry>Same as 00:00:00</entry>
1240 <entry>allballs</entry>
1241 <entry>Same as 00:00:00</entry>
1249 <table tocentry="1">
1250 <title><productname>Postgres</productname> Time Zone Input</title>
1251 <titleabbrev>Time Zone Inputs</titleabbrev>
1255 <entry>Time Zone</entry>
1256 <entry>Description</entry>
1262 <entry>Pacific Standard Time</entry>
1265 <entry>-8:00</entry>
1266 <entry>ISO-8601 offset for PST</entry>
1270 <entry>ISO-8601 offset for PST</entry>
1274 <entry>ISO-8601 offset for PST</entry>
1282 See <xref linkend="datetime-appendix-title" endterm="datetime-appendix-title">
1283 for details on time zones recognized by <productname>Postgres</productname>.
1287 If the compiler option USE_AUSTRALIAN_RULES is set
1288 then <literal>EST</literal> refers to Australia Eastern Std Time,
1289 which has an offset of +10:00 hours from UTC.
1295 Australian time zones and their naming variants
1296 account for fully one quarter of all time zones in the
1297 <productname>Postgres</productname> time zone lookup table.
1303 <title>datetime</title>
1306 General-use date and time is input using a wide range of
1307 styles, including ISO-compatible, <acronym>SQL</acronym>-compatible, traditional
1308 <productname>Postgres</productname> (see section on "absolute time")
1309 and other permutations of date and time. Output styles can be ISO-compatible,
1310 <acronym>SQL</acronym>-compatible, or traditional
1311 <productname>Postgres</productname>, with the default set to be compatible
1312 with <productname>Postgres</productname> v6.0.
1316 <type>datetime</type> is specified using the following syntax:
1319 Year-Month-Day [ Hour : Minute : Second ] [AD,BC] [ Timezone ]
1320 YearMonthDay [ Hour : Minute : Second ] [AD,BC] [ Timezone ]
1321 Month Day [ Hour : Minute : Second ] Year [AD,BC] [ Timezone ]
1323 Year is 4013 BC, ..., very large
1324 Month is Jan, Feb, ..., Dec or 1, 2, ..., 12
1325 Day is 1, 2, ..., 31
1326 Hour is 00, 02, ..., 23
1327 Minute is 00, 01, ..., 59
1328 Second is 00, 01, ..., 59 (60 for leap second)
1329 Timezone is 3 characters or ISO offset to GMT
1334 Valid dates are from Nov 13 00:00:00 4013 BC GMT to far into the future.
1335 Timezones are either three characters (e.g. "GMT" or "PST") or ISO-compatible
1336 offsets to GMT (e.g. "-08" or "-08:00" when in Pacific Standard Time).
1337 Dates are stored internally in Greenwich Mean Time. Input and output routines
1338 translate time to the local time zone of the server.
1342 <title><type>timespan</type></title>
1345 General-use time span is input using a wide range of
1346 syntaxes, including ISO-compatible, <acronym>SQL</acronym>-compatible,
1348 <productname>Postgres</productname> (see section on "relative time")
1349 and other permutations of time span. Output formats can be ISO-compatible,
1350 <acronym>SQL</acronym>-compatible, or traditional
1351 <productname>Postgres</productname>,
1352 with the default set to be <productname>Postgres</productname>-compatible.
1353 Months and years are a "qualitative" time interval, and are stored separately
1354 from the other "quantitative" time intervals such as day or hour.
1355 For date arithmetic,
1356 the qualitative time units are instantiated in the context of the
1357 relevant date or time.
1361 Time span is specified with the following syntax:
1364 Quantity Unit [Quantity Unit...] [Direction]
1365 @ Quantity Unit [Direction]
1367 Quantity is ..., <literal>-1</literal>, <literal>0</literal>, <literal>1</literal>, <literal>2</literal>, ...
1368 Unit is <literal>second</literal>, <literal>minute</literal>, <literal>hour</literal>, <literal>day</literal>, <literal>week</literal>, <literal>month</literal>, <literal>year</literal>,
1369 <literal>decade</literal>, <literal>century</literal>, <literal>millenium</literal>, or abbreviations or plurals of these units.
1370 Direction is <literal>ago</literal>.
1376 <title>abstime</title>
1379 Absolute time (<type>abstime</type>) is a limited-range (+/- 68 years) and
1380 limited-precision (1 sec)
1381 date data type. <type>datetime</type> may be preferred, since it
1382 covers a larger range with greater precision.
1386 Absolute time is specified using the following syntax:
1389 Month Day [ Hour : Minute : Second ] Year [ Timezone ]
1391 Month is Jan, Feb, ..., Dec
1392 Day is 1, 2, ..., 31
1393 Hour is 01, 02, ..., 24
1394 Minute is 00, 01, ..., 59
1395 Second is 00, 01, ..., 59
1396 Year is 1901, 1902, ..., 2038
1401 Valid dates are from <literal>Dec 13 20:45:53 1901 GMT</literal>
1402 to <literal>Jan 19 03:14:04 2038 GMT</literal>.
1405 <title>Historical Note</title>
1407 As of Version 3.0, times are no longer read and written
1408 using Greenwich Mean Time; the input and output routines default to
1409 the local time zone.</para>
1412 All special values allowed for <type>datetime</type> are also
1413 allowed for "absolute time".
1419 <title>reltime</title>
1422 Relative time <type>reltime</type> is a limited-range (+/- 68 years)
1423 and limited-precision (1 sec) time span data type.
1424 <type>timespan</type> should be preferred, since it
1425 covers a larger range with greater precision and, more importantly,
1426 can distinguish between
1427 relative units (months and years) and quantitative units (days, hours, etc).
1429 must force months to be exactly 30 days, so time arithmetic does not
1430 always work as expected.
1431 For example, adding one reltime <literal>year</literal> to abstime <literal>today</literal> does not
1432 produce today's date one year from
1433 now, but rather a date 360 days from today.
1437 <type>reltime</type> shares input and output routines with the other
1439 The section on <type>timespan</type> covers this in more detail.
1445 <title><type>timestamp</type></title>
1448 This is currently a limited-range absolute time which closely resembles the
1450 data type. It shares the general input parser with the other date/time types.
1451 In future releases this type will absorb the capabilities of the
1452 <type>datetime</type> type
1453 and will move toward <acronym>SQL92</acronym> compliance.
1457 <type>timestamp</type> is specified using the same syntax as for
1458 <type>datetime</type>.
1463 <title><type>interval</type></title>
1466 <type>interval</type> is an <acronym>SQL92</acronym> data type which is
1467 currently mapped to the <type>timespan</type>
1468 <productname>Postgres</productname> data type.
1473 <title>tinterval</title>
1476 Time ranges are specified as:
1479 [ 'abstime' 'abstime']
1481 abstime is a time in the absolute time format.
1484 Special abstime values such as
1485 <literal>current', <literal>infinity' and <literal>-infinity' can be used.</literal></literal></literal>
1491 <title>Boolean Type</title>
1494 <productname>Postgres</productname> supports <type>bool</type> as
1495 the <acronym>SQL3</acronym> boolean type.
1496 <type>bool</type> can have one of only two states: 'true' or 'false'.
1497 A third state, 'unknown', is not
1498 implemented and is not suggested in <acronym>SQL3</acronym>;
1499 <acronym>NULL</acronym> is an
1500 effective substitute. <type>bool</type> can be used in any boolean expression,
1501 and boolean expressions
1502 always evaluate to a result compatible with this type.</para>
1505 <type>bool</type> uses 1 byte of storage.
1509 <table tocentry="1">
1510 <title><productname>Postgres</productname> Boolean Type</title>
1511 <titleabbrev>Booleans</titleabbrev>
1515 <entry>State</entry>
1516 <entry>Output</entry>
1517 <entry>Input</entry>
1524 <entry>TRUE, 't', 'true', 'y', 'yes', '1'</entry>
1527 <entry>False</entry>
1529 <entry>FALSE, 'f', 'false', 'n', 'no', '0'</entry>
1538 <title>Geometric Types</title>
1541 Geometric types represent two-dimensional spatial objects.
1542 The most fundamental type,
1543 the point, forms the basis for all of the other types.
1547 <table tocentry="1">
1548 <title><productname>Postgres</productname> Geometric Types</title>
1549 <titleabbrev>Geometrics</titleabbrev>
1553 <entry>Geometric Type</entry>
1554 <entry>Storage</entry>
1555 <entry>Representation</entry>
1556 <entry>Description</entry>
1561 <entry>point</entry>
1562 <entry>16 bytes</entry>
1563 <entry>(x,y)</entry>
1564 <entry>Point in space</entry>
1568 <entry>32 bytes</entry>
1569 <entry>((x1,y1),(x2,y2))</entry>
1570 <entry>Infinite line</entry>
1574 <entry>32 bytes</entry>
1575 <entry>((x1,y1),(x2,y2))</entry>
1576 <entry>Finite line segment</entry>
1580 <entry>32 bytes</entry>
1581 <entry>((x1,y1),(x2,y2))</entry>
1582 <entry>Rectangular box</entry>
1586 <entry>4+32n bytes</entry>
1587 <entry>((x1,y1),...)</entry>
1588 <entry>Closed path (similar to polygon)</entry>
1592 <entry>4+32n bytes</entry>
1593 <entry>[(x1,y1),...]</entry>
1594 <entry>Open path</entry>
1597 <entry>polygon</entry>
1598 <entry>4+32n bytes</entry>
1599 <entry>((x1,y1),...)</entry>
1600 <entry>Polygon (similar to closed path)</entry>
1603 <entry>circle</entry>
1604 <entry>24 bytes</entry>
1605 <entry><(x,y),r></entry>
1606 <entry>Circle (center and radius)</entry>
1614 A rich set of functions and operators is available to perform various geometric
1615 operations such as scaling, translation, rotation, and determining
1620 <title>Point</title>
1623 Points are the fundamental two-dimensional building block for geometric types.
1627 <type>point</type> is specified using the following syntax:
1633 x is the x-axis coordinate as a floating point number
1634 y is the y-axis coordinate as a floating point number
1640 <title>Line Segment</title>
1643 Line segments (lseg) are represented by pairs of points.
1647 <type>lseg</type> is specified using the following syntax:
1649 ( ( x1 , y1 ) , ( x2 , y2 ) )
1650 ( x1 , y1 ) , ( x2 , y2 )
1653 (x1,y1) and (x2,y2) are the endpoints of the segment
1662 Boxes are represented by pairs of points which are opposite
1667 <type>box</type> is specified using the following syntax:
1670 ( ( x1 , y1 ) , ( x2 , y2 ) )
1671 ( x1 , y1 ) , ( x2 , y2 )
1674 (x1,y1) and (x2,y2) are opposite corners
1677 Boxes are output using the first syntax.
1678 The corners are reordered on input to store
1679 the lower left corner first and the upper right corner last.
1680 Other corners of the box can be entered, but the lower
1681 left and upper right corners are determined from the input and stored.
1689 Paths are represented by connected sets of points. Paths can be "open", where
1690 the first and last points in the set are not connected, and "closed",
1691 where the first and last point are connected. Functions
1692 <function>popen(p)</function>
1694 <function>pclose(p)</function>
1695 are supplied to force a path to be open or closed, and functions
1696 <function>isopen(p)</function>
1698 <function>isclosed(p)</function>
1699 are supplied to select either type in a query.
1703 path is specified using the following syntax:
1706 ( ( x1 , y1 ) , ... , ( xn , yn ) )
1707 [ ( x1 , y1 ) , ... , ( xn , yn ) ]
1708 ( x1 , y1 ) , ... , ( xn , yn )
1709 ( x1 , y1 , ... , xn , yn )
1710 x1 , y1 , ... , xn , yn
1712 (x1,y1),...,(xn,yn) are points 1 through n
1713 a leading "[" indicates an open path
1714 a leading "(" indicates a closed path
1716 Paths are output using the first syntax.
1717 Note that <productname>Postgres</productname> versions prior to
1718 v6.1 used a format for paths which had a single leading parenthesis,
1720 an integer count of the number of points, then the list of points followed by a
1721 closing parenthesis.
1722 The built-in function <function>upgradepath</function> is supplied to convert
1723 paths dumped and reloaded from pre-v6.1 databases.
1728 <title>Polygon</title>
1731 Polygons are represented by sets of points. Polygons should probably be
1732 considered equivalent to closed paths, but are stored differently
1733 and have their own set of support routines.
1737 <type>polygon</type> is specified using the following syntax:
1740 ( ( x1 , y1 ) , ... , ( xn , yn ) )
1741 ( x1 , y1 ) , ... , ( xn , yn )
1742 ( x1 , y1 , ... , xn , yn )
1743 x1 , y1 , ... , xn , yn
1745 (x1,y1),...,(xn,yn) are points 1 through n
1748 Polygons are output using the first syntax.
1749 Note that <productname>Postgres</productname> versions prior to
1750 v6.1 used a format for polygons which had a single leading parenthesis, the list
1751 of x-axis coordinates, the list of y-axis coordinates,
1752 followed by a closing parenthesis.
1753 The built-in function <function>upgradepoly</function> is supplied to convert
1754 polygons dumped and reloaded from pre-v6.1 databases.
1759 <title>Circle</title>
1762 Circles are represented by a center point and a radius.
1766 circle is specified using the following syntax:
1774 (x,y) is the center of the circle
1775 r is the radius of the circle
1778 Circles are output using the first syntax.
1785 <title>IP Version 4 Networks and Host Addresses</title>
1788 The <type>cidr</type> type stores networks specified
1789 in <acronym>CIDR</acronym> (Classless Inter-Domain Routing) notation.
1790 The <type>inet</type> type stores hosts and networks in CIDR notation using a simple
1791 variation in representation to represent simple host TCP/IP addresses.
1795 <table tocentry="1">
1796 <title><productname>Postgres</productname>IP Version 4 Types</title>
1797 <titleabbrev>IPV4</titleabbrev>
1801 <entry>IPV4 Type</entry>
1802 <entry>Storage</entry>
1803 <entry>Description</entry>
1804 <entry>Range</entry>
1810 <entry>variable</entry>
1811 <entry>CIDR networks</entry>
1812 <entry>Valid IPV4 CIDR blocks</entry>
1816 <entry>variable</entry>
1817 <entry>nets and hosts</entry>
1818 <entry>Valid IPV4 CIDR blocks</entry>
1829 The <type>cidr</type> type holds a CIDR network.
1830 The format for specifying classless networks is <replaceable class="parameter">x.x.x.x/y</replaceable>
1831 where <replaceable class="parameter">x.x.x.x</replaceable> is the
1832 network and <replaceable class="parameter">/y</replaceable> is the number of bits in the netmask.
1833 If <replaceable class="parameter">/y</replaceable> omitted, it is calculated using assumptions from
1834 the older classfull naming system except that it is extended to include at least
1835 all of the octets in the input.
1839 Here are some examples:
1841 <table tocentry="1">
1842 <title><productname>Postgres</productname>IP Types Examples</title>
1846 <entry>CIDR Input</entry>
1847 <entry>CIDR Displayed</entry>
1852 <entry>192.168.1</entry>
1853 <entry>192.168.1/24</entry>
1856 <entry>192.168</entry>
1857 <entry>192.168.0/24</entry>
1860 <entry>128.1</entry>
1861 <entry>128.1/16</entry>
1865 <entry>128.0/16</entry>
1868 <entry>128.1.2</entry>
1869 <entry>128.1.2/24</entry>
1872 <entry>10.1.2</entry>
1873 <entry>10.1.2/24</entry>
1877 <entry>10.1/16</entry>
1890 <title id="inet-type"><type>inet</type></title>
1893 The <type>inet</type> type is designed to hold, in one field, all of the information
1894 about a host including the CIDR-style subnet that it is in.
1895 Note that if you want to store proper CIDR networks,
1896 you should use the <type>cidr</type> type.
1897 The <type>inet</type> type is similar to the <type>cidr</type> type except that the bits in the
1898 host part can be non-zero.
1899 Functions exist to extract the various elements of the field.
1903 The input format for this function is
1904 <replaceable class="parameter">x.x.x.x/y</replaceable>
1905 where <replaceable class="parameter">x.x.x.x</replaceable> is
1906 an internet host and <replaceable class="parameter">y</replaceable>
1907 is the number of bits in the netmask.
1908 If the <replaceable class="parameter">/y</replaceable> part is left off,
1909 it is treated as <literal>/32</literal>.
1910 On output, the <replaceable class="parameter">/y</replaceable> part is not printed
1911 if it is <literal>/32</literal>.
1912 This allows the type to be used as a straight host type by just leaving off
1919 <!-- Keep this comment at the end of the file
1924 sgml-minimize-attributes:nil
1925 sgml-always-quote-attributes:t
1928 sgml-parent-document:nil
1929 sgml-default-dtd-file:"./reference.ced"
1930 sgml-exposed-tags:nil
1931 sgml-local-catalogs:"/usr/lib/sgml/catalog"
1932 sgml-local-ecat-files:nil