2 $Header: /cvsroot/pgsql/doc/src/sgml/datatype.sgml,v 1.110 2002/12/06 05:17:42 momjian Exp $
5 <chapter id="datatype">
6 <title id="datatype-title">Data Types</title>
8 <indexterm zone="datatype">
9 <primary>data types</primary>
13 <primary>types</primary>
18 <productname>PostgreSQL</productname> has a rich set of native data
19 types available to users.
20 Users may add new types to <productname>PostgreSQL</productname> using the
21 <command>CREATE TYPE</command> command.
25 <xref linkend="datatype-table"> shows all general-purpose data types
26 included in the standard distribution. Most of the alternative names
28 <quote>Aliases</quote> column are the names used internally by
29 <productname>PostgreSQL</productname> for historical reasons. In
30 addition, some internally used or deprecated types are available,
31 but they are not listed here.
35 <table id="datatype-table">
36 <title>Data Types</title>
40 <entry>Type Name</entry>
41 <entry>Aliases</entry>
42 <entry>Description</entry>
48 <entry><type>bigint</type></entry>
49 <entry><type>int8</type></entry>
50 <entry>signed eight-byte integer</entry>
54 <entry><type>bigserial</type></entry>
55 <entry><type>serial8</type></entry>
56 <entry>autoincrementing eight-byte integer</entry>
60 <entry><type>bit</type></entry>
62 <entry>fixed-length bit string</entry>
66 <entry><type>bit varying(<replaceable>n</replaceable>)</type></entry>
67 <entry><type>varbit(<replaceable>n</replaceable>)</type></entry>
68 <entry>variable-length bit string</entry>
72 <entry><type>boolean</type></entry>
73 <entry><type>bool</type></entry>
74 <entry>logical Boolean (true/false)</entry>
78 <entry><type>box</type></entry>
80 <entry>rectangular box in 2D plane</entry>
84 <entry><type>bytea</type></entry>
86 <entry>binary data</entry>
90 <entry><type>character(<replaceable>n</replaceable>)</type></entry>
91 <entry><type>char(<replaceable>n</replaceable>)</type></entry>
92 <entry>fixed-length character string</entry>
96 <entry><type>character varying(<replaceable>n</replaceable>)</type></entry>
97 <entry><type>varchar(<replaceable>n</replaceable>)</type></entry>
98 <entry>variable-length character string</entry>
102 <entry><type>cidr</type></entry>
104 <entry>IP network address</entry>
108 <entry><type>circle</type></entry>
110 <entry>circle in 2D plane</entry>
114 <entry><type>date</type></entry>
116 <entry>calendar date (year, month, day)</entry>
120 <entry><type>double precision</type></entry>
121 <entry><type>float8</type></entry>
122 <entry>double precision floating-point number</entry>
126 <entry><type>inet</type></entry>
128 <entry>IP host address</entry>
132 <entry><type>integer</type></entry>
133 <entry><type>int</type>, <type>int4</type></entry>
134 <entry>signed four-byte integer</entry>
138 <entry><type>interval(<replaceable>p</replaceable>)</type></entry>
140 <entry>general-use time span</entry>
144 <entry><type>line</type></entry>
146 <entry>infinite line in 2D plane (not implemented)</entry>
150 <entry><type>lseg</type></entry>
152 <entry>line segment in 2D plane</entry>
156 <entry><type>macaddr</type></entry>
158 <entry>MAC address</entry>
162 <entry><type>money</type></entry>
164 <entry>currency amount</entry>
168 <entry><type>numeric [ (<replaceable>p</replaceable>,
169 <replaceable>s</replaceable>) ]</type></entry>
170 <entry><type>decimal [ (<replaceable>p</replaceable>,
171 <replaceable>s</replaceable>) ]</type></entry>
172 <entry>exact numeric with selectable precision</entry>
176 <entry><type>path</type></entry>
178 <entry>open and closed geometric path in 2D plane</entry>
182 <entry><type>point</type></entry>
184 <entry>geometric point in 2D plane</entry>
188 <entry><type>polygon</type></entry>
190 <entry>closed geometric path in 2D plane</entry>
194 <entry><type>real</type></entry>
195 <entry><type>float4</type></entry>
196 <entry>single precision floating-point number</entry>
200 <entry><type>smallint</type></entry>
201 <entry><type>int2</type></entry>
202 <entry>signed two-byte integer</entry>
206 <entry><type>serial</type></entry>
207 <entry><type>serial4</type></entry>
208 <entry>autoincrementing four-byte integer</entry>
212 <entry><type>text</type></entry>
214 <entry>variable-length character string</entry>
218 <entry><type>time [ (<replaceable>p</replaceable>) ] [ without time zone ]</type></entry>
220 <entry>time of day</entry>
224 <entry><type>time [ (<replaceable>p</replaceable>) ] with time zone</type></entry>
225 <entry><type>timetz</type></entry>
226 <entry>time of day, including time zone</entry>
230 <entry><type>timestamp [ (<replaceable>p</replaceable>) ] without time zone</type></entry>
231 <entry><type>timestamp</type></entry>
232 <entry>date and time</entry>
236 <entry><type>timestamp [ (<replaceable>p</replaceable>) ] [ with time zone ]</type></entry>
237 <entry><type>timestamptz</type></entry>
238 <entry>date and time, including time zone</entry>
246 <title>Compatibility</title>
248 The following types (or spellings thereof) are specified by
249 <acronym>SQL</acronym>: <type>bit</type>, <type>bit
250 varying</type>, <type>boolean</type>, <type>char</type>,
251 <type>character</type>, <type>character varying</type>,
252 <type>varchar</type>, <type>date</type>, <type>double
253 precision</type>, <type>integer</type>, <type>interval</type>,
254 <type>numeric</type>, <type>decimal</type>, <type>real</type>,
255 <type>smallint</type>, <type>time</type>, <type>timestamp</type>
256 (both with or without time zone).
261 Each data type has an external representation determined by its input
262 and output functions. Many of the built-in types have
263 obvious external formats. However, several types are either unique
264 to <productname>PostgreSQL</productname>, such as open and closed
265 paths, or have several possibilities for formats, such as the date
267 Most of the input and output functions corresponding to the
268 base types (e.g., integers and floating-point numbers) do some
270 Some of the input and output functions are not invertible. That is,
271 the result of an output function may lose precision when compared to
276 Some of the operators and functions (e.g.,
277 addition and multiplication) do not perform run-time error-checking in the
278 interests of improving execution speed.
279 On some systems, for example, the numeric operators for some data types may
280 silently underflow or overflow.
283 <sect1 id="datatype-numeric">
284 <title>Numeric Types</title>
286 <indexterm zone="datatype-numeric">
287 <primary>data types</primary>
288 <secondary>numeric</secondary>
291 <indexterm zone="datatype-numeric">
292 <primary>integer</primary>
295 <indexterm zone="datatype-numeric">
296 <primary>smallint</primary>
299 <indexterm zone="datatype-numeric">
300 <primary>bigint</primary>
304 <primary>int4</primary>
309 <primary>int2</primary>
314 <primary>int8</primary>
318 <indexterm zone="datatype-numeric">
319 <primary>numeric (data type)</primary>
323 <primary>decimal</primary>
327 <indexterm zone="datatype-numeric">
328 <primary>real</primary>
331 <indexterm zone="datatype-numeric">
332 <primary>double precision</primary>
336 <primary>float4</primary>
341 <primary>float8</primary>
342 <see>double precision</see>
345 <indexterm zone="datatype-numeric">
346 <primary>floating point</primary>
350 Numeric types consist of two-, four-, and eight-byte integers,
351 four- and eight-byte floating-point numbers, and fixed-precision
352 decimals. <xref linkend="datatype-numeric-table"> lists the
356 <table id="datatype-numeric-table">
357 <title>Numeric Types</title>
361 <entry>Type name</entry>
362 <entry>Storage size</entry>
363 <entry>Description</entry>
370 <entry><type>smallint</></entry>
371 <entry>2 bytes</entry>
372 <entry>small range fixed-precision</entry>
373 <entry>-32768 to +32767</entry>
376 <entry><type>integer</></entry>
377 <entry>4 bytes</entry>
378 <entry>usual choice for fixed-precision</entry>
379 <entry>-2147483648 to +2147483647</entry>
382 <entry><type>bigint</></entry>
383 <entry>8 bytes</entry>
384 <entry>large range fixed-precision</entry>
385 <entry>-9223372036854775808 to 9223372036854775807</entry>
389 <entry><type>decimal</></entry>
390 <entry>variable</entry>
391 <entry>user-specified precision, exact</entry>
392 <entry>no limit</entry>
395 <entry><type>numeric</></entry>
396 <entry>variable</entry>
397 <entry>user-specified precision, exact</entry>
398 <entry>no limit</entry>
402 <entry><type>real</></entry>
403 <entry>4 bytes</entry>
404 <entry>variable-precision, inexact</entry>
405 <entry>6 decimal digits precision</entry>
408 <entry><type>double precision</></entry>
409 <entry>8 bytes</entry>
410 <entry>variable-precision, inexact</entry>
411 <entry>15 decimal digits precision</entry>
415 <entry><type>serial</></entry>
416 <entry>4 bytes</entry>
417 <entry>autoincrementing integer</entry>
418 <entry>1 to 2147483647</entry>
422 <entry><type>bigserial</type></entry>
423 <entry>8 bytes</entry>
424 <entry>large autoincrementing integer</entry>
425 <entry>1 to 9223372036854775807</entry>
432 The syntax of constants for the numeric types is described in
433 <xref linkend="sql-syntax-constants">. The numeric types have a
434 full set of corresponding arithmetic operators and
435 functions. Refer to <xref linkend="functions"> for more
436 information. The following sections describe the types in detail.
439 <sect2 id="datatype-int">
440 <title>The Integer Types</title>
443 The types <type>smallint</type>, <type>integer</type>,
444 <type>bigint</type> store whole numbers, that is, numbers without
445 fractional components, of various ranges. Attempts to store
446 values outside of the allowed range will result in an error.
450 The type <type>integer</type> is the usual choice, as it offers
451 the best balance between range, storage size, and performance.
452 The <type>smallint</type> type is generally only used if disk
453 space is at a premium. The <type>bigint</type> type should only
454 be used if the <type>integer</type> range is not sufficient,
455 because the latter is definitely faster.
459 The <type>bigint</type> type may not function correctly on all
460 platforms, since it relies on compiler support for eight-byte
461 integers. On a machine without such support, <type>bigint</type>
462 acts the same as <type>integer</type> (but still takes up eight
463 bytes of storage). However, we are not aware of any reasonable
464 platform where this is actually the case.
468 <acronym>SQL</acronym> only specifies the integer types
469 <type>integer</type> (or <type>int</type>) and
470 <type>smallint</type>. The type <type>bigint</type>, and the
471 type names <type>int2</type>, <type>int4</type>, and
472 <type>int8</type> are extensions, which are shared with various
473 other <acronym>SQL</acronym> database systems.
478 If you have a column of type <type>smallint</type> or
479 <type>bigint</type> with an index, you may encounter problems
480 getting the system to use that index. For instance, a clause of
483 ... WHERE smallint_column = 42
485 will not use an index, because the system assigns type
486 <type>integer</type> to the constant 42, and
487 <productname>PostgreSQL</productname> currently
488 cannot use an index when two different data types are involved. A
489 workaround is to single-quote the constant, thus:
491 ... WHERE smallint_column = '42'
493 This will cause the system to delay type resolution and will
494 assign the right type to the constant.
500 <sect2 id="datatype-numeric-decimal">
501 <title>Arbitrary Precision Numbers</title>
504 The type <type>numeric</type> can store numbers with up to 1,000
505 digits of precision and perform calculations exactly. It is
506 especially recommended for storing monetary amounts and other
507 quantities where exactness is required. However, the
508 <type>numeric</type> type is very slow compared to the
509 floating-point types described in the next section.
513 In what follows we use these terms: The
514 <firstterm>scale</firstterm> of a <type>numeric</type> is the
515 count of decimal digits in the fractional part, to the right of
516 the decimal point. The <firstterm>precision</firstterm> of a
517 <type>numeric</type> is the total count of significant digits in
518 the whole number, that is, the number of digits to both sides of
519 the decimal point. So the number 23.5141 has a precision of 6
520 and a scale of 4. Integers can be considered to have a scale of
525 Both the precision and the scale of the numeric type can be
526 configured. To declare a column of type <type>numeric</type> use
529 NUMERIC(<replaceable>precision</replaceable>, <replaceable>scale</replaceable>)
531 The precision must be positive, the scale zero or positive.
534 NUMERIC(<replaceable>precision</replaceable>)
536 selects a scale of 0. Specifying
540 without any precision or scale creates a column in which numeric
541 values of any precision and scale can be stored, up to the
542 implementation limit on precision. A column of this kind will
543 not coerce input values to any particular scale, whereas
544 <type>numeric</type> columns with a declared scale will coerce
545 input values to that scale. (The <acronym>SQL</acronym> standard
546 requires a default scale of 0, i.e., coercion to integer
547 precision. We find this a bit useless. If you're concerned
548 about portability, always specify the precision and scale
553 If the precision or scale of a value is greater than the declared
554 precision or scale of a column, the system will attempt to round
555 the value. If the value cannot be rounded so as to satisfy the
556 declared limits, an error is raised.
560 The types <type>decimal</type> and <type>numeric</type> are
561 equivalent. Both types are part of the <acronym>SQL</acronym>
567 <sect2 id="datatype-float">
568 <title>Floating-Point Types</title>
571 The data types <type>real</type> and <type>double
572 precision</type> are inexact, variable-precision numeric types.
573 In practice, these types are usually implementations of
574 <acronym>IEEE</acronym> Standard 754 for Binary Floating-Point
575 Arithmetic (single and double precision, respectively), to the
576 extent that the underlying processor, operating system, and
581 Inexact means that some values cannot be converted exactly to the
582 internal format and are stored as approximations, so that storing
583 and printing back out a value may show slight discrepancies.
584 Managing these errors and how they propagate through calculations
585 is the subject of an entire branch of mathematics and computer
586 science and will not be discussed further here, except for the
591 If you require exact storage and calculations (such as for
592 monetary amounts), use the <type>numeric</type> type instead.
598 If you want to do complicated calculations with these types
599 for anything important, especially if you rely on certain
600 behavior in boundary cases (infinity, underflow), you should
601 evaluate the implementation carefully.
607 Comparing two floating-point values for equality may or may
608 not work as expected.
615 Normally, the <type>real</type> type has a range of at least
616 -1E+37 to +1E+37 with a precision of at least 6 decimal digits. The
617 <type>double precision</type> type normally has a range of around
618 -1E+308 to +1E+308 with a precision of at least 15 digits. Values that
619 are too large or too small will cause an error. Rounding may
620 take place if the precision of an input number is too high.
621 Numbers too close to zero that are not representable as distinct
622 from zero will cause an underflow error.
627 <sect2 id="datatype-serial">
628 <title>The Serial Types</title>
630 <indexterm zone="datatype-serial">
631 <primary>serial</primary>
634 <indexterm zone="datatype-serial">
635 <primary>bigserial</primary>
638 <indexterm zone="datatype-serial">
639 <primary>serial4</primary>
642 <indexterm zone="datatype-serial">
643 <primary>serial8</primary>
647 <primary>auto-increment</primary>
652 <primary>sequences</primary>
653 <secondary>and serial type</secondary>
657 The <type>serial</type> data type is not a true type, but merely
658 a notational convenience for setting up identifier columns
659 (similar to the <literal>AUTO_INCREMENT</literal> property
660 supported by some other databases). In the current
661 implementation, specifying
664 CREATE TABLE <replaceable class="parameter">tablename</replaceable> (
665 <replaceable class="parameter">colname</replaceable> SERIAL
669 is equivalent to specifying:
672 CREATE SEQUENCE <replaceable class="parameter">tablename</replaceable>_<replaceable class="parameter">colname</replaceable>_seq;
673 CREATE TABLE <replaceable class="parameter">tablename</replaceable> (
674 <replaceable class="parameter">colname</replaceable> integer DEFAULT nextval('<replaceable class="parameter">tablename</replaceable>_<replaceable class="parameter">colname</replaceable>_seq') NOT NULL
678 Thus, we have created an integer column and arranged for its default
679 values to be assigned from a sequence generator. A <literal>NOT NULL</>
680 constraint is applied to ensure that a null value cannot be explicitly
681 inserted, either. In most cases you would also want to attach a
682 <literal>UNIQUE</> or <literal>PRIMARY KEY</> constraint to prevent
683 duplicate values from being inserted by accident, but this is
688 To use a <type>serial</type> column to insert the next value of
689 the sequence into the table, specify that the <type>serial</type>
690 column should be assigned the default value. This can be done
691 either be excluding from the column from the list of columns in
692 the <command>INSERT</command> statement, or through the use of
693 the <literal>DEFAULT</literal> keyword.
697 The type names <type>serial</type> and <type>serial4</type> are
698 equivalent: both create <type>integer</type> columns. The type
699 names <type>bigserial</type> and <type>serial8</type> work just
700 the same way, except that they create a <type>bigint</type>
701 column. <type>bigserial</type> should be used if you anticipate
702 the use of more than 2<superscript>31</> identifiers over the
703 lifetime of the table.
707 The sequence created by a <type>serial</type> type is
708 automatically dropped when the owning column is dropped, and
709 cannot be dropped otherwise. (This was not true in
710 <productname>PostgreSQL</productname> releases before 7.3. Note
711 that this automatic drop linkage will not occur for a sequence
712 created by reloading a dump from a pre-7.3 database; the dump
713 file does not contain the information needed to establish the
714 dependency link.) Furthermore, this dependency between sequence
715 and column is made only for the <type>serial</> column itself; if
716 any other columns reference the sequence (perhaps by manually
717 calling the <function>nextval()</>) function), they may be broken
718 if the sequence is removed. Using <type>serial</> columns in
719 fashion is considered bad form.
724 Prior to <productname>PostgreSQL</> 7.3, <type>serial</type>
725 implied <literal>UNIQUE</literal>. This is no longer automatic.
726 If you wish a serial column to be <literal>UNIQUE</literal> or a
727 <literal>PRIMARY KEY</literal> it must now be specified, just as
728 with any other data type.
734 <sect1 id="datatype-money">
735 <title>Monetary Type</title>
740 The <type>money</type> type is deprecated. Use
741 <type>numeric</type> or <type>decimal</type> instead, in
742 combination with the <function>to_char</function> function. The
743 money type may become a locale-aware layer over the
744 <type>numeric</type> type in a future release.
749 The <type>money</type> type stores a currency amount with fixed
750 decimal point representation; see <xref
751 linkend="datatype-money-table">. The output format is
756 Input is accepted in a variety of formats, including integer and
757 floating-point literals, as well as <quote>typical</quote>
758 currency formatting, such as <literal>'$1,000.00'</literal>.
759 Output is in the latter form.
762 <table id="datatype-money-table">
763 <title>Monetary Types</title>
767 <entry>Type Name</entry>
768 <entry>Storage</entry>
769 <entry>Description</entry>
776 <entry>4 bytes</entry>
777 <entry>currency amount</entry>
778 <entry>-21474836.48 to +21474836.47</entry>
786 <sect1 id="datatype-character">
787 <title>Character Types</title>
789 <indexterm zone="datatype-character">
790 <primary>character strings</primary>
791 <secondary>data types</secondary>
795 <primary>strings</primary>
796 <see>character strings</see>
800 <primary>text</primary>
801 <see>character strings</see>
804 <table id="datatype-character-table">
805 <title>Character Types</title>
809 <entry>Type name</entry>
810 <entry>Description</entry>
815 <entry><type>character(<replaceable>n</>)</type>, <type>char(<replaceable>n</>)</type></entry>
816 <entry>fixed-length, blank padded</entry>
819 <entry><type>character varying(<replaceable>n</>)</type>, <type>varchar(<replaceable>n</>)</type></entry>
820 <entry>variable-length with limit</entry>
823 <entry><type>text</type></entry>
824 <entry>variable unlimited length</entry>
831 <xref linkend="datatype-character-table"> shows the
832 general-purpose character types available in
833 <productname>PostgreSQL</productname>.
837 <acronym>SQL</acronym> defines two primary character types:
838 <type>character(<replaceable>n</>)</type> and <type>character
839 varying(<replaceable>n</>)</type>, where <replaceable>n</> is a
840 positive integer. Both of these types can store strings up to
841 <replaceable>n</> characters in length. An attempt to store a
842 longer string into a column of these types will result in an
843 error, unless the excess characters are all spaces, in which case
844 the string will be truncated to the maximum length. (This
845 somewhat bizarre exception is required by the
846 <acronym>SQL</acronym> standard.) If the string to be stored is
847 shorter than the declared length, values of type
848 <type>character</type> will be space-padded; values of type
849 <type>character varying</type> will simply store the shorter
855 If one explicitly casts a value to
856 <type>character(<replaceable>n</>)</type> or <type>character
857 varying(<replaceable>n</>)</type>, then an overlength value will
858 be truncated to <replaceable>n</> characters without raising an
859 error. (This too is required by the <acronym>SQL</acronym>
866 Prior to <productname>PostgreSQL</> 7.2, strings that were too long were
867 always truncated without raising an error, in either explicit or
868 implicit casting contexts.
873 The notations <type>char(<replaceable>n</>)</type> and
874 <type>varchar(<replaceable>n</>)</type> are aliases for
875 <type>character(<replaceable>n</>)</type> and <type>character
876 varying(<replaceable>n</>)</type>,
877 respectively. <type>character</type> without length specifier is
878 equivalent to <type>character(1)</type>; if <type>character
879 varying</type> is used without length specifier, the type accepts
880 strings of any size. The latter is a <productname>PostgreSQL</> extension.
884 In addition, <productname>PostgreSQL</productname> supports the
885 more general <type>text</type> type, which stores strings of any
886 length. Unlike <type>character varying</type>, <type>text</type>
887 does not require an explicit declared upper limit on the size of
888 the string. Although the type <type>text</type> is not in the
889 <acronym>SQL</acronym> standard, many other RDBMS packages have it
894 The storage requirement for data of these types is 4 bytes plus the
895 actual string, and in case of <type>character</type> plus the
896 padding. Long strings are compressed by the system automatically, so
897 the physical requirement on disk may be less. Long values are also
898 stored in background tables so they don't interfere with rapid
899 access to the shorter column values. In any case, the longest
900 possible character string that can be stored is about 1 GB. (The
901 maximum value that will be allowed for <replaceable>n</> in the data
902 type declaration is less than that. It wouldn't be very useful to
903 change this because with multibyte character encodings the number of
904 characters and bytes can be quite different anyway. If you desire to
905 store long strings with no specific upper limit, use
906 <type>text</type> or <type>character varying</type> without a length
907 specifier, rather than making up an arbitrary length limit.)
912 There are no performance differences between these three types,
913 apart from the increased storage size when using the blank-padded
919 Refer to <xref linkend="sql-syntax-strings"> for information about
920 the syntax of string literals, and to <xref linkend="functions">
921 for information about available operators and functions.
925 <title>Using the character types</title>
928 CREATE TABLE test1 (a character(4));
929 INSERT INTO test1 VALUES ('ok');
930 SELECT a, char_length(a) FROM test1; -- <co id="co.datatype-char">
937 CREATE TABLE test2 (b varchar(5));
938 INSERT INTO test2 VALUES ('ok');
939 INSERT INTO test2 VALUES ('good ');
940 INSERT INTO test2 VALUES ('too long');
941 <computeroutput>ERROR: value too long for type character varying(5)</computeroutput>
942 INSERT INTO test2 VALUES ('too long'::varchar(5)); -- explicit truncation
943 SELECT b, char_length(b) FROM test2;
946 -------+-------------
953 <callout arearefs="co.datatype-char">
955 The <function>char_length</function> function is discussed in
956 <xref linkend="functions-string">.
963 There are two other fixed-length character types in
964 <productname>PostgreSQL</productname>, shown in <xref linkend="datatype-character-special-table">.
965 The <type>name</type> type
966 exists <emphasis>only</emphasis> for storage of internal catalog
967 names and is not intended for use by the general user. Its length
968 is currently defined as 64 bytes (63 usable characters plus terminator)
969 but should be referenced using the constant
970 <symbol>NAMEDATALEN</symbol>. The length is set at compile time
971 (and is therefore adjustable for special uses); the default
972 maximum length may change in a future release. The type
973 <type>"char"</type> (note the quotes) is different from
974 <type>char(1)</type> in that it only uses one byte of storage. It
975 is internally used in the system catalogs as a poor-man's
979 <table id="datatype-character-special-table">
980 <title>Specialty Character Types</title>
984 <entry>Type Name</entry>
985 <entry>Storage</entry>
986 <entry>Description</entry>
991 <entry><type>"char"</type></entry>
992 <entry>1 byte</entry>
993 <entry>single character internal type</entry>
996 <entry><type>name</type></entry>
997 <entry>64 bytes</entry>
998 <entry>sixty-three character internal type</entry>
1006 <sect1 id="datatype-binary">
1007 <title>Binary Strings</title>
1009 The <type>bytea</type> data type allows storage of binary strings;
1010 see <xref linkend="datatype-binary-table">.
1013 <table id="datatype-binary-table">
1014 <title>Binary String Types</title>
1018 <entry>Type Name</entry>
1019 <entry>Storage</entry>
1020 <entry>Description</entry>
1025 <entry><type>bytea</type></entry>
1026 <entry>4 bytes plus the actual binary string</entry>
1027 <entry>Variable (not specifically limited)
1028 length binary string</entry>
1035 A binary string is a sequence of octets (or bytes). Binary
1036 strings are distinguished from characters strings by two
1037 characteristics: First, binary strings specifically allow storing
1038 octets of zero value and other <quote>non-printable</quote>
1039 octets. Second, operations on binary strings process the actual
1040 bytes, whereas the encoding and processing of character strings
1041 depends on locale settings.
1045 When entering <type>bytea</type> values, octets of certain values
1046 <emphasis>must</emphasis> be escaped (but all octet values
1047 <emphasis>may</emphasis> be escaped) when used as part of a string
1048 literal in an <acronym>SQL</acronym> statement. In general, to
1049 escape an octet, it is converted into the three-digit octal number
1050 equivalent of its decimal octet value, and preceded by two
1051 backslashes. Some octet values have alternate escape sequences, as
1052 shown in <xref linkend="datatype-binary-sqlesc">.
1055 <table id="datatype-binary-sqlesc">
1056 <title><type>bytea</> Literal Escaped Octets</title>
1060 <entry>Decimal Octet Value</entry>
1061 <entry>Description</entry>
1062 <entry>Input Escaped Representation</entry>
1063 <entry>Example</entry>
1064 <entry>Printed Result</entry>
1071 <entry>zero octet</entry>
1072 <entry><literal>'\\000'</literal></entry>
1073 <entry><literal>SELECT '\\000'::bytea;</literal></entry>
1074 <entry><literal>\000</literal></entry>
1079 <entry>single quote</entry>
1080 <entry><literal>'\''</literal> or <literal>'\\047'</literal></entry>
1081 <entry><literal>SELECT '\''::bytea;</literal></entry>
1082 <entry><literal>'</literal></entry>
1087 <entry>backslash</entry>
1088 <entry><literal>'\\\\'</literal> or <literal>'\\134'</literal></entry>
1089 <entry><literal>SELECT '\\\\'::bytea;</literal></entry>
1090 <entry><literal>\\</literal></entry>
1098 Note that the result in each of the examples in <xref linkend="datatype-binary-sqlesc"> was exactly one
1099 octet in length, even though the output representation of the zero
1100 octet and backslash are more than one character. <type>Bytea</type>
1101 output octets are also escaped. In general, each
1102 <quote>non-printable</quote> octet decimal value is converted into
1103 its equivalent three digit octal value, and preceded by one backslash.
1104 Most <quote>printable</quote> octets are represented by their standard
1105 representation in the client character set. The octet with decimal
1106 value 92 (backslash) has a special alternate output representation.
1107 Details are in <xref linkend="datatype-binary-resesc">.
1110 <table id="datatype-binary-resesc">
1111 <title><type>bytea</> Output Escaped Octets</title>
1115 <entry>Decimal Octet Value</entry>
1116 <entry>Description</entry>
1117 <entry>Output Escaped Representation</entry>
1118 <entry>Example</entry>
1119 <entry>Printed Result</entry>
1127 <entry>backslash</entry>
1128 <entry><literal>\\</literal></entry>
1129 <entry><literal>SELECT '\\134'::bytea;</literal></entry>
1130 <entry><literal>\\</literal></entry>
1134 <entry>0 to 31 and 127 to 255</entry>
1135 <entry><quote>non-printable</quote> octets</entry>
1136 <entry><literal>\### (octal value)</literal></entry>
1137 <entry><literal>SELECT '\\001'::bytea;</literal></entry>
1138 <entry><literal>\001</literal></entry>
1142 <entry>32 to 126</entry>
1143 <entry><quote>printable</quote> octets</entry>
1144 <entry>ASCII representation</entry>
1145 <entry><literal>SELECT '\\176'::bytea;</literal></entry>
1146 <entry><literal>~</literal></entry>
1154 To use the <type>bytea</type> escaped octet notation, string
1155 literals (input strings) must contain two backslashes because they
1156 must pass through two parsers in the <productname>PostgreSQL</>
1157 server. The first backslash is interpreted as an escape character
1158 by the string-literal parser, and therefore is consumed, leaving
1159 the characters that follow. The remaining backslash is recognized
1160 by the <type>bytea</type> input function as the prefix of a three
1161 digit octal value. For example, a string literal passed to the
1162 backend as <literal>'\\001'</literal> becomes
1163 <literal>'\001'</literal> after passing through the string-literal
1164 parser. The <literal>'\001'</literal> is then sent to the
1165 <type>bytea</type> input function, where it is converted to a
1166 single octet with a decimal value of 1.
1170 For a similar reason, a backslash must be input as
1171 <literal>'\\\\'</literal> (or <literal>'\\134'</literal>). The first
1172 and third backslashes are interpreted as escape characters by the
1173 string-literal parser, and therefore are consumed, leaving two
1174 backslashes in the string passed to the <type>bytea</type> input function,
1175 which interprets them as representing a single backslash.
1176 For example, a string literal passed to the
1177 server as <literal>'\\\\'</literal> becomes <literal>'\\'</literal>
1178 after passing through the string-literal parser. The
1179 <literal>'\\'</literal> is then sent to the <type>bytea</type> input
1180 function, where it is converted to a single octet with a decimal
1185 A single quote is a bit different in that it must be input as
1186 <literal>'\''</literal> (or <literal>'\\047'</literal>),
1187 <emphasis>not</emphasis> as <literal>'\\''</literal>. This is because,
1188 while the literal parser interprets the single quote as a special
1189 character, and will consume the single backslash, the
1190 <type>bytea</type> input function does <emphasis>not</emphasis>
1191 recognize a single quote as a special octet. Therefore a string
1192 literal passed to the backend as <literal>'\''</literal> becomes
1193 <literal>'''</literal> after passing through the string-literal
1194 parser. The <literal>'''</literal> is then sent to the
1195 <type>bytea</type> input function, where it is retains its single
1196 octet decimal value of 39.
1200 Depending on the front end to <productname>PostgreSQL</> you use,
1201 you may have additional work to do in terms of escaping and
1202 unescaping <type>bytea</type> strings. For example, you may also
1203 have to escape line feeds and carriage returns if your interface
1204 automatically translates these. Or you may have to double up on
1205 backslashes if the parser for your language or choice also treats
1206 them as an escape character.
1210 The <acronym>SQL</acronym> standard defines a different binary
1211 string type, called <type>BLOB</type> or <type>BINARY LARGE
1212 OBJECT</type>. The input format is different compared to
1213 <type>bytea</type>, but the provided functions and operators are
1219 <sect1 id="datatype-datetime">
1220 <title>Date/Time Types</title>
1223 <productname>PostgreSQL</productname> supports the full set of
1224 <acronym>SQL</acronym> date and time types, shown in <xref
1225 linkend="datatype-datetime-table">.
1228 <table id="datatype-datetime-table">
1229 <title>Date/Time Types</title>
1234 <entry>Description</entry>
1235 <entry>Storage</entry>
1236 <entry>Earliest</entry>
1237 <entry>Latest</entry>
1238 <entry>Resolution</entry>
1243 <entry><type>timestamp [ (<replaceable>p</replaceable>) ] [ without time zone ]</type></entry>
1244 <entry>both date and time</entry>
1245 <entry>8 bytes</entry>
1246 <entry>4713 BC</entry>
1247 <entry>AD 1465001</entry>
1248 <entry>1 microsecond / 14 digits</entry>
1251 <entry><type>timestamp [ (<replaceable>p</replaceable>) ] with time zone</type></entry>
1252 <entry>both date and time</entry>
1253 <entry>8 bytes</entry>
1254 <entry>4713 BC</entry>
1255 <entry>AD 1465001</entry>
1256 <entry>1 microsecond / 14 digits</entry>
1259 <entry><type>interval [ (<replaceable>p</replaceable>) ]</type></entry>
1260 <entry>time intervals</entry>
1261 <entry>12 bytes</entry>
1262 <entry>-178000000 years</entry>
1263 <entry>178000000 years</entry>
1264 <entry>1 microsecond</entry>
1267 <entry><type>date</type></entry>
1268 <entry>dates only</entry>
1269 <entry>4 bytes</entry>
1270 <entry>4713 BC</entry>
1271 <entry>32767 AD</entry>
1272 <entry>1 day</entry>
1275 <entry><type>time [ (<replaceable>p</replaceable>) ] [ without time zone ]</type></entry>
1276 <entry>times of day only</entry>
1277 <entry>8 bytes</entry>
1278 <entry>00:00:00.00</entry>
1279 <entry>23:59:59.99</entry>
1280 <entry>1 microsecond</entry>
1283 <entry><type>time [ (<replaceable>p</replaceable>) ] with time zone</type></entry>
1284 <entry>times of day only</entry>
1285 <entry>12 bytes</entry>
1286 <entry>00:00:00.00+12</entry>
1287 <entry>23:59:59.99-12</entry>
1288 <entry>1 microsecond</entry>
1295 <type>time</type>, <type>timestamp</type>, and
1296 <type>interval</type> accept an optional precision value
1297 <replaceable>p</replaceable> which specifies the number of
1298 fractional digits retained in the seconds field. By default, there
1299 is no explicit bound on precision. The allowed range of
1300 <replaceable>p</replaceable> is from 0 to 6 for the
1301 <type>timestamp</type> and <type>interval</type> types, 0 to 13
1302 for the <type>time</type> types.
1307 When <type>timestamp</> values are stored as double precision floating-point
1308 numbers (currently the default), the effective limit of precision
1309 may be less than 6, since timestamp values are stored as seconds
1310 since 2000-01-01. Microsecond precision is achieved for dates within
1311 a few years of 2000-01-01, but the precision degrades for dates further
1312 away. When timestamps are stored as eight-byte integers (a compile-time
1313 option), microsecond precision is available over the full range of
1319 Time zones, and time-zone conventions, are influenced by
1320 political decisions, not just earth geometry. Time zones around the
1321 world became somewhat standardized during the 1900's,
1322 but continue to be prone to arbitrary changes.
1323 <productname>PostgreSQL</productname> uses your operating
1324 system's underlying features to provide output time-zone
1325 support, and these systems usually contain information for only
1326 the time period 1902 through 2038 (corresponding to the full
1327 range of conventional Unix system time).
1328 <type>timestamp with time zone</type> and <type>time with time
1329 zone</type> will use time zone
1330 information only within that year range, and assume that times
1331 outside that range are in <acronym>UTC</acronym>.
1335 The type <type>time with time zone</type> is defined by the SQL
1336 standard, but the definition exhibits properties which lead to
1337 questionable usefulness. In most cases, a combination of
1338 <type>date</type>, <type>time</type>, <type>timestamp without time
1339 zone</type> and <type>timestamp with time zone</type> should
1340 provide a complete range of date/time functionality required by
1345 The types <type>abstime</type>
1346 and <type>reltime</type> are lower precision types which are used internally.
1347 You are discouraged from using these types in new
1348 applications and are encouraged to move any old
1349 ones over when appropriate. Any or all of these internal types
1350 might disappear in a future release.
1353 <sect2 id="datatype-datetime-input">
1354 <title>Date/Time Input</title>
1357 Date and time input is accepted in almost any reasonable format, including
1358 <acronym>ISO 8601</acronym>, <acronym>SQL</acronym>-compatible,
1359 traditional <productname>PostgreSQL</productname>, and others.
1360 For some formats, ordering of month and day in date input can be
1361 ambiguous and there is support for specifying the expected
1362 ordering of these fields.
1364 <literal>SET DateStyle TO 'US'</literal>
1365 or <literal>SET DateStyle TO 'NonEuropean'</literal>
1366 specifies the variant <quote>month before day</quote>, the command
1367 <literal>SET DateStyle TO 'European'</literal> sets the variant
1368 <quote>day before month</quote>.
1372 <productname>PostgreSQL</productname> is more flexible in
1373 handling date/time than the
1374 <acronym>SQL</acronym> standard requires.
1375 See <xref linkend="datetime-appendix">
1376 for the exact parsing rules of date/time input and for the
1377 recognized text fields including months, days of the week, and
1382 Remember that any date or time literal input needs to be enclosed
1383 in single quotes, like text strings. Refer to
1384 <xref linkend="sql-syntax-constants-generic"> for more
1386 <acronym>SQL</acronym> requires the following syntax
1388 <replaceable>type</replaceable> [ (<replaceable>p</replaceable>) ] '<replaceable>value</replaceable>'
1390 where <replaceable>p</replaceable> in the optional precision
1391 specification is an integer corresponding to the
1392 number of fractional digits in the seconds field. Precision can
1394 for <type>time</type>, <type>timestamp</type>, and
1395 <type>interval</type> types.
1399 <title>Dates</title>
1402 <primary>date</primary>
1403 <secondary>data type</secondary>
1407 <xref linkend="datatype-datetime-date-table"> shows some possible
1408 inputs for the <type>date</type> type.
1411 <table id="datatype-datetime-date-table">
1412 <title>Date Input</title>
1416 <entry>Example</entry>
1417 <entry>Description</entry>
1422 <entry>January 8, 1999</entry>
1423 <entry>unambiguous</entry>
1426 <entry>1999-01-08</entry>
1427 <entry>ISO-8601 format, preferred</entry>
1430 <entry>1/8/1999</entry>
1431 <entry>U.S.; read as August 1 in European mode</entry>
1434 <entry>8/1/1999</entry>
1435 <entry>European; read as August 1 in U.S. mode</entry>
1438 <entry>1/18/1999</entry>
1439 <entry>U.S.; read as January 18 in any mode</entry>
1442 <entry>19990108</entry>
1443 <entry>ISO-8601 year, month, day</entry>
1446 <entry>990108</entry>
1447 <entry>ISO-8601 year, month, day</entry>
1450 <entry>1999.008</entry>
1451 <entry>year and day of year</entry>
1454 <entry>99008</entry>
1455 <entry>year and day of year</entry>
1458 <entry>J2451187</entry>
1459 <entry>Julian day</entry>
1462 <entry>January 8, 99 BC</entry>
1463 <entry>year 99 before the Common Era</entry>
1471 <title>Times</title>
1474 <primary>time</primary>
1475 <secondary>data type</secondary>
1478 <primary>time without time zone</primary>
1479 <secondary>time</secondary>
1482 <primary>time with time zone</primary>
1483 <secondary>data type</secondary>
1487 The <type>time</type> type can be specified as <type>time</type> or
1488 as <type>time without time zone</type>. The optional precision
1489 <replaceable>p</replaceable> should be between 0 and 13, and
1490 defaults to the precision of the input time literal.
1494 <xref linkend="datatype-datetime-time-table"> shows the valid <type>time</type> inputs.
1497 <table id="datatype-datetime-time-table">
1498 <title>Time Input</title>
1502 <entry>Example</entry>
1503 <entry>Description</entry>
1508 <entry><literal>04:05:06.789</literal></entry>
1509 <entry>ISO 8601</entry>
1512 <entry><literal>04:05:06</literal></entry>
1513 <entry>ISO 8601</entry>
1516 <entry><literal>04:05</literal></entry>
1517 <entry>ISO 8601</entry>
1520 <entry><literal>040506</literal></entry>
1521 <entry>ISO 8601</entry>
1524 <entry><literal>04:05 AM</literal></entry>
1525 <entry>same as 04:05; AM does not affect value</entry>
1528 <entry><literal>04:05 PM</literal></entry>
1529 <entry>same as 16:05; input hour must be <= 12</entry>
1532 <entry><literal>allballs</literal></entry>
1533 <entry>same as 00:00:00</entry>
1540 The type <type>time with time zone</type> accepts all input also
1541 legal for the <type>time</type> type, appended with a legal time
1542 zone, as shown in <xref
1543 linkend="datatype-datetime-timetz-table">.
1546 <table id="datatype-datetime-timetz-table">
1547 <title>Time With Time Zone Input</title>
1551 <entry>Example</entry>
1552 <entry>Description</entry>
1557 <entry>04:05:06.789-8</entry>
1558 <entry>ISO 8601</entry>
1561 <entry>04:05:06-08:00</entry>
1562 <entry>ISO 8601</entry>
1565 <entry>04:05-08:00</entry>
1566 <entry>ISO 8601</entry>
1569 <entry>040506-08</entry>
1570 <entry>ISO 8601</entry>
1577 Refer to <xref linkend="datatype-timezone-table"> for
1578 more examples of time zones.
1583 <title>Time stamps</title>
1586 <primary>timestamp</primary>
1587 <secondary>data type</secondary>
1591 <primary>timestamp with time zone</primary>
1592 <secondary>data type</secondary>
1596 <primary>timestamp without time zone</primary>
1597 <secondary>data type</secondary>
1601 The time stamp types are <type>timestamp [
1602 (<replaceable>p</replaceable>) ] without time zone</type> and
1603 <type>timestamp [ (<replaceable>p</replaceable>) ] with time
1604 zone</type>. Writing just <type>timestamp</type> is equivalent to
1605 <type>timestamp without time zone</type>.
1610 Prior to <productname>PostgreSQL</productname> 7.3, writing just
1611 <type>timestamp</type> was equivalent to <type>timestamp with time
1612 zone</type>. This was changed for SQL spec compliance.
1617 Valid input for the time stamp types consists of a concatenation
1618 of a date and a time, followed by an optional
1619 <literal>AD</literal> or <literal>BC</literal>, followed by an
1620 optional time zone. (See <xref
1621 linkend="datatype-timezone-table">.) Thus
1628 1999-01-08 04:05:06 -8:00
1631 are valid values, which follow the <acronym>ISO</acronym> 8601
1632 standard. In addition, the wide-spread format
1635 January 8 04:05:06 1999 PST
1641 The optional precision
1642 <replaceable>p</replaceable> should be between 0 and 6, and
1643 defaults to the precision of the input <type>timestamp</type> literal.
1647 For <type>timestamp without time zone</type>, any explicit time
1648 zone specified in the input is silently ignored. That is, the
1649 resulting date/time value is derived from the explicit date/time
1650 fields in the input value, and is not adjusted for time zone.
1654 For <type>timestamp with time zone</type>, the internally stored
1655 value is always in UTC (GMT). An input value that has an explicit
1656 time zone specified is converted to UTC using the appropriate offset
1657 for that time zone. If no time zone is stated in the input string,
1658 then it is assumed to be in the time zone indicated by the system's
1659 <varname>TimeZone</> parameter, and is converted to UTC using the
1660 offset for the <varname>TimeZone</> zone.
1664 When a <type>timestamp with time
1665 zone</type> value is output, it is always converted from UTC to the
1666 current <varname>TimeZone</> zone, and displayed as local time in that
1667 zone. To see the time in another time zone, either change
1668 <varname>TimeZone</> or use the <literal>AT TIME ZONE</> construct
1669 (see <xref linkend="functions-datetime-zoneconvert">).
1673 Conversions between <type>timestamp without time zone</type> and
1674 <type>timestamp with time zone</type> normally assume that the
1675 <type>timestamp without time zone</type> value should be taken or given
1676 as <varname>TimeZone</> local time. A different zone reference can
1677 be specified for the conversion using <literal>AT TIME ZONE</>.
1680 <table tocentry="1" id="datatype-timezone-table">
1681 <title>Time Zone Input</title>
1685 <entry>Time Zone</entry>
1686 <entry>Description</entry>
1692 <entry>Pacific Standard Time</entry>
1695 <entry>-8:00</entry>
1696 <entry>ISO-8601 offset for PST</entry>
1700 <entry>ISO-8601 offset for PST</entry>
1704 <entry>ISO-8601 offset for PST</entry>
1712 <title>Intervals</title>
1715 <primary>interval</primary>
1719 <type>interval</type> values can be written with the following syntax:
1722 Quantity Unit [Quantity Unit...] [Direction]
1723 @ Quantity Unit [Quantity Unit...] [Direction]
1726 where: <literal>Quantity</literal> is a number (possibly signed),
1727 <literal>Unit</literal> is <literal>second</literal>,
1728 <literal>minute</literal>, <literal>hour</literal>, <literal>day</literal>,
1729 <literal>week</literal>, <literal>month</literal>, <literal>year</literal>,
1730 <literal>decade</literal>, <literal>century</literal>, <literal>millennium</literal>,
1731 or abbreviations or plurals of these units;
1732 <literal>Direction</literal> can be <literal>ago</literal> or
1733 empty. The at sign (<literal>@</>) is optional noise. The amounts
1734 of different units are implicitly added up with appropriate
1739 Quantities of days, hours, minutes, and seconds can be specified without
1740 explicit unit markings. For example, <literal>'1 12:59:10'</> is read
1741 the same as <literal>'1 day 12 hours 59 min 10 sec'</>.
1745 The optional precision
1746 <replaceable>p</replaceable> should be between 0 and 6, and
1747 defaults to the precision of the input literal.
1752 <title>Special values</title>
1755 <primary>time</primary>
1756 <secondary>constants</secondary>
1760 <primary>date</primary>
1761 <secondary>constants</secondary>
1765 The following <acronym>SQL</acronym>-compatible functions can be
1766 used as date or time
1767 values for the corresponding data type: <literal>CURRENT_DATE</literal>,
1768 <literal>CURRENT_TIME</literal>,
1769 <literal>CURRENT_TIMESTAMP</literal>. The latter two accept an
1770 optional precision specification. (See also <xref linkend="functions-datetime-current">.)
1774 <productname>PostgreSQL</productname> also supports several
1775 special date/time input values for convenience, as shown in <xref
1776 linkend="datatype-datetime-special-table">. The values
1777 <literal>infinity</literal> and <literal>-infinity</literal>
1778 are specially represented inside the system and will be displayed
1779 the same way; but the others are simply notational shorthands
1780 that will be converted to ordinary date/time values when read.
1783 <table id="datatype-datetime-special-table">
1784 <title>Special Date/Time Inputs</title>
1788 <entry>Input string</entry>
1789 <entry>Description</entry>
1794 <entry><literal>epoch</literal></entry>
1795 <entry>1970-01-01 00:00:00+00 (Unix system time zero)</entry>
1798 <entry><literal>infinity</literal></entry>
1799 <entry>later than all other timestamps (not available for
1800 type <type>date</>)</entry>
1803 <entry><literal>-infinity</literal></entry>
1804 <entry>earlier than all other timestamps (not available for
1805 type <type>date</>)</entry>
1808 <entry><literal>now</literal></entry>
1809 <entry>current transaction time</entry>
1812 <entry><literal>today</literal></entry>
1813 <entry>midnight today</entry>
1816 <entry><literal>tomorrow</literal></entry>
1817 <entry>midnight tomorrow</entry>
1820 <entry><literal>yesterday</literal></entry>
1821 <entry>midnight yesterday</entry>
1824 <entry><literal>zulu</>, <literal>allballs</>, <literal>z</></entry>
1825 <entry>00:00:00.00 GMT</entry>
1834 <sect2 id="datatype-datetime-output">
1835 <title>Date/Time Output</title>
1838 <primary>date</primary>
1839 <secondary>output format</secondary>
1840 <seealso>Formatting</seealso>
1844 <primary>time</primary>
1845 <secondary>output format</secondary>
1846 <seealso>Formatting</seealso>
1850 Output formats can be set to one of the four styles ISO 8601,
1851 <acronym>SQL</acronym> (Ingres), traditional PostgreSQL, and
1852 German, using the <command>SET DateStyle</command>. The default
1853 is the <acronym>ISO</acronym> format. (The
1854 <acronym>SQL</acronym> standard requires the use of the ISO 8601
1855 format. The name of the <quote>SQL</quote> output format is a
1856 historical accident.) <xref
1857 linkend="datatype-datetime-output-table"> shows examples of each
1858 output style. The output of the <type>date</type> and
1859 <type>time</type> types is of course only the date or time part
1860 in accordance with the given examples.
1863 <table id="datatype-datetime-output-table">
1864 <title>Date/Time Output Styles</title>
1868 <entry>Style Specification</entry>
1869 <entry>Description</entry>
1870 <entry>Example</entry>
1876 <entry>ISO 8601/SQL standard</entry>
1877 <entry>1997-12-17 07:37:16-08</entry>
1881 <entry>traditional style</entry>
1882 <entry>12/17/1997 07:37:16.00 PST</entry>
1885 <entry>PostgreSQL</entry>
1886 <entry>original style</entry>
1887 <entry>Wed Dec 17 07:37:16 1997 PST</entry>
1890 <entry>German</entry>
1891 <entry>regional style</entry>
1892 <entry>17.12.1997 07:37:16.00 PST</entry>
1899 The <acronym>SQL</acronym> style has European and non-European
1900 (U.S.) variants, which determines whether month follows day or
1901 vice versa. (See <xref linkend="datatype-datetime-input">
1902 for how this setting also affects interpretation of input values.)
1903 <xref linkend="datatype-datetime-output2-table"> shows an
1907 <table id="datatype-datetime-output2-table">
1908 <title>Date Order Conventions</title>
1912 <entry>Style Specification</entry>
1913 <entry>Description</entry>
1914 <entry>Example</entry>
1919 <entry>European</entry>
1920 <entry><replaceable>day</replaceable>/<replaceable>month</replaceable>/<replaceable>year</replaceable></entry>
1921 <entry>17/12/1997 15:37:16.00 MET</entry>
1925 <entry><replaceable>month</replaceable>/<replaceable>day</replaceable>/<replaceable>year</replaceable></entry>
1926 <entry>12/17/1997 07:37:16.00 PST</entry>
1933 <type>interval</type> output looks like the input format, except that units like
1934 <literal>week</literal> or <literal>century</literal> are converted to years and days.
1935 In ISO mode the output looks like
1938 [ Quantity Units [ ... ] ] [ Days ] Hours:Minutes [ ago ]
1943 The date/time styles can be selected by the user using the
1944 <command>SET DATESTYLE</command> command, the
1945 <varname>datestyle</varname> parameter in the
1946 <filename>postgresql.conf</filename> configuration file, and the
1947 <envar>PGDATESTYLE</envar> environment variable on the server or
1948 client. The formatting function <function>to_char</function>
1949 (see <xref linkend="functions-formatting">) is also available as
1950 a more flexible way to format the date/time output.
1954 <sect2 id="datatype-timezones">
1955 <title>Time Zones</title>
1957 <indexterm zone="datatype-timezones">
1958 <primary>time zones</primary>
1962 <productname>PostgreSQL</productname> endeavors to be compatible with
1963 the <acronym>SQL</acronym> standard definitions for typical usage.
1964 However, the <acronym>SQL</acronym> standard has an odd mix of date and
1965 time types and capabilities. Two obvious problems are:
1970 Although the <type>date</type> type
1971 does not have an associated time zone, the
1972 <type>time</type> type can.
1973 Time zones in the real world can have no meaning unless
1974 associated with a date as well as a time
1975 since the offset may vary through the year with daylight-saving
1982 The default time zone is specified as a constant integer offset
1983 from <acronym>GMT</>/<acronym>UTC</>. It is not possible to adapt to daylight-saving
1984 time when doing date/time arithmetic across
1985 <acronym>DST</acronym> boundaries.
1993 To address these difficulties, we recommend using date/time types
1994 that contain both date and time when using time zones. We
1995 recommend <emphasis>not</emphasis> using the type <type>time with
1996 time zone</type> (though it is supported by
1997 <productname>PostgreSQL</productname> for legacy applications and
1998 for compatibility with other <acronym>SQL</acronym>
1999 implementations). <productname>PostgreSQL</productname> assumes
2000 your local time zone for any type containing only date or
2001 time. Further, time zone support is derived from the underlying
2002 operating system time-zone capabilities, and hence can handle
2003 daylight-saving time and other expected behavior.
2007 <productname>PostgreSQL</productname> obtains time-zone support
2008 from the underlying operating system for dates between 1902 and
2009 2038 (near the typical date limits for Unix-style
2010 systems). Outside of this range, all dates are assumed to be
2011 specified and used in Universal Coordinated Time
2012 (<acronym>UTC</acronym>).
2016 All dates and times are stored internally in
2017 <acronym>UTC</acronym>, traditionally known as Greenwich Mean
2018 Time (<acronym>GMT</acronym>). Times are converted to local time
2019 on the database server before being sent to the client frontend,
2020 hence by default are in the server time zone.
2024 There are several ways to select the time zone used by the server:
2029 The <envar>TZ</envar> environment variable on the server host
2030 is used by the server as the default time zone, if no other is
2037 The <varname>timezone</varname> configuration parameter can be
2038 set in <filename>postgresql.conf</>.
2044 The <envar>PGTZ</envar> environment variable, if set at the
2045 client, is used by <application>libpq</application>
2046 applications to send a <command>SET TIME ZONE</command>
2047 command to the server upon connection.
2053 The <acronym>SQL</acronym> command <command>SET TIME ZONE</command>
2054 sets the time zone for the session.
2062 If an invalid time zone is specified, the time zone becomes
2063 <acronym>UTC</acronym> (on most systems anyway).
2068 Refer to <xref linkend="datetime-appendix"> for a list of
2069 available time zones.
2074 <sect2 id="datatype-datetime-internals">
2075 <title>Internals</title>
2078 <productname>PostgreSQL</productname> uses Julian dates
2079 for all date/time calculations. They have the nice property of correctly
2080 predicting/calculating any date more recent than 4713 BC
2081 to far into the future, using the assumption that the length of the
2082 year is 365.2425 days.
2086 Date conventions before the 19th century make for interesting reading,
2087 but are not consistent enough to warrant coding into a date/time handler.
2093 <sect1 id="datatype-boolean">
2094 <title>Boolean Type</title>
2096 <indexterm zone="datatype-boolean">
2097 <primary>Boolean</primary>
2098 <secondary>data type</secondary>
2101 <indexterm zone="datatype-boolean">
2102 <primary>true</primary>
2105 <indexterm zone="datatype-boolean">
2106 <primary>false</primary>
2110 <productname>PostgreSQL</productname> provides the
2111 standard <acronym>SQL</acronym> type <type>boolean</type>.
2112 <type>boolean</type> can have one of only two states:
2113 <quote>true</quote> or <quote>false</quote>. A third state,
2114 <quote>unknown</quote>, is represented by the
2115 <acronym>SQL</acronym> null value.
2119 Valid literal values for the <quote>true</quote> state are:
2121 <member><literal>TRUE</literal></member>
2122 <member><literal>'t'</literal></member>
2123 <member><literal>'true'</literal></member>
2124 <member><literal>'y'</literal></member>
2125 <member><literal>'yes'</literal></member>
2126 <member><literal>'1'</literal></member>
2128 For the <quote>false</quote> state, the following values can be
2131 <member><literal>FALSE</literal></member>
2132 <member><literal>'f'</literal></member>
2133 <member><literal>'false'</literal></member>
2134 <member><literal>'n'</literal></member>
2135 <member><literal>'no'</literal></member>
2136 <member><literal>'0'</literal></member>
2138 Using the key words <literal>TRUE</literal> and
2139 <literal>FALSE</literal> is preferred (and
2140 <acronym>SQL</acronym>-compliant).
2143 <example id="datatype-boolean-example">
2144 <title>Using the <type>boolean</type> type</title>
2147 CREATE TABLE test1 (a boolean, b text);
2148 INSERT INTO test1 VALUES (TRUE, 'sic est');
2149 INSERT INTO test1 VALUES (FALSE, 'non est');
2150 SELECT * FROM test1;
2156 SELECT * FROM test1 WHERE a;
2164 <xref linkend="datatype-boolean-example"> shows that
2165 <type>boolean</type> values are output using the letters
2166 <literal>t</literal> and <literal>f</literal>.
2171 Values of the <type>boolean</type> type cannot be cast directly
2172 to other types (e.g., <literal>CAST
2173 (<replaceable>boolval</replaceable> AS integer)</literal> does
2174 not work). This can be accomplished using the
2175 <literal>CASE</literal> expression: <literal>CASE WHEN
2176 <replaceable>boolval</replaceable> THEN 'value if true' ELSE
2177 'value if false' END</literal>. See also <xref
2178 linkend="functions-conditional">.
2183 <type>boolean</type> uses 1 byte of storage.
2187 <sect1 id="datatype-geometric">
2188 <title>Geometric Types</title>
2191 Geometric data types represent two-dimensional spatial
2192 objects. <xref linkend="datatype-geo-table"> shows the geometric
2193 types available in <productname>PostgreSQL</productname>. The
2194 most fundamental type, the point, forms the basis for all of the
2198 <table id="datatype-geo-table">
2199 <title>Geometric Types</title>
2203 <entry>Geometric Type</entry>
2204 <entry>Storage</entry>
2205 <entry>Representation</entry>
2206 <entry>Description</entry>
2211 <entry><type>point</type></entry>
2212 <entry>16 bytes</entry>
2213 <entry>(x,y)</entry>
2214 <entry>Point in space</entry>
2217 <entry><type>line</type></entry>
2218 <entry>32 bytes</entry>
2219 <entry>((x1,y1),(x2,y2))</entry>
2220 <entry>Infinite line (not fully implemented)</entry>
2223 <entry><type>lseg</type></entry>
2224 <entry>32 bytes</entry>
2225 <entry>((x1,y1),(x2,y2))</entry>
2226 <entry>Finite line segment</entry>
2229 <entry><type>box</type></entry>
2230 <entry>32 bytes</entry>
2231 <entry>((x1,y1),(x2,y2))</entry>
2232 <entry>Rectangular box</entry>
2235 <entry><type>path</type></entry>
2236 <entry>16+16n bytes</entry>
2237 <entry>((x1,y1),...)</entry>
2238 <entry>Closed path (similar to polygon)</entry>
2241 <entry><type>path</type></entry>
2242 <entry>16+16n bytes</entry>
2243 <entry>[(x1,y1),...]</entry>
2244 <entry>Open path</entry>
2247 <entry><type>polygon</type></entry>
2248 <entry>40+16n bytes</entry>
2249 <entry>((x1,y1),...)</entry>
2250 <entry>Polygon (similar to closed path)</entry>
2253 <entry><type>circle</type></entry>
2254 <entry>24 bytes</entry>
2255 <entry><(x,y),r></entry>
2256 <entry>Circle (center and radius)</entry>
2263 A rich set of functions and operators is available to perform various geometric
2264 operations such as scaling, translation, rotation, and determining
2265 intersections. They are explained in <xref linkend="functions-geometry">.
2269 <title>Point</title>
2272 <primary>point</primary>
2276 Points are the fundamental two-dimensional building block for geometric types.
2277 <type>point</type> is specified using the following syntax:
2280 ( <replaceable>x</replaceable> , <replaceable>y</replaceable> )
2281 <replaceable>x</replaceable> , <replaceable>y</replaceable>
2284 where the arguments are
2288 <term><replaceable>x</replaceable></term>
2291 the x-axis coordinate as a floating-point number
2297 <term><replaceable>y</replaceable></term>
2300 the y-axis coordinate as a floating-point number
2309 <title>Line Segment</title>
2312 <primary>line</primary>
2316 Line segments (<type>lseg</type>) are represented by pairs of points.
2317 <type>lseg</type> is specified using the following syntax:
2320 ( ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ( <replaceable>x2</replaceable> , <replaceable>y2</replaceable> ) )
2321 ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ( <replaceable>x2</replaceable> , <replaceable>y2</replaceable> )
2322 <replaceable>x1</replaceable> , <replaceable>y1</replaceable> , <replaceable>x2</replaceable> , <replaceable>y2</replaceable>
2325 where the arguments are
2329 <term>(<replaceable>x1</replaceable>,<replaceable>y1</replaceable>)</term>
2330 <term>(<replaceable>x2</replaceable>,<replaceable>y2</replaceable>)</term>
2333 the end points of the line segment
2345 <primary>box (data type)</primary>
2349 Boxes are represented by pairs of points that are opposite
2351 <type>box</type> is specified using the following syntax:
2354 ( ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ( <replaceable>x2</replaceable> , <replaceable>y2</replaceable> ) )
2355 ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ( <replaceable>x2</replaceable> , <replaceable>y2</replaceable> )
2356 <replaceable>x1</replaceable> , <replaceable>y1</replaceable> , <replaceable>x2</replaceable> , <replaceable>y2</replaceable>
2359 where the arguments are
2363 <term>(<replaceable>x1</replaceable>,<replaceable>y1</replaceable>)</term>
2364 <term>(<replaceable>x2</replaceable>,<replaceable>y2</replaceable>)</term>
2367 opposite corners of the box
2375 Boxes are output using the first syntax.
2376 The corners are reordered on input to store
2377 the upper right corner, then the lower left corner.
2378 Other corners of the box can be entered, but the lower
2379 left and upper right corners are determined from the input and stored corners.
2387 <primary>path (data type)</primary>
2391 Paths are represented by connected sets of points. Paths can be
2392 <firstterm>open</firstterm>, where
2393 the first and last points in the set are not connected, and <firstterm>closed</firstterm>,
2394 where the first and last point are connected. Functions
2395 <function>popen(p)</function>
2397 <function>pclose(p)</function>
2398 are supplied to force a path to be open or closed, and functions
2399 <function>isopen(p)</function>
2401 <function>isclosed(p)</function>
2402 are supplied to test for either type in a query.
2406 <type>path</type> is specified using the following syntax:
2409 ( ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ... , ( <replaceable>xn</replaceable> , <replaceable>yn</replaceable> ) )
2410 [ ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ... , ( <replaceable>xn</replaceable> , <replaceable>yn</replaceable> ) ]
2411 ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ... , ( <replaceable>xn</replaceable> , <replaceable>yn</replaceable> )
2412 ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> , ... , <replaceable>xn</replaceable> , <replaceable>yn</replaceable> )
2413 <replaceable>x1</replaceable> , <replaceable>y1</replaceable> , ... , <replaceable>xn</replaceable> , <replaceable>yn</replaceable>
2416 where the arguments are
2420 <term>(<replaceable>x</replaceable>,<replaceable>y</replaceable>)</term>
2423 End points of the line segments comprising the path.
2424 A leading square bracket (<literal>[</>) indicates an open path, while
2425 a leading parenthesis (<literal>(</>) indicates a closed path.
2433 Paths are output using the first syntax.
2438 <title>Polygon</title>
2441 <primary>polygon</primary>
2445 Polygons are represented by sets of points. Polygons should probably be
2446 considered equivalent to closed paths, but are stored differently
2447 and have their own set of support routines.
2451 <type>polygon</type> is specified using the following syntax:
2454 ( ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ... , ( <replaceable>xn</replaceable> , <replaceable>yn</replaceable> ) )
2455 ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ... , ( <replaceable>xn</replaceable> , <replaceable>yn</replaceable> )
2456 ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> , ... , <replaceable>xn</replaceable> , <replaceable>yn</replaceable> )
2457 <replaceable>x1</replaceable> , <replaceable>y1</replaceable> , ... , <replaceable>xn</replaceable> , <replaceable>yn</replaceable>
2460 where the arguments are
2464 <term>(<replaceable>x</replaceable>,<replaceable>y</replaceable>)</term>
2467 End points of the line segments comprising the boundary of the
2476 Polygons are output using the first syntax.
2481 <title>Circle</title>
2484 <primary>circle</primary>
2488 Circles are represented by a center point and a radius.
2489 <type>circle</type> is specified using the following syntax:
2492 < ( <replaceable>x</replaceable> , <replaceable>y</replaceable> ) , <replaceable>r</replaceable> >
2493 ( ( <replaceable>x</replaceable> , <replaceable>y</replaceable> ) , <replaceable>r</replaceable> )
2494 ( <replaceable>x</replaceable> , <replaceable>y</replaceable> ) , <replaceable>r</replaceable>
2495 <replaceable>x</replaceable> , <replaceable>y</replaceable> , <replaceable>r</replaceable>
2498 where the arguments are
2502 <term>(<replaceable>x</replaceable>,<replaceable>y</replaceable>)</term>
2505 center of the circle
2511 <term><replaceable>r</replaceable></term>
2514 radius of the circle
2522 Circles are output using the first syntax.
2528 <sect1 id="datatype-net-types">
2529 <title>Network Address Data Types</title>
2531 <indexterm zone="datatype-net-types">
2532 <primary>network</primary>
2533 <secondary>addresses</secondary>
2537 <productname>PostgreSQL</> offers data types to store IP and MAC
2538 addresses, shown in <xref linkend="datatype-net-types-table">. It
2539 is preferable to use these types over plain text types, because
2540 these types offer input error checking and several specialized
2541 operators and functions.
2544 <table tocentry="1" id="datatype-net-types-table">
2545 <title>Network Address Data Types</title>
2550 <entry>Storage</entry>
2551 <entry>Description</entry>
2552 <entry>Range</entry>
2558 <entry><type>cidr</type></entry>
2559 <entry>12 bytes</entry>
2560 <entry>IP networks</entry>
2561 <entry>valid IPv4 networks</entry>
2565 <entry><type>inet</type></entry>
2566 <entry>12 bytes</entry>
2567 <entry>IP hosts and networks</entry>
2568 <entry>valid IPv4 hosts or networks</entry>
2572 <entry><type>macaddr</type></entry>
2573 <entry>6 bytes</entry>
2574 <entry>MAC addresses</entry>
2575 <entry>customary formats</entry>
2583 IPv6 is not yet supported.
2587 <sect2 id="datatype-inet">
2588 <title><type>inet</type></title>
2591 <primary>inet (data type)</primary>
2595 The <type>inet</type> type holds an IP host address, and
2596 optionally the identity of the subnet it is in, all in one field.
2597 The subnet identity is represented by the number of bits in the
2598 network part of the address (the <quote>netmask</quote>). If the
2600 then the value does not indicate a subnet, only a single host.
2601 Note that if you want to accept networks only, you should use the
2602 <type>cidr</type> type rather than <type>inet</type>.
2606 The input format for this type is <replaceable
2607 class="parameter">x.x.x.x/y</replaceable> where <replaceable
2608 class="parameter">x.x.x.x</replaceable> is an IP address and
2609 <replaceable class="parameter">y</replaceable> is the number of
2610 bits in the netmask. If the <replaceable
2611 class="parameter">/y</replaceable> part is left off, then the
2612 netmask is 32, and the value represents just a single host.
2613 On display, the <replaceable class="parameter">/y</replaceable>
2614 portion is suppressed if the netmask is 32.
2618 <sect2 id="datatype-cidr">
2619 <title><type>cidr</></title>
2622 <primary>cidr</primary>
2626 The <type>cidr</type> type holds an IP network specification.
2627 Input and output formats follow Classless Internet Domain Routing
2630 specifying classless networks is <replaceable
2631 class="parameter">x.x.x.x/y</> where <replaceable
2632 class="parameter">x.x.x.x</> is the network and <replaceable
2633 class="parameter">y</> is the number of bits in the netmask. If
2634 <replaceable class="parameter">y</> is omitted, it is calculated
2635 using assumptions from the older classful numbering system, except
2636 that it will be at least large enough to include all of the octets
2637 written in the input.
2641 <xref linkend="datatype-net-cidr-table"> shows some examples.
2644 <table id="datatype-net-cidr-table">
2645 <title><type>cidr</> Type Input Examples</title>
2649 <entry><type>CIDR</type> Input</entry>
2650 <entry><type>CIDR</type> Displayed</entry>
2651 <entry><function>abbrev</function>(<type>CIDR</type>)</entry>
2656 <entry>192.168.100.128/25</entry>
2657 <entry>192.168.100.128/25</entry>
2658 <entry>192.168.100.128/25</entry>
2661 <entry>192.168/24</entry>
2662 <entry>192.168.0.0/24</entry>
2663 <entry>192.168.0/24</entry>
2666 <entry>192.168/25</entry>
2667 <entry>192.168.0.0/25</entry>
2668 <entry>192.168.0.0/25</entry>
2671 <entry>192.168.1</entry>
2672 <entry>192.168.1.0/24</entry>
2673 <entry>192.168.1/24</entry>
2676 <entry>192.168</entry>
2677 <entry>192.168.0.0/24</entry>
2678 <entry>192.168.0/24</entry>
2681 <entry>128.1</entry>
2682 <entry>128.1.0.0/16</entry>
2683 <entry>128.1/16</entry>
2687 <entry>128.0.0.0/16</entry>
2688 <entry>128.0/16</entry>
2691 <entry>128.1.2</entry>
2692 <entry>128.1.2.0/24</entry>
2693 <entry>128.1.2/24</entry>
2696 <entry>10.1.2</entry>
2697 <entry>10.1.2.0/24</entry>
2698 <entry>10.1.2/24</entry>
2702 <entry>10.1.0.0/16</entry>
2703 <entry>10.1/16</entry>
2707 <entry>10.0.0.0/8</entry>
2715 <sect2 id="datatype-inet-vs-cidr">
2716 <title><type>inet</type> vs <type>cidr</type></title>
2719 The essential difference between <type>inet</type> and <type>cidr</type>
2720 data types is that <type>inet</type> accepts values with nonzero bits to
2721 the right of the netmask, whereas <type>cidr</type> does not.
2725 If you do not like the output format for <type>inet</type> or
2726 <type>cidr</type> values, try the <function>host</>(),
2727 <function>text</>(), and <function>abbrev</>() functions.
2733 <sect2 id="datatype-macaddr">
2734 <title><type>macaddr</></>
2737 <primary>macaddr (data type)</primary>
2741 <primary>MAC address</primary>
2746 The <type>macaddr</> type stores MAC addresses, i.e., Ethernet
2747 card hardware addresses (although MAC addresses are used for
2748 other purposes as well). Input is accepted in various customary
2752 <member><literal>'08002b:010203'</></member>
2753 <member><literal>'08002b-010203'</></member>
2754 <member><literal>'0800.2b01.0203'</></member>
2755 <member><literal>'08-00-2b-01-02-03'</></member>
2756 <member><literal>'08:00:2b:01:02:03'</></member>
2759 which would all specify the same
2760 address. Upper and lower case is accepted for the digits
2761 <literal>a</> through <literal>f</>. Output is always in the
2762 last of the shown forms.
2766 The directory <filename class="directory">contrib/mac</filename>
2767 in the <productname>PostgreSQL</productname> source distribution
2768 contains tools that can be used to map MAC addresses to hardware
2775 <sect1 id="datatype-bit">
2776 <title>Bit String Types</title>
2778 <indexterm zone="datatype-bit">
2779 <primary>bit strings</primary>
2780 <secondary>data type</secondary>
2784 Bit strings are strings of 1's and 0's. They can be used to store
2785 or visualize bit masks. There are two SQL bit types:
2786 <type>BIT(<replaceable>n</replaceable>)</type> and <type>BIT
2787 VARYING(<replaceable>n</replaceable>)</type>, where
2788 <replaceable>n</replaceable> is a positive integer.
2792 <type>BIT</type> type data must match the length
2793 <replaceable>n</replaceable> exactly; it is an error to attempt to
2794 store shorter or longer bit strings. <type>BIT VARYING</type> data is
2795 of variable length up to the maximum length
2796 <replaceable>n</replaceable>; longer strings will be rejected.
2797 Writing <type>BIT</type> without a length is equivalent to
2798 <literal>BIT(1)</literal>, while <type>BIT VARYING</type> without a length
2799 specification means unlimited length.
2804 If one explicitly casts a bit-string value to
2805 <type>BIT(<replaceable>n</>)</type>, it will be truncated or
2806 zero-padded on the right to be exactly <replaceable>n</> bits,
2807 without raising an error. Similarly,
2808 if one explicitly casts a bit-string value to
2809 <type>BIT VARYING(<replaceable>n</>)</type>, it will be truncated
2810 on the right if it is more than <replaceable>n</> bits.
2816 Prior to <productname>PostgreSQL</> 7.2, <type>BIT</type> data
2817 was always silently truncated or zero-padded on the right, with
2818 or without an explicit cast. This was changed to comply with the
2819 <acronym>SQL</acronym> standard.
2825 linkend="sql-syntax-bit-strings"> for information about the syntax
2826 of bit string constants. Bit-logical operators and string
2827 manipulation functions are available; see <xref
2828 linkend="functions">.
2832 <title>Using the bit string types</title>
2835 CREATE TABLE test (a BIT(3), b BIT VARYING(5));
2836 INSERT INTO test VALUES (B'101', B'00');
2837 INSERT INTO test VALUES (B'10', B'101');
2839 ERROR: Bit string length 2 does not match type BIT(3)
2841 INSERT INTO test VALUES (B'10'::bit(3), B'101');
2854 <sect1 id="datatype-oid">
2855 <title>Object Identifier Types</title>
2857 <indexterm zone="datatype-oid">
2858 <primary>object identifier</primary>
2859 <secondary>data type</secondary>
2862 <indexterm zone="datatype-oid">
2863 <primary>oid</primary>
2866 <indexterm zone="datatype-oid">
2867 <primary>regproc</primary>
2870 <indexterm zone="datatype-oid">
2871 <primary>regprocedure</primary>
2874 <indexterm zone="datatype-oid">
2875 <primary>regoper</primary>
2878 <indexterm zone="datatype-oid">
2879 <primary>regoperator</primary>
2882 <indexterm zone="datatype-oid">
2883 <primary>regclass</primary>
2886 <indexterm zone="datatype-oid">
2887 <primary>regtype</primary>
2890 <indexterm zone="datatype-oid">
2891 <primary>xid</primary>
2894 <indexterm zone="datatype-oid">
2895 <primary>cid</primary>
2898 <indexterm zone="datatype-oid">
2899 <primary>tid</primary>
2903 Object identifiers (OIDs) are used internally by
2904 <productname>PostgreSQL</productname> as primary keys for various system
2905 tables. Also, an OID system column is added to user-created tables
2906 (unless <literal>WITHOUT OIDS</> is specified at table creation time).
2907 Type <type>oid</> represents an object identifier. There are also
2908 several aliases for <type>oid</>: <type>regproc</>, <type>regprocedure</>,
2909 <type>regoper</>, <type>regoperator</>, <type>regclass</>,
2910 and <type>regtype</>. <xref linkend="datatype-oid-table"> shows an overview.
2914 The <type>oid</> type is currently implemented as an unsigned four-byte
2916 Therefore, it is not large enough to provide database-wide uniqueness
2917 in large databases, or even in large individual tables. So, using a
2918 user-created table's OID column as a primary key is discouraged.
2919 OIDs are best used only for references to system tables.
2923 The <type>oid</> type itself has few operations beyond comparison
2924 (which is implemented as unsigned comparison). It can be cast to
2925 integer, however, and then manipulated using the standard integer
2926 operators. (Beware of possible signed-versus-unsigned confusion
2931 The <type>oid</> alias types have no operations of their own except
2932 for specialized input and output routines. These routines are able
2933 to accept and display symbolic names for system objects, rather than
2934 the raw numeric value that type <type>oid</> would use. The alias
2935 types allow simplified lookup of OID values for objects: for example,
2936 one may write <literal>'mytable'::regclass</> to get the OID of table
2937 <literal>mytable</>, rather than <literal>SELECT oid FROM pg_class WHERE
2938 relname = 'mytable'</>. (In reality, a much more complicated <command>SELECT</> would
2939 be needed to deal with selecting the right OID when there are multiple
2940 tables named <literal>mytable</> in different schemas.)
2943 <table id="datatype-oid-table">
2944 <title>Object Identifier Types</title>
2948 <entry>Type name</entry>
2949 <entry>References</entry>
2950 <entry>Description</entry>
2951 <entry>Value example</entry>
2958 <entry><type>oid</></entry>
2960 <entry>numeric object identifier</entry>
2961 <entry><literal>564182</></entry>
2965 <entry><type>regproc</></entry>
2966 <entry><structname>pg_proc</></entry>
2967 <entry>function name</entry>
2968 <entry><literal>sum</></entry>
2972 <entry><type>regprocedure</></entry>
2973 <entry><structname>pg_proc</></entry>
2974 <entry>function with argument types</entry>
2975 <entry><literal>sum(int4)</></entry>
2979 <entry><type>regoper</></entry>
2980 <entry><structname>pg_operator</></entry>
2981 <entry>operator name</entry>
2982 <entry><literal>+</></entry>
2986 <entry><type>regoperator</></entry>
2987 <entry><structname>pg_operator</></entry>
2988 <entry>operator with argument types</entry>
2989 <entry><literal>*(integer,integer)</> or <literal>-(NONE,integer)</></entry>
2993 <entry><type>regclass</></entry>
2994 <entry><structname>pg_class</></entry>
2995 <entry>relation name</entry>
2996 <entry><literal>pg_type</></entry>
3000 <entry><type>regtype</></entry>
3001 <entry><structname>pg_type</></entry>
3002 <entry>type name</entry>
3003 <entry><literal>integer</></entry>
3010 All of the OID alias types accept schema-qualified names, and will
3011 display schema-qualified names on output if the object would not
3012 be found in the current search path without being qualified.
3013 The <type>regproc</> and <type>regoper</> alias types will only
3014 accept input names that are unique (not overloaded), so they are
3015 of limited use; for most uses <type>regprocedure</> or
3016 <type>regoperator</> is more appropriate. For <type>regoperator</>,
3017 unary operators are identified by writing <literal>NONE</> for the unused
3022 OIDs are 32-bit quantities and are assigned from a single cluster-wide
3023 counter. In a large or long-lived database, it is possible for the
3024 counter to wrap around. Hence, it is bad practice to assume that OIDs
3025 are unique, unless you take steps to ensure that they are unique.
3026 Recommended practice when using OIDs for row identification is to create
3027 a unique constraint on the OID column of each table for which the OID will
3028 be used. Never assume that OIDs are unique across tables; use the
3029 combination of <structfield>tableoid</> and row OID if you need a
3030 database-wide identifier. (Future releases of
3031 <productname>PostgreSQL</productname> are likely to use a separate
3032 OID counter for each table, so that <structfield>tableoid</>
3033 <emphasis>must</> be included to arrive at a globally unique identifier.)
3037 Another identifier type used by the system is <type>xid</>, or transaction
3038 (abbreviated <abbrev>xact</>) identifier. This is the data type of the system columns
3039 <structfield>xmin</> and <structfield>xmax</>.
3040 Transaction identifiers are 32-bit quantities. In a long-lived
3041 database it is possible for transaction IDs to wrap around. This
3042 is not a fatal problem given appropriate maintenance procedures;
3043 see the &cite-admin; for details. However, it is
3044 unwise to depend on uniqueness of transaction IDs over the long term
3045 (more than one billion transactions).
3049 A third identifier type used by the system is <type>cid</>, or
3050 command identifier. This is the data type of the system columns
3051 <structfield>cmin</> and <structfield>cmax</>. Command
3052 identifiers are also 32-bit quantities. This creates a hard limit
3053 of 2<superscript>32</> (4 billion) <acronym>SQL</acronym> commands
3054 within a single transaction. In practice this limit is not a
3055 problem --- note that the limit is on number of
3056 <acronym>SQL</acronym> commands, not number of tuples processed.
3060 A final identifier type used by the system is <type>tid</>, or tuple
3061 identifier. This is the data type of the system column
3062 <structfield>ctid</>. A tuple ID is a pair
3063 (block number, tuple index within block) that identifies the
3064 physical location of the tuple within its table.
3069 <sect1 id="datatype-pseudo">
3070 <title>Pseudo-Types</title>
3072 <indexterm zone="datatype-pseudo">
3073 <primary>record</primary>
3076 <indexterm zone="datatype-pseudo">
3077 <primary>any</primary>
3080 <indexterm zone="datatype-pseudo">
3081 <primary>anyarray</primary>
3084 <indexterm zone="datatype-pseudo">
3085 <primary>void</primary>
3088 <indexterm zone="datatype-pseudo">
3089 <primary>trigger</primary>
3092 <indexterm zone="datatype-pseudo">
3093 <primary>language_handler</primary>
3096 <indexterm zone="datatype-pseudo">
3097 <primary>cstring</primary>
3100 <indexterm zone="datatype-pseudo">
3101 <primary>internal</primary>
3104 <indexterm zone="datatype-pseudo">
3105 <primary>opaque</primary>
3109 The <productname>PostgreSQL</productname> type system contains a
3110 number of special-purpose entries that are collectively called
3111 <firstterm>pseudo-types</>. A pseudo-type cannot be used as a
3112 column data type, but it can be used to declare a function's
3113 argument or result type. Each of the available pseudo-types is
3114 useful in situations where a function's behavior does not
3115 correspond to simply taking or returning a value of a specific
3116 <acronym>SQL</acronym> data type. <xref
3117 linkend="datatype-pseudotypes-table"> lists the existing
3121 <table id="datatype-pseudotypes-table">
3122 <title>Pseudo-Types</title>
3126 <entry>Type name</entry>
3127 <entry>Description</entry>
3134 <entry><type>record</></entry>
3135 <entry>Identifies a function returning an unspecified row type</entry>
3139 <entry><type>any</></entry>
3140 <entry>Indicates that a function accepts any input data type whatever</entry>
3144 <entry><type>anyarray</></entry>
3145 <entry>Indicates that a function accepts any array data type</entry>
3149 <entry><type>void</></entry>
3150 <entry>Indicates that a function returns no value</entry>
3154 <entry><type>trigger</></entry>
3155 <entry>A trigger function is declared to return <type>trigger</></entry>
3159 <entry><type>language_handler</></entry>
3160 <entry>A procedural language call handler is declared to return <type>language_handler</></entry>
3164 <entry><type>cstring</></entry>
3165 <entry>Indicates that a function accepts or returns a null-terminated C string</entry>
3169 <entry><type>internal</></entry>
3170 <entry>Indicates that a function accepts or returns a server-internal
3175 <entry><type>opaque</></entry>
3176 <entry>An obsolete type name that formerly served all the above purposes</entry>
3183 Functions coded in C (whether built-in or dynamically loaded) may be
3184 declared to accept or return any of these pseudo data types. It is up to
3185 the function author to ensure that the function will behave safely
3186 when a pseudo-type is used as an argument type.
3190 Functions coded in procedural languages may use pseudo-types only as
3191 allowed by their implementation languages. At present the procedural
3192 languages all forbid use of a pseudo-type as argument type, and allow
3193 only <type>void</> as a result type (plus <type>trigger</> when the
3194 function is used as a trigger).
3198 The <type>internal</> pseudo-type is used to declare functions
3199 that are meant only to be called internally by the database
3200 system, and not by direct invocation in a <acronym>SQL</acronym>
3201 query. If a function has at least one <type>internal</>-type
3202 argument then it cannot be called from <acronym>SQL</acronym>. To
3203 preserve the type safety of this restriction it is important to
3204 follow this coding rule: do not create any function that is
3205 declared to return <type>internal</> unless it has at least one
3206 <type>internal</> argument.
3215 <!-- Keep this comment at the end of the file
3220 sgml-minimize-attributes:nil
3221 sgml-always-quote-attributes:t
3223 sgml-indent-tabs-mode:nil
3225 sgml-parent-document:nil
3226 sgml-default-dtd-file:"./reference.ced"
3227 sgml-exposed-tags:nil
3228 sgml-local-catalogs:("/usr/share/sgml/catalog")
3229 sgml-local-ecat-files:nil