| blob | f7ee8e9f228387ee4dff881b1c4cdd476fdf0764 |
1 <!-- $PostgreSQL$ -->
8 </indexterm>
10 <indexterm>
13 </indexterm>
15 <para>
17 types available to users. Users can add new types to
20 </para>
22 <para>
24 types. Most of the alternative names listed in the
27 addition, some internally used or deprecated types are available,
28 but are not listed here.
29 </para>
34 <thead>
35 <row>
39 </row>
40 </thead>
42 <tbody>
43 <row>
47 </row>
49 <row>
53 </row>
55 <row>
57 <entry></entry>
59 </row>
61 <row>
65 </row>
67 <row>
71 </row>
73 <row>
75 <entry></entry>
77 </row>
79 <row>
81 <entry></entry>
83 </row>
85 <row>
89 </row>
91 <row>
95 </row>
97 <row>
99 <entry></entry>
101 </row>
103 <row>
105 <entry></entry>
107 </row>
109 <row>
111 <entry></entry>
113 </row>
115 <row>
119 </row>
121 <row>
123 <entry></entry>
125 </row>
127 <row>
131 </row>
133 <row>
134 <entry><type>interval [ <replaceable>fields</replaceable> ] [ (<replaceable>p</replaceable>) ]</type></entry>
135 <entry></entry>
137 </row>
139 <row>
141 <entry></entry>
143 </row>
145 <row>
147 <entry></entry>
149 </row>
151 <row>
153 <entry></entry>
155 </row>
157 <row>
159 <entry></entry>
161 </row>
163 <row>
169 </row>
171 <row>
173 <entry></entry>
175 </row>
177 <row>
179 <entry></entry>
181 </row>
183 <row>
185 <entry></entry>
187 </row>
189 <row>
193 </row>
195 <row>
199 </row>
201 <row>
205 </row>
207 <row>
209 <entry></entry>
211 </row>
213 <row>
215 <entry></entry>
217 </row>
219 <row>
223 </row>
225 <row>
227 <entry></entry>
229 </row>
231 <row>
235 </row>
237 <row>
239 <entry></entry>
241 </row>
243 <row>
245 <entry></entry>
247 </row>
249 <row>
251 <entry></entry>
253 </row>
255 <row>
257 <entry></entry>
259 </row>
261 <row>
263 <entry></entry>
265 </row>
266 </tbody>
267 </tgroup>
268 </table>
270 <note>
272 <para>
273 The following types (or spellings thereof) are specified by
283 </para>
284 </note>
286 <para>
287 Each data type has an external representation determined by its input
288 and output functions. Many of the built-in types have
289 obvious external formats. However, several types are either unique
291 paths, or have several possible formats, such as the date
292 and time types.
293 Some of the input and output functions are not invertible, i.e.,
294 the result of an output function might lose accuracy when compared to
295 the original input.
296 </para>
304 </indexterm>
306 <para>
307 Numeric types consist of two-, four-, and eight-byte integers,
308 four- and eight-byte floating-point numbers, and selectable-precision
310 available types.
311 </para>
316 <thead>
317 <row>
322 </row>
323 </thead>
325 <tbody>
326 <row>
331 </row>
332 <row>
337 </row>
338 <row>
343 </row>
345 <row>
350 </row>
351 <row>
356 </row>
358 <row>
363 </row>
364 <row>
369 </row>
371 <row>
376 </row>
378 <row>
383 </row>
384 </tbody>
385 </tgroup>
386 </table>
388 <para>
389 The syntax of constants for the numeric types is described in
391 full set of corresponding arithmetic operators and
393 information. The following sections describe the types in detail.
394 </para>
401 </indexterm>
405 </indexterm>
409 </indexterm>
411 <indexterm>
414 </indexterm>
416 <indexterm>
419 </indexterm>
421 <indexterm>
424 </indexterm>
426 <para>
429 fractional components, of various ranges. Attempts to store
430 values outside of the allowed range will result in an error.
431 </para>
433 <para>
435 the best balance between range, storage size, and performance.
439 because the latter is definitely faster.
440 </para>
442 <para>
444 might not function correctly, because it relies on compiler support
447 bytes of storage. (We are not aware of any modern
448 platform where this is the case.)
449 </para>
451 <para>
458 </para>
460 </sect2>
465 <indexterm>
467 </indexterm>
469 <indexterm>
471 </indexterm>
473 <indexterm>
476 </indexterm>
478 <para>
480 digits of precision and perform calculations exactly. It is
481 especially recommended for storing monetary amounts and other
482 quantities where exactness is required. However, arithmetic on
484 types, or to the floating-point types described in the next section.
485 </para>
487 <para>
488 We use the following terms below: The
490 count of decimal digits in the fractional part, to the right of
493 the whole number, that is, the number of digits to both sides of
495 and a scale of 4. Integers can be considered to have a scale of
496 zero.
497 </para>
499 <para>
500 Both the maximum precision and the maximum scale of a
503 the syntax:
504 <programlisting>
506 </programlisting>
507 The precision must be positive, the scale zero or positive.
508 Alternatively:
509 <programlisting>
511 </programlisting>
512 selects a scale of 0. Specifying:
513 <programlisting>
514 NUMERIC
515 </programlisting>
516 without any precision or scale creates a column in which numeric
517 values of any precision and scale can be stored, up to the
518 implementation limit on precision. A column of this kind will
519 not coerce input values to any particular scale, whereas
522 requires a default scale of 0, i.e., coercion to integer
523 precision. We find this a bit useless. If you're concerned
524 about portability, always specify the precision and scale
525 explicitly.)
526 </para>
528 <para>
529 If the scale of a value to be stored is greater than the declared
530 scale of the column, the system will round the value to the specified
531 number of fractional digits. Then, if the number of digits to the
532 left of the decimal point exceeds the declared precision minus the
533 declared scale, an error is raised.
534 </para>
536 <para>
537 Numeric values are physically stored without any extra leading or
538 trailing zeroes. Thus, the declared precision and scale of a column
542 requirement is two bytes for each group of four decimal digits,
543 plus five to eight bytes overhead.
544 </para>
546 <indexterm>
549 </indexterm>
551 <indexterm>
554 </indexterm>
556 <para>
561 as a constant in an SQL command, you must put quotes around it,
564 </para>
566 <note>
567 <para>
574 values.
575 </para>
576 </note>
578 <para>
581 standard.
582 </para>
583 </sect2>
591 </indexterm>
595 </indexterm>
597 <indexterm>
600 </indexterm>
602 <indexterm>
605 </indexterm>
609 </indexterm>
611 <para>
613 precision</type> are inexact, variable-precision numeric types.
614 In practice, these types are usually implementations of
616 Arithmetic (single and double precision, respectively), to the
617 extent that the underlying processor, operating system, and
618 compiler support it.
619 </para>
621 <para>
622 Inexact means that some values cannot be converted exactly to the
623 internal format and are stored as approximations, so that storing
624 and retrieving a value might show slight discrepancies.
625 Managing these errors and how they propagate through calculations
626 is the subject of an entire branch of mathematics and computer
627 science and will not be discussed here, except for the
628 following points:
629 <itemizedlist>
630 <listitem>
631 <para>
632 If you require exact storage and calculations (such as for
634 </para>
635 </listitem>
637 <listitem>
638 <para>
639 If you want to do complicated calculations with these types
640 for anything important, especially if you rely on certain
641 behavior in boundary cases (infinity, underflow), you should
642 evaluate the implementation carefully.
643 </para>
644 </listitem>
646 <listitem>
647 <para>
648 Comparing two floating-point values for equality might not
649 always work as expected.
650 </para>
651 </listitem>
652 </itemizedlist>
653 </para>
655 <para>
660 are too large or too small will cause an error. Rounding might
661 take place if the precision of an input number is too high.
662 Numbers too close to zero that are not representable as distinct
663 from zero will cause an underflow error.
664 </para>
666 <indexterm>
669 </indexterm>
671 <para>
672 In addition to ordinary numeric values, the floating-point types
673 have several special values:
674 <literallayout>
678 </literallayout>
679 These represent the IEEE 754 special values
682 floating-point arithmetic does not follow IEEE 754, these values
683 will probably not work as expected.) When writing these values
684 as constants in an SQL command, you must put quotes around them,
686 these strings are recognized in a case-insensitive manner.
687 </para>
689 <note>
690 <para>
693 In order to allow floating-point values to be sorted and used
697 </para>
698 </note>
700 <para>
711 outside the allowed range draw an error.
714 </para>
716 <note>
717 <para>
721 standard, which specifies that the precision is measured in binary
724 mantissa respectively is correct for IEEE-standard floating point
725 implementations. On non-IEEE platforms it might be off a little, but
727 on all platforms.
728 </para>
729 </note>
731 </sect2>
738 </indexterm>
742 </indexterm>
746 </indexterm>
750 </indexterm>
752 <indexterm>
755 </indexterm>
757 <indexterm>
760 </indexterm>
762 <para>
764 are not true types, but merely
765 a notational convenience for creating unique identifier columns
767 supported by some other databases). In the current
768 implementation, specifying:
770 <programlisting>
773 );
774 </programlisting>
776 is equivalent to specifying:
778 <programlisting>
779 CREATE SEQUENCE <replaceable class="parameter">tablename</replaceable>_<replaceable class="parameter">colname</replaceable>_seq;
781 <replaceable class="parameter">colname</replaceable> integer NOT NULL DEFAULT nextval('<replaceable class="parameter">tablename</replaceable>_<replaceable class="parameter">colname</replaceable>_seq')
782 );
783 ALTER SEQUENCE <replaceable class="parameter">tablename</replaceable>_<replaceable class="parameter">colname</replaceable>_seq OWNED BY <replaceable class="parameter">tablename</replaceable>.<replaceable class="parameter">colname</replaceable>;
784 </programlisting>
786 Thus, we have created an integer column and arranged for its default
788 constraint is applied to ensure that a null value cannot be
789 inserted. (In most cases you would also want to attach a
791 duplicate values from being inserted by accident, but this is
793 the column, so that it will be dropped if the column or table is dropped.
794 </para>
796 <note>
797 <para>
800 you wish a serial column to have a unique constraint or be a
801 primary key, it must now be specified, just like
802 any other data type.
803 </para>
804 </note>
806 <para>
809 column should be assigned its default value. This can be done
810 either by excluding the column from the list of columns in
813 </para>
815 <para>
822 lifetime of the table.
823 </para>
825 <para>
827 automatically dropped when the owning column is dropped.
828 You can drop the sequence without dropping the column, but this
829 will force removal of the column default expression.
830 </para>
831 </sect2>
832 </sect1>
837 <para>
839 fractional precision; see <xref
842 Input is accepted in a variety of formats, including integer and
843 floating-point literals, as well as typical
845 Output is generally in the latter form but depends on the locale.
849 <programlisting>
850 SELECT 1234::text::money;
851 </programlisting>
852 There is no simple way of doing the reverse in a locale-independent
854 If you know the currency symbol and thousands separator you can use
856 <programlisting>
857 SELECT regexp_replace('52093.89'::money::text, '[$,]', '', 'g')::numeric;
858 </programlisting>
860 </para>
862 <para>
863 Since the output of this data type is locale-sensitive, it might not
867 equivalent value as in the database that was dumped.
868 </para>
873 <thead>
874 <row>
879 </row>
880 </thead>
881 <tbody>
882 <row>
887 </row>
888 </tbody>
889 </tgroup>
890 </table>
891 </sect1>
900 </indexterm>
902 <indexterm>
905 </indexterm>
909 </indexterm>
913 </indexterm>
917 </indexterm>
921 </indexterm>
925 </indexterm>
930 <thead>
931 <row>
934 </row>
935 </thead>
936 <tbody>
937 <row>
938 <entry><type>character varying(<replaceable>n</>)</type>, <type>varchar(<replaceable>n</>)</type></entry>
940 </row>
941 <row>
944 </row>
945 <row>
948 </row>
949 </tbody>
950 </tgroup>
951 </table>
953 <para>
955 general-purpose character types available in
957 </para>
959 <para>
963 is a positive integer. Both of these types can store strings up to
965 longer string into a column of these types will result in an
966 error, unless the excess characters are all spaces, in which case
967 the string will be truncated to the maximum length. (This somewhat
969 standard.) If the string to be stored is shorter than the declared
972 shorter
973 string.
974 </para>
976 <para>
977 If one explicitly casts a value to <type>character
981 raising an error. (This too is required by the
983 </para>
985 <para>
992 without length specifier, the type accepts strings of any size. The
994 </para>
996 <para>
1001 management systems have it as well.
1002 </para>
1004 <para>
1007 stored and displayed that way. However, the padding spaces are
1008 treated as semantically insignificant. Trailing spaces are
1011 to one of the other string types. Note that trailing spaces
1014 </para>
1016 <para>
1018 plus the actual string, which includes the space padding in the case of
1020 of 1. Long strings are compressed by the system automatically, so
1021 the physical requirement on disk might be less. Very long values are also
1022 stored in background tables so that they do not interfere with rapid
1023 access to shorter column values. In any case, the longest
1024 possible character string that can be stored is about 1 GB. (The
1026 type declaration is less than that. It wouldn't be useful to
1027 change this because with multibyte character encodings the number of
1028 characters and bytes can be quite different. If you desire to
1029 store long strings with no specific upper limit, use
1031 specifier, rather than making up an arbitrary length limit.)
1032 </para>
1034 <tip>
1035 <para>
1036 There is no performance difference among these three types,
1037 apart from increased storage space when using the blank-padded
1038 type, and a few extra CPU cycles to check the length when storing into
1039 a length-constrained column. While
1041 advantages in some other database systems, there is no such advantage in
1044 the three because of its additional storage costs. In most situations
1046 instead.
1047 </para>
1048 </tip>
1050 <para>
1053 for information about available operators and functions. The
1054 database character set determines the character set used to store
1055 textual values; for more information on character set support,
1057 </para>
1059 <example>
1062 <programlisting>
1063 CREATE TABLE test1 (a character(4));
1064 INSERT INTO test1 VALUES ('ok');
1066 <computeroutput>
1067 a | char_length
1068 ------+-------------
1069 ok | 2
1070 </computeroutput>
1072 CREATE TABLE test2 (b varchar(5));
1073 INSERT INTO test2 VALUES ('ok');
1074 INSERT INTO test2 VALUES ('good ');
1075 INSERT INTO test2 VALUES ('too long');
1077 INSERT INTO test2 VALUES ('too long'::varchar(5)); -- explicit truncation
1078 SELECT b, char_length(b) FROM test2;
1079 <computeroutput>
1080 b | char_length
1081 -------+-------------
1082 ok | 2
1083 good | 5
1084 too l | 5
1085 </computeroutput>
1086 </programlisting>
1087 <calloutlist>
1089 <para>
1092 </para>
1093 </callout>
1094 </calloutlist>
1095 </example>
1097 <para>
1098 There are two other fixed-length character types in
1102 in the internal system catalogs and is not intended for use by the general user. Its
1104 terminator) but should be referenced using the constant
1106 The length is set at compile time (and
1107 is therefore adjustable for special uses); the default maximum
1110 only uses one byte of storage. It is internally used in the system
1111 catalogs as a simplistic enumeration type.
1112 </para>
1117 <thead>
1118 <row>
1122 </row>
1123 </thead>
1124 <tbody>
1125 <row>
1129 </row>
1130 <row>
1134 </row>
1135 </tbody>
1136 </tgroup>
1137 </table>
1139 </sect1>
1146 </indexterm>
1150 </indexterm>
1152 <para>
1155 </para>
1160 <thead>
1161 <row>
1165 </row>
1166 </thead>
1167 <tbody>
1168 <row>
1172 </row>
1173 </tbody>
1174 </tgroup>
1175 </table>
1177 <para>
1178 A binary string is a sequence of octets (or bytes). Binary
1179 strings are distinguished from character strings in two
1180 ways: First, binary strings specifically allow storing
1183 Character strings disallow zero octets, and also disallow any
1184 other octet values and sequences of octet values that are invalid
1185 according to the database's selected character set encoding.
1186 Second, operations on binary strings process the actual bytes,
1187 whereas the processing of character strings depends on locale settings.
1188 In short, binary strings are appropriate for storing data that the
1190 strings are appropriate for storing text.
1191 </para>
1193 <para>
1198 general, to escape an octet, convert it into its three-digit
1199 octal value and precede it
1201 shows the characters that must be escaped, and gives the alternative
1202 escape sequences where applicable.
1203 </para>
1208 <thead>
1209 <row>
1215 </row>
1216 </thead>
1218 <tbody>
1219 <row>
1225 </row>
1227 <row>
1233 </row>
1235 <row>
1241 </row>
1243 <row>
1249 </row>
1251 </tbody>
1252 </tgroup>
1253 </table>
1255 <para>
1257 varies depending on locale settings. In some instances you can get away
1258 with leaving them unescaped. Note that the result in each of the examples
1260 length, even though the output representation is sometimes
1261 more than one character.
1262 </para>
1264 <para>
1265 The reason multiple backslashes are required, as shown
1267 string written as a string literal must pass through two parse
1269 The first backslash of each pair is interpreted as an escape
1270 character by the string-literal parser (assuming escape string
1271 syntax is used) and is therefore consumed, leaving the second backslash of the
1272 pair. (Dollar-quoted strings can be used to avoid this level
1273 of escaping.) The remaining backslash is then recognized by the
1275 digit octal value or escaping another backslash. For example,
1280 to a single octet with a decimal value of 1. Note that the
1282 so it follows the normal rules for string literals. (See also
1284 </para>
1286 <para>
1289 its equivalent three-digit octal value and preceded by one backslash.
1291 representation in the client character set. The octet with decimal
1292 value 92 (backslash) is doubled in the output.
1294 </para>
1299 <thead>
1300 <row>
1306 </row>
1307 </thead>
1309 <tbody>
1311 <row>
1317 </row>
1319 <row>
1325 </row>
1327 <row>
1333 </row>
1335 </tbody>
1336 </tgroup>
1337 </table>
1339 <para>
1341 you might have additional work to do in terms of escaping and
1343 have to escape line feeds and carriage returns if your interface
1344 automatically translates these.
1345 </para>
1347 <para>
1350 OBJECT</type>. The input format is different from
1352 mostly the same.
1353 </para>
1354 </sect1>
1362 </indexterm>
1365 </indexterm>
1368 </indexterm>
1371 </indexterm>
1374 </indexterm>
1377 </indexterm>
1380 </indexterm>
1383 </indexterm>
1386 </indexterm>
1388 <para>
1392 on these data types are described in
1394 </para>
1399 <thead>
1400 <row>
1407 </row>
1408 </thead>
1409 <tbody>
1410 <row>
1417 </row>
1418 <row>
1425 </row>
1426 <row>
1433 </row>
1434 <row>
1441 </row>
1442 <row>
1449 </row>
1450 <row>
1451 <entry><type>interval [ <replaceable>fields</replaceable> ] [ (<replaceable>p</replaceable>) ]</type></entry>
1457 </row>
1458 </tbody>
1459 </tgroup>
1460 </table>
1462 <note>
1463 <para>
1466 time zone</type>. This was changed for SQL compliance.
1467 </para>
1468 </note>
1470 <para>
1474 fractional digits retained in the seconds field. By default, there
1475 is no explicit bound on precision. The allowed range of
1478 </para>
1480 <note>
1481 <para>
1483 (currently the default), microsecond precision is available over
1485 stored as double precision floating-point numbers instead (a
1486 deprecated compile-time option), the effective limit of precision
1490 numbers, microsecond precision is achieved for dates within a few
1492 away. Note that using floating-point datetimes allows a larger
1495 </para>
1497 <para>
1498 The same compile-time option also determines whether
1500 floating-point numbers or eight-byte integers. In the
1502 precision as the size of the interval increases.
1503 </para>
1504 </note>
1506 <para>
1510 </para>
1512 <para>
1514 to restrict the set of stored fields by writing one of these phrases:
1515 <programlisting>
1516 YEAR
1517 MONTH
1518 DAY
1519 HOUR
1520 MINUTE
1521 SECOND
1522 YEAR TO MONTH
1523 DAY TO HOUR
1524 DAY TO MINUTE
1525 DAY TO SECOND
1526 HOUR TO MINUTE
1527 HOUR TO SECOND
1528 MINUTE TO SECOND
1529 </programlisting>
1533 since the precision applies only to the seconds.
1534 </para>
1536 <para>
1538 standard, but the definition exhibits properties which lead to
1539 questionable usefulness. In most cases, a combination of
1542 provide a complete range of date/time functionality required by
1543 any application.
1544 </para>
1546 <para>
1549 You are discouraged from using these types in
1550 applications; these internal types
1551 might disappear in a future release.
1552 </para>
1557 <para>
1558 Date and time input is accepted in almost any reasonable format, including
1561 For some formats, ordering of day, month, and year in date input is
1562 ambiguous and there is support for specifying the expected
1567 </para>
1569 <para>
1571 handling date/time input than the
1574 for the exact parsing rules of date/time input and for the
1575 recognized text fields including months, days of the week, and
1576 time zones.
1577 </para>
1579 <para>
1580 Remember that any date or time literal input needs to be enclosed
1581 in single quotes, like text strings. Refer to
1583 information.
1585 <synopsis>
1586 <replaceable>type</replaceable> [ (<replaceable>p</replaceable>) ] '<replaceable>value</replaceable>'
1587 </synopsis>
1589 specification giving the number of
1590 fractional digits in the seconds field. Precision can be
1593 above. If no precision is specified in a constant specification,
1594 it defaults to the precision of the literal value.
1595 </para>
1597 <sect3>
1600 <indexterm>
1602 </indexterm>
1604 <para>
1607 </para>
1612 <thead>
1613 <row>
1616 </row>
1617 </thead>
1618 <tbody>
1619 <row>
1622 (recommended format)</entry>
1623 </row>
1624 <row>
1627 </row>
1628 <row>
1632 </row>
1633 <row>
1636 rejected in other modes</entry>
1637 </row>
1638 <row>
1643 </entry>
1644 </row>
1645 <row>
1648 </row>
1649 <row>
1652 </row>
1653 <row>
1656 </row>
1657 <row>
1660 </row>
1661 <row>
1664 </row>
1665 <row>
1668 </row>
1669 <row>
1672 </row>
1673 <row>
1676 </row>
1677 <row>
1680 </row>
1681 <row>
1684 </row>
1685 <row>
1688 </row>
1689 </tbody>
1690 </tgroup>
1691 </table>
1692 </sect3>
1694 <sect3>
1697 <indexterm>
1699 </indexterm>
1700 <indexterm>
1702 </indexterm>
1703 <indexterm>
1705 </indexterm>
1707 <para>
1708 The time-of-day types are <type>time [
1713 </para>
1715 <para>
1716 Valid input for these types consists of a time of day followed
1717 by an optional time zone. (See <xref
1721 it is silently ignored. You can also specify a date but it will
1722 be ignored, except when you use a time zone name that involves a
1723 daylight-savings rule, such as
1725 is required in order to determine whether standard or daylight-savings
1726 time applies. The appropriate time zone offset is recorded in the
1728 </para>
1733 <thead>
1734 <row>
1737 </row>
1738 </thead>
1739 <tbody>
1740 <row>
1743 </row>
1744 <row>
1747 </row>
1748 <row>
1751 </row>
1752 <row>
1755 </row>
1756 <row>
1759 </row>
1760 <row>
1763 </row>
1764 <row>
1767 </row>
1768 <row>
1771 </row>
1772 <row>
1775 </row>
1776 <row>
1779 </row>
1780 <row>
1783 </row>
1784 <row>
1787 </row>
1788 </tbody>
1789 </tgroup>
1790 </table>
1795 <thead>
1796 <row>
1799 </row>
1800 </thead>
1801 <tbody>
1802 <row>
1805 </row>
1806 <row>
1809 </row>
1810 <row>
1813 </row>
1814 <row>
1817 </row>
1818 <row>
1821 </row>
1822 <row>
1825 </row>
1826 <row>
1829 </row>
1830 <row>
1833 </row>
1834 </tbody>
1835 </tgroup>
1836 </table>
1838 <para>
1840 to specify time zones.
1841 </para>
1842 </sect3>
1844 <sect3>
1847 <indexterm>
1849 </indexterm>
1851 <indexterm>
1853 </indexterm>
1855 <indexterm>
1857 </indexterm>
1859 <para>
1860 Valid input for the time stamp types consists of the concatenation
1861 of a date and a time, followed by an optional time zone,
1864 before the time zone, but this is not the preferred ordering.)
1865 Thus:
1867 <programlisting>
1869 </programlisting>
1870 and:
1871 <programlisting>
1873 </programlisting>
1876 standard. In addition, the common format:
1877 <programlisting>
1879 </programlisting>
1880 is supported.
1881 </para>
1883 <para>
1888 the time. Hence, according to the standard,
1898 literal string before determining its type, and therefore will treat
1900 ensure that a literal is treated as <type>timestamp with time
1901 zone</type>, give it the correct explicit type:
1905 In a literal that has been determined to be <type>timestamp without time
1907 any time zone indication.
1908 That is, the resulting value is derived from the date/time
1909 fields in the input value, and is not adjusted for time zone.
1910 </para>
1912 <para>
1914 value is always in UTC (Universal
1915 Coordinated Time, traditionally known as Greenwich Mean Time,
1917 time zone specified is converted to UTC using the appropriate offset
1918 for that time zone. If no time zone is stated in the input string,
1919 then it is assumed to be in the time zone indicated by the system's
1922 </para>
1924 <para>
1925 When a <type>timestamp with time
1926 zone</type> value is output, it is always converted from UTC to the
1928 zone. To see the time in another time zone, either change
1931 </para>
1933 <para>
1939 </para>
1940 </sect3>
1942 <sect3>
1945 <indexterm>
1948 </indexterm>
1950 <indexterm>
1953 </indexterm>
1955 <para>
1957 special date/time input values for convenience, as shown in <xref
1960 are specially represented inside the system and will be displayed
1961 unchanged; but the others are simply notational shorthands
1962 that will be converted to ordinary date/time values when read.
1964 to a specific time value as soon as they are read.)
1965 All of these values need to be enclosed in single quotes when used
1966 as constants in SQL commands.
1967 </para>
1972 <thead>
1973 <row>
1977 </row>
1978 </thead>
1979 <tbody>
1980 <row>
1984 </row>
1985 <row>
1989 </row>
1990 <row>
1994 </row>
1995 <row>
1999 </row>
2000 <row>
2004 </row>
2005 <row>
2009 </row>
2010 <row>
2014 </row>
2015 <row>
2019 </row>
2020 </tbody>
2021 </tgroup>
2022 </table>
2024 <para>
2026 be used to obtain the current time value for the corresponding data
2027 type:
2031 optional subsecond precision specification. (See <xref
2034 </para>
2036 </sect3>
2037 </sect2>
2042 <indexterm>
2046 </indexterm>
2048 <indexterm>
2052 </indexterm>
2054 <para>
2055 The output format of the date/time types can be set to one of the four
2056 styles ISO 8601,
2059 German. The default
2063 historical accident.) <xref
2067 in accordance with the given examples.
2068 </para>
2073 <thead>
2074 <row>
2078 </row>
2079 </thead>
2080 <tbody>
2081 <row>
2085 </row>
2086 <row>
2090 </row>
2091 <row>
2095 </row>
2096 <row>
2100 </row>
2101 </tbody>
2102 </tgroup>
2103 </table>
2105 <para>
2107 month if DMY field ordering has been specified, otherwise month appears
2108 before day.
2110 for how this setting also affects interpretation of input values.)
2112 example.
2113 </para>
2118 <thead>
2119 <row>
2123 </row>
2124 </thead>
2125 <tbody>
2126 <row>
2128 <entry><replaceable>day</replaceable>/<replaceable>month</replaceable>/<replaceable>year</replaceable></entry>
2130 </row>
2131 <row>
2133 <entry><replaceable>month</replaceable>/<replaceable>day</replaceable>/<replaceable>year</replaceable></entry>
2135 </row>
2136 <row>
2138 <entry><replaceable>day</replaceable>/<replaceable>month</replaceable>/<replaceable>year</replaceable></entry>
2140 </row>
2141 </tbody>
2142 </tgroup>
2143 </table>
2145 <para>
2146 The date/time styles can be selected by the user using the
2153 a more flexible way to format date/time output.
2154 </para>
2155 </sect2>
2162 </indexterm>
2164 <para>
2165 Time zones, and time-zone conventions, are influenced by
2166 political decisions, not just earth geometry. Time zones around the
2167 world became somewhat standardized during the 1900's,
2168 but continue to be prone to arbitrary changes, particularly with
2169 respect to daylight-savings rules.
2172 historical time zone rules. For times in the future, the assumption
2173 is that the latest known rules for a given time zone will
2174 continue to be observed indefinitely far into the future.
2175 </para>
2177 <para>
2181 time types and capabilities. Two obvious problems are:
2183 <itemizedlist>
2184 <listitem>
2185 <para>
2187 cannot have an associated time zone, the
2189 Time zones in the real world have little meaning unless
2190 associated with a date as well as a time,
2191 since the offset can vary through the year with daylight-saving
2192 time boundaries.
2193 </para>
2194 </listitem>
2196 <listitem>
2197 <para>
2198 The default time zone is specified as a constant numeric offset
2200 daylight-saving time when doing date/time arithmetic across
2202 </para>
2203 </listitem>
2205 </itemizedlist>
2206 </para>
2208 <para>
2209 To address these difficulties, we recommend using date/time types
2210 that contain both date and time when using time zones. We
2212 time zone</type> (though it is supported by
2216 your local time zone for any type containing only date or time.
2217 </para>
2219 <para>
2220 All timezone-aware dates and times are stored internally in
2223 parameter before being displayed to the client.
2224 </para>
2226 <para>
2228 three different forms:
2229 <itemizedlist>
2230 <listitem>
2231 <para>
2233 The recognized time zone names are listed in the
2238 names are also recognized by much other software.
2239 </para>
2240 </listitem>
2241 <listitem>
2242 <para>
2244 specification merely defines a particular offset from UTC, in
2245 contrast to full time zone names which can imply a set of daylight
2246 savings transition-date rules as well. The recognized abbreviations
2251 zone abbreviation, but you can use abbreviations in
2253 operator.
2254 </para>
2255 </listitem>
2256 <listitem>
2257 <para>
2258 In addition to the timezone names and abbreviations,
2264 optional daylight-savings zone abbreviation, assumed to stand for one
2266 were not already a recognized zone name, it would be accepted and would
2267 be functionally equivalent to United States East Coast time. When a
2268 daylight-savings zone name is present, it is assumed to be used
2269 according to the same daylight-savings transition rules used in the
2273 that POSIX-style time zone specifications follow USA daylight-savings
2274 rules. If needed, you can adjust this behavior by replacing the
2276 </para>
2277 </listitem>
2278 </itemizedlist>
2280 In short, this is the difference between abbreviations
2281 and full names: abbreviations always represent a fixed offset from
2282 UTC, whereas most of the full names imply a local daylight-savings time
2283 rule, and so have two possible UTC offsets.
2284 </para>
2286 <para>
2287 One should be wary that the POSIX-style time zone feature can
2288 lead to silently accepting bogus input, since there is no check on the
2289 reasonableness of the zone abbreviations. For example, <literal>SET
2290 TIMEZONE TO FOOBAR0</> will work, leaving the system effectively using
2291 a rather peculiar abbreviation for UTC.
2292 Another issue to keep in mind is that in POSIX time zone names,
2296 of Greenwich.
2297 </para>
2299 <para>
2300 In all cases, timezone names are recognized case-insensitively.
2302 prior to 8.2, which were case-sensitive in some contexts but not others.)
2303 </para>
2305 <para>
2306 Neither full names nor abbreviations are hard-wired into the server;
2307 they are obtained from configuration files stored under
2309 of the installation directory
2311 </para>
2313 <para>
2317 There are also several special ways to set it:
2319 <itemizedlist>
2320 <listitem>
2321 <para>
2326 is not defined or is not any of the time zone names known to
2328 determine the operating system's default time zone by checking the
2330 default time zone is selected as the closest match among
2332 (These rules are also used to choose the default value of
2334 </para>
2335 </listitem>
2337 <listitem>
2338 <para>
2340 sets the time zone for the session. This is an alternative spelling
2342 </para>
2343 </listitem>
2345 <listitem>
2346 <para>
2350 command to the server upon connection.
2351 </para>
2352 </listitem>
2353 </itemizedlist>
2354 </para>
2355 </sect2>
2360 <indexterm>
2362 </indexterm>
2364 <para>
2366 verbose syntax:
2368 <synopsis>
2369 <optional>@</> <replaceable>quantity</> <replaceable>unit</> <optional><replaceable>quantity</> <replaceable>unit</>...</> <optional><replaceable>direction</></optional>
2370 </synopsis>
2378 or abbreviations or plurals of these units;
2381 of the different units are implicitly added with appropriate
2383 This syntax is also used for interval output, if
2386 </para>
2388 <para>
2389 Quantities of days, hours, minutes, and seconds can be specified without
2392 a combination of years and months can be specified with a dash;
2397 </para>
2399 <para>
2400 Interval values can also be written as ISO 8601 time intervals, using
2403 format with designators looks like this:
2404 <synopsis>
2405 P <replaceable>quantity</> <replaceable>unit</> <optional> <replaceable>quantity</> <replaceable>unit</> ...</optional> <optional> T <optional> <replaceable>quantity</> <replaceable>unit</> ...</optional></optional>
2406 </synopsis>
2409 available unit abbreviations are given in <xref
2411 omitted, and may be specified in any order, but units smaller than
2415 </para>
2420 <thead>
2421 <row>
2424 </row>
2425 </thead>
2426 <tbody>
2427 <row>
2430 </row>
2431 <row>
2434 </row>
2435 <row>
2438 </row>
2439 <row>
2442 </row>
2443 <row>
2446 </row>
2447 <row>
2450 </row>
2451 <row>
2454 </row>
2455 </tbody>
2456 </tgroup>
2457 </table>
2459 <para>
2460 In the alternative format:
2461 <synopsis>
2462 P <optional> <replaceable>years</>-<replaceable>months</>-<replaceable>days</> </optional> <optional> T <replaceable>hours</>:<replaceable>minutes</>:<replaceable>seconds</> </optional>
2463 </synopsis>
2466 The values are given as numbers similar to ISO 8601 dates.
2467 </para>
2469 <para>
2471 specification, or when assigning a string to an interval column that was
2479 results in dropping the seconds field, but not the day field.
2480 </para>
2482 <para>
2484 value must have the same sign, so a leading negative sign applies to all
2485 fields; for example the negative sign in the interval literal
2488 signs, and traditionally treats each field in the textual representation
2489 as independently signed, so that the hour/minute/second part is
2492 to apply to all fields (but only if no additional signs appear).
2494 used. To avoid ambiguity, it's recommended to attach an explicit sign
2495 to each field if any field is negative.
2496 </para>
2498 <para>
2500 and seconds. This is done because the number of days in a month
2502 time adjustment is involved. The months and days fields are integers
2503 while the seconds field can store fractions. Because intervals are
2505 this storage method works well in most cases. Functions
2507 available for adjusting days and hours that overflow their normal
2508 ranges.
2509 </para>
2511 <para>
2512 In the verbose input format, and in some fields of the more compact
2513 input formats, field values can have fractional parts; for example
2515 converted to the appropriate number of months, days, and seconds
2516 for storage. When this would result in a fractional number of
2517 months or days, the fraction is added to the lower-order fields
2520 Only seconds will ever be shown as fractional on output.
2521 </para>
2523 <para>
2526 </para>
2531 <thead>
2532 <row>
2535 </row>
2536 </thead>
2537 <tbody>
2538 <row>
2541 </row>
2542 <row>
2545 </row>
2546 <row>
2549 </row>
2550 <row>
2553 </row>
2554 <row>
2557 </row>
2558 </tbody>
2559 </tgroup>
2560 </table>
2562 </sect2>
2567 <indexterm>
2571 </indexterm>
2573 <para>
2574 The output format of the interval type can be set to one of the
2580 output style.
2581 </para>
2583 <para>
2585 the SQL standard's specification for interval literal strings, if
2586 the interval value meets the standard's restrictions (either year-month
2587 only or day-time only, with no mixing of positive
2588 and negative components). Otherwise the output looks like a standard
2589 year-month literal string followed by a day-time literal string,
2590 with explicit signs added to disambiguate mixed-sign intervals.
2591 </para>
2593 <para>
2597 </para>
2599 <para>
2603 </para>
2605 <para>
2608 ISO 8601 standard.
2609 </para>
2614 <thead>
2615 <row>
2620 </row>
2621 </thead>
2622 <tbody>
2623 <row>
2628 </row>
2629 <row>
2634 </row>
2635 <row>
2640 </row>
2641 <row>
2646 </row>
2647 </tbody>
2648 </tgroup>
2649 </table>
2651 </sect2>
2656 <para>
2658 for all date/time calculations. This has the useful property of correctly
2659 calculating dates from 4713 BC
2660 to far into the future, using the assumption that the length of the
2661 year is 365.2425 days.
2662 </para>
2664 <para>
2665 Date conventions before the 19th century make for interesting reading,
2666 but are not consistent enough to warrant coding into a date/time handler.
2667 </para>
2668 </sect2>
2670 </sect1>
2678 </indexterm>
2682 </indexterm>
2686 </indexterm>
2688 <para>
2695 </para>
2697 <para>
2699 <simplelist>
2707 </simplelist>
2709 used:
2710 <simplelist>
2718 </simplelist>
2719 Leading or trailing whitespace is ignored, and case does not matter.
2720 The key words
2723 </para>
2728 <programlisting>
2729 CREATE TABLE test1 (a boolean, b text);
2730 INSERT INTO test1 VALUES (TRUE, 'sic est');
2731 INSERT INTO test1 VALUES (FALSE, 'non est');
2732 SELECT * FROM test1;
2733 a | b
2734 ---+---------
2735 t | sic est
2736 f | non est
2738 SELECT * FROM test1 WHERE a;
2739 a | b
2740 ---+---------
2741 t | sic est
2742 </programlisting>
2743 </example>
2745 <para>
2749 </para>
2751 <para>
2753 </para>
2754 </sect1>
2762 </indexterm>
2766 </indexterm>
2768 <para>
2769 Enumerated (enum) types are data types that
2770 comprise a static, ordered set of values.
2772 types supported in a number of programming languages. An example of an enum
2773 type might be the days of the week, or a set of status values for
2774 a piece of data.
2775 </para>
2777 <sect2>
2780 <para>
2781 Enum types are created using the <xref
2783 for example:
2785 <programlisting>
2786 CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
2787 </programlisting>
2789 Once created, the enum type can be used in table and function
2790 definitions much like any other type:
2791 </para>
2793 <example>
2795 <programlisting>
2796 CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
2797 CREATE TABLE person (
2798 name text,
2799 current_mood mood
2800 );
2801 INSERT INTO person VALUES ('Moe', 'happy');
2802 SELECT * FROM person WHERE current_mood = 'happy';
2803 name | current_mood
2804 ------+--------------
2805 Moe | happy
2806 (1 row)
2807 </programlisting>
2808 </example>
2809 </sect2>
2811 <sect2>
2814 <para>
2815 The ordering of the values in an enum type is the
2816 order in which the values were listed when the type was created.
2817 All standard comparison operators and related
2818 aggregate functions are supported for enums. For example:
2819 </para>
2821 <example>
2823 <programlisting>
2824 INSERT INTO person VALUES ('Larry', 'sad');
2825 INSERT INTO person VALUES ('Curly', 'ok');
2826 SELECT * FROM person WHERE current_mood > 'sad';
2827 name | current_mood
2828 -------+--------------
2829 Moe | happy
2830 Curly | ok
2831 (2 rows)
2833 SELECT * FROM person WHERE current_mood > 'sad' ORDER BY current_mood;
2834 name | current_mood
2835 -------+--------------
2836 Curly | ok
2837 Moe | happy
2838 (2 rows)
2840 SELECT name
2841 FROM person
2842 WHERE current_mood = (SELECT MIN(current_mood) FROM person);
2843 name
2844 -------
2845 Larry
2846 (1 row)
2847 </programlisting>
2848 </example>
2849 </sect2>
2851 <sect2>
2854 <para>
2855 Each enumerated data type is separate and cannot
2856 be compared with other enumerated types.
2857 </para>
2859 <example>
2861 <programlisting>
2862 CREATE TYPE happiness AS ENUM ('happy', 'very happy', 'ecstatic');
2863 CREATE TABLE holidays (
2864 num_weeks integer,
2865 happiness happiness
2866 );
2867 INSERT INTO holidays(num_weeks,happiness) VALUES (4, 'happy');
2868 INSERT INTO holidays(num_weeks,happiness) VALUES (6, 'very happy');
2869 INSERT INTO holidays(num_weeks,happiness) VALUES (8, 'ecstatic');
2870 INSERT INTO holidays(num_weeks,happiness) VALUES (2, 'sad');
2871 ERROR: invalid input value for enum happiness: "sad"
2872 SELECT person.name, holidays.num_weeks FROM person, holidays
2873 WHERE person.current_mood = holidays.happiness;
2874 ERROR: operator does not exist: mood = happiness
2875 </programlisting>
2876 </example>
2878 <para>
2879 If you really need to do something like that, you can either
2880 write a custom operator or add explicit casts to your query:
2881 </para>
2883 <example>
2885 <programlisting>
2886 SELECT person.name, holidays.num_weeks FROM person, holidays
2887 WHERE person.current_mood::text = holidays.happiness::text;
2888 name | num_weeks
2889 ------+-----------
2890 Moe | 4
2891 (1 row)
2893 </programlisting>
2894 </example>
2895 </sect2>
2897 <sect2>
2900 <para>
2901 An enum value occupies four bytes on disk. The length of an enum
2904 builds this means at most 63 bytes.
2905 </para>
2907 <para>
2908 Enum labels are case sensitive, so
2910 White space in the labels is significant too.
2911 </para>
2913 <para>
2914 The translations from internal enum values to textual labels are
2915 kept in the system catalog
2917 Querying this catalog directly can be useful.
2918 </para>
2920 </sect2>
2921 </sect1>
2926 <para>
2927 Geometric data types represent two-dimensional spatial
2930 most fundamental type, the point, forms the basis for all of the
2931 other types.
2932 </para>
2937 <thead>
2938 <row>
2943 </row>
2944 </thead>
2945 <tbody>
2946 <row>
2951 </row>
2952 <row>
2957 </row>
2958 <row>
2963 </row>
2964 <row>
2969 </row>
2970 <row>
2975 </row>
2976 <row>
2981 </row>
2982 <row>
2987 </row>
2988 <row>
2993 </row>
2994 </tbody>
2995 </tgroup>
2996 </table>
2998 <para>
2999 A rich set of functions and operators is available to perform various geometric
3000 operations such as scaling, translation, rotation, and determining
3002 </para>
3004 <sect2>
3007 <indexterm>
3009 </indexterm>
3011 <para>
3012 Points are the fundamental two-dimensional building block for geometric types.
3015 <synopsis>
3018 </synopsis>
3021 coordinates, as floating-point numbers.
3022 </para>
3023 </sect2>
3025 <sect2>
3028 <indexterm>
3030 </indexterm>
3032 <indexterm>
3034 </indexterm>
3036 <para>
3040 <synopsis>
3041 ( ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ( <replaceable>x2</replaceable> , <replaceable>y2</replaceable> ) )
3042 ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ( <replaceable>x2</replaceable> , <replaceable>y2</replaceable> )
3043 <replaceable>x1</replaceable> , <replaceable>y1</replaceable> , <replaceable>x2</replaceable> , <replaceable>y2</replaceable>
3044 </synopsis>
3046 where
3048 and
3050 are the end points of the line segment.
3051 </para>
3052 </sect2>
3054 <sect2>
3057 <indexterm>
3059 </indexterm>
3061 <indexterm>
3063 </indexterm>
3065 <para>
3066 Boxes are represented by pairs of points that are opposite
3067 corners of the box.
3070 <synopsis>
3071 ( ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ( <replaceable>x2</replaceable> , <replaceable>y2</replaceable> ) )
3072 ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ( <replaceable>x2</replaceable> , <replaceable>y2</replaceable> )
3073 <replaceable>x1</replaceable> , <replaceable>y1</replaceable> , <replaceable>x2</replaceable> , <replaceable>y2</replaceable>
3074 </synopsis>
3076 where
3078 and
3080 are any two opposite corners of the box.
3081 </para>
3083 <para>
3084 Boxes are output using the first syntax.
3085 Any two opposite corners can be supplied on input, but the values
3086 will be reordered as needed to store the
3087 upper right and lower left corners.
3088 </para>
3089 </sect2>
3091 <sect2>
3094 <indexterm>
3096 </indexterm>
3098 <para>
3099 Paths are represented by lists of connected points. Paths can be
3101 the first and last points in the list are considered not connected, or
3103 where the first and last points are considered connected.
3104 </para>
3106 <para>
3109 <synopsis>
3110 ( ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ... , ( <replaceable>xn</replaceable> , <replaceable>yn</replaceable> ) )
3111 [ ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ... , ( <replaceable>xn</replaceable> , <replaceable>yn</replaceable> ) ]
3112 ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ... , ( <replaceable>xn</replaceable> , <replaceable>yn</replaceable> )
3113 ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> , ... , <replaceable>xn</replaceable> , <replaceable>yn</replaceable> )
3114 <replaceable>x1</replaceable> , <replaceable>y1</replaceable> , ... , <replaceable>xn</replaceable> , <replaceable>yn</replaceable>
3115 </synopsis>
3117 where the points are the end points of the line segments
3120 closed path.
3121 </para>
3123 <para>
3124 Paths are output using the first or second syntax, as appropriate.
3125 </para>
3126 </sect2>
3128 <sect2>
3131 <indexterm>
3133 </indexterm>
3135 <para>
3136 Polygons are represented by lists of points (the vertexes of the
3137 polygon). Polygons are very similar to closed paths, but are
3138 stored differently
3139 and have their own set of support routines.
3140 </para>
3142 <para>
3145 <synopsis>
3146 ( ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ... , ( <replaceable>xn</replaceable> , <replaceable>yn</replaceable> ) )
3147 ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ... , ( <replaceable>xn</replaceable> , <replaceable>yn</replaceable> )
3148 ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> , ... , <replaceable>xn</replaceable> , <replaceable>yn</replaceable> )
3149 <replaceable>x1</replaceable> , <replaceable>y1</replaceable> , ... , <replaceable>xn</replaceable> , <replaceable>yn</replaceable>
3150 </synopsis>
3152 where the points are the end points of the line segments
3153 comprising the boundary of the polygon.
3154 </para>
3156 <para>
3157 Polygons are output using the first syntax.
3158 </para>
3159 </sect2>
3161 <sect2>
3164 <indexterm>
3166 </indexterm>
3168 <para>
3169 Circles are represented by a center point and radius.
3172 <synopsis>
3173 < ( <replaceable>x</replaceable> , <replaceable>y</replaceable> ) , <replaceable>r</replaceable> >
3174 ( ( <replaceable>x</replaceable> , <replaceable>y</replaceable> ) , <replaceable>r</replaceable> )
3177 </synopsis>
3179 where
3182 </para>
3184 <para>
3185 Circles are output using the first syntax.
3186 </para>
3187 </sect2>
3189 </sect1>
3197 </indexterm>
3199 <para>
3202 is better to use these types instead of plain text types to store
3203 network addresses, because
3204 these types offer input error checking and specialized
3206 </para>
3211 <thead>
3212 <row>
3216 </row>
3217 </thead>
3218 <tbody>
3220 <row>
3224 </row>
3226 <row>
3230 </row>
3232 <row>
3236 </row>
3238 </tbody>
3239 </tgroup>
3240 </table>
3242 <para>
3244 IPv4 addresses will always sort before IPv6 addresses, including
3245 IPv4 addresses encapsulated or mapped to IPv6 addresses, such as
3247 </para>
3253 <indexterm>
3255 </indexterm>
3257 <para>
3259 optionally its subnet, all in one field.
3260 The subnet is represented by the number of network address bits
3261 present in the host address (the
3263 then the value does not indicate a subnet, only a single host.
3265 unique host address. Note that if you
3266 want to accept only networks, you should use the
3268 </para>
3270 <para>
3271 The input format for this type is
3273 where
3275 is an IPv4 or IPv6 address and
3277 is the number of bits in the netmask. If the
3279 portion is missing, the
3281 just a single host. On display, the
3283 portion is suppressed if the netmask specifies a single host.
3284 </para>
3285 </sect2>
3290 <indexterm>
3292 </indexterm>
3294 <para>
3296 Input and output formats follow Classless Internet Domain Routing
3297 conventions.
3298 The format for specifying networks is <replaceable
3301 IPv4 or IPv6 address, and <replaceable
3304 using assumptions from the older classful network numbering system, except
3305 it will be at least large enough to include all of the octets
3306 written in the input. It is an error to specify a network address
3307 that has bits set to the right of the specified netmask.
3308 </para>
3310 <para>
3312 </para>
3317 <thead>
3318 <row>
3322 </row>
3323 </thead>
3324 <tbody>
3325 <row>
3329 </row>
3330 <row>
3334 </row>
3335 <row>
3339 </row>
3340 <row>
3344 </row>
3345 <row>
3349 </row>
3350 <row>
3354 </row>
3355 <row>
3359 </row>
3360 <row>
3364 </row>
3365 <row>
3369 </row>
3370 <row>
3374 </row>
3375 <row>
3379 </row>
3380 <row>
3384 </row>
3385 <row>
3389 </row>
3390 <row>
3394 </row>
3395 <row>
3399 </row>
3400 <row>
3404 </row>
3405 </tbody>
3406 </tgroup>
3407 </table>
3408 </sect2>
3413 <para>
3417 </para>
3419 <tip>
3420 <para>
3424 </para>
3425 </tip>
3426 </sect2>
3431 <indexterm>
3433 </indexterm>
3435 <indexterm>
3438 </indexterm>
3440 <para>
3442 from Ethernet card hardware addresses (although MAC addresses are
3443 used for other purposes as well). Input is accepted in the
3444 following formats:
3446 <simplelist>
3453 </simplelist>
3455 These examples would all specify the same address. Upper and
3456 lower case is accepted for the digits
3458 first of the forms shown.
3459 </para>
3461 <para>
3463 as the canonical form for MAC addresses, and specifies the first
3464 form (with colons) as the bit-reversed notation, so that
3466 ignored nowadays, and it is only relevant for obsolete network
3467 protocols (such as Token Ring). PostgreSQL makes no provisions
3468 for bit reversal, and all accepted formats use the canonical LSB
3469 order.
3470 </para>
3472 <para>
3473 The remaining four input formats are not part of any standard.
3474 </para>
3475 </sect2>
3477 </sect1>
3485 </indexterm>
3487 <para>
3489 or visualize bit masks. There are two SQL bit types:
3493 </para>
3495 <para>
3499 of variable length up to the maximum length
3503 specification means unlimited length.
3504 </para>
3506 <note>
3507 <para>
3508 If one explicitly casts a bit-string value to
3511 without raising an error. Similarly,
3512 if one explicitly casts a bit-string value to
3515 </para>
3516 </note>
3518 <para>
3519 Refer to <xref
3521 of bit string constants. Bit-logical operators and string
3522 manipulation functions are available; see <xref
3524 </para>
3526 <example>
3529 <programlisting>
3533 <computeroutput>
3535 </computeroutput>
3537 SELECT * FROM test;
3538 <computeroutput>
3539 a | b
3540 -----+-----
3543 </computeroutput>
3544 </programlisting>
3545 </example>
3547 <para>
3550 (but long values may be compressed or moved out-of-line, as explained
3552 </para>
3553 </sect1>
3561 </indexterm>
3566 </indexterm>
3568 <para>
3570 are designed to support full text search, which is the activity of
3575 a text query.
3578 related functions and operators.
3579 </para>
3584 <indexterm>
3586 </indexterm>
3588 <para>
3593 duplicate-elimination are done automatically during input, as shown in
3594 this example:
3596 <programlisting>
3597 SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector;
3598 tsvector
3599 ----------------------------------------------------
3600 'a' 'and' 'ate' 'cat' 'fat' 'mat' 'on' 'rat' 'sat'
3601 </programlisting>
3603 To represent
3604 lexemes containing whitespace or punctuation, surround them with quotes:
3606 <programlisting>
3607 SELECT $$the lexeme ' ' contains spaces$$::tsvector;
3608 tsvector
3609 -------------------------------------------
3610 ' ' 'contains' 'lexeme' 'spaces' 'the'
3611 </programlisting>
3613 (We use dollar-quoted string literals in this example and the next one
3614 to avoid the confusion of having to double quote marks within the
3615 literals.) Embedded quotes and backslashes must be doubled:
3617 <programlisting>
3618 SELECT $$the lexeme 'Joe''s' contains a quote$$::tsvector;
3619 tsvector
3620 ------------------------------------------------
3621 'Joe''s' 'a' 'contains' 'lexeme' 'quote' 'the'
3622 </programlisting>
3625 can be attached to lexemes:
3627 <programlisting>
3629 tsvector
3630 -------------------------------------------------------------------------------
3632 </programlisting>
3634 A position normally indicates the source word's location in the
3635 document. Positional information can be used for
3638 Duplicate positions for the same lexeme are discarded.
3639 </para>
3641 <para>
3642 Lexemes that have positions can further be labeled with a
3647 <programlisting>
3649 tsvector
3650 ----------------------------
3652 </programlisting>
3654 Weights are typically used to reflect document structure, for example
3655 by marking title words differently from body words. Text search
3656 ranking functions can assign different priorities to the different
3657 weight markers.
3658 </para>
3660 <para>
3661 It is important to understand that the
3663 it assumes the words it is given are normalized appropriately
3664 for the application. For example,
3666 <programlisting>
3667 select 'The Fat Rats'::tsvector;
3668 tsvector
3669 --------------------
3670 'Fat' 'Rats' 'The'
3671 </programlisting>
3673 For most English-text-searching applications the above words would
3675 Raw document text should usually be passed through
3677 for searching:
3679 <programlisting>
3680 SELECT to_tsvector('english', 'The Fat Rats');
3681 to_tsvector
3682 -----------------
3684 </programlisting>
3687 </para>
3689 </sect2>
3694 <indexterm>
3696 </indexterm>
3698 <para>
3700 searched for, and combines them honoring the boolean operators
3703 of the operators:
3705 <programlisting>
3706 SELECT 'fat & rat'::tsquery;
3707 tsquery
3708 ---------------
3709 'fat' & 'rat'
3711 SELECT 'fat & (rat | cat)'::tsquery;
3712 tsquery
3713 ---------------------------
3714 'fat' & ( 'rat' | 'cat' )
3717 tsquery
3718 ------------------------
3720 </programlisting>
3725 </para>
3727 <para>
3729 one or more weight letters, which restricts them to match only
3732 <programlisting>
3733 SELECT 'fat:ab & cat'::tsquery;
3734 tsquery
3735 ------------------
3736 'fat':AB & 'cat'
3737 </programlisting>
3738 </para>
3740 <para>
3742 to specify prefix matching:
3743 <programlisting>
3744 SELECT 'super:*'::tsquery;
3745 tsquery
3746 -----------
3747 'super':*
3748 </programlisting>
3751 </para>
3753 <para>
3754 Quoting rules for lexemes are the same as described previously for
3756 any required normalization of words must be done before converting
3758 function is convenient for performing such normalization:
3760 <programlisting>
3761 SELECT to_tsquery('Fat:ab & Cats');
3762 to_tsquery
3763 ------------------
3764 'fat':AB & 'cat'
3765 </programlisting>
3766 </para>
3768 </sect2>
3770 </sect1>
3777 </indexterm>
3779 <para>
3782 (Some systems refer to this data type as a globally unique identifier, or
3784 identifier is a 128-bit quantity that is generated by an algorithm chosen
3785 to make it very unlikely that the same identifier will be generated by
3786 anyone else in the known universe using the same algorithm. Therefore,
3787 for distributed systems, these identifiers provide a better uniqueness
3788 guarantee than sequence generators, which
3789 are only unique within a single database.
3790 </para>
3792 <para>
3793 A UUID is written as a sequence of lower-case hexadecimal digits,
3794 in several groups separated by hyphens, specifically a group of 8
3795 digits followed by three groups of 4 digits followed by a group of
3797 example of a UUID in this standard form is:
3798 <programlisting>
3800 </programlisting>
3802 alternative forms for input:
3803 use of upper-case digits, the standard format surrounded by
3804 braces, omitting some or all hyphens, adding a hyphen after any
3805 group of four digits. Examples are:
3806 <programlisting>
3809 a0eebc999c0b4ef8bb6d6bb9bd380a11
3811 {a0eebc99-9c0b4ef8-bb6d6bb9-bd380a11}
3812 </programlisting>
3813 Output is always in the standard form.
3814 </para>
3816 <para>
3818 functions for UUIDs, but the core database does not include any
3819 function for generating UUIDs, because no single algorithm is well
3820 suited for every application. The contrib module
3822 several standard algorithms.
3823 Alternatively, UUIDs could be generated by client applications or
3824 other libraries invoked through a server-side function.
3825 </para>
3826 </sect1>
3833 </indexterm>
3835 <para>
3838 checks the input values for well-formedness, and there are support
3839 functions to perform type-safe operations on it; see <xref
3841 installation to have been built with <command>configure
3842 --with-libxml</>.
3843 </para>
3845 <para>
3850 standard. Roughly, this means that content fragments can have
3851 more than one top-level element or character node. The expression
3854 value is a full document or only a content fragment.
3855 </para>
3857 <sect2>
3859 <para>
3861 use the function
3863 <synopsis>
3865 </synopsis>
3866 Examples:
3867 <programlisting><![CDATA[
3868 XMLPARSE (DOCUMENT '<?xml version="1.0"?><book><title>Manual</title><chapter>...</chapter></book>')
3870 ]]></programlisting>
3871 While this is the only way to convert character strings into XML
3872 values according to the SQL standard, the PostgreSQL-specific
3873 syntaxes:
3874 <programlisting><![CDATA[
3877 ]]></programlisting>
3878 can also be used.
3879 </para>
3881 <para>
3883 against a document type declaration
3885 even when the input value specifies a DTD.
3886 </para>
3888 <para>
3889 The inverse operation, producing a character string value from
3892 <synopsis>
3893 XMLSERIALIZE ( { DOCUMENT | CONTENT } <replaceable>value</replaceable> AS <replaceable>type</replaceable> )
3894 </synopsis>
3898 to the SQL standard, this is the only way to convert between type
3900 you to simply cast the value.
3901 </para>
3903 <para>
3904 When a character string value is cast to or from type
3910 session configuration parameter, which can be set using the
3911 standard command:
3912 <synopsis>
3913 SET XML OPTION { DOCUMENT | CONTENT };
3914 </synopsis>
3915 or the more PostgreSQL-like syntax
3916 <synopsis>
3917 SET xmloption TO { DOCUMENT | CONTENT };
3918 </synopsis>
3920 data are allowed.
3921 </para>
3922 </sect2>
3924 <sect2>
3926 <para>
3927 Care must be taken when dealing with multiple character encodings
3928 on the client, server, and in the XML data passed through them.
3929 When using the text mode to pass queries to the server and query
3930 results to the client (which is the normal mode), PostgreSQL
3931 converts all character data passed between the client and the
3932 server and vice versa to the character encoding of the respective
3934 representations of XML values, such as in the above examples.
3935 This would ordinarily mean that encoding declarations contained in
3936 XML data can become invalid as the character data is converted
3937 to other encodings while travelling between client and server,
3938 because the embedded encoding declaration is not changed. To cope
3939 with this behavior, encoding declarations contained in
3942 to be in the current server encoding. Consequently, for correct
3943 processing, character strings of XML data must be sent
3944 from the client in the current client encoding. It is the
3945 responsibility of the client to either convert documents to the
3946 current client encoding before sending them to the server, or to
3947 adjust the client encoding appropriately. On output, values of
3949 clients should assume all data is in the current client
3950 encoding.
3951 </para>
3953 <para>
3954 When using binary mode to pass query parameters to the server
3955 and query results back to the client, no character set conversion
3956 is performed, so the situation is different. In this case, an
3957 encoding declaration in the XML data will be observed, and if it
3958 is absent, the data will be assumed to be in UTF-8 (as required by
3959 the XML standard; note that PostgreSQL does not support UTF-16).
3960 On output, data will have an encoding declaration
3961 specifying the client encoding, unless the client encoding is
3962 UTF-8, in which case it will be omitted.
3963 </para>
3965 <para>
3966 Needless to say, processing XML data with PostgreSQL will be less
3967 error-prone and more efficient if the XML data encoding, client encoding,
3968 and server encoding are the same. Since XML data is internally
3969 processed in UTF-8, computations will be most efficient if the
3970 server encoding is also UTF-8.
3971 </para>
3973 <caution>
3974 <para>
3975 Some XML-related functions may not work at all on non-ASCII data
3976 when the server encoding is not UTF-8. This is known to be an
3978 </para>
3979 </caution>
3980 </sect2>
3982 <sect2>
3985 <para>
3987 provide any comparison operators. This is because there is no
3988 well-defined and universally useful comparison algorithm for XML
3989 data. One consequence of this is that you cannot retrieve rows by
3991 values should therefore typically be accompanied by a separate key
3992 field such as an ID. An alternative solution for comparing XML
3993 values is to convert them to character strings first, but note
3994 that character string comparison has little to do with a useful
3995 XML comparison method.
3996 </para>
3998 <para>
4000 data type, it is not possible to create an index directly on a
4001 column of this type. If speedy searches in XML data are desired,
4002 possible workarounds include casting the expression to a
4003 character string type and indexing that, or indexing an XPath
4004 expression. Of course, the actual query would have to be adjusted
4005 to search by the indexed expression.
4006 </para>
4008 <para>
4009 The text-search functionality in PostgreSQL can also be used to speed
4010 up full-document searches of XML data. The necessary
4011 preprocessing support is, however, not yet available in the PostgreSQL
4012 distribution.
4013 </para>
4014 </sect2>
4015 </sect1>
4017 &array;
4019 &rowtypes;
4027 </indexterm>
4031 </indexterm>
4035 </indexterm>
4039 </indexterm>
4043 </indexterm>
4047 </indexterm>
4051 </indexterm>
4055 </indexterm>
4059 </indexterm>
4063 </indexterm>
4067 </indexterm>
4071 </indexterm>
4075 </indexterm>
4077 <para>
4078 Object identifiers (OIDs) are used internally by
4080 system tables. OIDs are not added to user-created tables, unless
4084 an object identifier. There are also several alias types for
4089 </para>
4091 <para>
4093 four-byte integer. Therefore, it is not large enough to provide
4094 database-wide uniqueness in large databases, or even in large
4095 individual tables. So, using a user-created table's OID column as
4096 a primary key is discouraged. OIDs are best used only for
4097 references to system tables.
4098 </para>
4100 <para>
4102 It can be cast to integer, however, and then manipulated using the
4103 standard integer operators. (Beware of possible
4104 signed-versus-unsigned confusion if you do this.)
4105 </para>
4107 <para>
4108 The OID alias types have no operations of their own except
4109 for specialized input and output routines. These routines are able
4110 to accept and display symbolic names for system objects, rather than
4112 types allow simplified lookup of OID values for objects. For example,
4115 <programlisting>
4116 SELECT * FROM pg_attribute WHERE attrelid = 'mytable'::regclass;
4117 </programlisting>
4118 rather than:
4119 <programlisting>
4120 SELECT * FROM pg_attribute
4121 WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'mytable');
4122 </programlisting>
4123 While that doesn't look all that bad by itself, it's still oversimplified.
4124 A far more complicated sub-select would be needed to
4125 select the right OID if there are multiple tables named
4129 automatically. Similarly, casting a table's OID to
4131 </para>
4136 <thead>
4137 <row>
4142 </row>
4143 </thead>
4145 <tbody>
4147 <row>
4152 </row>
4154 <row>
4159 </row>
4161 <row>
4166 </row>
4168 <row>
4173 </row>
4175 <row>
4180 </row>
4182 <row>
4187 </row>
4189 <row>
4194 </row>
4196 <row>
4201 </row>
4203 <row>
4208 </row>
4209 </tbody>
4210 </tgroup>
4211 </table>
4213 <para>
4214 All of the OID alias types accept schema-qualified names, and will
4215 display schema-qualified names on output if the object would not
4216 be found in the current search path without being qualified.
4218 accept input names that are unique (not overloaded), so they are
4222 operand.
4223 </para>
4225 <para>
4226 An additional property of the OID alias types is the creation of
4227 dependencies. If a
4228 constant of one of these types appears in a stored expression
4229 (such as a column default expression or view), it creates a dependency
4230 on the referenced object. For example, if a column has a default
4233 understands that the default expression depends on the sequence
4235 without first removing the default expression.
4236 </para>
4238 <para>
4242 </para>
4244 <para>
4246 command identifier. This is the data type of the system columns
4248 </para>
4250 <para>
4252 identifier (row identifier). This is the data type of the system column
4254 (block number, tuple index within block) that identifies the
4255 physical location of the row within its table.
4256 </para>
4258 <para>
4259 (The system columns are further explained in <xref
4261 </para>
4262 </sect1>
4269 </indexterm>
4273 </indexterm>
4277 </indexterm>
4281 </indexterm>
4285 </indexterm>
4289 </indexterm>
4293 </indexterm>
4297 </indexterm>
4301 </indexterm>
4305 </indexterm>
4309 </indexterm>
4313 </indexterm>
4315 <para>
4317 number of special-purpose entries that are collectively called
4319 column data type, but it can be used to declare a function's
4320 argument or result type. Each of the available pseudo-types is
4321 useful in situations where a function's behavior does not
4322 correspond to simply taking or returning a value of a specific
4325 pseudo-types.
4326 </para>
4331 <thead>
4332 <row>
4335 </row>
4336 </thead>
4338 <tbody>
4339 <row>
4342 </row>
4344 <row>
4346 <entry>Indicates that a function accepts any array data type
4348 </row>
4350 <row>
4352 <entry>Indicates that a function accepts any data type
4354 </row>
4356 <row>
4358 <entry>Indicates that a function accepts any enum data type
4361 </row>
4363 <row>
4365 <entry>Indicates that a function accepts any non-array data type
4367 </row>
4369 <row>
4372 </row>
4374 <row>
4376 <entry>Indicates that a function accepts or returns a server-internal
4377 data type.</entry>
4378 </row>
4380 <row>
4382 <entry>A procedural language call handler is declared to return <type>language_handler</>.</entry>
4383 </row>
4385 <row>
4388 </row>
4390 <row>
4393 </row>
4395 <row>
4398 </row>
4400 <row>
4403 </row>
4404 </tbody>
4405 </tgroup>
4406 </table>
4408 <para>
4409 Functions coded in C (whether built-in or dynamically loaded) can be
4410 declared to accept or return any of these pseudo data types. It is up to
4411 the function author to ensure that the function will behave safely
4412 when a pseudo-type is used as an argument type.
4413 </para>
4415 <para>
4416 Functions coded in procedural languages can use pseudo-types only as
4417 allowed by their implementation languages. At present the procedural
4418 languages all forbid use of a pseudo-type as argument type, and allow
4423 </para>
4425 <para>
4427 that are meant only to be called internally by the database
4431 preserve the type safety of this restriction it is important to
4432 follow this coding rule: do not create any function that is
4435 </para>
4437 </sect1>
4439 </chapter>