1 <chapter id="datatype">
2 <title id="datatype-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> command.
19 In the context of data types, the following sections will discuss
20 <acronym>SQL</acronym> standards compliance, porting issues, and usage.
22 Some <productname>Postgres</productname> types correspond directly to
23 <acronym>SQL92</acronym>-compatible types. In other
24 cases, data types defined by <acronym>SQL92</acronym> syntax are mapped directly
25 into native <productname>Postgres</productname> types.
27 Many of the built-in types have obvious external formats. However, several
28 types are either unique to <productname>Postgres</productname>,
29 such as open and closed paths, or have
30 several possibilities for formats, such as the date and time types.
35 <title><productname>Postgres</productname> Data Types</title>
36 <titleabbrev>Data Types</titleabbrev>
40 <entry><productname>Postgres</productname> Type</entry>
41 <entry><acronym>SQL92</acronym> or <acronym>SQL3</acronym> Type</entry>
42 <entry>Description</entry>
48 <entry>boolean</entry>
49 <entry>logical boolean (true/false)</entry>
54 <entry>rectangular box in 2D plane</entry>
57 <entry>char(n)</entry>
58 <entry>character(n)</entry>
59 <entry>fixed-length character string</entry>
64 <entry>IP version 4 network or host address</entry>
69 <entry>circle in 2D plane</entry>
74 <entry>calendar date without time of day</entry>
77 <entry>decimal</entry>
78 <entry>decimal(p,s)</entry>
79 <entry>exact numeric for p <= 9, s = 0</entry>
83 <entry>float(<replaceable>p</replaceable>), <replaceable>p</replaceable> < 7</entry>
84 <entry>floating-point number with precision <replaceable>p</replaceable></entry>
88 <entry>float(<replaceable>p</replaceable>), 7 <= <replaceable>p</replaceable> < 16</entry>
89 <entry>floating-point number with precision <replaceable>p</replaceable></entry>
94 <entry>IP version 4 network or host address</entry>
98 <entry>smallint</entry>
99 <entry>signed two-byte integer</entry>
103 <entry>int, integer</entry>
104 <entry>signed 4-byte integer</entry>
109 <entry>signed 8-byte integer</entry>
112 <entry>interval</entry>
113 <entry>interval</entry>
114 <entry>general-use time span</entry>
119 <entry>infinite line in 2D plane</entry>
124 <entry>line segment in 2D plane</entry>
128 <entry>decimal(9,2)</entry>
129 <entry>US-style currency</entry>
132 <entry>numeric</entry>
133 <entry>numeric(p,s)</entry>
134 <entry>exact numeric for p == 9, s = 0</entry>
139 <entry>open and closed geometric path in 2D plane</entry>
144 <entry>geometric point in 2D plane</entry>
147 <entry>polygon</entry>
149 <entry>closed geometric path in 2D plane</entry>
152 <entry>serial</entry>
154 <entry>unique id for indexing and cross-reference</entry>
159 <entry>time of day</entry>
162 <entry>timetz</entry>
163 <entry>time with time zone</entry>
164 <entry>time of day, including time zone</entry>
167 <entry>timestamp</entry>
168 <entry>timestamp with time zone</entry>
169 <entry>date/time</entry>
172 <entry>varchar(n)</entry>
173 <entry>character varying(n)</entry>
174 <entry>variable-length character string</entry>
184 The <type>cidr</type> and <type>inet</type> types are designed to handle any IP type
185 but only ipv4 is handled in the current implementation.
186 Everything here that talks about ipv4 will apply to ipv6 in a
194 <title><productname>Postgres</productname> Function Constants</title>
195 <titleabbrev>Constants</titleabbrev>
199 <entry><productname>Postgres</productname> Function</entry>
200 <entry><acronym>SQL92</acronym> Constant</entry>
201 <entry>Description</entry>
206 <entry>getpgusername()</entry>
207 <entry>current_user</entry>
208 <entry>user name in current session</entry>
211 <entry>date('now')</entry>
212 <entry>current_date</entry>
213 <entry>date of current transaction</entry>
216 <entry>time('now')</entry>
217 <entry>current_time</entry>
218 <entry>time of current transaction</entry>
221 <entry>timestamp('now')</entry>
222 <entry>current_timestamp</entry>
223 <entry>date and time of current transaction</entry>
231 <productname>Postgres</productname> has features at the forefront of
232 <acronym>ORDBMS</acronym> development. In addition to
233 <acronym>SQL3</acronym> conformance, substantial portions
234 of <acronym>SQL92</acronym> are also supported.
235 Although we strive for <acronym>SQL92</acronym> compliance,
236 there are some aspects of the standard
237 which are ill considered and which should not live through subsequent standards.
238 <productname>Postgres</productname> will not make great efforts to
239 conform to these features; however, these tend to apply in little-used
240 or obsure cases, and a typical user is not likely to run into them.
244 Most of the input and output functions corresponding to the
245 base types (e.g., integers and floating point numbers) do some
247 Some of the operators and functions (e.g.,
248 addition and multiplication) do not perform run-time error-checking in the
249 interests of improving execution speed.
250 On some systems, for example, the numeric operators for some data types may
251 silently underflow or overflow.
255 Some of the input and output functions are not invertible. That is,
256 the result of an output function may lose precision when compared to
261 The original <productname>Postgres</productname> v4.2 code received from
262 Berkeley rounded all double precision floating point results to six digits for
263 output. Starting with v6.1, floating point numbers are allowed to retain
264 most of the intrinsic precision of the type (typically 15 digits for doubles,
265 6 digits for 4-byte floats).
266 Other types with underlying floating point fields (e.g. geometric
267 types) carry similar precision.
273 <title>Numeric Types</title>
276 Numeric types consist of two- and four-byte integers and four- and eight-byte
277 floating point numbers.
282 <title><productname>Postgres</productname> Numeric Types</title>
283 <titleabbrev>Numerics</titleabbrev>
287 <entry>Numeric Type</entry>
288 <entry>Storage</entry>
289 <entry>Description</entry>
295 <entry>decimal</entry>
296 <entry>variable</entry>
297 <entry>User-specified precision</entry>
298 <entry>no limit</entry>
301 <entry>float4</entry>
302 <entry>4 bytes</entry>
303 <entry>Variable-precision</entry>
304 <entry>6 decimal places</entry>
307 <entry>float8</entry>
308 <entry>8 bytes</entry>
309 <entry>Variable-precision</entry>
310 <entry>15 decimal places</entry>
314 <entry>2 bytes</entry>
315 <entry>Fixed-precision</entry>
316 <entry>-32768 to +32767</entry>
320 <entry>4 bytes</entry>
321 <entry>Usual choice for fixed-precision</entry>
322 <entry>-2147483648 to +2147483647</entry>
326 <entry>8 bytes</entry>
327 <entry>Very large range fixed-precision</entry>
328 <entry>+/- > 18 decimal places</entry>
331 <entry>numeric</entry>
332 <entry>variable</entry>
333 <entry>User-specified precision</entry>
334 <entry>no limit</entry>
337 <entry>serial</entry>
338 <entry>4 bytes</entry>
339 <entry>Identifer or cross-reference</entry>
340 <entry>0 to +2147483647</entry>
348 The numeric types have a full set of corresponding arithmetic operators and
349 functions. Refer to <xref endterm="math-opers" linkend="math-opers">
350 and <xref endterm="math-funcs" linkend="math-funcs"> for more information.
354 The <type>int8</type> type may not be available on all platforms since
355 it relies on compiler support for this.
359 <title>The Serial Type</title>
362 The <type>serial</type> type is a special-case type constructed by
363 <productname>Postgres</productname> from other existing components.
364 It is typically used to create unique identifiers for table entries.
365 In the current implementation, specifying
368 CREATE TABLE <replaceable class="parameter">tablename</replaceable> (<replaceable class="parameter">colname</replaceable> SERIAL);
371 is equivalent to specifying:
374 CREATE SEQUENCE <replaceable class="parameter">tablename</replaceable>_<replaceable class="parameter">colname</replaceable>_seq;
375 CREATE TABLE <replaceable class="parameter">tablename</replaceable>
376 (<replaceable class="parameter">colname</replaceable> INT4 DEFAULT nextval('<replaceable class="parameter">tablename</replaceable>_<replaceable class="parameter">colname</replaceable>_seq');
377 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>);
382 The implicit sequence created for the <type>serial</type> type will
383 <emphasis>not</emphasis> be automatically removed when the
388 Implicit sequences supporting the <type>serial</type> are
389 not automatically dropped when a table containing a serial type
390 is dropped. So, the following commands executed in order will likely fail:
393 CREATE TABLE <replaceable class="parameter">tablename</replaceable> (<replaceable class="parameter">colname</replaceable> SERIAL);
394 DROP TABLE <replaceable class="parameter">tablename</replaceable>;
395 CREATE TABLE <replaceable class="parameter">tablename</replaceable> (<replaceable class="parameter">colname</replaceable> SERIAL);
398 The sequence will remain in the database until explicitly dropped using
399 <command>DROP SEQUENCE</command>.
405 <title>Monetary Type</title>
408 <title>Obsolete Type</title>
410 The <type>money</type> is now deprecated. Use <type>numeric</type>
411 or <type>decimal</type> instead. The money type may become a
412 locale-aware layer over the numeric type in a future release.
417 The <type>money</type> type supports US-style currency with
418 fixed decimal point representation.
419 If <productname>Postgres</productname> is compiled with USE_LOCALE
420 then the money type should use the monetary conventions defined for
421 <citetitle>locale(7)</citetitle>.
426 <title><productname>Postgres</productname> Monetary Types</title>
427 <titleabbrev>Money</titleabbrev>
431 <entry>Monetary Type</entry>
432 <entry>Storage</entry>
433 <entry>Description</entry>
440 <entry>4 bytes</entry>
441 <entry>Fixed-precision</entry>
442 <entry>-21474836.48 to +21474836.47</entry>
451 will replace the money type, and should be preferred.
457 <title>Character Types</title>
460 <acronym>SQL92</acronym> defines two primary character types:
461 <type>char</type> and <type>varchar</type>.
462 <productname>Postgres</productname> supports these types, in
463 addition to the more general <type>text</type> type,
464 which unlike <type>varchar</type>
465 does not require an explicit declared upper
466 limit on the size of the field.
471 <title><productname>Postgres</productname> Character Types</title>
472 <titleabbrev>Characters</titleabbrev>
476 <entry>Character Type</entry>
477 <entry>Storage</entry>
478 <entry>Recommendation</entry>
479 <entry>Description</entry>
485 <entry>1 byte</entry>
486 <entry><acronym>SQL92</acronym>-compatible</entry>
487 <entry>Single character</entry>
490 <entry>char(n)</entry>
491 <entry>(4+n) bytes</entry>
492 <entry><acronym>SQL92</acronym>-compatible</entry>
493 <entry>Fixed-length blank padded</entry>
497 <entry>(4+x) bytes</entry>
498 <entry>Best choice</entry>
499 <entry>Variable-length</entry>
502 <entry>varchar(n)</entry>
503 <entry>(4+n) bytes</entry>
504 <entry><acronym>SQL92</acronym>-compatible</entry>
505 <entry>Variable-length with limit</entry>
513 There is one other fixed-length character type in <productname>Postgres</productname>.
514 The <type>name</type> type
515 only has one purpose and that is for storage of internal catalog
517 It is not intended for use by the general user.
518 Its length is currently defined as 32 bytes (31 characters plus terminator)
519 but should be reference using NAMEDATALEN.
520 The length is set at compile time (and is therefore adjustable for
521 special uses); the default maximum length may change in a future release.
526 <title><productname>Postgres</productname> Specialty Character Type</title>
527 <titleabbrev>Specialty Characters</titleabbrev>
531 <entry>Character Type</entry>
532 <entry>Storage</entry>
533 <entry>Description</entry>
539 <entry>32 bytes</entry>
540 <entry>Thirty-one character internal type</entry>
550 <title>Date/Time Types</title>
553 <productname>PostgreSQL</productname> supports the full set of
554 <acronym>SQL</acronym> date and time types.
559 <title><productname>PostgreSQL</productname> Date/Time Types</title>
560 <titleabbrev>Date/Time</titleabbrev>
565 <entry>Description</entry>
566 <entry>Storage</entry>
567 <entry>Earliest</entry>
568 <entry>Latest</entry>
569 <entry>Resolution</entry>
574 <entry><type>timestamp</type></entry>
575 <entry>for data containing both date and time</entry>
576 <entry>8 bytes</entry>
577 <entry>4713 BC</entry>
578 <entry>AD 1465001</entry>
579 <entry>1 microsec / 14 digits</entry>
582 <entry><type>timestamp with time zone</type></entry>
583 <entry>date and time including time zone</entry>
584 <entry>8 bytes</entry>
585 <entry>1903 AD</entry>
586 <entry>2037 AD</entry>
587 <entry>1 microsec / 14 digits</entry>
590 <entry><type>interval</type></entry>
591 <entry>for time intervals</entry>
592 <entry>12 bytes</entry>
593 <entry>-178000000 years</entry>
594 <entry>178000000 years</entry>
595 <entry>1 mircosecond</entry>
598 <entry><type>date</type></entry>
599 <entry>for data containing only dates</entry>
600 <entry>4 bytes</entry>
601 <entry>4713 BC</entry>
602 <entry>32767 AD</entry>
606 <entry><type>time</type></entry>
607 <entry>for data containing only times of the day</entry>
608 <entry>4 bytes</entry>
609 <entry>00:00:00.00</entry>
610 <entry>23:59:59.99</entry>
611 <entry>1 microsecond</entry>
614 <entry><type>time with time zone</type></entry>
615 <entry>times of the day</entry>
616 <entry>4 bytes</entry>
617 <entry>00:00:00.00+12</entry>
618 <entry>23:59:59.99-12</entry>
619 <entry>1 microsecond</entry>
627 To ensure compatibility to earlier versions of <productname>PostgreSQL</productname>
628 we also continue to provide <type>datetime</type> (equivalent to <type>timestamp</type>) and
629 <type>timespan</type> (equivalent to <type>interval</type>). The types <type>abstime</type>
630 and <type>reltime</type> are lower precision types which are used internally.
631 You are discouraged from using any of these types in new
632 applications and are encouraged to move any old
633 ones over when appropriate. Any or all of these types might disappear in a future release.
640 <title>Date/Time Input</title>
643 Date and time input is accepted in almost any reasonable format, including
644 <acronym>ISO-8601</acronym>, <acronym>SQL</acronym>-compatible,
645 traditional <productname>Postgres</productname>, and others.
646 The ordering of month and day in date input can be ambiguous, therefore a setting
647 exists to specify how it should be interpreted. The command
648 <literal>SET DateStyle TO 'US'</literal> or <literal>SET DateStyle TO 'NonEuropean'</literal>
649 specifies the variant <quote>month before day</quote>, the command
650 <literal>SET DateStyle TO 'European'</literal> sets the variant
651 <quote>day before month</quote>. The <literal>US</literal> style
652 is the default but this default can be changed at compile time or at run time.
656 See <xref endterm="datetime-appendix-title" linkend="datetime-appendix-title">
657 for the exact parsing rules of date/time input and for the recognized time zones.
661 Remember that any date or time input needs to be enclosed into single quotes,
668 The following are possible inputs for the <type>date</type> type.
671 <title><productname>PostgreSQL</productname> Date Input</title>
672 <titleabbrev>Date Inputs</titleabbrev>
676 <entry>Example</entry>
677 <entry>Description</entry>
682 <entry>January 8, 1999</entry>
683 <entry>Unambiguous</entry>
686 <entry>1999-01-08</entry>
687 <entry>ISO-8601 format, preferred</entry>
690 <entry>1/8/1999</entry>
691 <entry>US; read as August 1 in European mode</entry>
694 <entry>8/1/1999</entry>
695 <entry>European; read as August 1 in US mode</entry>
698 <entry>1/18/1999</entry>
699 <entry>US; read as January 18 in any mode</entry>
702 <entry>1999.008</entry>
703 <entry>Year and day of year</entry>
706 <entry>19990108</entry>
707 <entry>ISO-8601 year, month, day</entry>
710 <entry>990108</entry>
711 <entry>ISO-8601 year, month, day</entry>
714 <entry>1999.008</entry>
715 <entry>Year and day of year</entry>
719 <entry>Year and day of year</entry>
722 <entry>January 8, 99 BC</entry>
723 <entry>Year 99 before the common era</entry>
732 <title><productname>PostgreSQL</productname> Month Abbreviations</title>
733 <titleabbrev>Month Abbreviations</titleabbrev>
738 <entry>Abbreviations</entry>
747 <entry>August</entry>
751 <entry>December</entry>
755 <entry>February</entry>
759 <entry>January</entry>
775 <entry>November</entry>
779 <entry>October</entry>
783 <entry>September</entry>
784 <entry>Sep, Sept</entry>
792 The month <literal>May</literal> has no explicit abbreviation, for obvious reasons.
799 <title><productname>PostgreSQL</productname> Day of Week Abbreviations</title>
800 <titleabbrev>Day of Week Abbreviations</titleabbrev>
805 <entry>Abbreviation</entry>
810 <entry>Sunday</entry>
814 <entry>Monday</entry>
818 <entry>Tuesday</entry>
819 <entry>Tue, Tues</entry>
822 <entry>Wednesday</entry>
823 <entry>Wed, Weds</entry>
826 <entry>Thursday</entry>
827 <entry>Thu, Thur, Thurs</entry>
830 <entry>Friday</entry>
834 <entry>Saturday</entry>
846 The following are valid <type>time</type> inputs.
849 <title><productname>PostgreSQL</productname> Time Input</title>
850 <titleabbrev>Time Inputs</titleabbrev>
854 <entry>Example</entry>
855 <entry>Description</entry>
860 <entry>04:05:06.789</entry>
861 <entry>ISO-8601</entry>
864 <entry>04:05:06</entry>
865 <entry>ISO-8601</entry>
869 <entry>ISO-8601</entry>
872 <entry>040506</entry>
873 <entry>ISO-8601</entry>
876 <entry>04:05 AM</entry>
877 <entry>Same as 04:05; AM does not affect value</entry>
880 <entry>04:05 PM</entry>
881 <entry>Same as 16:05; input hour must be <= 12</entry>
885 <entry>Same as 00:00:00</entry>
889 <entry>Same as 00:00:00</entry>
892 <entry>allballs</entry>
893 <entry>Same as 00:00:00</entry>
902 <title>time with time zone</title>
904 This type is defined by SQL92, but the definition exhibits
905 fundamental deficiencies which renders the type near useless. In
906 most cases, a combination of <type>date</type>,
907 <type>time</type>, and <type>timestamp with time zone</type>
908 should provide a complete range of date/time functionality
909 required by an application.
913 <type>time with time zone</type> accepts all input also legal
914 for the <type>time</type> type, appended with a legal time zone,
918 <title><productname>PostgreSQL</productname> Time With Time
920 <titleabbrev>Time With Time Zone Inputs</titleabbrev>
924 <entry>Example</entry>
925 <entry>Description</entry>
930 <entry>04:05:06.789-8</entry>
931 <entry>ISO-8601</entry>
934 <entry>04:05:06-08:00</entry>
935 <entry>ISO-8601</entry>
938 <entry>04:05-08:00</entry>
939 <entry>ISO-8601</entry>
942 <entry>040506-08</entry>
943 <entry>ISO-8601</entry>
951 Refer to <xref endterm="timezone-title" linkend="timezone"> for
952 more examples of time zones.
957 <title>timestamp</title>
959 Valid input for the <type>timestamp</type> type consists of a concatenation
960 of a date and a time, followed by an optional <literal>AD</literal> or
961 <literal>BC</literal>, followed by an optional time zone. (See below.)
964 1999-01-08 04:05:06 -8:00
966 is a valid <type>timestamp</type> value, which is <acronym>ISO</acronym>-compliant.
967 In addition, the wide-spread format
969 January 8 04:05:06 1999 PST
975 <table tocentry="1" id="timezone">
976 <title id="timezone-title"><productname>PostgreSQL</productname> Time Zone Input</title>
977 <titleabbrev>Time Zone Inputs</titleabbrev>
981 <entry>Time Zone</entry>
982 <entry>Description</entry>
988 <entry>Pacific Standard Time</entry>
992 <entry>ISO-8601 offset for PST</entry>
996 <entry>ISO-8601 offset for PST</entry>
1000 <entry>ISO-8601 offset for PST</entry>
1009 <title>interval</title>
1011 <type>interval</type>s can be specified with the following syntax:
1013 Quantity Unit [Quantity Unit...] [Direction]
1014 @ Quantity Unit [Direction]
1016 where: <literal>Quantity</literal> is ..., <literal>-1</literal>,
1017 <literal>0</literal>, <literal>1</literal>, <literal>2</literal>, ...;
1018 <literal>Unit</literal> is <literal>second</literal>,
1019 <literal>minute</literal>, <literal>hour</literal>, <literal>day</literal>,
1020 <literal>week</literal>, <literal>month</literal>, <literal>year</literal>,
1021 <literal>decade</literal>, <literal>century</literal>, <literal>millenium</literal>,
1022 or abbreviations or plurals of these units;
1023 <literal>Direction</literal> can be <literal>ago</literal> or
1029 <title>Special values</title>
1031 The following <acronym>SQL</acronym>-compatible functions can be used as date or time
1032 input for the corresponding datatype: <literal>CURRENT_DATE</literal>,
1033 <literal>CURRENT_TIME</literal>, <literal>CURRENT_TIMESTAMP</literal>.
1036 <productname>PostgreSQL</productname> also supports several special constants for
1039 <table tocentry="1">
1040 <title><productname>PostgresSQL</productname> Special Date/Time Constants</title>
1041 <titleabbrev>Constants</titleabbrev>
1045 <entry>Constant</entry>
1046 <entry>Description</entry>
1051 <entry>current</entry>
1052 <entry>Current transaction time, deferred</entry>
1055 <entry>epoch</entry>
1056 <entry>1970-01-01 00:00:00+00 (Unix system time zero)</entry>
1059 <entry>infinity</entry>
1060 <entry>Later than other valid times</entry>
1063 <entry>-infinity</entry>
1064 <entry>Earlier than other valid times</entry>
1067 <entry>invalid</entry>
1068 <entry>Illegal entry</entry>
1072 <entry>Current transaction time</entry>
1075 <entry>today</entry>
1076 <entry>Midnight today</entry>
1079 <entry>tomorrow</entry>
1080 <entry>Midnight tomorrow</entry>
1083 <entry>yesterday</entry>
1084 <entry>Midnight yesterday</entry>
1089 <literal>'now'</literal> is resolved when the value is inserted, <literal>'current'</literal>
1090 is resolved everytime the value is retrieved. So you probably want to use <literal>'now'</literal>
1091 in most applications. (Of course you <emphasis>really</emphasis> want to use
1092 <literal>CURRENT_TIMESTAMP</literal>, which is equivalent to <literal>'now'</literal>.)
1100 <title>Date/Time Output</title>
1103 Output formats can be set to one of the four styles
1104 ISO-8601, <acronym>SQL</acronym> (Ingres), traditional
1105 Postgres, and German, using the <command>SET DateStyle</command>.
1106 The default is the <acronym>ISO</acronym> format.
1108 <table tocentry="1">
1109 <title><productname>PostgreSQL</productname> Date/Time Output Styles</title>
1110 <titleabbrev>Styles</titleabbrev>
1114 <entry>Style Specification</entry>
1115 <entry>Description</entry>
1116 <entry>Example</entry>
1121 <entry>'ISO'</entry>
1122 <entry>ISO-8601 standard</entry>
1123 <entry>1997-12-17 07:37:16-08</entry>
1126 <entry>'SQL'</entry>
1127 <entry>Traditional style</entry>
1128 <entry>12/17/1997 07:37:16.00 PST</entry>
1131 <entry>'Postgres'</entry>
1132 <entry>Original style</entry>
1133 <entry>Wed Dec 17 07:37:16 1997 PST</entry>
1136 <entry>'German'</entry>
1137 <entry>Regional style</entry>
1138 <entry>17.12.1997 07:37:16.00 PST</entry>
1146 The output of the <type>date</type> and <type>time</type> styles is of course
1147 only the date or time part in accordance with the above examples
1151 The <acronym>SQL</acronym> style has European and non-European (US) variants,
1152 which determines whether month follows day or vica versa. (See also above
1153 at Date/Time Input, how this setting affects interpretation of input values.)
1155 <table tocentry="1">
1156 <title><productname>PostgreSQL</productname> Date Order Conventions</title>
1157 <titleabbrev>Order</titleabbrev>
1161 <entry>Style Specification</entry>
1162 <entry>Example</entry>
1167 <entry>European</entry>
1168 <entry>17/12/1997 15:37:16.00 MET</entry>
1172 <entry>12/17/1997 07:37:16.00 PST</entry>
1180 <type>interval</type> output looks like the input format, expect that units like
1181 <literal>week</literal> or <literal>century</literal> are converted to years and days.
1182 In ISO mode the output looks like
1184 [ Quantity Units [ ... ] ] [ Days ] Hours:Minutes [ ago ]
1189 There are several ways to affect the appearance of date/time types:
1191 <itemizedlist spacing="compact" mark="bullet">
1194 The <envar>PGDATESTYLE</envar> environment variable used by the backend directly
1195 on postmaster startup.
1200 The <envar>PGDATESTYLE</envar> environment variable used by the frontend libpq
1206 <command>SET DATESTYLE</command> <acronym>SQL</acronym> command.
1215 <title>Time Zones</title>
1218 <productname>PostgreSQL</productname> endeavors to be compatible with
1219 <acronym>SQL92</acronym> definitions for typical usage.
1220 However, the <acronym>SQL92</acronym> standard has an odd mix of date and
1221 time types and capabilities. Two obvious problems are:
1226 Although the <type>date</type> type
1227 does not have an associated time zone, the
1228 <type>time</type> type can or does.
1234 The default time zone is specified as a constant integer offset
1241 Time zones in the real world can have no meaning unless
1242 associated with a date as well as a time
1243 since the offset may vary through the year with daylight savings
1248 To address these difficulties, <productname>PostgreSQL</productname>
1249 associates time zones only with date and time
1250 types which contain both date and time,
1251 and assumes local time for any type containing only
1252 date or time. Further, time zone support is derived from
1253 the underlying operating system
1254 time zone capabilities, and hence can handle daylight savings time
1255 and other expected behavior.
1259 <productname>PostgreSQL</productname> obtains time zone support
1260 from the underlying operating system for dates between 1902 and
1261 2038 (near the typical date limits for Unix-style
1262 systems). Outside of this range, all dates are assumed to be
1263 specified and used in Universal Coordinated Time (UTC).
1267 All dates and times are stored internally in Universal UTC,
1268 alternately known as Greenwich Mean Time (GMT).
1269 Times are converted to local time on the database server before being
1270 sent to the client frontend, hence by default are in the server
1275 There are several ways to affect the time zone behavior:
1277 <itemizedlist spacing="compact" mark="bullet">
1280 The TZ environment variable used by the backend directly
1281 on postmaster startup as the default time zone.
1286 The PGTZ environment variable set at the client used by libpq
1287 to send time zone information to the backend upon connection.
1292 The <acronym>SQL</acronym> command <command>SET TIME ZONE</command>
1293 sets the time zone for the session.
1300 If an invalid time zone is specified,
1301 the time zone becomes GMT (on most systems anyway).
1308 If the compiler option USE_AUSTRALIAN_RULES is set
1309 then <literal>EST</literal> refers to Australia Eastern Std Time,
1310 which has an offset of +10:00 hours from UTC.
1318 <title>Internals</title>
1321 <productname>PostgreSQL</productname> uses Julian dates
1322 for all date/time calculations. They have the nice property of correctly
1323 predicting/calculating any date more recent than 4713BC
1324 to far into the future, using the assumption that the length of the
1325 year is 365.2425 days.
1329 Date conventions before the 19th century make for interesting reading,
1330 but are not consistant enough to warrant coding into a date/time handler.
1337 <title>Boolean Type</title>
1340 <productname>Postgres</productname> supports <type>bool</type> as
1341 the <acronym>SQL3</acronym> boolean type.
1342 <type>bool</type> can have one of only two states: 'true' or 'false'.
1343 A third state, 'unknown', is not
1344 implemented and is not suggested in <acronym>SQL3</acronym>;
1345 <acronym>NULL</acronym> is an
1346 effective substitute. <type>bool</type> can be used in any boolean expression,
1347 and boolean expressions
1348 always evaluate to a result compatible with this type.</para>
1351 <type>bool</type> uses 1 byte of storage.
1355 <table tocentry="1">
1356 <title><productname>Postgres</productname> Boolean Type</title>
1357 <titleabbrev>Booleans</titleabbrev>
1361 <entry>State</entry>
1362 <entry>Output</entry>
1363 <entry>Input</entry>
1370 <entry>TRUE, 't', 'true', 'y', 'yes', '1'</entry>
1373 <entry>False</entry>
1375 <entry>FALSE, 'f', 'false', 'n', 'no', '0'</entry>
1384 <title>Geometric Types</title>
1387 Geometric types represent two-dimensional spatial objects.
1388 The most fundamental type,
1389 the point, forms the basis for all of the other types.
1393 <table tocentry="1">
1394 <title><productname>Postgres</productname> Geometric Types</title>
1395 <titleabbrev>Geometrics</titleabbrev>
1399 <entry>Geometric Type</entry>
1400 <entry>Storage</entry>
1401 <entry>Representation</entry>
1402 <entry>Description</entry>
1407 <entry>point</entry>
1408 <entry>16 bytes</entry>
1409 <entry>(x,y)</entry>
1410 <entry>Point in space</entry>
1414 <entry>32 bytes</entry>
1415 <entry>((x1,y1),(x2,y2))</entry>
1416 <entry>Infinite line</entry>
1420 <entry>32 bytes</entry>
1421 <entry>((x1,y1),(x2,y2))</entry>
1422 <entry>Finite line segment</entry>
1426 <entry>32 bytes</entry>
1427 <entry>((x1,y1),(x2,y2))</entry>
1428 <entry>Rectangular box</entry>
1432 <entry>4+32n bytes</entry>
1433 <entry>((x1,y1),...)</entry>
1434 <entry>Closed path (similar to polygon)</entry>
1438 <entry>4+32n bytes</entry>
1439 <entry>[(x1,y1),...]</entry>
1440 <entry>Open path</entry>
1443 <entry>polygon</entry>
1444 <entry>4+32n bytes</entry>
1445 <entry>((x1,y1),...)</entry>
1446 <entry>Polygon (similar to closed path)</entry>
1449 <entry>circle</entry>
1450 <entry>24 bytes</entry>
1451 <entry><(x,y),r></entry>
1452 <entry>Circle (center and radius)</entry>
1460 A rich set of functions and operators is available to perform various geometric
1461 operations such as scaling, translation, rotation, and determining
1466 <title>Point</title>
1469 Points are the fundamental two-dimensional building block for geometric types.
1473 <type>point</type> is specified using the following syntax:
1479 x is the x-axis coordinate as a floating point number
1480 y is the y-axis coordinate as a floating point number
1486 <title>Line Segment</title>
1489 Line segments (<type>lseg</type>) are represented by pairs of points.
1493 <type>lseg</type> is specified using the following syntax:
1495 ( ( x1 , y1 ) , ( x2 , y2 ) )
1496 ( x1 , y1 ) , ( x2 , y2 )
1499 (x1,y1) and (x2,y2) are the endpoints of the segment
1508 Boxes are represented by pairs of points which are opposite
1513 <type>box</type> is specified using the following syntax:
1516 ( ( x1 , y1 ) , ( x2 , y2 ) )
1517 ( x1 , y1 ) , ( x2 , y2 )
1520 (x1,y1) and (x2,y2) are opposite corners
1523 Boxes are output using the first syntax.
1524 The corners are reordered on input to store
1525 the lower left corner first and the upper right corner last.
1526 Other corners of the box can be entered, but the lower
1527 left and upper right corners are determined from the input and stored.
1535 Paths are represented by connected sets of points. Paths can be "open", where
1536 the first and last points in the set are not connected, and "closed",
1537 where the first and last point are connected. Functions
1538 <function>popen(p)</function>
1540 <function>pclose(p)</function>
1541 are supplied to force a path to be open or closed, and functions
1542 <function>isopen(p)</function>
1544 <function>isclosed(p)</function>
1545 are supplied to select either type in a query.
1549 <type>path</type> is specified using the following syntax:
1552 ( ( x1 , y1 ) , ... , ( xn , yn ) )
1553 [ ( x1 , y1 ) , ... , ( xn , yn ) ]
1554 ( x1 , y1 ) , ... , ( xn , yn )
1555 ( x1 , y1 , ... , xn , yn )
1556 x1 , y1 , ... , xn , yn
1558 (x1,y1),...,(xn,yn) are points 1 through n
1559 a leading "[" indicates an open path
1560 a leading "(" indicates a closed path
1563 Paths are output using the first syntax.
1564 Note that <productname>Postgres</productname> versions prior to
1565 v6.1 used a format for paths which had a single leading parenthesis,
1567 an integer count of the number of points, then the list of points followed by a
1568 closing parenthesis.
1569 The built-in function <function>upgradepath</function> is supplied to convert
1570 paths dumped and reloaded from pre-v6.1 databases.
1575 <title>Polygon</title>
1578 Polygons are represented by sets of points. Polygons should probably be
1579 considered equivalent to closed paths, but are stored differently
1580 and have their own set of support routines.
1584 <type>polygon</type> is specified using the following syntax:
1587 ( ( x1 , y1 ) , ... , ( xn , yn ) )
1588 ( x1 , y1 ) , ... , ( xn , yn )
1589 ( x1 , y1 , ... , xn , yn )
1590 x1 , y1 , ... , xn , yn
1592 (x1,y1),...,(xn,yn) are points 1 through n
1595 Polygons are output using the first syntax.
1596 Note that <productname>Postgres</productname> versions prior to
1597 v6.1 used a format for polygons which had a single leading parenthesis, the list
1598 of x-axis coordinates, the list of y-axis coordinates,
1599 followed by a closing parenthesis.
1600 The built-in function <function>upgradepoly</function> is supplied to convert
1601 polygons dumped and reloaded from pre-v6.1 databases.
1606 <title>Circle</title>
1609 Circles are represented by a center point and a radius.
1613 <type>circle</type> is specified using the following syntax:
1621 (x,y) is the center of the circle
1622 r is the radius of the circle
1625 Circles are output using the first syntax.
1632 <title>IP Version 4 Networks and Host Addresses</title>
1635 The <type>cidr</type> type stores networks specified
1636 in <acronym>CIDR</acronym> (Classless Inter-Domain Routing) notation.
1637 The <type>inet</type> type stores hosts and networks in CIDR notation using a simple
1638 variation in representation to represent simple host TCP/IP addresses.
1642 <table tocentry="1">
1643 <title><productname>Postgres</productname>IP Version 4 Types</title>
1644 <titleabbrev>IPV4</titleabbrev>
1648 <entry>IPV4 Type</entry>
1649 <entry>Storage</entry>
1650 <entry>Description</entry>
1651 <entry>Range</entry>
1657 <entry>variable</entry>
1658 <entry>CIDR networks</entry>
1659 <entry>Valid IPV4 CIDR blocks</entry>
1663 <entry>variable</entry>
1664 <entry>nets and hosts</entry>
1665 <entry>Valid IPV4 CIDR blocks</entry>
1676 The <type>cidr</type> type holds a CIDR network.
1677 The format for specifying classless networks is
1678 <replaceable class="parameter">x.x.x.x/y</replaceable>
1679 where <replaceable class="parameter">x.x.x.x</replaceable> is the
1680 network and <replaceable class="parameter">/y</replaceable> is
1681 the number of bits in the netmask.
1682 If <replaceable class="parameter">/y</replaceable> omitted, it is
1683 calculated using assumptions from
1684 the older classfull naming system except that it is extended to include at least
1685 all of the octets in the input.
1689 Here are some examples:
1691 <table tocentry="1">
1692 <title><productname>Postgres</productname>IP Types Examples</title>
1696 <entry>CIDR Input</entry>
1697 <entry>CIDR Displayed</entry>
1702 <entry>192.168.1</entry>
1703 <entry>192.168.1/24</entry>
1706 <entry>192.168</entry>
1707 <entry>192.168.0/24</entry>
1710 <entry>128.1</entry>
1711 <entry>128.1/16</entry>
1715 <entry>128.0/16</entry>
1718 <entry>128.1.2</entry>
1719 <entry>128.1.2/24</entry>
1722 <entry>10.1.2</entry>
1723 <entry>10.1.2/24</entry>
1727 <entry>10.1/16</entry>
1740 <title id="inet-type"><type>inet</type></title>
1743 The <type>inet</type> type is designed to hold, in one field, all of the information
1744 about a host including the CIDR-style subnet that it is in.
1745 Note that if you want to store proper CIDR networks,
1746 you should use the <type>cidr</type> type.
1747 The <type>inet</type> type is similar to the <type>cidr</type>
1748 type except that the bits in the
1749 host part can be non-zero.
1750 Functions exist to extract the various elements of the field.
1754 The input format for this function is
1755 <replaceable class="parameter">x.x.x.x/y</replaceable>
1756 where <replaceable class="parameter">x.x.x.x</replaceable> is
1757 an internet host and <replaceable class="parameter">y</replaceable>
1758 is the number of bits in the netmask.
1759 If the <replaceable class="parameter">/y</replaceable> part is left off,
1760 it is treated as <literal>/32</literal>.
1761 On output, the <replaceable class="parameter">/y</replaceable> part is not printed
1762 if it is <literal>/32</literal>.
1763 This allows the type to be used as a straight host type by just leaving off
1771 <!-- Keep this comment at the end of the file
1776 sgml-minimize-attributes:nil
1777 sgml-always-quote-attributes:t
1780 sgml-parent-document:nil
1781 sgml-default-dtd-file:"./reference.ced"
1782 sgml-exposed-tags:nil
1783 sgml-local-catalogs:"/usr/lib/sgml/catalog"
1784 sgml-local-ecat-files:nil