1 <!-- $PostgreSQL: pgsql/doc/src/sgml/datatype.sgml,v 1.179 2006/10/18 16:43:13 tgl Exp $ -->
3 <chapter id="datatype">
4 <title id="datatype-title">Data Types</title>
6 <indexterm zone="datatype">
7 <primary>data type</primary>
11 <primary>type</primary>
16 <productname>PostgreSQL</productname> has a rich set of native data
17 types available to users. Users may add new types to
18 <productname>PostgreSQL</productname> using the <xref
19 linkend="sql-createtype" endterm="sql-createtype-title"> command.
23 <xref linkend="datatype-table"> shows all the built-in general-purpose data
24 types. Most of the alternative names listed in the
25 <quote>Aliases</quote> column are the names used internally by
26 <productname>PostgreSQL</productname> for historical reasons. In
27 addition, some internally used or deprecated types are available,
28 but they are not listed here.
31 <table id="datatype-table">
32 <title>Data Types</title>
37 <entry>Aliases</entry>
38 <entry>Description</entry>
44 <entry><type>bigint</type></entry>
45 <entry><type>int8</type></entry>
46 <entry>signed eight-byte integer</entry>
50 <entry><type>bigserial</type></entry>
51 <entry><type>serial8</type></entry>
52 <entry>autoincrementing eight-byte integer</entry>
56 <entry><type>bit [ (<replaceable>n</replaceable>) ]</type></entry>
58 <entry>fixed-length bit string</entry>
62 <entry><type>bit varying [ (<replaceable>n</replaceable>) ]</type></entry>
63 <entry><type>varbit</type></entry>
64 <entry>variable-length bit string</entry>
68 <entry><type>boolean</type></entry>
69 <entry><type>bool</type></entry>
70 <entry>logical Boolean (true/false)</entry>
74 <entry><type>box</type></entry>
76 <entry>rectangular box in the plane</entry>
80 <entry><type>bytea</type></entry>
82 <entry>binary data (<quote>byte array</>)</entry>
86 <entry><type>character varying [ (<replaceable>n</replaceable>) ]</type></entry>
87 <entry><type>varchar [ (<replaceable>n</replaceable>) ]</type></entry>
88 <entry>variable-length character string</entry>
92 <entry><type>character [ (<replaceable>n</replaceable>) ]</type></entry>
93 <entry><type>char [ (<replaceable>n</replaceable>) ]</type></entry>
94 <entry>fixed-length character string</entry>
98 <entry><type>cidr</type></entry>
100 <entry>IPv4 or IPv6 network address</entry>
104 <entry><type>circle</type></entry>
106 <entry>circle in the plane</entry>
110 <entry><type>date</type></entry>
112 <entry>calendar date (year, month, day)</entry>
116 <entry><type>double precision</type></entry>
117 <entry><type>float8</type></entry>
118 <entry>double precision floating-point number</entry>
122 <entry><type>inet</type></entry>
124 <entry>IPv4 or IPv6 host address</entry>
128 <entry><type>integer</type></entry>
129 <entry><type>int</type>, <type>int4</type></entry>
130 <entry>signed four-byte integer</entry>
134 <entry><type>interval [ (<replaceable>p</replaceable>) ]</type></entry>
136 <entry>time span</entry>
140 <entry><type>line</type></entry>
142 <entry>infinite line in the plane</entry>
146 <entry><type>lseg</type></entry>
148 <entry>line segment in the plane</entry>
152 <entry><type>macaddr</type></entry>
154 <entry>MAC address</entry>
158 <entry><type>money</type></entry>
160 <entry>currency amount</entry>
164 <entry><type>numeric [ (<replaceable>p</replaceable>,
165 <replaceable>s</replaceable>) ]</type></entry>
166 <entry><type>decimal [ (<replaceable>p</replaceable>,
167 <replaceable>s</replaceable>) ]</type></entry>
168 <entry>exact numeric of selectable precision</entry>
172 <entry><type>path</type></entry>
174 <entry>geometric path in the plane</entry>
178 <entry><type>point</type></entry>
180 <entry>geometric point in the plane</entry>
184 <entry><type>polygon</type></entry>
186 <entry>closed geometric path in the plane</entry>
190 <entry><type>real</type></entry>
191 <entry><type>float4</type></entry>
192 <entry>single precision floating-point number</entry>
196 <entry><type>smallint</type></entry>
197 <entry><type>int2</type></entry>
198 <entry>signed two-byte integer</entry>
202 <entry><type>serial</type></entry>
203 <entry><type>serial4</type></entry>
204 <entry>autoincrementing four-byte integer</entry>
208 <entry><type>text</type></entry>
210 <entry>variable-length character string</entry>
214 <entry><type>time [ (<replaceable>p</replaceable>) ] [ without time zone ]</type></entry>
216 <entry>time of day</entry>
220 <entry><type>time [ (<replaceable>p</replaceable>) ] with time zone</type></entry>
221 <entry><type>timetz</type></entry>
222 <entry>time of day, including time zone</entry>
226 <entry><type>timestamp [ (<replaceable>p</replaceable>) ] [ without time zone ]</type></entry>
228 <entry>date and time</entry>
232 <entry><type>timestamp [ (<replaceable>p</replaceable>) ] with time zone</type></entry>
233 <entry><type>timestamptz</type></entry>
234 <entry>date and time, including time zone</entry>
241 <title>Compatibility</title>
243 The following types (or spellings thereof) are specified by
244 <acronym>SQL</acronym>: <type>bit</type>, <type>bit
245 varying</type>, <type>boolean</type>, <type>char</type>,
246 <type>character varying</type>, <type>character</type>,
247 <type>varchar</type>, <type>date</type>, <type>double
248 precision</type>, <type>integer</type>, <type>interval</type>,
249 <type>numeric</type>, <type>decimal</type>, <type>real</type>,
250 <type>smallint</type>, <type>time</type> (with or without time zone),
251 <type>timestamp</type> (with or without time zone).
256 Each data type has an external representation determined by its input
257 and output functions. Many of the built-in types have
258 obvious external formats. However, several types are either unique
259 to <productname>PostgreSQL</productname>, such as geometric
260 paths, or have several possibilities for formats, such as the date
262 Some of the input and output functions are not invertible. That is,
263 the result of an output function may lose accuracy when compared to
267 <sect1 id="datatype-numeric">
268 <title>Numeric Types</title>
270 <indexterm zone="datatype-numeric">
271 <primary>data type</primary>
272 <secondary>numeric</secondary>
276 Numeric types consist of two-, four-, and eight-byte integers,
277 four- and eight-byte floating-point numbers, and selectable-precision
278 decimals. <xref linkend="datatype-numeric-table"> lists the
282 <table id="datatype-numeric-table">
283 <title>Numeric Types</title>
288 <entry>Storage Size</entry>
289 <entry>Description</entry>
296 <entry><type>smallint</></entry>
297 <entry>2 bytes</entry>
298 <entry>small-range integer</entry>
299 <entry>-32768 to +32767</entry>
302 <entry><type>integer</></entry>
303 <entry>4 bytes</entry>
304 <entry>usual choice for integer</entry>
305 <entry>-2147483648 to +2147483647</entry>
308 <entry><type>bigint</></entry>
309 <entry>8 bytes</entry>
310 <entry>large-range integer</entry>
311 <entry>-9223372036854775808 to 9223372036854775807</entry>
315 <entry><type>decimal</></entry>
316 <entry>variable</entry>
317 <entry>user-specified precision, exact</entry>
318 <entry>no limit</entry>
321 <entry><type>numeric</></entry>
322 <entry>variable</entry>
323 <entry>user-specified precision, exact</entry>
324 <entry>no limit</entry>
328 <entry><type>real</></entry>
329 <entry>4 bytes</entry>
330 <entry>variable-precision, inexact</entry>
331 <entry>6 decimal digits precision</entry>
334 <entry><type>double precision</></entry>
335 <entry>8 bytes</entry>
336 <entry>variable-precision, inexact</entry>
337 <entry>15 decimal digits precision</entry>
341 <entry><type>serial</></entry>
342 <entry>4 bytes</entry>
343 <entry>autoincrementing integer</entry>
344 <entry>1 to 2147483647</entry>
348 <entry><type>bigserial</type></entry>
349 <entry>8 bytes</entry>
350 <entry>large autoincrementing integer</entry>
351 <entry>1 to 9223372036854775807</entry>
358 The syntax of constants for the numeric types is described in
359 <xref linkend="sql-syntax-constants">. The numeric types have a
360 full set of corresponding arithmetic operators and
361 functions. Refer to <xref linkend="functions"> for more
362 information. The following sections describe the types in detail.
365 <sect2 id="datatype-int">
366 <title>Integer Types</title>
368 <indexterm zone="datatype-int">
369 <primary>integer</primary>
372 <indexterm zone="datatype-int">
373 <primary>smallint</primary>
376 <indexterm zone="datatype-int">
377 <primary>bigint</primary>
381 <primary>int4</primary>
386 <primary>int2</primary>
391 <primary>int8</primary>
396 The types <type>smallint</type>, <type>integer</type>, and
397 <type>bigint</type> store whole numbers, that is, numbers without
398 fractional components, of various ranges. Attempts to store
399 values outside of the allowed range will result in an error.
403 The type <type>integer</type> is the usual choice, as it offers
404 the best balance between range, storage size, and performance.
405 The <type>smallint</type> type is generally only used if disk
406 space is at a premium. The <type>bigint</type> type should only
407 be used if the <type>integer</type> range is not sufficient,
408 because the latter is definitely faster.
412 The <type>bigint</type> type may not function correctly on all
413 platforms, since it relies on compiler support for eight-byte
414 integers. On a machine without such support, <type>bigint</type>
415 acts the same as <type>integer</type> (but still takes up eight
416 bytes of storage). However, we are not aware of any reasonable
417 platform where this is actually the case.
421 <acronym>SQL</acronym> only specifies the integer types
422 <type>integer</type> (or <type>int</type>) and
423 <type>smallint</type>. The type <type>bigint</type>, and the
424 type names <type>int2</type>, <type>int4</type>, and
425 <type>int8</type> are extensions, which are shared with various
426 other <acronym>SQL</acronym> database systems.
431 <sect2 id="datatype-numeric-decimal">
432 <title>Arbitrary Precision Numbers</title>
434 <indexterm zone="datatype-numeric-decimal">
435 <primary>numeric (data type)</primary>
439 <primary>decimal</primary>
444 The type <type>numeric</type> can store numbers with up to 1000
445 digits of precision and perform calculations exactly. It is
446 especially recommended for storing monetary amounts and other
447 quantities where exactness is required. However, arithmetic on
448 <type>numeric</type> values is very slow compared to the integer
449 types, or to the floating-point types described in the next section.
453 In what follows we use these terms: The
454 <firstterm>scale</firstterm> of a <type>numeric</type> is the
455 count of decimal digits in the fractional part, to the right of
456 the decimal point. The <firstterm>precision</firstterm> of a
457 <type>numeric</type> is the total count of significant digits in
458 the whole number, that is, the number of digits to both sides of
459 the decimal point. So the number 23.5141 has a precision of 6
460 and a scale of 4. Integers can be considered to have a scale of
465 Both the maximum precision and the maximum scale of a
466 <type>numeric</type> column can be
467 configured. To declare a column of type <type>numeric</type> use
470 NUMERIC(<replaceable>precision</replaceable>, <replaceable>scale</replaceable>)
472 The precision must be positive, the scale zero or positive.
475 NUMERIC(<replaceable>precision</replaceable>)
477 selects a scale of 0. Specifying
481 without any precision or scale creates a column in which numeric
482 values of any precision and scale can be stored, up to the
483 implementation limit on precision. A column of this kind will
484 not coerce input values to any particular scale, whereas
485 <type>numeric</type> columns with a declared scale will coerce
486 input values to that scale. (The <acronym>SQL</acronym> standard
487 requires a default scale of 0, i.e., coercion to integer
488 precision. We find this a bit useless. If you're concerned
489 about portability, always specify the precision and scale
494 If the scale of a value to be stored is greater than the declared
495 scale of the column, the system will round the value to the specified
496 number of fractional digits. Then, if the number of digits to the
497 left of the decimal point exceeds the declared precision minus the
498 declared scale, an error is raised.
502 Numeric values are physically stored without any extra leading or
503 trailing zeroes. Thus, the declared precision and scale of a column
504 are maximums, not fixed allocations. (In this sense the <type>numeric</>
505 type is more akin to <type>varchar(<replaceable>n</>)</type>
506 than to <type>char(<replaceable>n</>)</type>.) The actual storage
507 requirement is two bytes for each group of four decimal digits,
508 plus eight bytes overhead.
512 In addition to ordinary numeric values, the <type>numeric</type>
513 type allows the special value <literal>NaN</>, meaning
514 <quote>not-a-number</quote>. Any operation on <literal>NaN</>
515 yields another <literal>NaN</>. When writing this value
516 as a constant in a SQL command, you must put quotes around it,
517 for example <literal>UPDATE table SET x = 'NaN'</>. On input,
518 the string <literal>NaN</> is recognized in a case-insensitive manner.
522 The types <type>decimal</type> and <type>numeric</type> are
523 equivalent. Both types are part of the <acronym>SQL</acronym>
529 <sect2 id="datatype-float">
530 <title>Floating-Point Types</title>
532 <indexterm zone="datatype-float">
533 <primary>real</primary>
536 <indexterm zone="datatype-float">
537 <primary>double precision</primary>
541 <primary>float4</primary>
546 <primary>float8</primary>
547 <see>double precision</see>
550 <indexterm zone="datatype-float">
551 <primary>floating point</primary>
555 The data types <type>real</type> and <type>double
556 precision</type> are inexact, variable-precision numeric types.
557 In practice, these types are usually implementations of
558 <acronym>IEEE</acronym> Standard 754 for Binary Floating-Point
559 Arithmetic (single and double precision, respectively), to the
560 extent that the underlying processor, operating system, and
565 Inexact means that some values cannot be converted exactly to the
566 internal format and are stored as approximations, so that storing
567 and printing back out a value may show slight discrepancies.
568 Managing these errors and how they propagate through calculations
569 is the subject of an entire branch of mathematics and computer
570 science and will not be discussed further here, except for the
575 If you require exact storage and calculations (such as for
576 monetary amounts), use the <type>numeric</type> type instead.
582 If you want to do complicated calculations with these types
583 for anything important, especially if you rely on certain
584 behavior in boundary cases (infinity, underflow), you should
585 evaluate the implementation carefully.
591 Comparing two floating-point values for equality may or may
592 not work as expected.
599 On most platforms, the <type>real</type> type has a range of at least
600 1E-37 to 1E+37 with a precision of at least 6 decimal digits. The
601 <type>double precision</type> type typically has a range of around
602 1E-307 to 1E+308 with a precision of at least 15 digits. Values that
603 are too large or too small will cause an error. Rounding may
604 take place if the precision of an input number is too high.
605 Numbers too close to zero that are not representable as distinct
606 from zero will cause an underflow error.
610 In addition to ordinary numeric values, the floating-point types
611 have several special values:
613 <literal>Infinity</literal>
614 <literal>-Infinity</literal>
615 <literal>NaN</literal>
617 These represent the IEEE 754 special values
618 <quote>infinity</quote>, <quote>negative infinity</quote>, and
619 <quote>not-a-number</quote>, respectively. (On a machine whose
620 floating-point arithmetic does not follow IEEE 754, these values
621 will probably not work as expected.) When writing these values
622 as constants in a SQL command, you must put quotes around them,
623 for example <literal>UPDATE table SET x = 'Infinity'</>. On input,
624 these strings are recognized in a case-insensitive manner.
628 <productname>PostgreSQL</productname> also supports the SQL-standard
629 notations <type>float</type> and
630 <type>float(<replaceable>p</replaceable>)</type> for specifying
631 inexact numeric types. Here, <replaceable>p</replaceable> specifies
632 the minimum acceptable precision in binary digits.
633 <productname>PostgreSQL</productname> accepts
634 <type>float(1)</type> to <type>float(24)</type> as selecting the
635 <type>real</type> type, while
636 <type>float(25)</type> to <type>float(53)</type> select
637 <type>double precision</type>. Values of <replaceable>p</replaceable>
638 outside the allowed range draw an error.
639 <type>float</type> with no precision specified is taken to mean
640 <type>double precision</type>.
645 Prior to <productname>PostgreSQL</productname> 7.4, the precision in
646 <type>float(<replaceable>p</replaceable>)</type> was taken to mean
647 so many decimal digits. This has been corrected to match the SQL
648 standard, which specifies that the precision is measured in binary
649 digits. The assumption that <type>real</type> and
650 <type>double precision</type> have exactly 24 and 53 bits in the
651 mantissa respectively is correct for IEEE-standard floating point
652 implementations. On non-IEEE platforms it may be off a little, but
653 for simplicity the same ranges of <replaceable>p</replaceable> are used
660 <sect2 id="datatype-serial">
661 <title>Serial Types</title>
663 <indexterm zone="datatype-serial">
664 <primary>serial</primary>
667 <indexterm zone="datatype-serial">
668 <primary>bigserial</primary>
671 <indexterm zone="datatype-serial">
672 <primary>serial4</primary>
675 <indexterm zone="datatype-serial">
676 <primary>serial8</primary>
680 <primary>auto-increment</primary>
685 <primary>sequence</primary>
686 <secondary>and serial type</secondary>
690 The data types <type>serial</type> and <type>bigserial</type>
691 are not true types, but merely
692 a notational convenience for setting up unique identifier columns
693 (similar to the <literal>AUTO_INCREMENT</literal> property
694 supported by some other databases). In the current
695 implementation, specifying
698 CREATE TABLE <replaceable class="parameter">tablename</replaceable> (
699 <replaceable class="parameter">colname</replaceable> SERIAL
703 is equivalent to specifying:
706 CREATE SEQUENCE <replaceable class="parameter">tablename</replaceable>_<replaceable class="parameter">colname</replaceable>_seq;
707 CREATE TABLE <replaceable class="parameter">tablename</replaceable> (
708 <replaceable class="parameter">colname</replaceable> integer NOT NULL DEFAULT nextval('<replaceable class="parameter">tablename</replaceable>_<replaceable class="parameter">colname</replaceable>_seq')
710 ALTER SEQUENCE <replaceable class="parameter">tablename</replaceable>_<replaceable class="parameter">colname</replaceable>_seq OWNED BY <replaceable class="parameter">tablename</replaceable>.<replaceable class="parameter">colname</replaceable>;
713 Thus, we have created an integer column and arranged for its default
714 values to be assigned from a sequence generator. A <literal>NOT NULL</>
715 constraint is applied to ensure that a null value cannot be explicitly
716 inserted, either. (In most cases you would also want to attach a
717 <literal>UNIQUE</> or <literal>PRIMARY KEY</> constraint to prevent
718 duplicate values from being inserted by accident, but this is
719 not automatic.) Lastly, the sequence is marked as <quote>owned by</>
720 the column, so that it will be dropped if the column or table is dropped.
725 Prior to <productname>PostgreSQL</productname> 7.3, <type>serial</type>
726 implied <literal>UNIQUE</literal>. This is no longer automatic. If
727 you wish a serial column to be in a unique constraint or a
728 primary key, it must now be specified, same as with
734 To insert the next value of the sequence into the <type>serial</type>
735 column, specify that the <type>serial</type>
736 column should be assigned its default value. This can be done
737 either by excluding the column from the list of columns in
738 the <command>INSERT</command> statement, or through the use of
739 the <literal>DEFAULT</literal> key word.
743 The type names <type>serial</type> and <type>serial4</type> are
744 equivalent: both create <type>integer</type> columns. The type
745 names <type>bigserial</type> and <type>serial8</type> work just
746 the same way, except that they create a <type>bigint</type>
747 column. <type>bigserial</type> should be used if you anticipate
748 the use of more than 2<superscript>31</> identifiers over the
749 lifetime of the table.
753 The sequence created for a <type>serial</type> column is
754 automatically dropped when the owning column is dropped.
755 You can drop the sequence without dropping the column, but this
756 will force removal of the column default expression.
761 <sect1 id="datatype-money">
762 <title>Monetary Types</title>
766 The <type>money</type> type is deprecated. Use
767 <type>numeric</type> or <type>decimal</type> instead, in
768 combination with the <function>to_char</function> function.
773 The <type>money</type> type stores a currency amount with a fixed
774 fractional precision; see <xref
775 linkend="datatype-money-table">.
776 Input is accepted in a variety of formats, including integer and
777 floating-point literals, as well as <quote>typical</quote>
778 currency formatting, such as <literal>'$1,000.00'</literal>.
779 Output is generally in the latter form but depends on the locale.
782 <table id="datatype-money-table">
783 <title>Monetary Types</title>
788 <entry>Storage Size</entry>
789 <entry>Description</entry>
796 <entry>4 bytes</entry>
797 <entry>currency amount</entry>
798 <entry>-21474836.48 to +21474836.47</entry>
806 <sect1 id="datatype-character">
807 <title>Character Types</title>
809 <indexterm zone="datatype-character">
810 <primary>character string</primary>
811 <secondary>data types</secondary>
815 <primary>string</primary>
816 <see>character string</see>
819 <indexterm zone="datatype-character">
820 <primary>character</primary>
823 <indexterm zone="datatype-character">
824 <primary>character varying</primary>
827 <indexterm zone="datatype-character">
828 <primary>text</primary>
831 <indexterm zone="datatype-character">
832 <primary>char</primary>
835 <indexterm zone="datatype-character">
836 <primary>varchar</primary>
839 <table id="datatype-character-table">
840 <title>Character Types</title>
845 <entry>Description</entry>
850 <entry><type>character varying(<replaceable>n</>)</type>, <type>varchar(<replaceable>n</>)</type></entry>
851 <entry>variable-length with limit</entry>
854 <entry><type>character(<replaceable>n</>)</type>, <type>char(<replaceable>n</>)</type></entry>
855 <entry>fixed-length, blank padded</entry>
858 <entry><type>text</type></entry>
859 <entry>variable unlimited length</entry>
866 <xref linkend="datatype-character-table"> shows the
867 general-purpose character types available in
868 <productname>PostgreSQL</productname>.
872 <acronym>SQL</acronym> defines two primary character types:
873 <type>character varying(<replaceable>n</>)</type> and
874 <type>character(<replaceable>n</>)</type>, where <replaceable>n</>
875 is a positive integer. Both of these types can store strings up to
876 <replaceable>n</> characters in length. An attempt to store a
877 longer string into a column of these types will result in an
878 error, unless the excess characters are all spaces, in which case
879 the string will be truncated to the maximum length. (This somewhat
880 bizarre exception is required by the <acronym>SQL</acronym>
881 standard.) If the string to be stored is shorter than the declared
882 length, values of type <type>character</type> will be space-padded;
883 values of type <type>character varying</type> will simply store the
889 If one explicitly casts a value to <type>character
890 varying(<replaceable>n</>)</type> or
891 <type>character(<replaceable>n</>)</type>, then an over-length
892 value will be truncated to <replaceable>n</> characters without
893 raising an error. (This too is required by the
894 <acronym>SQL</acronym> standard.)
898 The notations <type>varchar(<replaceable>n</>)</type> and
899 <type>char(<replaceable>n</>)</type> are aliases for <type>character
900 varying(<replaceable>n</>)</type> and
901 <type>character(<replaceable>n</>)</type>, respectively.
902 <type>character</type> without length specifier is equivalent to
903 <type>character(1)</type>. If <type>character varying</type> is used
904 without length specifier, the type accepts strings of any size. The
905 latter is a <productname>PostgreSQL</> extension.
909 In addition, <productname>PostgreSQL</productname> provides the
910 <type>text</type> type, which stores strings of any length.
911 Although the type <type>text</type> is not in the
912 <acronym>SQL</acronym> standard, several other SQL database
913 management systems have it as well.
917 Values of type <type>character</type> are physically padded
918 with spaces to the specified width <replaceable>n</>, and are
919 stored and displayed that way. However, the padding spaces are
920 treated as semantically insignificant. Trailing spaces are
921 disregarded when comparing two values of type <type>character</type>,
922 and they will be removed when converting a <type>character</type> value
923 to one of the other string types. Note that trailing spaces
924 <emphasis>are</> semantically significant in
925 <type>character varying</type> and <type>text</type> values.
929 The storage requirement for data of these types is 4 bytes plus the
930 actual string, and in case of <type>character</type> plus the
931 padding. Long strings are compressed by the system automatically, so
932 the physical requirement on disk may be less. Long values are also
933 stored in background tables so they do not interfere with rapid
934 access to the shorter column values. In any case, the longest
935 possible character string that can be stored is about 1 GB. (The
936 maximum value that will be allowed for <replaceable>n</> in the data
937 type declaration is less than that. It wouldn't be very useful to
938 change this because with multibyte character encodings the number of
939 characters and bytes can be quite different anyway. If you desire to
940 store long strings with no specific upper limit, use
941 <type>text</type> or <type>character varying</type> without a length
942 specifier, rather than making up an arbitrary length limit.)
947 There are no performance differences between these three types,
948 apart from the increased storage size when using the blank-padded
949 type. While <type>character(<replaceable>n</>)</type> has performance
950 advantages in some other database systems, it has no such advantages in
951 <productname>PostgreSQL</productname>. In most situations
952 <type>text</type> or <type>character varying</type> should be used
958 Refer to <xref linkend="sql-syntax-strings"> for information about
959 the syntax of string literals, and to <xref linkend="functions">
960 for information about available operators and functions. The
961 database character set determines the character set used to store
962 textual values; for more information on character set support,
963 refer to <xref linkend="multibyte">.
967 <title>Using the character types</title>
970 CREATE TABLE test1 (a character(4));
971 INSERT INTO test1 VALUES ('ok');
972 SELECT a, char_length(a) FROM test1; -- <co id="co.datatype-char">
979 CREATE TABLE test2 (b varchar(5));
980 INSERT INTO test2 VALUES ('ok');
981 INSERT INTO test2 VALUES ('good ');
982 INSERT INTO test2 VALUES ('too long');
983 <computeroutput>ERROR: value too long for type character varying(5)</computeroutput>
984 INSERT INTO test2 VALUES ('too long'::varchar(5)); -- explicit truncation
985 SELECT b, char_length(b) FROM test2;
988 -------+-------------
995 <callout arearefs="co.datatype-char">
997 The <function>char_length</function> function is discussed in
998 <xref linkend="functions-string">.
1005 There are two other fixed-length character types in
1006 <productname>PostgreSQL</productname>, shown in <xref
1007 linkend="datatype-character-special-table">. The <type>name</type>
1008 type exists <emphasis>only</emphasis> for storage of identifiers
1009 in the internal system catalogs and is not intended for use by the general user. Its
1010 length is currently defined as 64 bytes (63 usable characters plus
1011 terminator) but should be referenced using the constant
1012 <symbol>NAMEDATALEN</symbol>. The length is set at compile time (and
1013 is therefore adjustable for special uses); the default maximum
1014 length may change in a future release. The type <type>"char"</type>
1015 (note the quotes) is different from <type>char(1)</type> in that it
1016 only uses one byte of storage. It is internally used in the system
1017 catalogs as a poor-man's enumeration type.
1020 <table id="datatype-character-special-table">
1021 <title>Special Character Types</title>
1026 <entry>Storage Size</entry>
1027 <entry>Description</entry>
1032 <entry><type>"char"</type></entry>
1033 <entry>1 byte</entry>
1034 <entry>single-character internal type</entry>
1037 <entry><type>name</type></entry>
1038 <entry>64 bytes</entry>
1039 <entry>internal type for object names</entry>
1047 <sect1 id="datatype-binary">
1048 <title>Binary Data Types</title>
1050 <indexterm zone="datatype-binary">
1051 <primary>binary data</primary>
1054 <indexterm zone="datatype-binary">
1055 <primary>bytea</primary>
1059 The <type>bytea</type> data type allows storage of binary strings;
1060 see <xref linkend="datatype-binary-table">.
1063 <table id="datatype-binary-table">
1064 <title>Binary Data Types</title>
1069 <entry>Storage Size</entry>
1070 <entry>Description</entry>
1075 <entry><type>bytea</type></entry>
1076 <entry>4 bytes plus the actual binary string</entry>
1077 <entry>variable-length binary string</entry>
1084 A binary string is a sequence of octets (or bytes). Binary
1085 strings are distinguished from character strings by two
1086 characteristics: First, binary strings specifically allow storing
1087 octets of value zero and other <quote>non-printable</quote>
1088 octets (usually, octets outside the range 32 to 126).
1089 Character strings disallow zero octets, and also disallow any
1090 other octet values and sequences of octet values that are invalid
1091 according to the database's selected character set encoding.
1092 Second, operations on binary strings process the actual bytes,
1093 whereas the processing of character strings depends on locale settings.
1094 In short, binary strings are appropriate for storing data that the
1095 programmer thinks of as <quote>raw bytes</>, whereas character
1096 strings are appropriate for storing text.
1100 When entering <type>bytea</type> values, octets of certain values
1101 <emphasis>must</emphasis> be escaped (but all octet values
1102 <emphasis>can</emphasis> be escaped) when used as part of a string
1103 literal in an <acronym>SQL</acronym> statement. In general, to
1104 escape an octet, it is converted into the three-digit octal number
1105 equivalent of its decimal octet value, and preceded by two
1106 backslashes. <xref linkend="datatype-binary-sqlesc"> shows the
1107 characters that must be escaped, and gives the alternate escape
1108 sequences where applicable.
1111 <table id="datatype-binary-sqlesc">
1112 <title><type>bytea</> Literal Escaped Octets</title>
1116 <entry>Decimal Octet Value</entry>
1117 <entry>Description</entry>
1118 <entry>Escaped Input Representation</entry>
1119 <entry>Example</entry>
1120 <entry>Output Representation</entry>
1127 <entry>zero octet</entry>
1128 <entry><literal>'\\000'</literal></entry>
1129 <entry><literal>SELECT '\\000'::bytea;</literal></entry>
1130 <entry><literal>\000</literal></entry>
1135 <entry>single quote</entry>
1136 <entry><literal>'\''</literal> or <literal>'\\047'</literal></entry>
1137 <entry><literal>SELECT '\''::bytea;</literal></entry>
1138 <entry><literal>'</literal></entry>
1143 <entry>backslash</entry>
1144 <entry><literal>'\\\\'</literal> or <literal>'\\134'</literal></entry>
1145 <entry><literal>SELECT '\\\\'::bytea;</literal></entry>
1146 <entry><literal>\\</literal></entry>
1150 <entry>0 to 31 and 127 to 255</entry>
1151 <entry><quote>non-printable</quote> octets</entry>
1152 <entry><literal>'\\<replaceable>xxx'</></literal> (octal value)</entry>
1153 <entry><literal>SELECT '\\001'::bytea;</literal></entry>
1154 <entry><literal>\001</literal></entry>
1162 The requirement to escape <quote>non-printable</quote> octets actually
1163 varies depending on locale settings. In some instances you can get away
1164 with leaving them unescaped. Note that the result in each of the examples
1165 in <xref linkend="datatype-binary-sqlesc"> was exactly one octet in
1166 length, even though the output representation of the zero octet and
1167 backslash are more than one character.
1171 The reason that you have to write so many backslashes, as shown in
1172 <xref linkend="datatype-binary-sqlesc">, is that an input string
1173 written as a string literal must pass through two parse phases in
1174 the <productname>PostgreSQL</productname> server. The first
1175 backslash of each pair is interpreted as an escape character by
1176 the string-literal parser and is therefore consumed, leaving the
1177 second backslash of the pair. The remaining backslash is then
1178 recognized by the <type>bytea</type> input function as starting
1179 either a three digit octal value or escaping another backslash.
1180 For example, a string literal passed to the server as
1181 <literal>'\\001'</literal> becomes <literal>\001</literal> after
1182 passing through the string-literal parser. The
1183 <literal>\001</literal> is then sent to the <type>bytea</type>
1184 input function, where it is converted to a single octet with a
1185 decimal value of 1. Note that the apostrophe character is not
1186 treated specially by <type>bytea</type>, so it follows the normal
1187 rules for string literals. (See also <xref
1188 linkend="sql-syntax-strings">.)
1192 <type>Bytea</type> octets are also escaped in the output. In general, each
1193 <quote>non-printable</quote> octet is converted into
1194 its equivalent three-digit octal value and preceded by one backslash.
1195 Most <quote>printable</quote> octets are represented by their standard
1196 representation in the client character set. The octet with decimal
1197 value 92 (backslash) has a special alternative output representation.
1198 Details are in <xref linkend="datatype-binary-resesc">.
1201 <table id="datatype-binary-resesc">
1202 <title><type>bytea</> Output Escaped Octets</title>
1206 <entry>Decimal Octet Value</entry>
1207 <entry>Description</entry>
1208 <entry>Escaped Output Representation</entry>
1209 <entry>Example</entry>
1210 <entry>Output Result</entry>
1218 <entry>backslash</entry>
1219 <entry><literal>\\</literal></entry>
1220 <entry><literal>SELECT '\\134'::bytea;</literal></entry>
1221 <entry><literal>\\</literal></entry>
1225 <entry>0 to 31 and 127 to 255</entry>
1226 <entry><quote>non-printable</quote> octets</entry>
1227 <entry><literal>\<replaceable>xxx</></literal> (octal value)</entry>
1228 <entry><literal>SELECT '\\001'::bytea;</literal></entry>
1229 <entry><literal>\001</literal></entry>
1233 <entry>32 to 126</entry>
1234 <entry><quote>printable</quote> octets</entry>
1235 <entry>client character set representation</entry>
1236 <entry><literal>SELECT '\\176'::bytea;</literal></entry>
1237 <entry><literal>~</literal></entry>
1245 Depending on the front end to <productname>PostgreSQL</> you use,
1246 you may have additional work to do in terms of escaping and
1247 unescaping <type>bytea</type> strings. For example, you may also
1248 have to escape line feeds and carriage returns if your interface
1249 automatically translates these.
1253 The <acronym>SQL</acronym> standard defines a different binary
1254 string type, called <type>BLOB</type> or <type>BINARY LARGE
1255 OBJECT</type>. The input format is different from
1256 <type>bytea</type>, but the provided functions and operators are
1262 <sect1 id="datatype-datetime">
1263 <title>Date/Time Types</title>
1265 <indexterm zone="datatype-datetime">
1266 <primary>date</primary>
1268 <indexterm zone="datatype-datetime">
1269 <primary>time</primary>
1271 <indexterm zone="datatype-datetime">
1272 <primary>time without time zone</primary>
1274 <indexterm zone="datatype-datetime">
1275 <primary>time with time zone</primary>
1277 <indexterm zone="datatype-datetime">
1278 <primary>timestamp</primary>
1280 <indexterm zone="datatype-datetime">
1281 <primary>timestamp with time zone</primary>
1283 <indexterm zone="datatype-datetime">
1284 <primary>timestamp without time zone</primary>
1286 <indexterm zone="datatype-datetime">
1287 <primary>interval</primary>
1289 <indexterm zone="datatype-datetime">
1290 <primary>time span</primary>
1294 <productname>PostgreSQL</productname> supports the full set of
1295 <acronym>SQL</acronym> date and time types, shown in <xref
1296 linkend="datatype-datetime-table">. The operations available
1297 on these data types are described in
1298 <xref linkend="functions-datetime">.
1301 <table id="datatype-datetime-table">
1302 <title>Date/Time Types</title>
1307 <entry>Storage Size</entry>
1308 <entry>Description</entry>
1309 <entry>Low Value</entry>
1310 <entry>High Value</entry>
1311 <entry>Resolution</entry>
1316 <entry><type>timestamp [ (<replaceable>p</replaceable>) ] [ without time zone ]</type></entry>
1317 <entry>8 bytes</entry>
1318 <entry>both date and time</entry>
1319 <entry>4713 BC</entry>
1320 <entry>5874897 AD</entry>
1321 <entry>1 microsecond / 14 digits</entry>
1324 <entry><type>timestamp [ (<replaceable>p</replaceable>) ] with time zone</type></entry>
1325 <entry>8 bytes</entry>
1326 <entry>both date and time, with time zone</entry>
1327 <entry>4713 BC</entry>
1328 <entry>5874897 AD</entry>
1329 <entry>1 microsecond / 14 digits</entry>
1332 <entry><type>interval [ (<replaceable>p</replaceable>) ]</type></entry>
1333 <entry>12 bytes</entry>
1334 <entry>time intervals</entry>
1335 <entry>-178000000 years</entry>
1336 <entry>178000000 years</entry>
1337 <entry>1 microsecond / 14 digits</entry>
1340 <entry><type>date</type></entry>
1341 <entry>4 bytes</entry>
1342 <entry>dates only</entry>
1343 <entry>4713 BC</entry>
1344 <entry>5874897 AD</entry>
1345 <entry>1 day</entry>
1348 <entry><type>time [ (<replaceable>p</replaceable>) ] [ without time zone ]</type></entry>
1349 <entry>8 bytes</entry>
1350 <entry>times of day only</entry>
1351 <entry>00:00:00</entry>
1352 <entry>24:00:00</entry>
1353 <entry>1 microsecond / 14 digits</entry>
1356 <entry><type>time [ (<replaceable>p</replaceable>) ] with time zone</type></entry>
1357 <entry>12 bytes</entry>
1358 <entry>times of day only, with time zone</entry>
1359 <entry>00:00:00+1459</entry>
1360 <entry>24:00:00-1459</entry>
1361 <entry>1 microsecond / 14 digits</entry>
1369 Prior to <productname>PostgreSQL</productname> 7.3, writing just
1370 <type>timestamp</type> was equivalent to <type>timestamp with
1371 time zone</type>. This was changed for SQL compliance.
1376 <type>time</type>, <type>timestamp</type>, and
1377 <type>interval</type> accept an optional precision value
1378 <replaceable>p</replaceable> which specifies the number of
1379 fractional digits retained in the seconds field. By default, there
1380 is no explicit bound on precision. The allowed range of
1381 <replaceable>p</replaceable> is from 0 to 6 for the
1382 <type>timestamp</type> and <type>interval</type> types.
1387 When <type>timestamp</> values are stored as double precision floating-point
1388 numbers (currently the default), the effective limit of precision
1389 may be less than 6. <type>timestamp</type> values are stored as seconds
1390 before or after midnight 2000-01-01. Microsecond precision is achieved for
1391 dates within a few years of 2000-01-01, but the precision degrades for
1392 dates further away. When <type>timestamp</type> values are stored as
1393 eight-byte integers (a compile-time
1394 option), microsecond precision is available over the full range of
1395 values. However eight-byte integer timestamps have a more limited range of
1396 dates than shown above: from 4713 BC up to 294276 AD. The same
1397 compile-time option also determines whether <type>time</type> and
1398 <type>interval</type> values are stored as floating-point or eight-byte
1399 integers. In the floating-point case, large <type>interval</type> values
1400 degrade in precision as the size of the interval increases.
1405 For the <type>time</type> types, the allowed range of
1406 <replaceable>p</replaceable> is from 0 to 6 when eight-byte integer
1407 storage is used, or from 0 to 10 when floating-point storage is used.
1411 The type <type>time with time zone</type> is defined by the SQL
1412 standard, but the definition exhibits properties which lead to
1413 questionable usefulness. In most cases, a combination of
1414 <type>date</type>, <type>time</type>, <type>timestamp without time
1415 zone</type>, and <type>timestamp with time zone</type> should
1416 provide a complete range of date/time functionality required by
1421 The types <type>abstime</type>
1422 and <type>reltime</type> are lower precision types which are used internally.
1423 You are discouraged from using these types in new
1424 applications and are encouraged to move any old
1425 ones over when appropriate. Any or all of these internal types
1426 might disappear in a future release.
1429 <sect2 id="datatype-datetime-input">
1430 <title>Date/Time Input</title>
1433 Date and time input is accepted in almost any reasonable format, including
1434 ISO 8601, <acronym>SQL</acronym>-compatible,
1435 traditional <productname>POSTGRES</productname>, and others.
1436 For some formats, ordering of month, day, and year in date input is
1437 ambiguous and there is support for specifying the expected
1438 ordering of these fields. Set the <xref linkend="guc-datestyle"> parameter
1439 to <literal>MDY</> to select month-day-year interpretation,
1440 <literal>DMY</> to select day-month-year interpretation, or
1441 <literal>YMD</> to select year-month-day interpretation.
1445 <productname>PostgreSQL</productname> is more flexible in
1446 handling date/time input than the
1447 <acronym>SQL</acronym> standard requires.
1448 See <xref linkend="datetime-appendix">
1449 for the exact parsing rules of date/time input and for the
1450 recognized text fields including months, days of the week, and
1455 Remember that any date or time literal input needs to be enclosed
1456 in single quotes, like text strings. Refer to
1457 <xref linkend="sql-syntax-constants-generic"> for more
1459 <acronym>SQL</acronym> requires the following syntax
1461 <replaceable>type</replaceable> [ (<replaceable>p</replaceable>) ] '<replaceable>value</replaceable>'
1463 where <replaceable>p</replaceable> in the optional precision
1464 specification is an integer corresponding to the number of
1465 fractional digits in the seconds field. Precision can be
1466 specified for <type>time</type>, <type>timestamp</type>, and
1467 <type>interval</type> types. The allowed values are mentioned
1468 above. If no precision is specified in a constant specification,
1469 it defaults to the precision of the literal value.
1473 <title>Dates</title>
1476 <primary>date</primary>
1480 <xref linkend="datatype-datetime-date-table"> shows some possible
1481 inputs for the <type>date</type> type.
1484 <table id="datatype-datetime-date-table">
1485 <title>Date Input</title>
1489 <entry>Example</entry>
1490 <entry>Description</entry>
1495 <entry>January 8, 1999</entry>
1496 <entry>unambiguous in any <varname>datestyle</varname> input mode</entry>
1499 <entry>1999-01-08</entry>
1500 <entry>ISO 8601; January 8 in any mode
1501 (recommended format)</entry>
1504 <entry>1/8/1999</entry>
1505 <entry>January 8 in <literal>MDY</> mode;
1506 August 1 in <literal>DMY</> mode</entry>
1509 <entry>1/18/1999</entry>
1510 <entry>January 18 in <literal>MDY</> mode;
1511 rejected in other modes</entry>
1514 <entry>01/02/03</entry>
1515 <entry>January 2, 2003 in <literal>MDY</> mode;
1516 February 1, 2003 in <literal>DMY</> mode;
1517 February 3, 2001 in <literal>YMD</> mode
1521 <entry>1999-Jan-08</entry>
1522 <entry>January 8 in any mode</entry>
1525 <entry>Jan-08-1999</entry>
1526 <entry>January 8 in any mode</entry>
1529 <entry>08-Jan-1999</entry>
1530 <entry>January 8 in any mode</entry>
1533 <entry>99-Jan-08</entry>
1534 <entry>January 8 in <literal>YMD</> mode, else error</entry>
1537 <entry>08-Jan-99</entry>
1538 <entry>January 8, except error in <literal>YMD</> mode</entry>
1541 <entry>Jan-08-99</entry>
1542 <entry>January 8, except error in <literal>YMD</> mode</entry>
1545 <entry>19990108</entry>
1546 <entry>ISO 8601; January 8, 1999 in any mode</entry>
1549 <entry>990108</entry>
1550 <entry>ISO 8601; January 8, 1999 in any mode</entry>
1553 <entry>1999.008</entry>
1554 <entry>year and day of year</entry>
1557 <entry>J2451187</entry>
1558 <entry>Julian day</entry>
1561 <entry>January 8, 99 BC</entry>
1562 <entry>year 99 before the Common Era</entry>
1570 <title>Times</title>
1573 <primary>time</primary>
1576 <primary>time without time zone</primary>
1579 <primary>time with time zone</primary>
1583 The time-of-day types are <type>time [
1584 (<replaceable>p</replaceable>) ] without time zone</type> and
1585 <type>time [ (<replaceable>p</replaceable>) ] with time
1586 zone</type>. Writing just <type>time</type> is equivalent to
1587 <type>time without time zone</type>.
1591 Valid input for these types consists of a time of day followed
1592 by an optional time zone. (See <xref
1593 linkend="datatype-datetime-time-table">
1594 and <xref linkend="datatype-timezone-table">.) If a time zone is
1595 specified in the input for <type>time without time zone</type>,
1596 it is silently ignored. You can also specify a date but it will
1597 be ignored, except when you use a time zone name that involves a
1598 daylight-savings rule, such as
1599 <literal>America/New_York</literal>. In this case specifying the date
1600 is required in order to determine whether standard or daylight-savings
1601 time applies. The appropriate time zone offset is recorded in the
1602 <type>time with time zone</type> value.
1605 <table id="datatype-datetime-time-table">
1606 <title>Time Input</title>
1610 <entry>Example</entry>
1611 <entry>Description</entry>
1616 <entry><literal>04:05:06.789</literal></entry>
1617 <entry>ISO 8601</entry>
1620 <entry><literal>04:05:06</literal></entry>
1621 <entry>ISO 8601</entry>
1624 <entry><literal>04:05</literal></entry>
1625 <entry>ISO 8601</entry>
1628 <entry><literal>040506</literal></entry>
1629 <entry>ISO 8601</entry>
1632 <entry><literal>04:05 AM</literal></entry>
1633 <entry>same as 04:05; AM does not affect value</entry>
1636 <entry><literal>04:05 PM</literal></entry>
1637 <entry>same as 16:05; input hour must be <= 12</entry>
1640 <entry><literal>04:05:06.789-8</literal></entry>
1641 <entry>ISO 8601</entry>
1644 <entry><literal>04:05:06-08:00</literal></entry>
1645 <entry>ISO 8601</entry>
1648 <entry><literal>04:05-08:00</literal></entry>
1649 <entry>ISO 8601</entry>
1652 <entry><literal>040506-08</literal></entry>
1653 <entry>ISO 8601</entry>
1656 <entry><literal>04:05:06 PST</literal></entry>
1657 <entry>time zone specified by abbreviation</entry>
1660 <entry><literal>2003-04-12 04:05:06 America/New_York</literal></entry>
1661 <entry>time zone specified by full name</entry>
1667 <table tocentry="1" id="datatype-timezone-table">
1668 <title>Time Zone Input</title>
1672 <entry>Example</entry>
1673 <entry>Description</entry>
1678 <entry><literal>PST</literal></entry>
1679 <entry>Abbreviation (for Pacific Standard Time)</entry>
1682 <entry><literal>America/New_York</literal></entry>
1683 <entry>Full time zone name</entry>
1686 <entry><literal>PST8PDT</literal></entry>
1687 <entry>POSIX-style time zone specification</entry>
1690 <entry><literal>-8:00</literal></entry>
1691 <entry>ISO-8601 offset for PST</entry>
1694 <entry><literal>-800</literal></entry>
1695 <entry>ISO-8601 offset for PST</entry>
1698 <entry><literal>-8</literal></entry>
1699 <entry>ISO-8601 offset for PST</entry>
1702 <entry><literal>zulu</literal></entry>
1703 <entry>Military abbreviation for UTC</entry>
1706 <entry><literal>z</literal></entry>
1707 <entry>Short form of <literal>zulu</literal></entry>
1714 Refer to <xref linkend="datatype-timezones"> for more information on how
1715 to specify time zones.
1720 <title>Time Stamps</title>
1723 <primary>timestamp</primary>
1727 <primary>timestamp with time zone</primary>
1731 <primary>timestamp without time zone</primary>
1735 Valid input for the time stamp types consists of a concatenation
1736 of a date and a time, followed by an optional time zone,
1737 followed by an optional <literal>AD</literal> or <literal>BC</literal>.
1738 (Alternatively, <literal>AD</literal>/<literal>BC</literal> can appear
1739 before the time zone, but this is not the preferred ordering.)
1747 1999-01-08 04:05:06 -8:00
1750 are valid values, which follow the <acronym>ISO</acronym> 8601
1751 standard. In addition, the wide-spread format
1753 January 8 04:05:06 1999 PST
1759 The <acronym>SQL</acronym> standard differentiates <type>timestamp without time zone</type>
1760 and <type>timestamp with time zone</type> literals by the presence of a
1761 <quote>+</quote> or <quote>-</quote>. Hence, according to the standard,
1762 <programlisting>TIMESTAMP '2004-10-19 10:23:54'</programlisting>
1763 is a <type>timestamp without time zone</type>, while
1764 <programlisting>TIMESTAMP '2004-10-19 10:23:54+02'</programlisting>
1765 is a <type>timestamp with time zone</type>.
1766 <productname>PostgreSQL</productname> never examines the content of a
1767 literal string before determining its type, and therefore will treat
1768 both of the above as <type>timestamp without time zone</type>. To
1769 ensure that a literal is treated as <type>timestamp with time
1770 zone</type>, give it the correct explicit type:
1771 <programlisting>TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'</programlisting>
1772 In a literal that has been decided to be <type>timestamp without time
1773 zone</type>, <productname>PostgreSQL</productname> will silently ignore
1774 any time zone indication.
1775 That is, the resulting value is derived from the date/time
1776 fields in the input value, and is not adjusted for time zone.
1780 For <type>timestamp with time zone</type>, the internally stored
1781 value is always in UTC (Universal
1782 Coordinated Time, traditionally known as Greenwich Mean Time,
1783 <acronym>GMT</>). An input value that has an explicit
1784 time zone specified is converted to UTC using the appropriate offset
1785 for that time zone. If no time zone is stated in the input string,
1786 then it is assumed to be in the time zone indicated by the system's
1787 <xref linkend="guc-timezone"> parameter, and is converted to UTC using the
1788 offset for the <varname>timezone</> zone.
1792 When a <type>timestamp with time
1793 zone</type> value is output, it is always converted from UTC to the
1794 current <varname>timezone</> zone, and displayed as local time in that
1795 zone. To see the time in another time zone, either change
1796 <varname>timezone</> or use the <literal>AT TIME ZONE</> construct
1797 (see <xref linkend="functions-datetime-zoneconvert">).
1801 Conversions between <type>timestamp without time zone</type> and
1802 <type>timestamp with time zone</type> normally assume that the
1803 <type>timestamp without time zone</type> value should be taken or given
1804 as <varname>timezone</> local time. A different zone reference can
1805 be specified for the conversion using <literal>AT TIME ZONE</>.
1810 <title>Intervals</title>
1813 <primary>interval</primary>
1817 <type>interval</type> values can be written with the following syntax:
1820 <optional>@</> <replaceable>quantity</> <replaceable>unit</> <optional><replaceable>quantity</> <replaceable>unit</>...</> <optional><replaceable>direction</></optional>
1823 Where: <replaceable>quantity</> is a number (possibly signed);
1824 <replaceable>unit</> is <literal>second</literal>,
1825 <literal>minute</literal>, <literal>hour</literal>, <literal>day</literal>,
1826 <literal>week</literal>, <literal>month</literal>, <literal>year</literal>,
1827 <literal>decade</literal>, <literal>century</literal>, <literal>millennium</literal>,
1828 or abbreviations or plurals of these units;
1829 <replaceable>direction</> can be <literal>ago</literal> or
1830 empty. The at sign (<literal>@</>) is optional noise. The amounts
1831 of different units are implicitly added up with appropriate
1836 Quantities of days, hours, minutes, and seconds can be specified without
1837 explicit unit markings. For example, <literal>'1 12:59:10'</> is read
1838 the same as <literal>'1 day 12 hours 59 min 10 sec'</>.
1842 The optional subsecond precision <replaceable>p</replaceable> should
1843 be between 0 and 6, and defaults to the precision of the input literal.
1847 Internally <type>interval</> values are stored as months, days,
1848 and seconds. This is done because the number of days in a month
1849 varies, and a day can have 23 or 25 hours if a daylight savings
1850 time adjustment is involved. Because intervals are usually created
1851 from constant strings or <type>timestamp</> subtraction, this
1852 storage method works well in most cases. Functions
1853 <function>justify_days</> and <function>justify_hours</> are
1854 available for adjusting days and hours that overflow their normal
1860 <title>Special Values</title>
1863 <primary>time</primary>
1864 <secondary>constants</secondary>
1868 <primary>date</primary>
1869 <secondary>constants</secondary>
1873 <productname>PostgreSQL</productname> supports several
1874 special date/time input values for convenience, as shown in <xref
1875 linkend="datatype-datetime-special-table">. The values
1876 <literal>infinity</literal> and <literal>-infinity</literal>
1877 are specially represented inside the system and will be displayed
1878 the same way; but the others are simply notational shorthands
1879 that will be converted to ordinary date/time values when read.
1880 (In particular, <literal>now</> and related strings are converted
1881 to a specific time value as soon as they are read.)
1882 All of these values need to be written in single quotes when used
1883 as constants in SQL commands.
1886 <table id="datatype-datetime-special-table">
1887 <title>Special Date/Time Inputs</title>
1891 <entry>Input String</entry>
1892 <entry>Valid Types</entry>
1893 <entry>Description</entry>
1898 <entry><literal>epoch</literal></entry>
1899 <entry><type>date</type>, <type>timestamp</type></entry>
1900 <entry>1970-01-01 00:00:00+00 (Unix system time zero)</entry>
1903 <entry><literal>infinity</literal></entry>
1904 <entry><type>timestamp</type></entry>
1905 <entry>later than all other time stamps</entry>
1908 <entry><literal>-infinity</literal></entry>
1909 <entry><type>timestamp</type></entry>
1910 <entry>earlier than all other time stamps</entry>
1913 <entry><literal>now</literal></entry>
1914 <entry><type>date</type>, <type>time</type>, <type>timestamp</type></entry>
1915 <entry>current transaction's start time</entry>
1918 <entry><literal>today</literal></entry>
1919 <entry><type>date</type>, <type>timestamp</type></entry>
1920 <entry>midnight today</entry>
1923 <entry><literal>tomorrow</literal></entry>
1924 <entry><type>date</type>, <type>timestamp</type></entry>
1925 <entry>midnight tomorrow</entry>
1928 <entry><literal>yesterday</literal></entry>
1929 <entry><type>date</type>, <type>timestamp</type></entry>
1930 <entry>midnight yesterday</entry>
1933 <entry><literal>allballs</literal></entry>
1934 <entry><type>time</type></entry>
1935 <entry>00:00:00.00 UTC</entry>
1942 The following <acronym>SQL</acronym>-compatible functions can also
1943 be used to obtain the current time value for the corresponding data
1945 <literal>CURRENT_DATE</literal>, <literal>CURRENT_TIME</literal>,
1946 <literal>CURRENT_TIMESTAMP</literal>, <literal>LOCALTIME</literal>,
1947 <literal>LOCALTIMESTAMP</literal>. The latter four accept an
1948 optional subsecond precision specification. (See <xref
1949 linkend="functions-datetime-current">.) Note however that these are
1950 SQL functions and are <emphasis>not</> recognized as data input strings.
1956 <sect2 id="datatype-datetime-output">
1957 <title>Date/Time Output</title>
1960 <primary>date</primary>
1961 <secondary>output format</secondary>
1962 <seealso>formatting</seealso>
1966 <primary>time</primary>
1967 <secondary>output format</secondary>
1968 <seealso>formatting</seealso>
1972 The output format of the date/time types can be set to one of the four
1974 <acronym>SQL</acronym> (Ingres), traditional POSTGRES, and
1975 German, using the command <literal>SET datestyle</literal>. The default
1976 is the <acronym>ISO</acronym> format. (The
1977 <acronym>SQL</acronym> standard requires the use of the ISO 8601
1978 format. The name of the <quote>SQL</quote> output format is a
1979 historical accident.) <xref
1980 linkend="datatype-datetime-output-table"> shows examples of each
1981 output style. The output of the <type>date</type> and
1982 <type>time</type> types is of course only the date or time part
1983 in accordance with the given examples.
1986 <table id="datatype-datetime-output-table">
1987 <title>Date/Time Output Styles</title>
1991 <entry>Style Specification</entry>
1992 <entry>Description</entry>
1993 <entry>Example</entry>
1999 <entry>ISO 8601/SQL standard</entry>
2000 <entry>1997-12-17 07:37:16-08</entry>
2004 <entry>traditional style</entry>
2005 <entry>12/17/1997 07:37:16.00 PST</entry>
2008 <entry>POSTGRES</entry>
2009 <entry>original style</entry>
2010 <entry>Wed Dec 17 07:37:16 1997 PST</entry>
2013 <entry>German</entry>
2014 <entry>regional style</entry>
2015 <entry>17.12.1997 07:37:16.00 PST</entry>
2022 In the <acronym>SQL</acronym> and POSTGRES styles, day appears before
2023 month if DMY field ordering has been specified, otherwise month appears
2025 (See <xref linkend="datatype-datetime-input">
2026 for how this setting also affects interpretation of input values.)
2027 <xref linkend="datatype-datetime-output2-table"> shows an
2031 <table id="datatype-datetime-output2-table">
2032 <title>Date Order Conventions</title>
2036 <entry><varname>datestyle</varname> Setting</entry>
2037 <entry>Input Ordering</entry>
2038 <entry>Example Output</entry>
2043 <entry><literal>SQL, DMY</></entry>
2044 <entry><replaceable>day</replaceable>/<replaceable>month</replaceable>/<replaceable>year</replaceable></entry>
2045 <entry>17/12/1997 15:37:16.00 CET</entry>
2048 <entry><literal>SQL, MDY</></entry>
2049 <entry><replaceable>month</replaceable>/<replaceable>day</replaceable>/<replaceable>year</replaceable></entry>
2050 <entry>12/17/1997 07:37:16.00 PST</entry>
2053 <entry><literal>Postgres, DMY</></entry>
2054 <entry><replaceable>day</replaceable>/<replaceable>month</replaceable>/<replaceable>year</replaceable></entry>
2055 <entry>Wed 17 Dec 07:37:16 1997 PST</entry>
2062 <type>interval</type> output looks like the input format, except
2063 that units like <literal>century</literal> or
2064 <literal>week</literal> are converted to years and days and
2065 <literal>ago</literal> is converted to an appropriate sign. In
2066 ISO mode the output looks like
2069 <optional> <replaceable>quantity</> <replaceable>unit</> <optional> ... </> </> <optional> <replaceable>days</> </> <optional> <replaceable>hours</>:<replaceable>minutes</>:<replaceable>seconds</> </optional>
2074 The date/time styles can be selected by the user using the
2075 <command>SET datestyle</command> command, the <xref
2076 linkend="guc-datestyle"> parameter in the
2077 <filename>postgresql.conf</filename> configuration file, or the
2078 <envar>PGDATESTYLE</envar> environment variable on the server or
2079 client. The formatting function <function>to_char</function>
2080 (see <xref linkend="functions-formatting">) is also available as
2081 a more flexible way to format the date/time output.
2085 <sect2 id="datatype-timezones">
2086 <title>Time Zones</title>
2088 <indexterm zone="datatype-timezones">
2089 <primary>time zone</primary>
2093 Time zones, and time-zone conventions, are influenced by
2094 political decisions, not just earth geometry. Time zones around the
2095 world became somewhat standardized during the 1900's,
2096 but continue to be prone to arbitrary changes, particularly with
2097 respect to daylight-savings rules.
2098 <productname>PostgreSQL</productname> currently supports daylight-savings
2099 rules over the time period 1902 through 2038 (corresponding to the full
2100 range of conventional Unix system time). Times outside that range are
2101 taken to be in <quote>standard time</> for the selected time zone, no
2102 matter what part of the year they fall in.
2106 <productname>PostgreSQL</productname> endeavors to be compatible with
2107 the <acronym>SQL</acronym> standard definitions for typical usage.
2108 However, the <acronym>SQL</acronym> standard has an odd mix of date and
2109 time types and capabilities. Two obvious problems are:
2114 Although the <type>date</type> type
2115 does not have an associated time zone, the
2116 <type>time</type> type can.
2117 Time zones in the real world have little meaning unless
2118 associated with a date as well as a time,
2119 since the offset may vary through the year with daylight-saving
2126 The default time zone is specified as a constant numeric offset
2127 from <acronym>UTC</>. It is therefore not possible to adapt to
2128 daylight-saving time when doing date/time arithmetic across
2129 <acronym>DST</acronym> boundaries.
2137 To address these difficulties, we recommend using date/time types
2138 that contain both date and time when using time zones. We
2139 recommend <emphasis>not</emphasis> using the type <type>time with
2140 time zone</type> (though it is supported by
2141 <productname>PostgreSQL</productname> for legacy applications and
2142 for compliance with the <acronym>SQL</acronym> standard).
2143 <productname>PostgreSQL</productname> assumes
2144 your local time zone for any type containing only date or time.
2148 All timezone-aware dates and times are stored internally in
2149 <acronym>UTC</acronym>. They are converted to local time
2150 in the zone specified by the <xref linkend="guc-timezone"> configuration
2151 parameter before being displayed to the client.
2155 <productname>PostgreSQL</productname> allows you to specify time zones in
2156 three different forms:
2160 A full time zone name, for example <literal>America/New_York</>.
2161 The recognized time zone names are listed in the
2162 <literal>pg_timezone_names</literal> view (see <xref
2163 linkend="view-pg-timezone-names">).
2164 <productname>PostgreSQL</productname> uses the widely-used
2165 <literal>zic</> time zone data for this purpose, so the same
2166 names are also recognized by much other software.
2171 A time zone abbreviation, for example <literal>PST</>. Such a
2172 specification merely defines a particular offset from UTC, in
2173 contrast to full time zone names which may imply a set of daylight
2174 savings transition-date rules as well. The recognized abbreviations
2175 are listed in the <literal>pg_timezone_abbrevs</> view (see <xref
2176 linkend="view-pg-timezone-abbrevs">). You cannot set the
2177 configuration parameter <xref linkend="guc-timezone"> using a time
2178 zone abbreviation, but you can use abbreviations in
2179 date/time input values and with the <literal>AT TIME ZONE</>
2185 In addition to the timezone names and abbreviations,
2186 <productname>PostgreSQL</productname> will accept POSIX-style time zone
2187 specifications of the form <replaceable>STD</><replaceable>offset</> or
2188 <replaceable>STD</><replaceable>offset</><replaceable>DST</>, where
2189 <replaceable>STD</> is a zone abbreviation, <replaceable>offset</> is a
2190 numeric offset in hours west from UTC, and <replaceable>DST</> is an
2191 optional daylight-savings zone abbreviation, assumed to stand for one
2192 hour ahead of the given offset. For example, if <literal>EST5EDT</>
2193 were not already a recognized zone name, it would be accepted and would
2194 be functionally equivalent to USA East Coast time. When a
2195 daylight-savings zone name is present, it is assumed to be used
2196 according to USA time zone rules, so this feature is of limited use
2197 outside North America. One should also be wary that this provision can
2198 lead to silently accepting bogus input, since there is no check on the
2199 reasonableness of the zone abbreviations. For example, <literal>SET
2200 TIMEZONE TO FOOBAR0</> will work, leaving the system effectively using
2201 a rather peculiar abbreviation for GMT.
2206 There is a conceptual and practical difference between the abbreviations
2207 and the full names: abbreviations always represent a fixed offset from
2208 UTC, whereas most of the full names imply a local daylight-savings time
2209 rule and so have two possible UTC offsets.
2213 In all cases, timezone names are recognized case-insensitively.
2214 (This is a change from <productname>PostgreSQL</productname> versions
2215 prior to 8.2, which were case-sensitive in some contexts and not others.)
2219 Neither full names nor abbreviations are hard-wired into the server;
2220 they are obtained from configuration files stored under
2221 <filename>.../share/timezone/</> and <filename>.../share/timezonesets/</>
2222 of the installation directory
2223 (see <xref linkend="datetime-config-files">).
2227 The <xref linkend="guc-timezone"> configuration parameter can
2228 be set in the file <filename>postgresql.conf</>, or in any of the
2229 other standard ways described in <xref linkend="runtime-config">.
2230 There are also several special ways to set it:
2235 If <varname>timezone</> is not specified in
2236 <filename>postgresql.conf</> nor as a server command-line option,
2237 the server attempts to use the value of the <envar>TZ</envar>
2238 environment variable as the default time zone. If <envar>TZ</envar>
2239 is not defined or is not any of the time zone names known to
2240 <productname>PostgreSQL</productname>, the server attempts to
2241 determine the operating system's default time zone by checking the
2242 behavior of the C library function <literal>localtime()</>. The
2243 default time zone is selected as the closest match among
2244 <productname>PostgreSQL</productname>'s known time zones.
2250 The <acronym>SQL</acronym> command <command>SET TIME ZONE</command>
2251 sets the time zone for the session. This is an alternative spelling
2252 of <command>SET TIMEZONE TO</> with a more SQL-spec-compatible syntax.
2258 The <envar>PGTZ</envar> environment variable, if set at the
2259 client, is used by <application>libpq</application>
2260 applications to send a <command>SET TIME ZONE</command>
2261 command to the server upon connection.
2268 <sect2 id="datatype-datetime-internals">
2269 <title>Internals</title>
2272 <productname>PostgreSQL</productname> uses Julian dates
2273 for all date/time calculations. They have the nice property of correctly
2274 predicting/calculating any date more recent than 4713 BC
2275 to far into the future, using the assumption that the length of the
2276 year is 365.2425 days.
2280 Date conventions before the 19th century make for interesting reading,
2281 but are not consistent enough to warrant coding into a date/time handler.
2287 <sect1 id="datatype-boolean">
2288 <title>Boolean Type</title>
2290 <indexterm zone="datatype-boolean">
2291 <primary>Boolean</primary>
2292 <secondary>data type</secondary>
2295 <indexterm zone="datatype-boolean">
2296 <primary>true</primary>
2299 <indexterm zone="datatype-boolean">
2300 <primary>false</primary>
2304 <productname>PostgreSQL</productname> provides the
2305 standard <acronym>SQL</acronym> type <type>boolean</type>.
2306 <type>boolean</type> can have one of only two states:
2307 <quote>true</quote> or <quote>false</quote>. A third state,
2308 <quote>unknown</quote>, is represented by the
2309 <acronym>SQL</acronym> null value.
2313 Valid literal values for the <quote>true</quote> state are:
2315 <member><literal>TRUE</literal></member>
2316 <member><literal>'t'</literal></member>
2317 <member><literal>'true'</literal></member>
2318 <member><literal>'y'</literal></member>
2319 <member><literal>'yes'</literal></member>
2320 <member><literal>'1'</literal></member>
2322 For the <quote>false</quote> state, the following values can be
2325 <member><literal>FALSE</literal></member>
2326 <member><literal>'f'</literal></member>
2327 <member><literal>'false'</literal></member>
2328 <member><literal>'n'</literal></member>
2329 <member><literal>'no'</literal></member>
2330 <member><literal>'0'</literal></member>
2332 Using the key words <literal>TRUE</literal> and
2333 <literal>FALSE</literal> is preferred (and
2334 <acronym>SQL</acronym>-compliant).
2337 <example id="datatype-boolean-example">
2338 <title>Using the <type>boolean</type> type</title>
2341 CREATE TABLE test1 (a boolean, b text);
2342 INSERT INTO test1 VALUES (TRUE, 'sic est');
2343 INSERT INTO test1 VALUES (FALSE, 'non est');
2344 SELECT * FROM test1;
2350 SELECT * FROM test1 WHERE a;
2358 <xref linkend="datatype-boolean-example"> shows that
2359 <type>boolean</type> values are output using the letters
2360 <literal>t</literal> and <literal>f</literal>.
2364 <type>boolean</type> uses 1 byte of storage.
2368 <sect1 id="datatype-geometric">
2369 <title>Geometric Types</title>
2372 Geometric data types represent two-dimensional spatial
2373 objects. <xref linkend="datatype-geo-table"> shows the geometric
2374 types available in <productname>PostgreSQL</productname>. The
2375 most fundamental type, the point, forms the basis for all of the
2379 <table id="datatype-geo-table">
2380 <title>Geometric Types</title>
2385 <entry>Storage Size</entry>
2386 <entry>Representation</entry>
2387 <entry>Description</entry>
2392 <entry><type>point</type></entry>
2393 <entry>16 bytes</entry>
2394 <entry>Point on the plane</entry>
2395 <entry>(x,y)</entry>
2398 <entry><type>line</type></entry>
2399 <entry>32 bytes</entry>
2400 <entry>Infinite line (not fully implemented)</entry>
2401 <entry>((x1,y1),(x2,y2))</entry>
2404 <entry><type>lseg</type></entry>
2405 <entry>32 bytes</entry>
2406 <entry>Finite line segment</entry>
2407 <entry>((x1,y1),(x2,y2))</entry>
2410 <entry><type>box</type></entry>
2411 <entry>32 bytes</entry>
2412 <entry>Rectangular box</entry>
2413 <entry>((x1,y1),(x2,y2))</entry>
2416 <entry><type>path</type></entry>
2417 <entry>16+16n bytes</entry>
2418 <entry>Closed path (similar to polygon)</entry>
2419 <entry>((x1,y1),...)</entry>
2422 <entry><type>path</type></entry>
2423 <entry>16+16n bytes</entry>
2424 <entry>Open path</entry>
2425 <entry>[(x1,y1),...]</entry>
2428 <entry><type>polygon</type></entry>
2429 <entry>40+16n bytes</entry>
2430 <entry>Polygon (similar to closed path)</entry>
2431 <entry>((x1,y1),...)</entry>
2434 <entry><type>circle</type></entry>
2435 <entry>24 bytes</entry>
2436 <entry>Circle</entry>
2437 <entry><(x,y),r> (center and radius)</entry>
2444 A rich set of functions and operators is available to perform various geometric
2445 operations such as scaling, translation, rotation, and determining
2446 intersections. They are explained in <xref linkend="functions-geometry">.
2450 <title>Points</title>
2453 <primary>point</primary>
2457 Points are the fundamental two-dimensional building block for geometric types.
2458 Values of type <type>point</type> are specified using the following syntax:
2461 ( <replaceable>x</replaceable> , <replaceable>y</replaceable> )
2462 <replaceable>x</replaceable> , <replaceable>y</replaceable>
2465 where <replaceable>x</> and <replaceable>y</> are the respective
2466 coordinates as floating-point numbers.
2471 <title>Line Segments</title>
2474 <primary>lseg</primary>
2478 <primary>line segment</primary>
2482 Line segments (<type>lseg</type>) are represented by pairs of points.
2483 Values of type <type>lseg</type> are specified using the following syntax:
2486 ( ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ( <replaceable>x2</replaceable> , <replaceable>y2</replaceable> ) )
2487 ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ( <replaceable>x2</replaceable> , <replaceable>y2</replaceable> )
2488 <replaceable>x1</replaceable> , <replaceable>y1</replaceable> , <replaceable>x2</replaceable> , <replaceable>y2</replaceable>
2492 <literal>(<replaceable>x1</replaceable>,<replaceable>y1</replaceable>)</literal>
2494 <literal>(<replaceable>x2</replaceable>,<replaceable>y2</replaceable>)</literal>
2495 are the end points of the line segment.
2500 <title>Boxes</title>
2503 <primary>box (data type)</primary>
2507 <primary>rectangle</primary>
2511 Boxes are represented by pairs of points that are opposite
2513 Values of type <type>box</type> are specified using the following syntax:
2516 ( ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ( <replaceable>x2</replaceable> , <replaceable>y2</replaceable> ) )
2517 ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ( <replaceable>x2</replaceable> , <replaceable>y2</replaceable> )
2518 <replaceable>x1</replaceable> , <replaceable>y1</replaceable> , <replaceable>x2</replaceable> , <replaceable>y2</replaceable>
2522 <literal>(<replaceable>x1</replaceable>,<replaceable>y1</replaceable>)</literal>
2524 <literal>(<replaceable>x2</replaceable>,<replaceable>y2</replaceable>)</literal>
2525 are any two opposite corners of the box.
2529 Boxes are output using the first syntax.
2530 The corners are reordered on input to store
2531 the upper right corner, then the lower left corner.
2532 Other corners of the box can be entered, but the lower
2533 left and upper right corners are determined from the input and stored.
2538 <title>Paths</title>
2541 <primary>path (data type)</primary>
2545 Paths are represented by lists of connected points. Paths can be
2546 <firstterm>open</firstterm>, where
2547 the first and last points in the list are not considered connected, or
2548 <firstterm>closed</firstterm>,
2549 where the first and last points are considered connected.
2553 Values of type <type>path</type> are specified using the following syntax:
2556 ( ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ... , ( <replaceable>xn</replaceable> , <replaceable>yn</replaceable> ) )
2557 [ ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ... , ( <replaceable>xn</replaceable> , <replaceable>yn</replaceable> ) ]
2558 ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ... , ( <replaceable>xn</replaceable> , <replaceable>yn</replaceable> )
2559 ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> , ... , <replaceable>xn</replaceable> , <replaceable>yn</replaceable> )
2560 <replaceable>x1</replaceable> , <replaceable>y1</replaceable> , ... , <replaceable>xn</replaceable> , <replaceable>yn</replaceable>
2563 where the points are the end points of the line segments
2564 comprising the path. Square brackets (<literal>[]</>) indicate
2565 an open path, while parentheses (<literal>()</>) indicate a
2570 Paths are output using the first syntax.
2575 <title>Polygons</title>
2578 <primary>polygon</primary>
2582 Polygons are represented by lists of points (the vertexes of the
2583 polygon). Polygons should probably be
2584 considered equivalent to closed paths, but are stored differently
2585 and have their own set of support routines.
2589 Values of type <type>polygon</type> are specified using the following syntax:
2592 ( ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ... , ( <replaceable>xn</replaceable> , <replaceable>yn</replaceable> ) )
2593 ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ... , ( <replaceable>xn</replaceable> , <replaceable>yn</replaceable> )
2594 ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> , ... , <replaceable>xn</replaceable> , <replaceable>yn</replaceable> )
2595 <replaceable>x1</replaceable> , <replaceable>y1</replaceable> , ... , <replaceable>xn</replaceable> , <replaceable>yn</replaceable>
2598 where the points are the end points of the line segments
2599 comprising the boundary of the polygon.
2603 Polygons are output using the first syntax.
2608 <title>Circles</title>
2611 <primary>circle</primary>
2615 Circles are represented by a center point and a radius.
2616 Values of type <type>circle</type> are specified using the following syntax:
2619 < ( <replaceable>x</replaceable> , <replaceable>y</replaceable> ) , <replaceable>r</replaceable> >
2620 ( ( <replaceable>x</replaceable> , <replaceable>y</replaceable> ) , <replaceable>r</replaceable> )
2621 ( <replaceable>x</replaceable> , <replaceable>y</replaceable> ) , <replaceable>r</replaceable>
2622 <replaceable>x</replaceable> , <replaceable>y</replaceable> , <replaceable>r</replaceable>
2626 <literal>(<replaceable>x</replaceable>,<replaceable>y</replaceable>)</literal>
2627 is the center and <replaceable>r</replaceable> is the radius of the circle.
2631 Circles are output using the first syntax.
2637 <sect1 id="datatype-net-types">
2638 <title>Network Address Types</title>
2640 <indexterm zone="datatype-net-types">
2641 <primary>network</primary>
2642 <secondary>data types</secondary>
2646 <productname>PostgreSQL</> offers data types to store IPv4, IPv6, and MAC
2647 addresses, as shown in <xref linkend="datatype-net-types-table">. It
2648 is preferable to use these types instead of plain text types to store
2649 network addresses, because
2650 these types offer input error checking and several specialized
2651 operators and functions (see <xref linkend="functions-net">).
2654 <table tocentry="1" id="datatype-net-types-table">
2655 <title>Network Address Types</title>
2660 <entry>Storage Size</entry>
2661 <entry>Description</entry>
2667 <entry><type>cidr</type></entry>
2668 <entry>12 or 24 bytes</entry>
2669 <entry>IPv4 and IPv6 networks</entry>
2673 <entry><type>inet</type></entry>
2674 <entry>12 or 24 bytes</entry>
2675 <entry>IPv4 and IPv6 hosts and networks</entry>
2679 <entry><type>macaddr</type></entry>
2680 <entry>6 bytes</entry>
2681 <entry>MAC addresses</entry>
2689 When sorting <type>inet</type> or <type>cidr</type> data types,
2690 IPv4 addresses will always sort before IPv6 addresses, including
2691 IPv4 addresses encapsulated or mapped into IPv6 addresses, such as
2692 ::10.2.3.4 or ::ffff::10.4.3.2.
2696 <sect2 id="datatype-inet">
2697 <title><type>inet</type></title>
2700 <primary>inet (data type)</primary>
2704 The <type>inet</type> type holds an IPv4 or IPv6 host address, and
2705 optionally the identity of the subnet it is in, all in one field.
2706 The subnet identity is represented by stating how many bits of
2707 the host address represent the network address (the
2708 <quote>netmask</quote>). If the netmask is 32 and the address is IPv4,
2709 then the value does not indicate a subnet, only a single host.
2710 In IPv6, the address length is 128 bits, so 128 bits specify a
2711 unique host address. Note that if you
2712 want to accept networks only, you should use the
2713 <type>cidr</type> type rather than <type>inet</type>.
2717 The input format for this type is
2718 <replaceable class="parameter">address/y</replaceable>
2720 <replaceable class="parameter">address</replaceable>
2721 is an IPv4 or IPv6 address and
2722 <replaceable class="parameter">y</replaceable>
2723 is the number of bits in the netmask. If the
2724 <replaceable class="parameter">/y</replaceable>
2725 part is left off, then the
2726 netmask is 32 for IPv4 and 128 for IPv6, so the value represents
2727 just a single host. On display, the
2728 <replaceable class="parameter">/y</replaceable>
2729 portion is suppressed if the netmask specifies a single host.
2733 <sect2 id="datatype-cidr">
2734 <title><type>cidr</></title>
2737 <primary>cidr</primary>
2741 The <type>cidr</type> type holds an IPv4 or IPv6 network specification.
2742 Input and output formats follow Classless Internet Domain Routing
2744 The format for specifying networks is <replaceable
2745 class="parameter">address/y</> where <replaceable
2746 class="parameter">address</> is the network represented as an
2747 IPv4 or IPv6 address, and <replaceable
2748 class="parameter">y</> is the number of bits in the netmask. If
2749 <replaceable class="parameter">y</> is omitted, it is calculated
2750 using assumptions from the older classful network numbering system, except
2751 that it will be at least large enough to include all of the octets
2752 written in the input. It is an error to specify a network address
2753 that has bits set to the right of the specified netmask.
2757 <xref linkend="datatype-net-cidr-table"> shows some examples.
2760 <table id="datatype-net-cidr-table">
2761 <title><type>cidr</> Type Input Examples</title>
2765 <entry><type>cidr</type> Input</entry>
2766 <entry><type>cidr</type> Output</entry>
2767 <entry><literal><function>abbrev</function>(<type>cidr</type>)</literal></entry>
2772 <entry>192.168.100.128/25</entry>
2773 <entry>192.168.100.128/25</entry>
2774 <entry>192.168.100.128/25</entry>
2777 <entry>192.168/24</entry>
2778 <entry>192.168.0.0/24</entry>
2779 <entry>192.168.0/24</entry>
2782 <entry>192.168/25</entry>
2783 <entry>192.168.0.0/25</entry>
2784 <entry>192.168.0.0/25</entry>
2787 <entry>192.168.1</entry>
2788 <entry>192.168.1.0/24</entry>
2789 <entry>192.168.1/24</entry>
2792 <entry>192.168</entry>
2793 <entry>192.168.0.0/24</entry>
2794 <entry>192.168.0/24</entry>
2797 <entry>128.1</entry>
2798 <entry>128.1.0.0/16</entry>
2799 <entry>128.1/16</entry>
2803 <entry>128.0.0.0/16</entry>
2804 <entry>128.0/16</entry>
2807 <entry>128.1.2</entry>
2808 <entry>128.1.2.0/24</entry>
2809 <entry>128.1.2/24</entry>
2812 <entry>10.1.2</entry>
2813 <entry>10.1.2.0/24</entry>
2814 <entry>10.1.2/24</entry>
2818 <entry>10.1.0.0/16</entry>
2819 <entry>10.1/16</entry>
2823 <entry>10.0.0.0/8</entry>
2827 <entry>10.1.2.3/32</entry>
2828 <entry>10.1.2.3/32</entry>
2829 <entry>10.1.2.3/32</entry>
2832 <entry>2001:4f8:3:ba::/64</entry>
2833 <entry>2001:4f8:3:ba::/64</entry>
2834 <entry>2001:4f8:3:ba::/64</entry>
2837 <entry>2001:4f8:3:ba:2e0:81ff:fe22:d1f1/128</entry>
2838 <entry>2001:4f8:3:ba:2e0:81ff:fe22:d1f1/128</entry>
2839 <entry>2001:4f8:3:ba:2e0:81ff:fe22:d1f1</entry>
2842 <entry>::ffff:1.2.3.0/120</entry>
2843 <entry>::ffff:1.2.3.0/120</entry>
2844 <entry>::ffff:1.2.3/120</entry>
2847 <entry>::ffff:1.2.3.0/128</entry>
2848 <entry>::ffff:1.2.3.0/128</entry>
2849 <entry>::ffff:1.2.3.0/128</entry>
2856 <sect2 id="datatype-inet-vs-cidr">
2857 <title><type>inet</type> vs. <type>cidr</type></title>
2860 The essential difference between <type>inet</type> and <type>cidr</type>
2861 data types is that <type>inet</type> accepts values with nonzero bits to
2862 the right of the netmask, whereas <type>cidr</type> does not.
2867 If you do not like the output format for <type>inet</type> or
2868 <type>cidr</type> values, try the functions <function>host</>,
2869 <function>text</>, and <function>abbrev</>.
2874 <sect2 id="datatype-macaddr">
2875 <title><type>macaddr</></>
2878 <primary>macaddr (data type)</primary>
2882 <primary>MAC address</primary>
2887 The <type>macaddr</> type stores MAC addresses, i.e., Ethernet
2888 card hardware addresses (although MAC addresses are used for
2889 other purposes as well). Input is accepted in various customary
2893 <member><literal>'08002b:010203'</></member>
2894 <member><literal>'08002b-010203'</></member>
2895 <member><literal>'0800.2b01.0203'</></member>
2896 <member><literal>'08-00-2b-01-02-03'</></member>
2897 <member><literal>'08:00:2b:01:02:03'</></member>
2900 which would all specify the same
2901 address. Upper and lower case is accepted for the digits
2902 <literal>a</> through <literal>f</>. Output is always in the
2903 last of the forms shown.
2909 <sect1 id="datatype-bit">
2910 <title>Bit String Types</title>
2912 <indexterm zone="datatype-bit">
2913 <primary>bit string</primary>
2914 <secondary>data type</secondary>
2918 Bit strings are strings of 1's and 0's. They can be used to store
2919 or visualize bit masks. There are two SQL bit types:
2920 <type>bit(<replaceable>n</replaceable>)</type> and <type>bit
2921 varying(<replaceable>n</replaceable>)</type>, where
2922 <replaceable>n</replaceable> is a positive integer.
2926 <type>bit</type> type data must match the length
2927 <replaceable>n</replaceable> exactly; it is an error to attempt to
2928 store shorter or longer bit strings. <type>bit varying</type> data is
2929 of variable length up to the maximum length
2930 <replaceable>n</replaceable>; longer strings will be rejected.
2931 Writing <type>bit</type> without a length is equivalent to
2932 <literal>bit(1)</literal>, while <type>bit varying</type> without a length
2933 specification means unlimited length.
2938 If one explicitly casts a bit-string value to
2939 <type>bit(<replaceable>n</>)</type>, it will be truncated or
2940 zero-padded on the right to be exactly <replaceable>n</> bits,
2941 without raising an error. Similarly,
2942 if one explicitly casts a bit-string value to
2943 <type>bit varying(<replaceable>n</>)</type>, it will be truncated
2944 on the right if it is more than <replaceable>n</> bits.
2950 linkend="sql-syntax-bit-strings"> for information about the syntax
2951 of bit string constants. Bit-logical operators and string
2952 manipulation functions are available; see <xref
2953 linkend="functions-bitstring">.
2957 <title>Using the bit string types</title>
2960 CREATE TABLE test (a BIT(3), b BIT VARYING(5));
2961 INSERT INTO test VALUES (B'101', B'00');
2962 INSERT INTO test VALUES (B'10', B'101');
2964 ERROR: bit string length 2 does not match type bit(3)
2966 INSERT INTO test VALUES (B'10'::bit(3), B'101');
2983 <sect1 id="datatype-oid">
2984 <title>Object Identifier Types</title>
2986 <indexterm zone="datatype-oid">
2987 <primary>object identifier</primary>
2988 <secondary>data type</secondary>
2991 <indexterm zone="datatype-oid">
2992 <primary>oid</primary>
2995 <indexterm zone="datatype-oid">
2996 <primary>regproc</primary>
2999 <indexterm zone="datatype-oid">
3000 <primary>regprocedure</primary>
3003 <indexterm zone="datatype-oid">
3004 <primary>regoper</primary>
3007 <indexterm zone="datatype-oid">
3008 <primary>regoperator</primary>
3011 <indexterm zone="datatype-oid">
3012 <primary>regclass</primary>
3015 <indexterm zone="datatype-oid">
3016 <primary>regtype</primary>
3019 <indexterm zone="datatype-oid">
3020 <primary>xid</primary>
3023 <indexterm zone="datatype-oid">
3024 <primary>cid</primary>
3027 <indexterm zone="datatype-oid">
3028 <primary>tid</primary>
3032 Object identifiers (OIDs) are used internally by
3033 <productname>PostgreSQL</productname> as primary keys for various
3034 system tables. OIDs are not added to user-created tables, unless
3035 <literal>WITH OIDS</literal> is specified when the table is
3036 created, or the <xref linkend="guc-default-with-oids">
3037 configuration variable is enabled. Type <type>oid</> represents
3038 an object identifier. There are also several alias types for
3039 <type>oid</>: <type>regproc</>, <type>regprocedure</>,
3040 <type>regoper</>, <type>regoperator</>, <type>regclass</>, and
3041 <type>regtype</>. <xref linkend="datatype-oid-table"> shows an
3046 The <type>oid</> type is currently implemented as an unsigned
3047 four-byte integer. Therefore, it is not large enough to provide
3048 database-wide uniqueness in large databases, or even in large
3049 individual tables. So, using a user-created table's OID column as
3050 a primary key is discouraged. OIDs are best used only for
3051 references to system tables.
3055 The <type>oid</> type itself has few operations beyond comparison.
3056 It can be cast to integer, however, and then manipulated using the
3057 standard integer operators. (Beware of possible
3058 signed-versus-unsigned confusion if you do this.)
3062 The OID alias types have no operations of their own except
3063 for specialized input and output routines. These routines are able
3064 to accept and display symbolic names for system objects, rather than
3065 the raw numeric value that type <type>oid</> would use. The alias
3066 types allow simplified lookup of OID values for objects. For example,
3067 to examine the <structname>pg_attribute</> rows related to a table
3068 <literal>mytable</>, one could write
3070 SELECT * FROM pg_attribute WHERE attrelid = 'mytable'::regclass;
3074 SELECT * FROM pg_attribute
3075 WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'mytable');
3077 While that doesn't look all that bad by itself, it's still oversimplified.
3078 A far more complicated sub-select would be needed to
3079 select the right OID if there are multiple tables named
3080 <literal>mytable</> in different schemas.
3081 The <type>regclass</> input converter handles the table lookup according
3082 to the schema path setting, and so it does the <quote>right thing</>
3083 automatically. Similarly, casting a table's OID to
3084 <type>regclass</> is handy for symbolic display of a numeric OID.
3087 <table id="datatype-oid-table">
3088 <title>Object Identifier Types</title>
3093 <entry>References</entry>
3094 <entry>Description</entry>
3095 <entry>Value Example</entry>
3102 <entry><type>oid</></entry>
3104 <entry>numeric object identifier</entry>
3105 <entry><literal>564182</></entry>
3109 <entry><type>regproc</></entry>
3110 <entry><structname>pg_proc</></entry>
3111 <entry>function name</entry>
3112 <entry><literal>sum</></entry>
3116 <entry><type>regprocedure</></entry>
3117 <entry><structname>pg_proc</></entry>
3118 <entry>function with argument types</entry>
3119 <entry><literal>sum(int4)</></entry>
3123 <entry><type>regoper</></entry>
3124 <entry><structname>pg_operator</></entry>
3125 <entry>operator name</entry>
3126 <entry><literal>+</></entry>
3130 <entry><type>regoperator</></entry>
3131 <entry><structname>pg_operator</></entry>
3132 <entry>operator with argument types</entry>
3133 <entry><literal>*(integer,integer)</> or <literal>-(NONE,integer)</></entry>
3137 <entry><type>regclass</></entry>
3138 <entry><structname>pg_class</></entry>
3139 <entry>relation name</entry>
3140 <entry><literal>pg_type</></entry>
3144 <entry><type>regtype</></entry>
3145 <entry><structname>pg_type</></entry>
3146 <entry>data type name</entry>
3147 <entry><literal>integer</></entry>
3154 All of the OID alias types accept schema-qualified names, and will
3155 display schema-qualified names on output if the object would not
3156 be found in the current search path without being qualified.
3157 The <type>regproc</> and <type>regoper</> alias types will only
3158 accept input names that are unique (not overloaded), so they are
3159 of limited use; for most uses <type>regprocedure</> or
3160 <type>regoperator</> is more appropriate. For <type>regoperator</>,
3161 unary operators are identified by writing <literal>NONE</> for the unused
3166 An additional property of the OID alias types is that if a
3167 constant of one of these types appears in a stored expression
3168 (such as a column default expression or view), it creates a dependency
3169 on the referenced object. For example, if a column has a default
3170 expression <literal>nextval('my_seq'::regclass)</>,
3171 <productname>PostgreSQL</productname>
3172 understands that the default expression depends on the sequence
3173 <literal>my_seq</>; the system will not let the sequence be dropped
3174 without first removing the default expression.
3178 Another identifier type used by the system is <type>xid</>, or transaction
3179 (abbreviated <abbrev>xact</>) identifier. This is the data type of the system columns
3180 <structfield>xmin</> and <structfield>xmax</>. Transaction identifiers are 32-bit quantities.
3184 A third identifier type used by the system is <type>cid</>, or
3185 command identifier. This is the data type of the system columns
3186 <structfield>cmin</> and <structfield>cmax</>. Command identifiers are also 32-bit quantities.
3190 A final identifier type used by the system is <type>tid</>, or tuple
3191 identifier (row identifier). This is the data type of the system column
3192 <structfield>ctid</>. A tuple ID is a pair
3193 (block number, tuple index within block) that identifies the
3194 physical location of the row within its table.
3198 (The system columns are further explained in <xref
3199 linkend="ddl-system-columns">.)
3203 <sect1 id="datatype-pseudo">
3204 <title>Pseudo-Types</title>
3206 <indexterm zone="datatype-pseudo">
3207 <primary>record</primary>
3210 <indexterm zone="datatype-pseudo">
3211 <primary>any</primary>
3214 <indexterm zone="datatype-pseudo">
3215 <primary>anyarray</primary>
3218 <indexterm zone="datatype-pseudo">
3219 <primary>anyelement</primary>
3222 <indexterm zone="datatype-pseudo">
3223 <primary>void</primary>
3226 <indexterm zone="datatype-pseudo">
3227 <primary>trigger</primary>
3230 <indexterm zone="datatype-pseudo">
3231 <primary>language_handler</primary>
3234 <indexterm zone="datatype-pseudo">
3235 <primary>cstring</primary>
3238 <indexterm zone="datatype-pseudo">
3239 <primary>internal</primary>
3242 <indexterm zone="datatype-pseudo">
3243 <primary>opaque</primary>
3247 The <productname>PostgreSQL</productname> type system contains a
3248 number of special-purpose entries that are collectively called
3249 <firstterm>pseudo-types</>. A pseudo-type cannot be used as a
3250 column data type, but it can be used to declare a function's
3251 argument or result type. Each of the available pseudo-types is
3252 useful in situations where a function's behavior does not
3253 correspond to simply taking or returning a value of a specific
3254 <acronym>SQL</acronym> data type. <xref
3255 linkend="datatype-pseudotypes-table"> lists the existing
3259 <table id="datatype-pseudotypes-table">
3260 <title>Pseudo-Types</title>
3265 <entry>Description</entry>
3271 <entry><type>any</></entry>
3272 <entry>Indicates that a function accepts any input data type whatever.</entry>
3276 <entry><type>anyarray</></entry>
3277 <entry>Indicates that a function accepts any array data type
3278 (see <xref linkend="extend-types-polymorphic">).</entry>
3282 <entry><type>anyelement</></entry>
3283 <entry>Indicates that a function accepts any data type
3284 (see <xref linkend="extend-types-polymorphic">).</entry>
3288 <entry><type>cstring</></entry>
3289 <entry>Indicates that a function accepts or returns a null-terminated C string.</entry>
3293 <entry><type>internal</></entry>
3294 <entry>Indicates that a function accepts or returns a server-internal
3299 <entry><type>language_handler</></entry>
3300 <entry>A procedural language call handler is declared to return <type>language_handler</>.</entry>
3304 <entry><type>record</></entry>
3305 <entry>Identifies a function returning an unspecified row type.</entry>
3309 <entry><type>trigger</></entry>
3310 <entry>A trigger function is declared to return <type>trigger.</></entry>
3314 <entry><type>void</></entry>
3315 <entry>Indicates that a function returns no value.</entry>
3319 <entry><type>opaque</></entry>
3320 <entry>An obsolete type name that formerly served all the above purposes.</entry>
3327 Functions coded in C (whether built-in or dynamically loaded) may be
3328 declared to accept or return any of these pseudo data types. It is up to
3329 the function author to ensure that the function will behave safely
3330 when a pseudo-type is used as an argument type.
3334 Functions coded in procedural languages may use pseudo-types only as
3335 allowed by their implementation languages. At present the procedural
3336 languages all forbid use of a pseudo-type as argument type, and allow
3337 only <type>void</> and <type>record</> as a result type (plus
3338 <type>trigger</> when the function is used as a trigger). Some also
3339 support polymorphic functions using the types <type>anyarray</> and
3340 <type>anyelement</>.
3344 The <type>internal</> pseudo-type is used to declare functions
3345 that are meant only to be called internally by the database
3346 system, and not by direct invocation in a <acronym>SQL</acronym>
3347 query. If a function has at least one <type>internal</>-type
3348 argument then it cannot be called from <acronym>SQL</acronym>. To
3349 preserve the type safety of this restriction it is important to
3350 follow this coding rule: do not create any function that is
3351 declared to return <type>internal</> unless it has at least one
3352 <type>internal</> argument.
3357 <sect1 id="datatype-xml">
3358 <title><acronym>XML</> Document Support</title>
3360 <indexterm zone="datatype">
3361 <primary>xml</primary>
3365 <acronym>XML</> (eXtensible Markup Language) support is not one
3366 capability, but a variety of features supported by a database
3367 system. These capabilities include storage, import/export,
3368 validation, indexing, efficiency of modification, searching,
3369 transforming, and <acronym>XML</> to <acronym>SQL</> mapping.
3370 <productname>PostgreSQL</> supports some but not all of these
3371 <acronym>XML</> capabilities. Future releases of
3372 <productname>PostgreSQL</> will continue to improve <acronym>XML</>
3373 support. For an overview of <acronym>XML</> use in databases, see <ulink
3374 url="http://www.rpbourret.com/xml/XMLAndDatabases.htm"></>.
3380 <term>Storage</term>
3384 PostgreSQL does not have a specialized <acronym>XML</> data type.
3385 Users should store <acronym>XML</> documents in ordinary
3386 <type>TEXT</> fields. If you need the document split apart into
3387 its component parts so each element is stored separately, you must
3388 use a middle-ware solution to do that, but once done, the data
3389 becomes relational and has to be processed accordingly.
3395 <term>Import/Export</term>
3399 There is no facility for mapping <acronym>XML</> to relational
3400 tables. An external tool must be used for this. One simple way to
3401 export <acronym>XML</> is to use <application>psql</> in
3402 <acronym>HTML</> mode (<literal>\pset format html</>), and convert
3403 the <acronym>XHTML</> output to XML using an external tool.
3409 <term>Validation</term>
3413 <filename>/contrib/xml2</> has a function called
3414 <function>xml_is_well_formed()</> that can be used in a <literal>CHECK</>
3415 constraint to enforce that a field contains well-formed <acronym>XML</>.
3416 It does not support validation against a specific <acronym>XML</>
3417 schema. A server-side language with <acronym>XML</> capabilities
3418 could be used to do schema-specific <acronym>XML</> checks.
3424 <term>Indexing</term>
3428 <filename>/contrib/xml2</> functions can be used in expression
3429 indexes to index specific <acronym>XML</> fields. To index the
3430 full contents of <acronym>XML</> documents, the full-text indexing
3431 tool <filename>/contrib/tsearch2</> can be used. Of course,
3432 tsearch2 indexes have no <acronym>XML</> awareness so additional
3433 <filename>/contrib/xml2</> checks should be added to queries.
3439 <term>Modification</term>
3443 If an <command>UPDATE</> does not modify an <acronym>XML</> field,
3444 the <acronym>XML</> data is shared between the old and new rows.
3445 However, if the <command>UPDATE</> modifies an <acronym>XML</>
3446 field, a full modified copy of the <acronym>XML</> field must be
3453 <term>Searching</term>
3457 XPath searches are implemented using <filename>/contrib/xml2</>.
3458 It processes <acronym>XML</> text documents and returns results
3459 based on the requested query.
3465 <term>Transforming</term>
3469 <filename>/contrib/xml2</> supports <acronym>XSLT</> (XML
3470 Stylesheet Language Transformation).
3476 <term>XML to SQL Mapping</term>
3480 This involves converting <acronym>XML</> data to and from
3481 relational structures. <productname>PostgreSQL</> has no internal
3482 support for such mapping, and relies on external tools to do such
3489 <term>Missing Features</term>
3493 Missing features include XQuery, SQL/XML syntax (ISO/IEC
3494 9075-14), and an <acronym>XML</> data type optimized for
3495 <acronym>XML</> storage.