2 $PostgreSQL: pgsql/doc/src/sgml/datatype.sgml,v 1.142 2004/02/01 06:55:07 tgl Exp $
5 <chapter id="datatype">
6 <title id="datatype-title">Data Types</title>
8 <indexterm zone="datatype">
9 <primary>data type</primary>
13 <primary>type</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 built-in general-purpose data types.
26 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.
34 <table id="datatype-table">
35 <title>Data Types</title>
40 <entry>Aliases</entry>
41 <entry>Description</entry>
47 <entry><type>bigint</type></entry>
48 <entry><type>int8</type></entry>
49 <entry>signed eight-byte integer</entry>
53 <entry><type>bigserial</type></entry>
54 <entry><type>serial8</type></entry>
55 <entry>autoincrementing eight-byte integer</entry>
59 <entry><type>bit</type></entry>
61 <entry>fixed-length bit string</entry>
65 <entry><type>bit varying(<replaceable>n</replaceable>)</type></entry>
66 <entry><type>varbit(<replaceable>n</replaceable>)</type></entry>
67 <entry>variable-length bit string</entry>
71 <entry><type>boolean</type></entry>
72 <entry><type>bool</type></entry>
73 <entry>logical Boolean (true/false)</entry>
77 <entry><type>box</type></entry>
79 <entry>rectangular box in the plane</entry>
83 <entry><type>bytea</type></entry>
85 <entry>binary data</entry>
89 <entry><type>character varying(<replaceable>n</replaceable>)</type></entry>
90 <entry><type>varchar(<replaceable>n</replaceable>)</type></entry>
91 <entry>variable-length character string</entry>
95 <entry><type>character(<replaceable>n</replaceable>)</type></entry>
96 <entry><type>char(<replaceable>n</replaceable>)</type></entry>
97 <entry>fixed-length character string</entry>
101 <entry><type>cidr</type></entry>
103 <entry>IPv4 or IPv6 network address</entry>
107 <entry><type>circle</type></entry>
109 <entry>circle in the plane</entry>
113 <entry><type>date</type></entry>
115 <entry>calendar date (year, month, day)</entry>
119 <entry><type>double precision</type></entry>
120 <entry><type>float8</type></entry>
121 <entry>double precision floating-point number</entry>
125 <entry><type>inet</type></entry>
127 <entry>IPv4 or IPv6 host address</entry>
131 <entry><type>integer</type></entry>
132 <entry><type>int</type>, <type>int4</type></entry>
133 <entry>signed four-byte integer</entry>
137 <entry><type>interval(<replaceable>p</replaceable>)</type></entry>
139 <entry>time span</entry>
143 <entry><type>line</type></entry>
145 <entry>infinite line in the plane (not fully implemented)</entry>
149 <entry><type>lseg</type></entry>
151 <entry>line segment in the plane</entry>
155 <entry><type>macaddr</type></entry>
157 <entry>MAC address</entry>
161 <entry><type>money</type></entry>
163 <entry>currency amount</entry>
167 <entry><type>numeric [ (<replaceable>p</replaceable>,
168 <replaceable>s</replaceable>) ]</type></entry>
169 <entry><type>decimal [ (<replaceable>p</replaceable>,
170 <replaceable>s</replaceable>) ]</type></entry>
171 <entry>exact numeric with selectable precision</entry>
175 <entry><type>path</type></entry>
177 <entry>open and closed geometric path in the plane</entry>
181 <entry><type>point</type></entry>
183 <entry>geometric point in the plane</entry>
187 <entry><type>polygon</type></entry>
189 <entry>closed geometric path in the plane</entry>
193 <entry><type>real</type></entry>
194 <entry><type>float4</type></entry>
195 <entry>single precision floating-point number</entry>
199 <entry><type>smallint</type></entry>
200 <entry><type>int2</type></entry>
201 <entry>signed two-byte integer</entry>
205 <entry><type>serial</type></entry>
206 <entry><type>serial4</type></entry>
207 <entry>autoincrementing four-byte integer</entry>
211 <entry><type>text</type></entry>
213 <entry>variable-length character string</entry>
217 <entry><type>time [ (<replaceable>p</replaceable>) ] [ without time zone ]</type></entry>
219 <entry>time of day</entry>
223 <entry><type>time [ (<replaceable>p</replaceable>) ] with time zone</type></entry>
224 <entry><type>timetz</type></entry>
225 <entry>time of day, including time zone</entry>
229 <entry><type>timestamp [ (<replaceable>p</replaceable>) ] without time zone</type></entry>
230 <entry><type>timestamp</type></entry>
231 <entry>date and time</entry>
235 <entry><type>timestamp [ (<replaceable>p</replaceable>) ] [ with time zone ]</type></entry>
236 <entry><type>timestamptz</type></entry>
237 <entry>date and time, including time zone</entry>
244 <title>Compatibility</title>
246 The following types (or spellings thereof) are specified by
247 <acronym>SQL</acronym>: <type>bit</type>, <type>bit
248 varying</type>, <type>boolean</type>, <type>char</type>,
249 <type>character varying</type>, <type>character</type>,
250 <type>varchar</type>, <type>date</type>, <type>double
251 precision</type>, <type>integer</type>, <type>interval</type>,
252 <type>numeric</type>, <type>decimal</type>, <type>real</type>,
253 <type>smallint</type>, <type>time</type> (with or without time zone),
254 <type>timestamp</type> (with or without time zone).
259 Each data type has an external representation determined by its input
260 and output functions. Many of the built-in types have
261 obvious external formats. However, several types are either unique
262 to <productname>PostgreSQL</productname>, such as open and closed
263 paths, or have several possibilities for formats, such as the date
265 Some of the input and output functions are not invertible. That is,
266 the result of an output function may lose accuracy when compared to
271 Some of the operators and functions (e.g.,
272 addition and multiplication) do not perform run-time error-checking in the
273 interests of improving execution speed.
274 On some systems, for example, the numeric operators for some data types may
275 silently cause underflow or overflow.
278 <sect1 id="datatype-numeric">
279 <title>Numeric Types</title>
281 <indexterm zone="datatype-numeric">
282 <primary>data type</primary>
283 <secondary>numeric</secondary>
287 Numeric types consist of two-, four-, and eight-byte integers,
288 four- and eight-byte floating-point numbers, and fixed-precision
289 decimals. <xref linkend="datatype-numeric-table"> lists the
293 <table id="datatype-numeric-table">
294 <title>Numeric Types</title>
299 <entry>Storage Size</entry>
300 <entry>Description</entry>
307 <entry><type>smallint</></entry>
308 <entry>2 bytes</entry>
309 <entry>small-range integer</entry>
310 <entry>-32768 to +32767</entry>
313 <entry><type>integer</></entry>
314 <entry>4 bytes</entry>
315 <entry>usual choice for integer</entry>
316 <entry>-2147483648 to +2147483647</entry>
319 <entry><type>bigint</></entry>
320 <entry>8 bytes</entry>
321 <entry>large-range integer</entry>
322 <entry>-9223372036854775808 to 9223372036854775807</entry>
326 <entry><type>decimal</></entry>
327 <entry>variable</entry>
328 <entry>user-specified precision, exact</entry>
329 <entry>no limit</entry>
332 <entry><type>numeric</></entry>
333 <entry>variable</entry>
334 <entry>user-specified precision, exact</entry>
335 <entry>no limit</entry>
339 <entry><type>real</></entry>
340 <entry>4 bytes</entry>
341 <entry>variable-precision, inexact</entry>
342 <entry>6 decimal digits precision</entry>
345 <entry><type>double precision</></entry>
346 <entry>8 bytes</entry>
347 <entry>variable-precision, inexact</entry>
348 <entry>15 decimal digits precision</entry>
352 <entry><type>serial</></entry>
353 <entry>4 bytes</entry>
354 <entry>autoincrementing integer</entry>
355 <entry>1 to 2147483647</entry>
359 <entry><type>bigserial</type></entry>
360 <entry>8 bytes</entry>
361 <entry>large autoincrementing integer</entry>
362 <entry>1 to 9223372036854775807</entry>
369 The syntax of constants for the numeric types is described in
370 <xref linkend="sql-syntax-constants">. The numeric types have a
371 full set of corresponding arithmetic operators and
372 functions. Refer to <xref linkend="functions"> for more
373 information. The following sections describe the types in detail.
376 <sect2 id="datatype-int">
377 <title>Integer Types</title>
379 <indexterm zone="datatype-int">
380 <primary>integer</primary>
383 <indexterm zone="datatype-int">
384 <primary>smallint</primary>
387 <indexterm zone="datatype-int">
388 <primary>bigint</primary>
392 <primary>int4</primary>
397 <primary>int2</primary>
402 <primary>int8</primary>
407 The types <type>smallint</type>, <type>integer</type>, and
408 <type>bigint</type> store whole numbers, that is, numbers without
409 fractional components, of various ranges. Attempts to store
410 values outside of the allowed range will result in an error.
414 The type <type>integer</type> is the usual choice, as it offers
415 the best balance between range, storage size, and performance.
416 The <type>smallint</type> type is generally only used if disk
417 space is at a premium. The <type>bigint</type> type should only
418 be used if the <type>integer</type> range is not sufficient,
419 because the latter is definitely faster.
423 The <type>bigint</type> type may not function correctly on all
424 platforms, since it relies on compiler support for eight-byte
425 integers. On a machine without such support, <type>bigint</type>
426 acts the same as <type>integer</type> (but still takes up eight
427 bytes of storage). However, we are not aware of any reasonable
428 platform where this is actually the case.
432 <acronym>SQL</acronym> only specifies the integer types
433 <type>integer</type> (or <type>int</type>) and
434 <type>smallint</type>. The type <type>bigint</type>, and the
435 type names <type>int2</type>, <type>int4</type>, and
436 <type>int8</type> are extensions, which are shared with various
437 other <acronym>SQL</acronym> database systems.
442 <sect2 id="datatype-numeric-decimal">
443 <title>Arbitrary Precision Numbers</title>
445 <indexterm zone="datatype-numeric-decimal">
446 <primary>numeric (data type)</primary>
450 <primary>decimal</primary>
455 The type <type>numeric</type> can store numbers with up to 1000
456 digits of precision and perform calculations exactly. It is
457 especially recommended for storing monetary amounts and other
458 quantities where exactness is required. However, the
459 <type>numeric</type> type is very slow compared to the
460 floating-point types described in the next section.
464 In what follows we use these terms: The
465 <firstterm>scale</firstterm> of a <type>numeric</type> is the
466 count of decimal digits in the fractional part, to the right of
467 the decimal point. The <firstterm>precision</firstterm> of a
468 <type>numeric</type> is the total count of significant digits in
469 the whole number, that is, the number of digits to both sides of
470 the decimal point. So the number 23.5141 has a precision of 6
471 and a scale of 4. Integers can be considered to have a scale of
476 Both the precision and the scale of the numeric type can be
477 configured. To declare a column of type <type>numeric</type> use
480 NUMERIC(<replaceable>precision</replaceable>, <replaceable>scale</replaceable>)
482 The precision must be positive, the scale zero or positive.
485 NUMERIC(<replaceable>precision</replaceable>)
487 selects a scale of 0. Specifying
491 without any precision or scale creates a column in which numeric
492 values of any precision and scale can be stored, up to the
493 implementation limit on precision. A column of this kind will
494 not coerce input values to any particular scale, whereas
495 <type>numeric</type> columns with a declared scale will coerce
496 input values to that scale. (The <acronym>SQL</acronym> standard
497 requires a default scale of 0, i.e., coercion to integer
498 precision. We find this a bit useless. If you're concerned
499 about portability, always specify the precision and scale
504 If the precision or scale of a value is greater than the declared
505 precision or scale of a column, the system will attempt to round
506 the value. If the value cannot be rounded so as to satisfy the
507 declared limits, an error is raised.
511 The types <type>decimal</type> and <type>numeric</type> are
512 equivalent. Both types are part of the <acronym>SQL</acronym>
518 <sect2 id="datatype-float">
519 <title>Floating-Point Types</title>
521 <indexterm zone="datatype-float">
522 <primary>real</primary>
525 <indexterm zone="datatype-float">
526 <primary>double precision</primary>
530 <primary>float4</primary>
535 <primary>float8</primary>
536 <see>double precision</see>
539 <indexterm zone="datatype-float">
540 <primary>floating point</primary>
544 The data types <type>real</type> and <type>double
545 precision</type> are inexact, variable-precision numeric types.
546 In practice, these types are usually implementations of
547 <acronym>IEEE</acronym> Standard 754 for Binary Floating-Point
548 Arithmetic (single and double precision, respectively), to the
549 extent that the underlying processor, operating system, and
554 Inexact means that some values cannot be converted exactly to the
555 internal format and are stored as approximations, so that storing
556 and printing back out a value may show slight discrepancies.
557 Managing these errors and how they propagate through calculations
558 is the subject of an entire branch of mathematics and computer
559 science and will not be discussed further here, except for the
564 If you require exact storage and calculations (such as for
565 monetary amounts), use the <type>numeric</type> type instead.
571 If you want to do complicated calculations with these types
572 for anything important, especially if you rely on certain
573 behavior in boundary cases (infinity, underflow), you should
574 evaluate the implementation carefully.
580 Comparing two floating-point values for equality may or may
581 not work as expected.
588 On most platforms, the <type>real</type> type has a range of at least
589 1E-37 to 1E+37 with a precision of at least 6 decimal digits. The
590 <type>double precision</type> type typically has a range of around
591 1E-307 to 1E+308 with a precision of at least 15 digits. Values that
592 are too large or too small will cause an error. Rounding may
593 take place if the precision of an input number is too high.
594 Numbers too close to zero that are not representable as distinct
595 from zero will cause an underflow error.
599 <productname>PostgreSQL</productname> also supports the SQL-standard
600 notations <type>float</type> and
601 <type>float(<replaceable>p</replaceable>)</type> for specifying
602 inexact numeric types. Here, <replaceable>p</replaceable> specifies
603 the minimum acceptable precision in binary digits.
604 <productname>PostgreSQL</productname> accepts
605 <type>float(1)</type> to <type>float(24)</type> as selecting the
606 <type>real</type> type, while
607 <type>float(25)</type> to <type>float(53)</type> select
608 <type>double precision</type>. Values of <replaceable>p</replaceable>
609 outside the allowed range draw an error.
610 <type>float</type> with no precision specified is taken to mean
611 <type>double precision</type>.
616 Prior to <productname>PostgreSQL</productname> 7.4, the precision in
617 <type>float(<replaceable>p</replaceable>)</type> was taken to mean
618 so many decimal digits. This has been corrected to match the SQL
619 standard, which specifies that the precision is measured in binary
620 digits. The assumption that <type>real</type> and
621 <type>double precision</type> have exactly 24 and 53 bits in the
622 mantissa respectively is correct for IEEE-standard floating point
623 implementations. On non-IEEE platforms it may be off a little, but
624 for simplicity the same ranges of <replaceable>p</replaceable> are used
631 <sect2 id="datatype-serial">
632 <title>Serial Types</title>
634 <indexterm zone="datatype-serial">
635 <primary>serial</primary>
638 <indexterm zone="datatype-serial">
639 <primary>bigserial</primary>
642 <indexterm zone="datatype-serial">
643 <primary>serial4</primary>
646 <indexterm zone="datatype-serial">
647 <primary>serial8</primary>
651 <primary>auto-increment</primary>
656 <primary>sequence</primary>
657 <secondary>and serial type</secondary>
661 The data types <type>serial</type> and <type>bigserial</type>
662 are not true types, but merely
663 a notational convenience for setting up unique identifier columns
664 (similar to the <literal>AUTO_INCREMENT</literal> property
665 supported by some other databases). In the current
666 implementation, specifying
669 CREATE TABLE <replaceable class="parameter">tablename</replaceable> (
670 <replaceable class="parameter">colname</replaceable> SERIAL
674 is equivalent to specifying:
677 CREATE SEQUENCE <replaceable class="parameter">tablename</replaceable>_<replaceable class="parameter">colname</replaceable>_seq;
678 CREATE TABLE <replaceable class="parameter">tablename</replaceable> (
679 <replaceable class="parameter">colname</replaceable> integer DEFAULT nextval('<replaceable class="parameter">tablename</replaceable>_<replaceable class="parameter">colname</replaceable>_seq') NOT NULL
683 Thus, we have created an integer column and arranged for its default
684 values to be assigned from a sequence generator. A <literal>NOT NULL</>
685 constraint is applied to ensure that a null value cannot be explicitly
686 inserted, either. In most cases you would also want to attach a
687 <literal>UNIQUE</> or <literal>PRIMARY KEY</> constraint to prevent
688 duplicate values from being inserted by accident, but this is
694 Prior to <productname>PostgreSQL</productname> 7.3, <type>serial</type>
695 implied <literal>UNIQUE</literal>. This is no longer automatic. If
696 you wish a serial column to be in a unique constraint or a
697 primary key, it must now be specified, same as with
703 To insert the next value of the sequence into the <type>serial</type>
704 column, specify that the <type>serial</type>
705 column should be assigned its default value. This can be done
706 either by excluding the column from the list of columns in
707 the <command>INSERT</command> statement, or through the use of
708 the <literal>DEFAULT</literal> key word.
712 The type names <type>serial</type> and <type>serial4</type> are
713 equivalent: both create <type>integer</type> columns. The type
714 names <type>bigserial</type> and <type>serial8</type> work just
715 the same way, except that they create a <type>bigint</type>
716 column. <type>bigserial</type> should be used if you anticipate
717 the use of more than 2<superscript>31</> identifiers over the
718 lifetime of the table.
722 The sequence created for a <type>serial</type> column is
723 automatically dropped when the owning column is dropped, and
724 cannot be dropped otherwise. (This was not true in
725 <productname>PostgreSQL</productname> releases before 7.3. Note
726 that this automatic drop linkage will not occur for a sequence
727 created by reloading a dump from a pre-7.3 database; the dump
728 file does not contain the information needed to establish the
729 dependency link.) Furthermore, this dependency between sequence
730 and column is made only for the <type>serial</> column itself; if
731 any other columns reference the sequence (perhaps by manually
732 calling the <function>nextval</> function), they will be broken
733 if the sequence is removed. Using a <type>serial</> column's sequence
734 in such a fashion is considered bad form; if you wish to feed several
735 columns from the same sequence generator, create the sequence as an
741 <sect1 id="datatype-money">
742 <title>Monetary Types</title>
746 The <type>money</type> type is deprecated. Use
747 <type>numeric</type> or <type>decimal</type> instead, in
748 combination with the <function>to_char</function> function.
753 The <type>money</type> type stores a currency amount with a fixed
754 fractional precision; see <xref
755 linkend="datatype-money-table">.
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 generally in the latter form but depends on the locale.
762 <table id="datatype-money-table">
763 <title>Monetary Types</title>
768 <entry>Storage Size</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 string</primary>
791 <secondary>data types</secondary>
795 <primary>string</primary>
796 <see>character string</see>
799 <indexterm zone="datatype-character">
800 <primary>character</primary>
803 <indexterm zone="datatype-character">
804 <primary>character varying</primary>
807 <indexterm zone="datatype-character">
808 <primary>text</primary>
811 <indexterm zone="datatype-character">
812 <primary>char</primary>
815 <indexterm zone="datatype-character">
816 <primary>varchar</primary>
819 <table id="datatype-character-table">
820 <title>Character Types</title>
825 <entry>Description</entry>
830 <entry><type>character varying(<replaceable>n</>)</type>, <type>varchar(<replaceable>n</>)</type></entry>
831 <entry>variable-length with limit</entry>
834 <entry><type>character(<replaceable>n</>)</type>, <type>char(<replaceable>n</>)</type></entry>
835 <entry>fixed-length, blank padded</entry>
838 <entry><type>text</type></entry>
839 <entry>variable unlimited length</entry>
846 <xref linkend="datatype-character-table"> shows the
847 general-purpose character types available in
848 <productname>PostgreSQL</productname>.
852 <acronym>SQL</acronym> defines two primary character types:
853 <type>character varying(<replaceable>n</>)</type> and
854 <type>character(<replaceable>n</>)</type>, where <replaceable>n</>
855 is a positive integer. Both of these types can store strings up to
856 <replaceable>n</> characters in length. An attempt to store a
857 longer string into a column of these types will result in an
858 error, unless the excess characters are all spaces, in which case
859 the string will be truncated to the maximum length. (This somewhat
860 bizarre exception is required by the <acronym>SQL</acronym>
861 standard.) If the string to be stored is shorter than the declared
862 length, values of type <type>character</type> will be space-padded;
863 values of type <type>character varying</type> will simply store the
869 If one explicitly casts a value to <type>character
870 varying(<replaceable>n</>)</type> or
871 <type>character(<replaceable>n</>)</type>, then an over-length
872 value will be truncated to <replaceable>n</> characters without
873 raising an error. (This too is required by the
874 <acronym>SQL</acronym> standard.)
879 Prior to <productname>PostgreSQL</> 7.2, strings that were too long were
880 always truncated without raising an error, in either explicit or
881 implicit casting contexts.
886 The notations <type>varchar(<replaceable>n</>)</type> and
887 <type>char(<replaceable>n</>)</type> are aliases for <type>character
888 varying(<replaceable>n</>)</type> and
889 <type>character(<replaceable>n</>)</type>, respectively.
890 <type>character</type> without length specifier is equivalent to
891 <type>character(1)</type>; if <type>character varying</type> is used
892 without length specifier, the type accepts strings of any size. The
893 latter is a <productname>PostgreSQL</> extension.
897 In addition, <productname>PostgreSQL</productname> provides the
898 <type>text</type> type, which stores strings of any length.
899 Although the type <type>text</type> is not in the
900 <acronym>SQL</acronym> standard, several other SQL database
901 management systems have it as well.
905 Values of type <type>character</type> are physically padded
906 with spaces to the specified width <replaceable>n</>, and are
907 stored and displayed that way. However, the padding spaces are
908 treated as semantically insignificant. Trailing spaces are
909 disregarded when comparing two values of type <type>character</type>,
910 and they will be removed when converting a <type>character</type> value
911 to one of the other string types. Note that trailing spaces
912 <emphasis>are</> semantically significant in
913 <type>character varying</type> and <type>text</type> values.
917 The storage requirement for data of these types is 4 bytes plus the
918 actual string, and in case of <type>character</type> plus the
919 padding. Long strings are compressed by the system automatically, so
920 the physical requirement on disk may be less. Long values are also
921 stored in background tables so they do not interfere with rapid
922 access to the shorter column values. In any case, the longest
923 possible character string that can be stored is about 1 GB. (The
924 maximum value that will be allowed for <replaceable>n</> in the data
925 type declaration is less than that. It wouldn't be very useful to
926 change this because with multibyte character encodings the number of
927 characters and bytes can be quite different anyway. If you desire to
928 store long strings with no specific upper limit, use
929 <type>text</type> or <type>character varying</type> without a length
930 specifier, rather than making up an arbitrary length limit.)
935 There are no performance differences between these three types,
936 apart from the increased storage size when using the blank-padded
937 type. While <type>character(<replaceable>n</>)</type> has performance
938 advantages in some other database systems, it has no such advantages in
939 <productname>PostgreSQL</productname>. In most situations
940 <type>text</type> or <type>character varying</type> should be used
946 Refer to <xref linkend="sql-syntax-strings"> for information about
947 the syntax of string literals, and to <xref linkend="functions">
948 for information about available operators and functions.
952 <title>Using the character types</title>
955 CREATE TABLE test1 (a character(4));
956 INSERT INTO test1 VALUES ('ok');
957 SELECT a, char_length(a) FROM test1; -- <co id="co.datatype-char">
964 CREATE TABLE test2 (b varchar(5));
965 INSERT INTO test2 VALUES ('ok');
966 INSERT INTO test2 VALUES ('good ');
967 INSERT INTO test2 VALUES ('too long');
968 <computeroutput>ERROR: value too long for type character varying(5)</computeroutput>
969 INSERT INTO test2 VALUES ('too long'::varchar(5)); -- explicit truncation
970 SELECT b, char_length(b) FROM test2;
973 -------+-------------
980 <callout arearefs="co.datatype-char">
982 The <function>char_length</function> function is discussed in
983 <xref linkend="functions-string">.
990 There are two other fixed-length character types in
991 <productname>PostgreSQL</productname>, shown in <xref
992 linkend="datatype-character-special-table">. The <type>name</type>
993 type exists <emphasis>only</emphasis> for storage of identifiers
994 in the internal system catalogs and is not intended for use by the general user. Its
995 length is currently defined as 64 bytes (63 usable characters plus
996 terminator) but should be referenced using the constant
997 <symbol>NAMEDATALEN</symbol>. The length is set at compile time (and
998 is therefore adjustable for special uses); the default maximum
999 length may change in a future release. The type <type>"char"</type>
1000 (note the quotes) is different from <type>char(1)</type> in that it
1001 only uses one byte of storage. It is internally used in the system
1002 catalogs as a poor-man's enumeration type.
1005 <table id="datatype-character-special-table">
1006 <title>Special Character Types</title>
1011 <entry>Storage Size</entry>
1012 <entry>Description</entry>
1017 <entry><type>"char"</type></entry>
1018 <entry>1 byte</entry>
1019 <entry>single-character internal type</entry>
1022 <entry><type>name</type></entry>
1023 <entry>64 bytes</entry>
1024 <entry>internal type for object names</entry>
1032 <sect1 id="datatype-binary">
1033 <title>Binary Data Types</title>
1035 <indexterm zone="datatype-binary">
1036 <primary>binary data</primary>
1039 <indexterm zone="datatype-binary">
1040 <primary>bytea</primary>
1044 The <type>bytea</type> data type allows storage of binary strings;
1045 see <xref linkend="datatype-binary-table">.
1048 <table id="datatype-binary-table">
1049 <title>Binary Data Types</title>
1054 <entry>Storage Size</entry>
1055 <entry>Description</entry>
1060 <entry><type>bytea</type></entry>
1061 <entry>4 bytes plus the actual binary string</entry>
1062 <entry>variable-length binary string</entry>
1069 A binary string is a sequence of octets (or bytes). Binary
1070 strings are distinguished from characters strings by two
1071 characteristics: First, binary strings specifically allow storing
1072 octets of value zero and other <quote>non-printable</quote>
1073 octets (defined as octets outside the range 32 to 126).
1074 Second, operations on binary strings process the actual bytes,
1075 whereas the encoding and processing of character strings depends
1080 When entering <type>bytea</type> values, octets of certain values
1081 <emphasis>must</emphasis> be escaped (but all octet values
1082 <emphasis>may</emphasis> be escaped) when used as part of a string
1083 literal in an <acronym>SQL</acronym> statement. In general, to
1084 escape an octet, it is converted into the three-digit octal number
1085 equivalent of its decimal octet value, and preceded by two
1086 backslashes. <xref linkend="datatype-binary-sqlesc"> contains the
1087 characters which must be escaped, and gives the alternate escape
1088 sequences where applicable.
1091 <table id="datatype-binary-sqlesc">
1092 <title><type>bytea</> Literal Escaped Octets</title>
1096 <entry>Decimal Octet Value</entry>
1097 <entry>Description</entry>
1098 <entry>Escaped Input Representation</entry>
1099 <entry>Example</entry>
1100 <entry>Output Representation</entry>
1107 <entry>zero octet</entry>
1108 <entry><literal>'\\000'</literal></entry>
1109 <entry><literal>SELECT '\\000'::bytea;</literal></entry>
1110 <entry><literal>\000</literal></entry>
1115 <entry>single quote</entry>
1116 <entry><literal>'\''</literal> or <literal>'\\047'</literal></entry>
1117 <entry><literal>SELECT '\''::bytea;</literal></entry>
1118 <entry><literal>'</literal></entry>
1123 <entry>backslash</entry>
1124 <entry><literal>'\\\\'</literal> or <literal>'\\134'</literal></entry>
1125 <entry><literal>SELECT '\\\\'::bytea;</literal></entry>
1126 <entry><literal>\\</literal></entry>
1130 <entry>0 to 31 and 127 to 255</entry>
1131 <entry><quote>non-printable</quote> octets</entry>
1132 <entry><literal>'\\<replaceable>xxx'</></literal> (octal value)</entry>
1133 <entry><literal>SELECT '\\001'::bytea;</literal></entry>
1134 <entry><literal>\001</literal></entry>
1142 The requirement to escape <quote>non-printable</quote> octets actually
1143 varies depending on locale settings. In some instances you can get away
1144 with leaving them unescaped. Note that the result in each of the examples
1145 in <xref linkend="datatype-binary-sqlesc"> was exactly one octet in
1146 length, even though the output representation of the zero octet and
1147 backslash are more than one character.
1151 The reason that you have to write so many backslashes, as shown in
1152 <xref linkend="datatype-binary-sqlesc">, is that an input string
1153 written as a string literal must pass through two parse phases in
1154 the <productname>PostgreSQL</productname> server. The first
1155 backslash of each pair is interpreted as an escape character by
1156 the string-literal parser and is therefore consumed, leaving the
1157 second backslash of the pair. The remaining backslash is then
1158 recognized by the <type>bytea</type> input function as starting
1159 either a three digit octal value or escaping another backslash.
1160 For example, a string literal passed to the server as
1161 <literal>'\\001'</literal> becomes <literal>\001</literal> after
1162 passing through the string-literal parser. The
1163 <literal>\001</literal> is then sent to the <type>bytea</type>
1164 input function, where it is converted to a single octet with a
1165 decimal value of 1. Note that the apostrophe character is not
1166 treated specially by <type>bytea</type>, so it follows the normal
1167 rules for string literals. (See also <xref
1168 linkend="sql-syntax-strings">.)
1172 <type>Bytea</type> octets are also escaped in the output. In general, each
1173 <quote>non-printable</quote> octet is converted into
1174 its equivalent three-digit octal value and preceded by one backslash.
1175 Most <quote>printable</quote> octets are represented by their standard
1176 representation in the client character set. The octet with decimal
1177 value 92 (backslash) has a special alternative output representation.
1178 Details are in <xref linkend="datatype-binary-resesc">.
1181 <table id="datatype-binary-resesc">
1182 <title><type>bytea</> Output Escaped Octets</title>
1186 <entry>Decimal Octet Value</entry>
1187 <entry>Description</entry>
1188 <entry>Escaped Output Representation</entry>
1189 <entry>Example</entry>
1190 <entry>Output Result</entry>
1198 <entry>backslash</entry>
1199 <entry><literal>\\</literal></entry>
1200 <entry><literal>SELECT '\\134'::bytea;</literal></entry>
1201 <entry><literal>\\</literal></entry>
1205 <entry>0 to 31 and 127 to 255</entry>
1206 <entry><quote>non-printable</quote> octets</entry>
1207 <entry><literal>\<replaceable>xxx</></literal> (octal value)</entry>
1208 <entry><literal>SELECT '\\001'::bytea;</literal></entry>
1209 <entry><literal>\001</literal></entry>
1213 <entry>32 to 126</entry>
1214 <entry><quote>printable</quote> octets</entry>
1215 <entry>client character set representation</entry>
1216 <entry><literal>SELECT '\\176'::bytea;</literal></entry>
1217 <entry><literal>~</literal></entry>
1225 Depending on the front end to <productname>PostgreSQL</> you use,
1226 you may have additional work to do in terms of escaping and
1227 unescaping <type>bytea</type> strings. For example, you may also
1228 have to escape line feeds and carriage returns if your interface
1229 automatically translates these.
1233 The <acronym>SQL</acronym> standard defines a different binary
1234 string type, called <type>BLOB</type> or <type>BINARY LARGE
1235 OBJECT</type>. The input format is different compared to
1236 <type>bytea</type>, but the provided functions and operators are
1242 <sect1 id="datatype-datetime">
1243 <title>Date/Time Types</title>
1245 <indexterm zone="datatype-datetime">
1246 <primary>date</primary>
1248 <indexterm zone="datatype-datetime">
1249 <primary>time</primary>
1251 <indexterm zone="datatype-datetime">
1252 <primary>time without time zone</primary>
1254 <indexterm zone="datatype-datetime">
1255 <primary>time with time zone</primary>
1257 <indexterm zone="datatype-datetime">
1258 <primary>timestamp</primary>
1260 <indexterm zone="datatype-datetime">
1261 <primary>timestamp with time zone</primary>
1263 <indexterm zone="datatype-datetime">
1264 <primary>timestamp without time zone</primary>
1266 <indexterm zone="datatype-datetime">
1267 <primary>interval</primary>
1269 <indexterm zone="datatype-datetime">
1270 <primary>time span</primary>
1274 <productname>PostgreSQL</productname> supports the full set of
1275 <acronym>SQL</acronym> date and time types, shown in <xref
1276 linkend="datatype-datetime-table">.
1279 <table id="datatype-datetime-table">
1280 <title>Date/Time Types</title>
1285 <entry>Storage Size</entry>
1286 <entry>Description</entry>
1287 <entry>Low Value</entry>
1288 <entry>High Value</entry>
1289 <entry>Resolution</entry>
1294 <entry><type>timestamp [ (<replaceable>p</replaceable>) ] [ without time zone ]</type></entry>
1295 <entry>8 bytes</entry>
1296 <entry>both date and time</entry>
1297 <entry>4713 BC</entry>
1298 <entry>5874897 AD</entry>
1299 <entry>1 microsecond / 14 digits</entry>
1302 <entry><type>timestamp [ (<replaceable>p</replaceable>) ] with time zone</type></entry>
1303 <entry>8 bytes</entry>
1304 <entry>both date and time, with time zone</entry>
1305 <entry>4713 BC</entry>
1306 <entry>5874897 AD</entry>
1307 <entry>1 microsecond / 14 digits</entry>
1310 <entry><type>interval [ (<replaceable>p</replaceable>) ]</type></entry>
1311 <entry>12 bytes</entry>
1312 <entry>time intervals</entry>
1313 <entry>-178000000 years</entry>
1314 <entry>178000000 years</entry>
1315 <entry>1 microsecond</entry>
1318 <entry><type>date</type></entry>
1319 <entry>4 bytes</entry>
1320 <entry>dates only</entry>
1321 <entry>4713 BC</entry>
1322 <entry>32767 AD</entry>
1323 <entry>1 day</entry>
1326 <entry><type>time [ (<replaceable>p</replaceable>) ] [ without time zone ]</type></entry>
1327 <entry>8 bytes</entry>
1328 <entry>times of day only</entry>
1329 <entry>00:00:00.00</entry>
1330 <entry>23:59:59.99</entry>
1331 <entry>1 microsecond</entry>
1334 <entry><type>time [ (<replaceable>p</replaceable>) ] with time zone</type></entry>
1335 <entry>12 bytes</entry>
1336 <entry>times of day only, with time zone</entry>
1337 <entry>00:00:00.00+12</entry>
1338 <entry>23:59:59.99-12</entry>
1339 <entry>1 microsecond</entry>
1347 Prior to <productname>PostgreSQL</productname> 7.3, writing just
1348 <type>timestamp</type> was equivalent to <type>timestamp with
1349 time zone</type>. This was changed for SQL compliance.
1354 <type>time</type>, <type>timestamp</type>, and
1355 <type>interval</type> accept an optional precision value
1356 <replaceable>p</replaceable> which specifies the number of
1357 fractional digits retained in the seconds field. By default, there
1358 is no explicit bound on precision. The allowed range of
1359 <replaceable>p</replaceable> is from 0 to 6 for the
1360 <type>timestamp</type> and <type>interval</type> types.
1365 When <type>timestamp</> values are stored as double precision floating-point
1366 numbers (currently the default), the effective limit of precision
1367 may be less than 6. <type>timestamp</type> values are stored as seconds
1368 before or after midnight 2000-01-01. Microsecond precision is achieved for
1369 dates within a few years of 2000-01-01, but the precision degrades for
1370 dates further away. When <type>timestamp</type> values are stored as
1371 eight-byte integers (a compile-time
1372 option), microsecond precision is available over the full range of
1373 values. However eight-byte integer timestamps have a more limited range of
1374 dates than shown above: from 4713 BC up to 294276 AD.
1379 For the <type>time</type> types, the allowed range of
1380 <replaceable>p</replaceable> is from 0 to 6 when eight-byte integer
1381 storage is used, or from 0 to 10 when floating-point storage is used.
1385 The type <type>time with time zone</type> is defined by the SQL
1386 standard, but the definition exhibits properties which lead to
1387 questionable usefulness. In most cases, a combination of
1388 <type>date</type>, <type>time</type>, <type>timestamp without time
1389 zone</type>, and <type>timestamp with time zone</type> should
1390 provide a complete range of date/time functionality required by
1395 The types <type>abstime</type>
1396 and <type>reltime</type> are lower precision types which are used internally.
1397 You are discouraged from using these types in new
1398 applications and are encouraged to move any old
1399 ones over when appropriate. Any or all of these internal types
1400 might disappear in a future release.
1403 <sect2 id="datatype-datetime-input">
1404 <title>Date/Time Input</title>
1407 Date and time input is accepted in almost any reasonable format, including
1408 ISO 8601, <acronym>SQL</acronym>-compatible,
1409 traditional <productname>POSTGRES</productname>, and others.
1410 For some formats, ordering of month, day, and year in date input is
1411 ambiguous and there is support for specifying the expected
1412 ordering of these fields. Set the <varname>datestyle</> parameter
1413 to <literal>MDY</> to select month-day-year interpretation,
1414 <literal>DMY</> to select day-month-year interpretation, or
1415 <literal>YMD</> to select year-month-day interpretation.
1419 <productname>PostgreSQL</productname> is more flexible in
1420 handling date/time input than the
1421 <acronym>SQL</acronym> standard requires.
1422 See <xref linkend="datetime-appendix">
1423 for the exact parsing rules of date/time input and for the
1424 recognized text fields including months, days of the week, and
1429 Remember that any date or time literal input needs to be enclosed
1430 in single quotes, like text strings. Refer to
1431 <xref linkend="sql-syntax-constants-generic"> for more
1433 <acronym>SQL</acronym> requires the following syntax
1435 <replaceable>type</replaceable> [ (<replaceable>p</replaceable>) ] '<replaceable>value</replaceable>'
1437 where <replaceable>p</replaceable> in the optional precision
1438 specification is an integer corresponding to the number of
1439 fractional digits in the seconds field. Precision can be
1440 specified for <type>time</type>, <type>timestamp</type>, and
1441 <type>interval</type> types. The allowed values are mentioned
1442 above. If no precision is specified in a constant specification,
1443 it defaults to the precision of the literal value.
1447 <title>Dates</title>
1450 <primary>date</primary>
1454 <xref linkend="datatype-datetime-date-table"> shows some possible
1455 inputs for the <type>date</type> type.
1458 <table id="datatype-datetime-date-table">
1459 <title>Date Input</title>
1463 <entry>Example</entry>
1464 <entry>Description</entry>
1469 <entry>January 8, 1999</entry>
1470 <entry>unambiguous in any <varname>datestyle</varname> input mode</entry>
1473 <entry>1999-01-08</entry>
1474 <entry>ISO 8601; January 8 in any mode
1475 (recommended format)</entry>
1478 <entry>1/8/1999</entry>
1479 <entry>January 8 in <literal>MDY</> mode;
1480 August 1 in <literal>DMY</> mode</entry>
1483 <entry>1/18/1999</entry>
1484 <entry>January 18 in <literal>MDY</> mode;
1485 rejected in other modes</entry>
1488 <entry>01/02/03</entry>
1489 <entry>January 2, 2003 in <literal>MDY</> mode;
1490 February 1, 2003 in <literal>DMY</> mode;
1491 February 3, 2001 in <literal>YMD</> mode
1495 <entry>1999-Jan-08</entry>
1496 <entry>January 8 in any mode</entry>
1499 <entry>Jan-08-1999</entry>
1500 <entry>January 8 in any mode</entry>
1503 <entry>08-Jan-1999</entry>
1504 <entry>January 8 in any mode</entry>
1507 <entry>99-Jan-08</entry>
1508 <entry>January 8 in <literal>YMD</> mode, else error</entry>
1511 <entry>08-Jan-99</entry>
1512 <entry>January 8, except error in <literal>YMD</> mode</entry>
1515 <entry>Jan-08-99</entry>
1516 <entry>January 8, except error in <literal>YMD</> mode</entry>
1519 <entry>19990108</entry>
1520 <entry>ISO 8601; January 8, 1999 in any mode</entry>
1523 <entry>990108</entry>
1524 <entry>ISO 8601; January 8, 1999 in any mode</entry>
1527 <entry>1999.008</entry>
1528 <entry>year and day of year</entry>
1531 <entry>J2451187</entry>
1532 <entry>Julian day</entry>
1535 <entry>January 8, 99 BC</entry>
1536 <entry>year 99 before the Common Era</entry>
1544 <title>Times</title>
1547 <primary>time</primary>
1550 <primary>time without time zone</primary>
1553 <primary>time with time zone</primary>
1557 The time-of-day types are <type>time [
1558 (<replaceable>p</replaceable>) ] without time zone</type> and
1559 <type>time [ (<replaceable>p</replaceable>) ] with time
1560 zone</type>. Writing just <type>time</type> is equivalent to
1561 <type>time without time zone</type>.
1565 Valid input for these types consists of a time of day followed
1566 by an optional time zone. (See <xref
1567 linkend="datatype-datetime-time-table">
1568 and <xref linkend="datatype-timezone-table">.) If a time zone is
1569 specified in the input for <type>time without time zone</type>,
1570 it is silently ignored.
1573 <table id="datatype-datetime-time-table">
1574 <title>Time Input</title>
1578 <entry>Example</entry>
1579 <entry>Description</entry>
1584 <entry><literal>04:05:06.789</literal></entry>
1585 <entry>ISO 8601</entry>
1588 <entry><literal>04:05:06</literal></entry>
1589 <entry>ISO 8601</entry>
1592 <entry><literal>04:05</literal></entry>
1593 <entry>ISO 8601</entry>
1596 <entry><literal>040506</literal></entry>
1597 <entry>ISO 8601</entry>
1600 <entry><literal>04:05 AM</literal></entry>
1601 <entry>same as 04:05; AM does not affect value</entry>
1604 <entry><literal>04:05 PM</literal></entry>
1605 <entry>same as 16:05; input hour must be <= 12</entry>
1608 <entry><literal>04:05:06.789-8</literal></entry>
1609 <entry>ISO 8601</entry>
1612 <entry><literal>04:05:06-08:00</literal></entry>
1613 <entry>ISO 8601</entry>
1616 <entry><literal>04:05-08:00</literal></entry>
1617 <entry>ISO 8601</entry>
1620 <entry><literal>040506-08</literal></entry>
1621 <entry>ISO 8601</entry>
1624 <entry><literal>04:05:06 PST</literal></entry>
1625 <entry>time zone specified by name</entry>
1631 <table tocentry="1" id="datatype-timezone-table">
1632 <title>Time Zone Input</title>
1636 <entry>Example</entry>
1637 <entry>Description</entry>
1642 <entry><literal>PST</literal></entry>
1643 <entry>Pacific Standard Time</entry>
1646 <entry><literal>-8:00</literal></entry>
1647 <entry>ISO-8601 offset for PST</entry>
1650 <entry><literal>-800</literal></entry>
1651 <entry>ISO-8601 offset for PST</entry>
1654 <entry><literal>-8</literal></entry>
1655 <entry>ISO-8601 offset for PST</entry>
1658 <entry><literal>zulu</literal></entry>
1659 <entry>Military abbreviation for UTC</entry>
1662 <entry><literal>z</literal></entry>
1663 <entry>Short form of <literal>zulu</literal></entry>
1671 <title>Time Stamps</title>
1674 <primary>timestamp</primary>
1678 <primary>timestamp with time zone</primary>
1682 <primary>timestamp without time zone</primary>
1686 Valid input for the time stamp types consists of a concatenation
1687 of a date and a time, followed by an optional
1688 <literal>AD</literal> or <literal>BC</literal>, followed by an
1689 optional time zone. Thus
1696 1999-01-08 04:05:06 -8:00
1699 are valid values, which follow the <acronym>ISO</acronym> 8601
1700 standard. In addition, the wide-spread format
1703 January 8 04:05:06 1999 PST
1709 For <type>timestamp [without time zone]</type>, any explicit time
1710 zone specified in the input is silently ignored. That is, the
1711 resulting date/time value is derived from the explicit date/time
1712 fields in the input value, and is not adjusted for time zone.
1716 For <type>timestamp with time zone</type>, the internally stored
1717 value is always in UTC (Universal
1718 Coordinated Time, traditionally known as Greenwich Mean Time,
1719 <acronym>GMT</>). An input value that has an explicit
1720 time zone specified is converted to UTC using the appropriate offset
1721 for that time zone. If no time zone is stated in the input string,
1722 then it is assumed to be in the time zone indicated by the system's
1723 <varname>timezone</> parameter, and is converted to UTC using the
1724 offset for the <varname>timezone</> zone.
1728 When a <type>timestamp with time
1729 zone</type> value is output, it is always converted from UTC to the
1730 current <varname>timezone</> zone, and displayed as local time in that
1731 zone. To see the time in another time zone, either change
1732 <varname>timezone</> or use the <literal>AT TIME ZONE</> construct
1733 (see <xref linkend="functions-datetime-zoneconvert">).
1737 Conversions between <type>timestamp without time zone</type> and
1738 <type>timestamp with time zone</type> normally assume that the
1739 <type>timestamp without time zone</type> value should be taken or given
1740 as <varname>timezone</> local time. A different zone reference can
1741 be specified for the conversion using <literal>AT TIME ZONE</>.
1746 <title>Intervals</title>
1749 <primary>interval</primary>
1753 <type>interval</type> values can be written with the following syntax:
1756 <optional>@</> <replaceable>quantity</> <replaceable>unit</> <optional><replaceable>quantity</> <replaceable>unit</>...</> <optional><replaceable>direction</></optional>
1759 Where: <replaceable>quantity</> is a number (possibly signed);
1760 <replaceable>unit</> is <literal>second</literal>,
1761 <literal>minute</literal>, <literal>hour</literal>, <literal>day</literal>,
1762 <literal>week</literal>, <literal>month</literal>, <literal>year</literal>,
1763 <literal>decade</literal>, <literal>century</literal>, <literal>millennium</literal>,
1764 or abbreviations or plurals of these units;
1765 <replaceable>direction</> can be <literal>ago</literal> or
1766 empty. The at sign (<literal>@</>) is optional noise. The amounts
1767 of different units are implicitly added up with appropriate
1772 Quantities of days, hours, minutes, and seconds can be specified without
1773 explicit unit markings. For example, <literal>'1 12:59:10'</> is read
1774 the same as <literal>'1 day 12 hours 59 min 10 sec'</>.
1778 The optional precision
1779 <replaceable>p</replaceable> should be between 0 and 6, and
1780 defaults to the precision of the input literal.
1785 <title>Special Values</title>
1788 <primary>time</primary>
1789 <secondary>constants</secondary>
1793 <primary>date</primary>
1794 <secondary>constants</secondary>
1798 The following <acronym>SQL</acronym>-compatible functions can be
1799 used as date or time values for the corresponding data type:
1800 <literal>CURRENT_DATE</literal>, <literal>CURRENT_TIME</literal>,
1801 <literal>CURRENT_TIMESTAMP</literal>, <literal>LOCALTIME</literal>,
1802 <literal>LOCALTIMESTAMP</literal>. The latter four accept an
1803 optional precision specification. (See also <xref
1804 linkend="functions-datetime-current">.)
1808 <productname>PostgreSQL</productname> also supports several
1809 special date/time input values for convenience, as shown in <xref
1810 linkend="datatype-datetime-special-table">. The values
1811 <literal>infinity</literal> and <literal>-infinity</literal>
1812 are specially represented inside the system and will be displayed
1813 the same way; but the others are simply notational shorthands
1814 that will be converted to ordinary date/time values when read.
1815 All of these values are treated as normal constants and need to be
1816 written in single quotes.
1819 <table id="datatype-datetime-special-table">
1820 <title>Special Date/Time Inputs</title>
1824 <entry>Input String</entry>
1825 <entry>Valid Types</entry>
1826 <entry>Description</entry>
1831 <entry><literal>epoch</literal></entry>
1832 <entry><type>date</type>, <type>timestamp</type></entry>
1833 <entry>1970-01-01 00:00:00+00 (Unix system time zero)</entry>
1836 <entry><literal>infinity</literal></entry>
1837 <entry><type>timestamp</type></entry>
1838 <entry>later than all other time stamps</entry>
1841 <entry><literal>-infinity</literal></entry>
1842 <entry><type>timestamp</type></entry>
1843 <entry>earlier than all other time stamps</entry>
1846 <entry><literal>now</literal></entry>
1847 <entry><type>date</type>, <type>time</type>, <type>timestamp</type></entry>
1848 <entry>current transaction's start time</entry>
1851 <entry><literal>today</literal></entry>
1852 <entry><type>date</type>, <type>timestamp</type></entry>
1853 <entry>midnight today</entry>
1856 <entry><literal>tomorrow</literal></entry>
1857 <entry><type>date</type>, <type>timestamp</type></entry>
1858 <entry>midnight tomorrow</entry>
1861 <entry><literal>yesterday</literal></entry>
1862 <entry><type>date</type>, <type>timestamp</type></entry>
1863 <entry>midnight yesterday</entry>
1866 <entry><literal>allballs</literal></entry>
1867 <entry><type>time</type></entry>
1868 <entry>00:00:00.00 UTC</entry>
1877 <sect2 id="datatype-datetime-output">
1878 <title>Date/Time Output</title>
1881 <primary>date</primary>
1882 <secondary>output format</secondary>
1883 <seealso>formatting</seealso>
1887 <primary>time</primary>
1888 <secondary>output format</secondary>
1889 <seealso>formatting</seealso>
1893 The output format of the date/time types can be set to one of the four
1895 <acronym>SQL</acronym> (Ingres), traditional POSTGRES, and
1896 German, using the command <literal>SET datestyle</literal>. The default
1897 is the <acronym>ISO</acronym> format. (The
1898 <acronym>SQL</acronym> standard requires the use of the ISO 8601
1899 format. The name of the <quote>SQL</quote> output format is a
1900 historical accident.) <xref
1901 linkend="datatype-datetime-output-table"> shows examples of each
1902 output style. The output of the <type>date</type> and
1903 <type>time</type> types is of course only the date or time part
1904 in accordance with the given examples.
1907 <table id="datatype-datetime-output-table">
1908 <title>Date/Time Output Styles</title>
1912 <entry>Style Specification</entry>
1913 <entry>Description</entry>
1914 <entry>Example</entry>
1920 <entry>ISO 8601/SQL standard</entry>
1921 <entry>1997-12-17 07:37:16-08</entry>
1925 <entry>traditional style</entry>
1926 <entry>12/17/1997 07:37:16.00 PST</entry>
1929 <entry>POSTGRES</entry>
1930 <entry>original style</entry>
1931 <entry>Wed Dec 17 07:37:16 1997 PST</entry>
1934 <entry>German</entry>
1935 <entry>regional style</entry>
1936 <entry>17.12.1997 07:37:16.00 PST</entry>
1943 In the <acronym>SQL</acronym> and POSTGRES styles, day appears before
1944 month if DMY field ordering has been specified, otherwise month appears
1946 (See <xref linkend="datatype-datetime-input">
1947 for how this setting also affects interpretation of input values.)
1948 <xref linkend="datatype-datetime-output2-table"> shows an
1952 <table id="datatype-datetime-output2-table">
1953 <title>Date Order Conventions</title>
1957 <entry><varname>datestyle</varname> Setting</entry>
1958 <entry>Input Ordering</entry>
1959 <entry>Example Output</entry>
1964 <entry><literal>SQL, DMY</></entry>
1965 <entry><replaceable>day</replaceable>/<replaceable>month</replaceable>/<replaceable>year</replaceable></entry>
1966 <entry>17/12/1997 15:37:16.00 CET</entry>
1969 <entry><literal>SQL, MDY</></entry>
1970 <entry><replaceable>month</replaceable>/<replaceable>day</replaceable>/<replaceable>year</replaceable></entry>
1971 <entry>12/17/1997 07:37:16.00 PST</entry>
1974 <entry><literal>Postgres, DMY</></entry>
1975 <entry><replaceable>day</replaceable>/<replaceable>month</replaceable>/<replaceable>year</replaceable></entry>
1976 <entry>Wed 17 Dec 07:37:16 1997 PST</entry>
1983 <type>interval</type> output looks like the input format, except
1984 that units like <literal>century</literal> or
1985 <literal>wek</literal> are converted to years and days and that
1986 <literal>ago</literal> is converted to an appropriate sign. In
1987 ISO mode the output looks like
1990 <optional> <replaceable>quantity</> <replaceable>unit</> <optional> ... </> </> <optional> <replaceable>days</> </> <optional> <replaceable>hours</>:<replaceable>minutes</>:<replaceable>sekunden</> </optional>
1995 The date/time styles can be selected by the user using the
1996 <command>SET datestyle</command> command, the
1997 <varname>datestyle</varname> parameter in the
1998 <filename>postgresql.conf</filename> configuration file, or the
1999 <envar>PGDATESTYLE</envar> environment variable on the server or
2000 client. The formatting function <function>to_char</function>
2001 (see <xref linkend="functions-formatting">) is also available as
2002 a more flexible way to format the date/time output.
2006 <sect2 id="datatype-timezones">
2007 <title>Time Zones</title>
2009 <indexterm zone="datatype-timezones">
2010 <primary>time zone</primary>
2014 Time zones, and time-zone conventions, are influenced by
2015 political decisions, not just earth geometry. Time zones around the
2016 world became somewhat standardized during the 1900's,
2017 but continue to be prone to arbitrary changes.
2018 <productname>PostgreSQL</productname> uses your operating
2019 system's underlying features to provide output time-zone
2020 support, and these systems usually contain information for only
2021 the time period 1902 through 2038 (corresponding to the full
2022 range of conventional Unix system time).
2023 <type>timestamp with time zone</type> and <type>time with time
2024 zone</type> will use time zone
2025 information only within that year range, and assume that times
2026 outside that range are in <acronym>UTC</acronym>.
2027 But since time zone support is derived from the underlying operating
2028 system time-zone capabilities, it can handle daylight-saving time
2029 and other special behavior.
2033 <productname>PostgreSQL</productname> endeavors to be compatible with
2034 the <acronym>SQL</acronym> standard definitions for typical usage.
2035 However, the <acronym>SQL</acronym> standard has an odd mix of date and
2036 time types and capabilities. Two obvious problems are:
2041 Although the <type>date</type> type
2042 does not have an associated time zone, the
2043 <type>time</type> type can.
2044 Time zones in the real world can have no meaning unless
2045 associated with a date as well as a time
2046 since the offset may vary through the year with daylight-saving
2053 The default time zone is specified as a constant numeric offset
2054 from <acronym>UTC</>. It is not possible to adapt to daylight-saving
2055 time when doing date/time arithmetic across
2056 <acronym>DST</acronym> boundaries.
2064 To address these difficulties, we recommend using date/time types
2065 that contain both date and time when using time zones. We
2066 recommend <emphasis>not</emphasis> using the type <type>time with
2067 time zone</type> (though it is supported by
2068 <productname>PostgreSQL</productname> for legacy applications and
2069 for compatibility with other <acronym>SQL</acronym>
2070 implementations). <productname>PostgreSQL</productname> assumes
2071 your local time zone for any type containing only date or time.
2075 All dates and times are stored internally in
2076 <acronym>UTC</acronym>. Times are converted to local time
2077 on the database server before being sent to the client,
2078 hence by default are in the server time zone.
2082 There are several ways to select the time zone used by the server:
2087 The <envar>TZ</envar> environment variable on the server host
2088 is used by the server as the default time zone, if no other is
2095 The <varname>timezone</varname> configuration parameter can be
2096 set in the file <filename>postgresql.conf</>.
2102 The <envar>PGTZ</envar> environment variable, if set at the
2103 client, is used by <application>libpq</application>
2104 applications to send a <command>SET TIME ZONE</command>
2105 command to the server upon connection.
2111 The <acronym>SQL</acronym> command <command>SET TIME ZONE</command>
2112 sets the time zone for the session.
2120 If an invalid time zone is specified, the time zone becomes
2121 <acronym>UTC</acronym> (on most systems anyway).
2126 Refer to <xref linkend="datetime-appendix"> for a list of
2127 available time zones.
2132 <sect2 id="datatype-datetime-internals">
2133 <title>Internals</title>
2136 <productname>PostgreSQL</productname> uses Julian dates
2137 for all date/time calculations. They have the nice property of correctly
2138 predicting/calculating any date more recent than 4713 BC
2139 to far into the future, using the assumption that the length of the
2140 year is 365.2425 days.
2144 Date conventions before the 19th century make for interesting reading,
2145 but are not consistent enough to warrant coding into a date/time handler.
2151 <sect1 id="datatype-boolean">
2152 <title>Boolean Type</title>
2154 <indexterm zone="datatype-boolean">
2155 <primary>Boolean</primary>
2156 <secondary>data type</secondary>
2159 <indexterm zone="datatype-boolean">
2160 <primary>true</primary>
2163 <indexterm zone="datatype-boolean">
2164 <primary>false</primary>
2168 <productname>PostgreSQL</productname> provides the
2169 standard <acronym>SQL</acronym> type <type>boolean</type>.
2170 <type>boolean</type> can have one of only two states:
2171 <quote>true</quote> or <quote>false</quote>. A third state,
2172 <quote>unknown</quote>, is represented by the
2173 <acronym>SQL</acronym> null value.
2177 Valid literal values for the <quote>true</quote> state are:
2179 <member><literal>TRUE</literal></member>
2180 <member><literal>'t'</literal></member>
2181 <member><literal>'true'</literal></member>
2182 <member><literal>'y'</literal></member>
2183 <member><literal>'yes'</literal></member>
2184 <member><literal>'1'</literal></member>
2186 For the <quote>false</quote> state, the following values can be
2189 <member><literal>FALSE</literal></member>
2190 <member><literal>'f'</literal></member>
2191 <member><literal>'false'</literal></member>
2192 <member><literal>'n'</literal></member>
2193 <member><literal>'no'</literal></member>
2194 <member><literal>'0'</literal></member>
2196 Using the key words <literal>TRUE</literal> and
2197 <literal>FALSE</literal> is preferred (and
2198 <acronym>SQL</acronym>-compliant).
2201 <example id="datatype-boolean-example">
2202 <title>Using the <type>boolean</type> type</title>
2205 CREATE TABLE test1 (a boolean, b text);
2206 INSERT INTO test1 VALUES (TRUE, 'sic est');
2207 INSERT INTO test1 VALUES (FALSE, 'non est');
2208 SELECT * FROM test1;
2214 SELECT * FROM test1 WHERE a;
2222 <xref linkend="datatype-boolean-example"> shows that
2223 <type>boolean</type> values are output using the letters
2224 <literal>t</literal> and <literal>f</literal>.
2229 Values of the <type>boolean</type> type cannot be cast directly
2230 to other types (e.g., <literal>CAST
2231 (<replaceable>boolval</replaceable> AS integer)</literal> does
2232 not work). This can be accomplished using the
2233 <literal>CASE</literal> expression: <literal>CASE WHEN
2234 <replaceable>boolval</replaceable> THEN 'value if true' ELSE
2235 'value if false' END</literal>. See also <xref
2236 linkend="functions-conditional">.
2241 <type>boolean</type> uses 1 byte of storage.
2245 <sect1 id="datatype-geometric">
2246 <title>Geometric Types</title>
2249 Geometric data types represent two-dimensional spatial
2250 objects. <xref linkend="datatype-geo-table"> shows the geometric
2251 types available in <productname>PostgreSQL</productname>. The
2252 most fundamental type, the point, forms the basis for all of the
2256 <table id="datatype-geo-table">
2257 <title>Geometric Types</title>
2262 <entry>Storage Size</entry>
2263 <entry>Representation</entry>
2264 <entry>Description</entry>
2269 <entry><type>point</type></entry>
2270 <entry>16 bytes</entry>
2271 <entry>Point on the plane</entry>
2272 <entry>(x,y)</entry>
2275 <entry><type>line</type></entry>
2276 <entry>32 bytes</entry>
2277 <entry>Infinite line (not fully implemented)</entry>
2278 <entry>((x1,y1),(x2,y2))</entry>
2281 <entry><type>lseg</type></entry>
2282 <entry>32 bytes</entry>
2283 <entry>Finite line segment</entry>
2284 <entry>((x1,y1),(x2,y2))</entry>
2287 <entry><type>box</type></entry>
2288 <entry>32 bytes</entry>
2289 <entry>Rectangular box</entry>
2290 <entry>((x1,y1),(x2,y2))</entry>
2293 <entry><type>path</type></entry>
2294 <entry>16+16n bytes</entry>
2295 <entry>Closed path (similar to polygon)</entry>
2296 <entry>((x1,y1),...)</entry>
2299 <entry><type>path</type></entry>
2300 <entry>16+16n bytes</entry>
2301 <entry>Open path</entry>
2302 <entry>[(x1,y1),...]</entry>
2305 <entry><type>polygon</type></entry>
2306 <entry>40+16n bytes</entry>
2307 <entry>Polygon (similar to closed path)</entry>
2308 <entry>((x1,y1),...)</entry>
2311 <entry><type>circle</type></entry>
2312 <entry>24 bytes</entry>
2313 <entry>Circle</entry>
2314 <entry><(x,y),r> (center and radius)</entry>
2321 A rich set of functions and operators is available to perform various geometric
2322 operations such as scaling, translation, rotation, and determining
2323 intersections. They are explained in <xref linkend="functions-geometry">.
2327 <title>Points</title>
2330 <primary>point</primary>
2334 Points are the fundamental two-dimensional building block for geometric types.
2335 Values of type <type>point</type> are specified using the following syntax:
2338 ( <replaceable>x</replaceable> , <replaceable>y</replaceable> )
2339 <replaceable>x</replaceable> , <replaceable>y</replaceable>
2342 where <replaceable>x</> and <replaceable>y</> are the respective
2343 coordinates as floating-point numbers.
2348 <title>Line Segments</title>
2351 <primary>lseg</primary>
2355 <primary>line segment</primary>
2359 Line segments (<type>lseg</type>) are represented by pairs of points.
2360 Values of type <type>lseg</type> are specified using the following syntax:
2363 ( ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ( <replaceable>x2</replaceable> , <replaceable>y2</replaceable> ) )
2364 ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ( <replaceable>x2</replaceable> , <replaceable>y2</replaceable> )
2365 <replaceable>x1</replaceable> , <replaceable>y1</replaceable> , <replaceable>x2</replaceable> , <replaceable>y2</replaceable>
2369 <literal>(<replaceable>x1</replaceable>,<replaceable>y1</replaceable>)</literal>
2371 <literal>(<replaceable>x2</replaceable>,<replaceable>y2</replaceable>)</literal>
2372 are the end points of the line segment.
2377 <title>Boxes</title>
2380 <primary>box (data type)</primary>
2384 <primary>rectangle</primary>
2388 Boxes are represented by pairs of points that are opposite
2390 Values of type <type>box</type> is specified using the following syntax:
2393 ( ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ( <replaceable>x2</replaceable> , <replaceable>y2</replaceable> ) )
2394 ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ( <replaceable>x2</replaceable> , <replaceable>y2</replaceable> )
2395 <replaceable>x1</replaceable> , <replaceable>y1</replaceable> , <replaceable>x2</replaceable> , <replaceable>y2</replaceable>
2399 <literal>(<replaceable>x1</replaceable>,<replaceable>y1</replaceable>)</literal>
2401 <literal>(<replaceable>x2</replaceable>,<replaceable>y2</replaceable>)</literal>
2402 are the opposite corners of the box.
2406 Boxes are output using the first syntax.
2407 The corners are reordered on input to store
2408 the upper right corner, then the lower left corner.
2409 Other corners of the box can be entered, but the lower
2410 left and upper right corners are determined from the input and stored corners.
2415 <title>Paths</title>
2418 <primary>path (data type)</primary>
2422 Paths are represented by connected sets of points. Paths can be
2423 <firstterm>open</firstterm>, where
2424 the first and last points in the set are not connected, and <firstterm>closed</firstterm>,
2425 where the first and last point are connected. The functions
2426 <function>popen(<replaceable>p</>)</function>
2428 <function>pclose(<replaceable>p</>)</function>
2429 are supplied to force a path to be open or closed, and the functions
2430 <function>isopen(<replaceable>p</>)</function>
2432 <function>isclosed(<replaceable>p</>)</function>
2433 are supplied to test for either type in an expression.
2437 Values of type <type>path</type> are specified using the following syntax:
2440 ( ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ... , ( <replaceable>xn</replaceable> , <replaceable>yn</replaceable> ) )
2441 [ ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ... , ( <replaceable>xn</replaceable> , <replaceable>yn</replaceable> ) ]
2442 ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ... , ( <replaceable>xn</replaceable> , <replaceable>yn</replaceable> )
2443 ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> , ... , <replaceable>xn</replaceable> , <replaceable>yn</replaceable> )
2444 <replaceable>x1</replaceable> , <replaceable>y1</replaceable> , ... , <replaceable>xn</replaceable> , <replaceable>yn</replaceable>
2447 where the points are the end points of the line segments
2448 comprising the path. Square brackets (<literal>[]</>) indicate
2449 an open path, while parentheses (<literal>()</>) indicate a
2454 Paths are output using the first syntax.
2459 <title>Polygons</title>
2462 <primary>polygon</primary>
2466 Polygons are represented by sets of points. Polygons should probably be
2467 considered equivalent to closed paths, but are stored differently
2468 and have their own set of support routines.
2472 Values of type <type>polygon</type> are specified using the following syntax:
2475 ( ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ... , ( <replaceable>xn</replaceable> , <replaceable>yn</replaceable> ) )
2476 ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ... , ( <replaceable>xn</replaceable> , <replaceable>yn</replaceable> )
2477 ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> , ... , <replaceable>xn</replaceable> , <replaceable>yn</replaceable> )
2478 <replaceable>x1</replaceable> , <replaceable>y1</replaceable> , ... , <replaceable>xn</replaceable> , <replaceable>yn</replaceable>
2481 where the points are the end points of the line segments
2482 comprising the boundary of the polygon.
2486 Polygons are output using the first syntax.
2491 <title>Circles</title>
2494 <primary>circle</primary>
2498 Circles are represented by a center point and a radius.
2499 Values of type <type>circle</type> are specified using the following syntax:
2502 < ( <replaceable>x</replaceable> , <replaceable>y</replaceable> ) , <replaceable>r</replaceable> >
2503 ( ( <replaceable>x</replaceable> , <replaceable>y</replaceable> ) , <replaceable>r</replaceable> )
2504 ( <replaceable>x</replaceable> , <replaceable>y</replaceable> ) , <replaceable>r</replaceable>
2505 <replaceable>x</replaceable> , <replaceable>y</replaceable> , <replaceable>r</replaceable>
2509 <literal>(<replaceable>x</replaceable>,<replaceable>y</replaceable>)</literal>
2510 is the center and <replaceable>r</replaceable> is the radius of the circle.
2514 Circles are output using the first syntax.
2520 <sect1 id="datatype-net-types">
2521 <title>Network Address Types</title>
2523 <indexterm zone="datatype-net-types">
2524 <primary>network</primary>
2525 <secondary>data types</secondary>
2529 <productname>PostgreSQL</> offers data types to store IPv4, IPv6, and MAC
2530 addresses, shown in <xref linkend="datatype-net-types-table">. It
2531 is preferable to use these types over plain text types, because
2532 these types offer input error checking and several specialized
2533 operators and functions.
2536 <table tocentry="1" id="datatype-net-types-table">
2537 <title>Network Address Types</title>
2542 <entry>Storage Size</entry>
2543 <entry>Description</entry>
2549 <entry><type>cidr</type></entry>
2550 <entry>12 or 24 bytes</entry>
2551 <entry>IPv4 or IPv6 networks</entry>
2555 <entry><type>inet</type></entry>
2556 <entry>12 or 24 bytes</entry>
2557 <entry>IPv4 and IPv6 hosts and networks</entry>
2561 <entry><type>macaddr</type></entry>
2562 <entry>6 bytes</entry>
2563 <entry>MAC addresses</entry>
2571 When sorting <type>inet</type> or <type>cidr</type> data types,
2572 IPv4 addresses will always sort before IPv6 addresses, including
2573 IPv4 addresses encapsulated or mapped into IPv6 addresses, such as
2574 ::10.2.3.4 or ::ffff::10.4.3.2.
2578 <sect2 id="datatype-inet">
2579 <title><type>inet</type></title>
2582 <primary>inet (data type)</primary>
2586 The <type>inet</type> type holds an IPv4 or IPv6 host address, and
2587 optionally the identity of the subnet it is in, all in one field.
2588 The subnet identity is represented by stating how many bits of
2589 the host address represent the network address (the
2590 <quote>netmask</quote>). If the netmask is 32 and the address is IPv4,
2591 then the value does not indicate a subnet, only a single host.
2592 In IPv6, the address length is 128 bits, so 128 bits will specify a
2593 unique host address. Note that if you
2594 want to accept networks only, you should use the
2595 <type>cidr</type> type rather than <type>inet</type>.
2599 The input format for this type is
2600 <replaceable class="parameter">address/y</replaceable>
2602 <replaceable class="parameter">address</replaceable>
2603 is an IPv4 or IPv6 address and
2604 <replaceable class="parameter">y</replaceable>
2605 is the number of bits in the netmask. If the
2606 <replaceable class="parameter">/y</replaceable>
2607 part is left off, then the
2608 netmask is 32 for IPv4 and 128 for IPv6, and the value represents
2609 just a single host. On display, the
2610 <replaceable class="parameter">/y</replaceable>
2611 portion is suppressed if the netmask specifies a single host.
2615 <sect2 id="datatype-cidr">
2616 <title><type>cidr</></title>
2619 <primary>cidr</primary>
2623 The <type>cidr</type> type holds an IPv4 or IPv6 network specification.
2624 Input and output formats follow Classless Internet Domain Routing
2626 The format for specifying networks is <replaceable
2627 class="parameter">address/y</> where <replaceable
2628 class="parameter">address</> is the network represented as an
2629 IPv4 or IPv6 address, and <replaceable
2630 class="parameter">y</> is the number of bits in the netmask. If
2631 <replaceable class="parameter">y</> is omitted, it is calculated
2632 using assumptions from the older classful network numbering system, except
2633 that it will be at least large enough to include all of the octets
2634 written in the input. It is an error to specify a network address
2635 that has bits set to the right of the specified netmask.
2639 <xref linkend="datatype-net-cidr-table"> shows some examples.
2642 <table id="datatype-net-cidr-table">
2643 <title><type>cidr</> Type Input Examples</title>
2647 <entry><type>cidr</type> Input</entry>
2648 <entry><type>cidr</type> Output</entry>
2649 <entry><literal><function>abbrev</function>(<type>cidr</type>)</literal></entry>
2654 <entry>192.168.100.128/25</entry>
2655 <entry>192.168.100.128/25</entry>
2656 <entry>192.168.100.128/25</entry>
2659 <entry>192.168/24</entry>
2660 <entry>192.168.0.0/24</entry>
2661 <entry>192.168.0/24</entry>
2664 <entry>192.168/25</entry>
2665 <entry>192.168.0.0/25</entry>
2666 <entry>192.168.0.0/25</entry>
2669 <entry>192.168.1</entry>
2670 <entry>192.168.1.0/24</entry>
2671 <entry>192.168.1/24</entry>
2674 <entry>192.168</entry>
2675 <entry>192.168.0.0/24</entry>
2676 <entry>192.168.0/24</entry>
2679 <entry>128.1</entry>
2680 <entry>128.1.0.0/16</entry>
2681 <entry>128.1/16</entry>
2685 <entry>128.0.0.0/16</entry>
2686 <entry>128.0/16</entry>
2689 <entry>128.1.2</entry>
2690 <entry>128.1.2.0/24</entry>
2691 <entry>128.1.2/24</entry>
2694 <entry>10.1.2</entry>
2695 <entry>10.1.2.0/24</entry>
2696 <entry>10.1.2/24</entry>
2700 <entry>10.1.0.0/16</entry>
2701 <entry>10.1/16</entry>
2705 <entry>10.0.0.0/8</entry>
2709 <entry>10.1.2.3/32</entry>
2710 <entry>10.1.2.3/32</entry>
2711 <entry>10.1.2.3/32</entry>
2714 <entry>2001:4f8:3:ba::/64</entry>
2715 <entry>2001:4f8:3:ba::/64</entry>
2716 <entry>2001:4f8:3:ba::/64</entry>
2719 <entry>2001:4f8:3:ba:2e0:81ff:fe22:d1f1/128</entry>
2720 <entry>2001:4f8:3:ba:2e0:81ff:fe22:d1f1/128</entry>
2721 <entry>2001:4f8:3:ba:2e0:81ff:fe22:d1f1</entry>
2724 <entry>::ffff:1.2.3.0/120</entry>
2725 <entry>::ffff:1.2.3.0/120</entry>
2726 <entry>::ffff:1.2.3/120</entry>
2729 <entry>::ffff:1.2.3.0/128</entry>
2730 <entry>::ffff:1.2.3.0/128</entry>
2731 <entry>::ffff:1.2.3.0/128</entry>
2738 <sect2 id="datatype-inet-vs-cidr">
2739 <title><type>inet</type> vs. <type>cidr</type></title>
2742 The essential difference between <type>inet</type> and <type>cidr</type>
2743 data types is that <type>inet</type> accepts values with nonzero bits to
2744 the right of the netmask, whereas <type>cidr</type> does not.
2749 If you do not like the output format for <type>inet</type> or
2750 <type>cidr</type> values, try the functions <function>host</>,
2751 <function>text</>, and <function>abbrev</>.
2756 <sect2 id="datatype-macaddr">
2757 <title><type>macaddr</></>
2760 <primary>macaddr (data type)</primary>
2764 <primary>MAC address</primary>
2769 The <type>macaddr</> type stores MAC addresses, i.e., Ethernet
2770 card hardware addresses (although MAC addresses are used for
2771 other purposes as well). Input is accepted in various customary
2775 <member><literal>'08002b:010203'</></member>
2776 <member><literal>'08002b-010203'</></member>
2777 <member><literal>'0800.2b01.0203'</></member>
2778 <member><literal>'08-00-2b-01-02-03'</></member>
2779 <member><literal>'08:00:2b:01:02:03'</></member>
2782 which would all specify the same
2783 address. Upper and lower case is accepted for the digits
2784 <literal>a</> through <literal>f</>. Output is always in the
2785 last of the shown forms.
2789 The directory <filename class="directory">contrib/mac</filename>
2790 in the <productname>PostgreSQL</productname> source distribution
2791 contains tools that can be used to map MAC addresses to hardware
2798 <sect1 id="datatype-bit">
2799 <title>Bit String Types</title>
2801 <indexterm zone="datatype-bit">
2802 <primary>bit string</primary>
2803 <secondary>data type</secondary>
2807 Bit strings are strings of 1's and 0's. They can be used to store
2808 or visualize bit masks. There are two SQL bit types:
2809 <type>bit(<replaceable>n</replaceable>)</type> and <type>bit
2810 varying(<replaceable>n</replaceable>)</type>, where
2811 <replaceable>n</replaceable> is a positive integer.
2815 <type>bit</type> type data must match the length
2816 <replaceable>n</replaceable> exactly; it is an error to attempt to
2817 store shorter or longer bit strings. <type>bit varying</type> data is
2818 of variable length up to the maximum length
2819 <replaceable>n</replaceable>; longer strings will be rejected.
2820 Writing <type>bit</type> without a length is equivalent to
2821 <literal>bit(1)</literal>, while <type>bit varying</type> without a length
2822 specification means unlimited length.
2827 If one explicitly casts a bit-string value to
2828 <type>bit(<replaceable>n</>)</type>, it will be truncated or
2829 zero-padded on the right to be exactly <replaceable>n</> bits,
2830 without raising an error. Similarly,
2831 if one explicitly casts a bit-string value to
2832 <type>bit varying(<replaceable>n</>)</type>, it will be truncated
2833 on the right if it is more than <replaceable>n</> bits.
2839 Prior to <productname>PostgreSQL</> 7.2, <type>bit</type> data
2840 was always silently truncated or zero-padded on the right, with
2841 or without an explicit cast. This was changed to comply with the
2842 <acronym>SQL</acronym> standard.
2848 linkend="sql-syntax-bit-strings"> for information about the syntax
2849 of bit string constants. Bit-logical operators and string
2850 manipulation functions are available; see <xref
2851 linkend="functions">.
2855 <title>Using the bit string types</title>
2858 CREATE TABLE test (a BIT(3), b BIT VARYING(5));
2859 INSERT INTO test VALUES (B'101', B'00');
2860 INSERT INTO test VALUES (B'10', B'101');
2862 ERROR: bit string length 2 does not match type bit(3)
2864 INSERT INTO test VALUES (B'10'::bit(3), B'101');
2879 <sect1 id="datatype-oid">
2880 <title>Object Identifier Types</title>
2882 <indexterm zone="datatype-oid">
2883 <primary>object identifier</primary>
2884 <secondary>data type</secondary>
2887 <indexterm zone="datatype-oid">
2888 <primary>oid</primary>
2891 <indexterm zone="datatype-oid">
2892 <primary>regproc</primary>
2895 <indexterm zone="datatype-oid">
2896 <primary>regprocedure</primary>
2899 <indexterm zone="datatype-oid">
2900 <primary>regoper</primary>
2903 <indexterm zone="datatype-oid">
2904 <primary>regoperator</primary>
2907 <indexterm zone="datatype-oid">
2908 <primary>regclass</primary>
2911 <indexterm zone="datatype-oid">
2912 <primary>regtype</primary>
2915 <indexterm zone="datatype-oid">
2916 <primary>xid</primary>
2919 <indexterm zone="datatype-oid">
2920 <primary>cid</primary>
2923 <indexterm zone="datatype-oid">
2924 <primary>tid</primary>
2928 Object identifiers (OIDs) are used internally by
2929 <productname>PostgreSQL</productname> as primary keys for various
2930 system tables. An OID system column is also added to user-created
2931 tables, unless <literal>WITHOUT OIDS</literal> is specified when
2932 the table is created, or the <varname>default_with_oids</varname>
2933 configuration variable is set to false. Type <type>oid</>
2934 represents an object identifier. There are also several alias
2935 types for <type>oid</>: <type>regproc</>, <type>regprocedure</>,
2936 <type>regoper</>, <type>regoperator</>, <type>regclass</>, and
2937 <type>regtype</>. <xref linkend="datatype-oid-table"> shows an
2942 The <type>oid</> type is currently implemented as an unsigned
2943 four-byte integer. Therefore, it is not large enough to provide
2944 database-wide uniqueness in large databases, or even in large
2945 individual tables. So, using a user-created table's OID column as
2946 a primary key is discouraged. OIDs are best used only for
2947 references to system tables.
2952 OIDs are included by default in user-created tables in
2953 <productname>PostgreSQL</productname> &version;. However, this
2954 behavior is likely to change in a future version of
2955 <productname>PostgreSQL</productname>. Eventually, user-created
2956 tables will not include an OID system column unless <literal>WITH
2957 OIDS</literal> is specified when the table is created, or the
2958 <varname>default_with_oids</varname> configuration variable is set
2959 to true. If your application requires the presence of an OID
2960 system column in a table, it should specify <literal>WITH
2961 OIDS</literal> when that table is created to ensure compatibility
2962 with future releases of <productname>PostgreSQL</productname>.
2967 The <type>oid</> type itself has few operations beyond comparison.
2969 integer, however, and then manipulated using the standard integer
2970 operators. (Beware of possible signed-versus-unsigned confusion
2975 The OID alias types have no operations of their own except
2976 for specialized input and output routines. These routines are able
2977 to accept and display symbolic names for system objects, rather than
2978 the raw numeric value that type <type>oid</> would use. The alias
2979 types allow simplified lookup of OID values for objects: for example,
2980 one may write <literal>'mytable'::regclass</> to get the OID of table
2981 <literal>mytable</>, rather than <literal>SELECT oid FROM pg_class WHERE
2982 relname = 'mytable'</>. (In reality, a much more complicated <command>SELECT</> would
2983 be needed to deal with selecting the right OID when there are multiple
2984 tables named <literal>mytable</> in different schemas.)
2987 <table id="datatype-oid-table">
2988 <title>Object Identifier Types</title>
2993 <entry>References</entry>
2994 <entry>Description</entry>
2995 <entry>Value Example</entry>
3002 <entry><type>oid</></entry>
3004 <entry>numeric object identifier</entry>
3005 <entry><literal>564182</></entry>
3009 <entry><type>regproc</></entry>
3010 <entry><structname>pg_proc</></entry>
3011 <entry>function name</entry>
3012 <entry><literal>sum</></entry>
3016 <entry><type>regprocedure</></entry>
3017 <entry><structname>pg_proc</></entry>
3018 <entry>function with argument types</entry>
3019 <entry><literal>sum(int4)</></entry>
3023 <entry><type>regoper</></entry>
3024 <entry><structname>pg_operator</></entry>
3025 <entry>operator name</entry>
3026 <entry><literal>+</></entry>
3030 <entry><type>regoperator</></entry>
3031 <entry><structname>pg_operator</></entry>
3032 <entry>operator with argument types</entry>
3033 <entry><literal>*(integer,integer)</> or <literal>-(NONE,integer)</></entry>
3037 <entry><type>regclass</></entry>
3038 <entry><structname>pg_class</></entry>
3039 <entry>relation name</entry>
3040 <entry><literal>pg_type</></entry>
3044 <entry><type>regtype</></entry>
3045 <entry><structname>pg_type</></entry>
3046 <entry>data type name</entry>
3047 <entry><literal>integer</></entry>
3054 All of the OID alias types accept schema-qualified names, and will
3055 display schema-qualified names on output if the object would not
3056 be found in the current search path without being qualified.
3057 The <type>regproc</> and <type>regoper</> alias types will only
3058 accept input names that are unique (not overloaded), so they are
3059 of limited use; for most uses <type>regprocedure</> or
3060 <type>regoperator</> is more appropriate. For <type>regoperator</>,
3061 unary operators are identified by writing <literal>NONE</> for the unused
3066 Another identifier type used by the system is <type>xid</>, or transaction
3067 (abbreviated <abbrev>xact</>) identifier. This is the data type of the system columns
3068 <structfield>xmin</> and <structfield>xmax</>. Transaction identifiers are 32-bit quantities.
3072 A third identifier type used by the system is <type>cid</>, or
3073 command identifier. This is the data type of the system columns
3074 <structfield>cmin</> and <structfield>cmax</>. Command identifiers are also 32-bit quantities.
3078 A final identifier type used by the system is <type>tid</>, or tuple
3079 identifier (row identifier). This is the data type of the system column
3080 <structfield>ctid</>. A tuple ID is a pair
3081 (block number, tuple index within block) that identifies the
3082 physical location of the row within its table.
3086 (The system columns are further explained in <xref
3087 linkend="ddl-system-columns">.)
3091 <sect1 id="datatype-pseudo">
3092 <title>Pseudo-Types</title>
3094 <indexterm zone="datatype-pseudo">
3095 <primary>record</primary>
3098 <indexterm zone="datatype-pseudo">
3099 <primary>any</primary>
3102 <indexterm zone="datatype-pseudo">
3103 <primary>anyarray</primary>
3106 <indexterm zone="datatype-pseudo">
3107 <primary>anyelement</primary>
3110 <indexterm zone="datatype-pseudo">
3111 <primary>void</primary>
3114 <indexterm zone="datatype-pseudo">
3115 <primary>trigger</primary>
3118 <indexterm zone="datatype-pseudo">
3119 <primary>language_handler</primary>
3122 <indexterm zone="datatype-pseudo">
3123 <primary>cstring</primary>
3126 <indexterm zone="datatype-pseudo">
3127 <primary>internal</primary>
3130 <indexterm zone="datatype-pseudo">
3131 <primary>opaque</primary>
3135 The <productname>PostgreSQL</productname> type system contains a
3136 number of special-purpose entries that are collectively called
3137 <firstterm>pseudo-types</>. A pseudo-type cannot be used as a
3138 column data type, but it can be used to declare a function's
3139 argument or result type. Each of the available pseudo-types is
3140 useful in situations where a function's behavior does not
3141 correspond to simply taking or returning a value of a specific
3142 <acronym>SQL</acronym> data type. <xref
3143 linkend="datatype-pseudotypes-table"> lists the existing
3147 <table id="datatype-pseudotypes-table">
3148 <title>Pseudo-Types</title>
3153 <entry>Description</entry>
3159 <entry><type>any</></entry>
3160 <entry>Indicates that a function accepts any input data type whatever.</entry>
3164 <entry><type>anyarray</></entry>
3165 <entry>Indicates that a function accepts any array data type
3166 (see <xref linkend="extend-types-polymorphic">).</entry>
3170 <entry><type>anyelement</></entry>
3171 <entry>Indicates that a function accepts any data type
3172 (see <xref linkend="extend-types-polymorphic">).</entry>
3176 <entry><type>cstring</></entry>
3177 <entry>Indicates that a function accepts or returns a null-terminated C string.</entry>
3181 <entry><type>internal</></entry>
3182 <entry>Indicates that a function accepts or returns a server-internal
3187 <entry><type>language_handler</></entry>
3188 <entry>A procedural language call handler is declared to return <type>language_handler</>.</entry>
3192 <entry><type>record</></entry>
3193 <entry>Identifies a function returning an unspecified row type.</entry>
3197 <entry><type>trigger</></entry>
3198 <entry>A trigger function is declared to return <type>trigger.</></entry>
3202 <entry><type>void</></entry>
3203 <entry>Indicates that a function returns no value.</entry>
3207 <entry><type>opaque</></entry>
3208 <entry>An obsolete type name that formerly served all the above purposes.</entry>
3215 Functions coded in C (whether built-in or dynamically loaded) may be
3216 declared to accept or return any of these pseudo data types. It is up to
3217 the function author to ensure that the function will behave safely
3218 when a pseudo-type is used as an argument type.
3222 Functions coded in procedural languages may use pseudo-types only as
3223 allowed by their implementation languages. At present the procedural
3224 languages all forbid use of a pseudo-type as argument type, and allow
3225 only <type>void</> and <type>record</> as a result type (plus
3226 <type>trigger</> when the function is used as a trigger). Some also
3227 support polymorphic functions using the types <type>anyarray</> and
3228 <type>anyelement</>.
3232 The <type>internal</> pseudo-type is used to declare functions
3233 that are meant only to be called internally by the database
3234 system, and not by direct invocation in a <acronym>SQL</acronym>
3235 query. If a function has at least one <type>internal</>-type
3236 argument then it cannot be called from <acronym>SQL</acronym>. To
3237 preserve the type safety of this restriction it is important to
3238 follow this coding rule: do not create any function that is
3239 declared to return <type>internal</> unless it has at least one
3240 <type>internal</> argument.
3247 <!-- Keep this comment at the end of the file
3252 sgml-minimize-attributes:nil
3253 sgml-always-quote-attributes:t
3255 sgml-indent-tabs-mode:nil
3257 sgml-parent-document:nil
3258 sgml-default-dtd-file:"./reference.ced"
3259 sgml-exposed-tags:nil
3260 sgml-local-catalogs:("/usr/share/sgml/catalog")
3261 sgml-local-ecat-files:nil