| blob | 3e6751d64cc70f6b5d5b44fd62ce8d6db1cede15 |
1 <!-- doc/src/sgml/datatype.sgml -->
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>
37 <thead>
38 <row>
42 </row>
43 </thead>
45 <tbody>
46 <row>
50 </row>
52 <row>
56 </row>
58 <row>
60 <entry></entry>
62 </row>
64 <row>
68 </row>
70 <row>
74 </row>
76 <row>
78 <entry></entry>
80 </row>
82 <row>
84 <entry></entry>
86 </row>
88 <row>
92 </row>
94 <row>
98 </row>
100 <row>
102 <entry></entry>
104 </row>
106 <row>
108 <entry></entry>
110 </row>
112 <row>
114 <entry></entry>
116 </row>
118 <row>
122 </row>
124 <row>
126 <entry></entry>
128 </row>
130 <row>
134 </row>
136 <row>
137 <entry><type>interval [ <replaceable>fields</replaceable> ] [ (<replaceable>p</replaceable>) ]</type></entry>
138 <entry></entry>
140 </row>
142 <row>
144 <entry></entry>
146 </row>
148 <row>
150 <entry></entry>
152 </row>
154 <row>
156 <entry></entry>
158 </row>
160 <row>
162 <entry></entry>
164 </row>
166 <row>
168 <entry></entry>
170 </row>
172 <row>
174 <entry></entry>
176 </row>
178 <row>
180 <entry></entry>
182 </row>
184 <row>
190 </row>
192 <row>
194 <entry></entry>
196 </row>
198 <row>
200 <entry></entry>
202 </row>
204 <row>
206 <entry></entry>
208 </row>
210 <row>
212 <entry></entry>
214 </row>
216 <row>
218 <entry></entry>
220 </row>
222 <row>
226 </row>
228 <row>
232 </row>
234 <row>
238 </row>
240 <row>
244 </row>
246 <row>
248 <entry></entry>
250 </row>
252 <row>
254 <entry></entry>
256 </row>
258 <row>
262 </row>
264 <row>
266 <entry></entry>
268 </row>
270 <row>
274 </row>
276 <row>
278 <entry></entry>
280 </row>
282 <row>
284 <entry></entry>
286 </row>
288 <row>
290 <entry></entry>
292 </row>
294 <row>
296 <entry></entry>
298 </row>
300 <row>
302 <entry></entry>
304 </row>
305 </tbody>
306 </tgroup>
307 </table>
309 <note>
311 <para>
312 The following types (or spellings thereof) are specified by
322 </para>
323 </note>
325 <para>
326 Each data type has an external representation determined by its input
327 and output functions. Many of the built-in types have
328 obvious external formats. However, several types are either unique
330 paths, or have several possible formats, such as the date
331 and time types.
332 Some of the input and output functions are not invertible, i.e.,
333 the result of an output function might lose accuracy when compared to
334 the original input.
335 </para>
343 </indexterm>
345 <para>
346 Numeric types consist of two-, four-, and eight-byte integers,
347 four- and eight-byte floating-point numbers, and selectable-precision
349 available types.
350 </para>
359 <thead>
360 <row>
365 </row>
366 </thead>
368 <tbody>
369 <row>
374 </row>
375 <row>
380 </row>
381 <row>
386 </row>
388 <row>
392 <entry>up to 131072 digits before the decimal point; up to 16383 digits after the decimal point</entry>
393 </row>
394 <row>
398 <entry>up to 131072 digits before the decimal point; up to 16383 digits after the decimal point</entry>
399 </row>
401 <row>
406 </row>
407 <row>
412 </row>
414 <row>
419 </row>
421 <row>
426 </row>
428 <row>
433 </row>
434 </tbody>
435 </tgroup>
436 </table>
438 <para>
439 The syntax of constants for the numeric types is described in
441 full set of corresponding arithmetic operators and
443 information. The following sections describe the types in detail.
444 </para>
451 </indexterm>
455 </indexterm>
459 </indexterm>
461 <indexterm>
464 </indexterm>
466 <indexterm>
469 </indexterm>
471 <indexterm>
474 </indexterm>
476 <para>
479 fractional components, of various ranges. Attempts to store
480 values outside of the allowed range will result in an error.
481 </para>
483 <para>
485 the best balance between range, storage size, and performance.
489 </para>
491 <para>
498 </para>
500 </sect2>
505 <indexterm>
507 </indexterm>
509 <indexterm>
511 </indexterm>
513 <indexterm>
516 </indexterm>
518 <para>
520 very large number of digits. It is especially recommended for
521 storing monetary amounts and other quantities where exactness is
523 results where possible, e.g., addition, subtraction, multiplication.
525 compared to the integer types, or to the floating-point types
526 described in the next section.
527 </para>
529 <para>
530 We use the following terms below: The
532 is the total count of significant digits in the whole number,
533 that is, the number of digits to both sides of the decimal point.
535 count of decimal digits in the fractional part, to the right of the
537 scale of 4. Integers can be considered to have a scale of zero.
538 </para>
540 <para>
541 Both the maximum precision and the maximum scale of a
544 the syntax:
545 <programlisting>
547 </programlisting>
548 The precision must be positive, while the scale may be positive or
549 negative (see below). Alternatively:
550 <programlisting>
552 </programlisting>
553 selects a scale of 0. Specifying:
554 <programlisting>
555 NUMERIC
556 </programlisting>
557 without any precision or scale creates an <quote>unconstrained
558 numeric</quote> column in which numeric values of any length can be
559 stored, up to the implementation limits. A column of this kind will
560 not coerce input values to any particular scale, whereas
563 requires a default scale of 0, i.e., coercion to integer
564 precision. We find this a bit useless. If you're concerned
565 about portability, always specify the precision and scale
566 explicitly.)
567 </para>
569 <note>
570 <para>
571 The maximum precision that can be explicitly specified in
575 </para>
576 </note>
578 <para>
579 If the scale of a value to be stored is greater than the declared
580 scale of the column, the system will round the value to the specified
581 number of fractional digits. Then, if the number of digits to the
582 left of the decimal point exceeds the declared precision minus the
583 declared scale, an error is raised.
584 For example, a column declared as
585 <programlisting>
587 </programlisting>
588 will round values to 1 decimal place and can store values between
590 </para>
592 <para>
595 values will be rounded to the left of the decimal point. The
596 precision still represents the maximum number of non-rounded digits.
597 Thus, a column declared as
598 <programlisting>
600 </programlisting>
601 will round values to the nearest thousand and can store values
603 It is also allowed to declare a scale larger than the declared
604 precision. Such a column can only hold fractional values, and it
605 requires the number of zero digits just to the right of the decimal
606 point to be at least the declared scale minus the declared precision.
607 For example, a column declared as
608 <programlisting>
610 </programlisting>
611 will round values to 5 decimal places and can store values between
613 </para>
615 <note>
616 <para>
621 Using scales outside that range may not be portable to other database
622 systems.
623 </para>
624 </note>
626 <para>
627 Numeric values are physically stored without any extra leading or
628 trailing zeroes. Thus, the declared precision and scale of a column
632 requirement is two bytes for each group of four decimal digits,
633 plus three to eight bytes overhead.
634 </para>
636 <indexterm>
639 </indexterm>
641 <indexterm>
644 </indexterm>
646 <indexterm>
649 </indexterm>
651 <para>
653 has several special values:
654 <literallayout>
658 </literallayout>
659 These are adapted from the IEEE 754 standard, and represent
662 as constants in an SQL command, you must put quotes around them,
664 On input, these strings are recognized in a case-insensitive manner.
667 </para>
669 <para>
670 The infinity values behave as per mathematical expectations. For
675 number), because it has no well-defined interpretation. Note that an
677 column, because it notionally exceeds any finite precision limit.
678 </para>
680 <para>
682 undefined calculational results. In general, any operation with
684 The only exception is when the operation's other inputs are such that
686 be replaced by any finite or infinite numeric value; then, that output
689 yields one.)
690 </para>
692 <note>
693 <para>
700 values.
701 </para>
702 </note>
704 <para>
707 standard.
708 </para>
710 <para>
714 number. For example:
716 <programlisting>
717 SELECT x,
718 round(x::numeric) AS num_round,
719 round(x::double precision) AS dbl_round
721 x | num_round | dbl_round
722 ------+-----------+-----------
731 (8 rows)
732 </programlisting>
733 </para>
734 </sect2>
742 </indexterm>
746 </indexterm>
748 <indexterm>
751 </indexterm>
753 <indexterm>
756 </indexterm>
760 </indexterm>
762 <para>
764 inexact, variable-precision numeric types. On all currently supported
766 Standard 754 for Binary Floating-Point Arithmetic (single and double
767 precision, respectively), to the extent that the underlying processor,
768 operating system, and compiler support it.
769 </para>
771 <para>
772 Inexact means that some values cannot be converted exactly to the
773 internal format and are stored as approximations, so that storing
774 and retrieving a value might show slight discrepancies.
775 Managing these errors and how they propagate through calculations
776 is the subject of an entire branch of mathematics and computer
777 science and will not be discussed here, except for the
778 following points:
779 <itemizedlist>
780 <listitem>
781 <para>
782 If you require exact storage and calculations (such as for
784 </para>
785 </listitem>
787 <listitem>
788 <para>
789 If you want to do complicated calculations with these types
790 for anything important, especially if you rely on certain
791 behavior in boundary cases (infinity, underflow), you should
792 evaluate the implementation carefully.
793 </para>
794 </listitem>
796 <listitem>
797 <para>
798 Comparing two floating-point values for equality might not
799 always work as expected.
800 </para>
801 </listitem>
802 </itemizedlist>
803 </para>
805 <para>
810 too large or too small will cause an error. Rounding might take place if
811 the precision of an input number is too high. Numbers too close to zero
812 that are not representable as distinct from zero will cause an underflow
813 error.
814 </para>
816 <para>
817 By default, floating point values are output in text form in their
818 shortest precise decimal representation; the decimal value produced is
819 closer to the true stored binary value than to any other value
820 representable in the same binary precision. (However, the output value is
822 representable values, in order to avoid a widespread bug where input
823 routines do not properly respect the round-to-nearest-even rule.) This value will
826 </para>
828 <note>
829 <para>
830 This shortest-precise output format is much faster to generate than the
831 historical rounded format.
832 </para>
833 </note>
835 <para>
836 For compatibility with output generated by older versions
839 parameter can be used to select rounded decimal output instead. Setting a
842 significant decimal digits. Setting a negative value reduces the number
844 respectively.
845 </para>
847 <para>
849 selects the shortest-precise format.
850 </para>
852 <note>
853 <para>
854 Applications that wanted precise values have historically had to set
856 maximum compatibility between versions, they should continue to do so.
857 </para>
858 </note>
860 <indexterm>
863 </indexterm>
865 <indexterm>
868 </indexterm>
870 <para>
871 In addition to ordinary numeric values, the floating-point types
872 have several special values:
873 <literallayout>
877 </literallayout>
878 These represent the IEEE 754 special values
881 as constants in an SQL command, you must put quotes around them,
883 these strings are recognized in a case-insensitive manner.
886 </para>
888 <note>
889 <para>
892 In order to allow floating-point values to be sorted and used
896 </para>
897 </note>
899 <para>
910 outside the allowed range draw an error.
913 </para>
915 </sect2>
922 </indexterm>
926 </indexterm>
930 </indexterm>
934 </indexterm>
938 </indexterm>
942 </indexterm>
944 <indexterm>
947 </indexterm>
949 <indexterm>
952 </indexterm>
954 <note>
955 <para>
956 This section describes a PostgreSQL-specific way to create an
957 autoincrementing column. Another way is to use the SQL-standard
959 </para>
960 </note>
962 <para>
965 a notational convenience for creating unique identifier columns
967 supported by some other databases). In the current
968 implementation, specifying:
970 <programlisting>
973 );
974 </programlisting>
976 is equivalent to specifying:
978 <programlisting>
979 CREATE SEQUENCE <replaceable class="parameter">tablename</replaceable>_<replaceable class="parameter">colname</replaceable>_seq AS integer;
981 <replaceable class="parameter">colname</replaceable> integer NOT NULL DEFAULT nextval('<replaceable class="parameter">tablename</replaceable>_<replaceable class="parameter">colname</replaceable>_seq')
982 );
983 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>;
984 </programlisting>
986 Thus, we have created an integer column and arranged for its default
988 constraint is applied to ensure that a null value cannot be
989 inserted. (In most cases you would also want to attach a
991 duplicate values from being inserted by accident, but this is
993 the column, so that it will be dropped if the column or table is dropped.
994 </para>
996 <note>
997 <para>
1000 be "holes" or gaps in the sequence of values which appears in the
1001 column, even if no rows are ever deleted. A value allocated
1002 from the sequence is still "used up" even if a row containing that
1003 value is never successfully inserted into the table column. This
1004 may happen, for example, if the inserting transaction rolls back.
1006 for details.
1007 </para>
1008 </note>
1010 <para>
1013 column should be assigned its default value. This can be done
1014 either by excluding the column from the list of columns in
1017 </para>
1019 <para>
1029 </para>
1031 <para>
1033 automatically dropped when the owning column is dropped.
1034 You can drop the sequence without dropping the column, but this
1035 will force removal of the column default expression.
1036 </para>
1037 </sect2>
1038 </sect1>
1043 <para>
1045 fractional precision; see <xref
1048 The range shown in the table assumes there are two fractional digits.
1049 Input is accepted in a variety of formats, including integer and
1050 floating-point literals, as well as typical
1052 Output is generally in the latter form but depends on the locale.
1053 </para>
1062 <thead>
1063 <row>
1068 </row>
1069 </thead>
1070 <tbody>
1071 <row>
1076 </row>
1077 </tbody>
1078 </tgroup>
1079 </table>
1081 <para>
1082 Since the output of this data type is locale-sensitive, it might not
1086 the same or equivalent value as in the database that was dumped.
1087 </para>
1089 <para>
1094 example:
1095 <programlisting>
1096 SELECT '12.34'::float8::numeric::money;
1097 </programlisting>
1098 However, this is not recommended. Floating point numbers should not be
1099 used to handle money due to the potential for rounding errors.
1100 </para>
1102 <para>
1104 loss of precision. Conversion to other types could potentially lose
1105 precision, and must also be done in two stages:
1106 <programlisting>
1107 SELECT '52093.89'::money::numeric::float8;
1108 </programlisting>
1109 </para>
1111 <para>
1113 with truncation of the fractional part towards zero. To get a rounded
1116 afterwards. (The latter is preferable to avoid risking precision loss.)
1119 not money); the currency units cancel each other out in the division.
1120 </para>
1121 </sect1>
1130 </indexterm>
1132 <indexterm>
1135 </indexterm>
1139 </indexterm>
1143 </indexterm>
1147 </indexterm>
1151 </indexterm>
1155 </indexterm>
1159 </indexterm>
1164 <thead>
1165 <row>
1168 </row>
1169 </thead>
1170 <tbody>
1171 <row>
1172 <entry><type>character varying(<replaceable>n</replaceable>)</type>, <type>varchar(<replaceable>n</replaceable>)</type></entry>
1174 </row>
1175 <row>
1176 <entry><type>character(<replaceable>n</replaceable>)</type>, <type>char(<replaceable>n</replaceable>)</type>, <type>bpchar(<replaceable>n</replaceable>)</type></entry>
1178 </row>
1179 <row>
1182 </row>
1183 <row>
1186 </row>
1187 </tbody>
1188 </tgroup>
1189 </table>
1191 <para>
1193 general-purpose character types available in
1195 </para>
1197 <para>
1201 is a positive integer. Both of these types can store strings up to
1203 longer string into a column of these types will result in an
1204 error, unless the excess characters are all spaces, in which case
1205 the string will be truncated to the maximum length. (This somewhat
1207 standard.)
1208 However, if one explicitly casts a value to <type>character
1212 raising an error. (This too is required by the
1214 If the string to be stored is shorter than the declared
1217 shorter
1218 string.
1219 </para>
1221 <para>
1226 management systems have it as well.
1228 string data type, in that most built-in functions operating on strings
1233 </para>
1235 <para>
1242 </para>
1244 <para>
1248 length specifier, the type accepts strings of any length. If
1250 of any length, but trailing spaces are semantically insignificant.
1253 </para>
1255 <para>
1258 stored and displayed that way. However, trailing spaces are treated as
1259 semantically insignificant and disregarded when comparing two values
1261 is significant, this behavior can produce unexpected results;
1264 locale would consider a space to be greater than a newline.
1266 to one of the other string types. Note that trailing spaces
1270 regular expressions.
1271 </para>
1273 <para>
1274 The characters that can be stored in any of these data types are
1275 determined by the database character set, which is selected when
1276 the database is created. Regardless of the specific character set,
1277 the character with code zero (sometimes called NUL) cannot be stored.
1279 </para>
1281 <para>
1283 plus the actual string, which includes the space padding in the case of
1285 of 1. Long strings are compressed by the system automatically, so
1286 the physical requirement on disk might be less. Very long values are also
1287 stored in background tables so that they do not interfere with rapid
1288 access to shorter column values. In any case, the longest
1289 possible character string that can be stored is about 1 GB. (The
1291 type declaration is less than that. It wouldn't be useful to
1292 change this because with multibyte character encodings the number of
1293 characters and bytes can be quite different. If you desire to
1294 store long strings with no specific upper limit, use
1296 specifier, rather than making up an arbitrary length limit.)
1297 </para>
1299 <tip>
1300 <para>
1301 There is no performance difference among these three types,
1302 apart from increased storage space when using the blank-padded
1303 type, and a few extra CPU cycles to check the length when storing into
1304 a length-constrained column. While
1306 advantages in some other database systems, there is no such advantage in
1309 the three because of its additional storage costs. In most situations
1311 instead.
1312 </para>
1313 </tip>
1315 <para>
1318 for information about available operators and functions.
1319 </para>
1321 <example>
1324 <programlisting>
1325 CREATE TABLE test1 (a character(4));
1326 INSERT INTO test1 VALUES ('ok');
1328 <computeroutput>
1329 a | char_length
1330 ------+-------------
1331 ok | 2
1332 </computeroutput>
1334 CREATE TABLE test2 (b varchar(5));
1335 INSERT INTO test2 VALUES ('ok');
1336 INSERT INTO test2 VALUES ('good ');
1337 INSERT INTO test2 VALUES ('too long');
1339 INSERT INTO test2 VALUES ('too long'::varchar(5)); -- explicit truncation
1340 SELECT b, char_length(b) FROM test2;
1341 <computeroutput>
1342 b | char_length
1343 -------+-------------
1344 ok | 2
1345 good | 5
1346 too l | 5
1347 </computeroutput>
1348 </programlisting>
1349 <calloutlist>
1351 <para>
1354 </para>
1355 </callout>
1356 </calloutlist>
1357 </example>
1359 <para>
1360 There are two other fixed-length character types in
1363 These are not intended for general-purpose use, only for use
1364 in the internal system catalogs.
1367 terminator) but should be referenced using the constant
1369 The length is set at compile time (and
1370 is therefore adjustable for special uses); the default maximum
1373 only uses one byte of storage, and therefore can store only a single
1374 ASCII character. It is used in the system
1375 catalogs as a simplistic enumeration type.
1376 </para>
1381 <thead>
1382 <row>
1386 </row>
1387 </thead>
1388 <tbody>
1389 <row>
1393 </row>
1394 <row>
1398 </row>
1399 </tbody>
1400 </tgroup>
1401 </table>
1403 </sect1>
1410 </indexterm>
1414 </indexterm>
1416 <para>
1419 </para>
1427 <thead>
1428 <row>
1432 </row>
1433 </thead>
1434 <tbody>
1435 <row>
1439 </row>
1440 </tbody>
1441 </tgroup>
1442 </table>
1444 <para>
1445 A binary string is a sequence of octets (or bytes). Binary
1446 strings are distinguished from character strings in two
1447 ways. First, binary strings specifically allow storing
1450 Character strings disallow zero octets, and also disallow any
1451 other octet values and sequences of octet values that are invalid
1452 according to the database's selected character set encoding.
1453 Second, operations on binary strings process the actual bytes,
1454 whereas the processing of character strings depends on locale settings.
1455 In short, binary strings are appropriate for storing data that the
1457 strings are appropriate for storing text.
1458 </para>
1460 <para>
1465 of these are always accepted on input. The output format depends
1467 the default is hex. (Note that the hex format was introduced in
1469 tools don't understand it.)
1470 </para>
1472 <para>
1475 OBJECT</type>. The input format is different from
1477 mostly the same.
1478 </para>
1483 <para>
1485 per byte, most significant nibble first. The entire string is
1487 from the escape format). In some contexts, the initial backslash may
1488 need to be escaped by doubling it
1490 For input, the hexadecimal digits can
1491 be either upper or lower case, and whitespace is permitted between
1492 digit pairs (but not within a digit pair nor in the starting
1494 The hex format is compatible with a wide
1495 range of external applications and protocols, and it tends to be
1496 faster to convert than the escape format, so its use is preferred.
1497 </para>
1499 <para>
1500 Example:
1501 <programlisting>
1502 SET bytea_output = 'hex';
1504 SELECT '\xDEADBEEF'::bytea;
1505 bytea
1506 ------------
1507 \xdeadbeef
1508 </programlisting>
1509 </para>
1510 </sect2>
1515 <para>
1518 type. It
1519 takes the approach of representing a binary string as a sequence
1520 of ASCII characters, while converting those bytes that cannot be
1521 represented as an ASCII character into special escape sequences.
1522 If, from the point of view of the application, representing bytes
1523 as characters makes sense, then this representation can be
1524 convenient. But in practice it is usually confusing because it
1525 fuzzes up the distinction between binary strings and character
1526 strings, and also the particular escape mechanism that was chosen is
1527 somewhat unwieldy. Therefore, this format should probably be avoided
1528 for most new applications.
1529 </para>
1531 <para>
1533 octets of certain
1536 general, to escape an octet, convert it into its three-digit
1537 octal value and precede it by a backslash.
1538 Backslash itself (octet decimal value 92) can alternatively be represented by
1539 double backslashes.
1541 shows the characters that must be escaped, and gives the alternative
1542 escape sequences where applicable.
1543 </para>
1553 <thead>
1554 <row>
1560 </row>
1561 </thead>
1563 <tbody>
1564 <row>
1570 </row>
1572 <row>
1578 </row>
1580 <row>
1586 </row>
1588 <row>
1594 </row>
1596 </tbody>
1597 </tgroup>
1598 </table>
1600 <para>
1602 varies depending on locale settings. In some instances you can get away
1603 with leaving them unescaped.
1604 </para>
1606 <para>
1607 The reason that single quotes must be doubled, as shown
1609 is true for any string literal in an SQL command. The generic
1610 string-literal parser consumes the outermost single quotes
1611 and reduces any pair of single quotes to one data character.
1613 single quote, which it treats as a plain data character.
1615 backslashes as special, and the other behaviors shown in
1617 that function.
1618 </para>
1620 <para>
1621 In some contexts, backslashes must be doubled compared to what is
1622 shown above, because the generic string-literal parser will also
1623 reduce pairs of backslashes to one data character;
1625 </para>
1627 <para>
1632 equivalent three-digit octal value and preceded by one backslash.
1634 representation in the client character set, e.g.:
1636 <programlisting>
1637 SET bytea_output = 'escape';
1640 bytea
1641 ----------------
1642 abc klm *\251T
1643 </programlisting>
1645 The octet with decimal value 92 (backslash) is doubled in the output.
1647 </para>
1657 <thead>
1658 <row>
1664 </row>
1665 </thead>
1667 <tbody>
1669 <row>
1675 </row>
1677 <row>
1683 </row>
1685 <row>
1691 </row>
1693 </tbody>
1694 </tgroup>
1695 </table>
1697 <para>
1699 you might have additional work to do in terms of escaping and
1701 have to escape line feeds and carriage returns if your interface
1702 automatically translates these.
1703 </para>
1704 </sect2>
1705 </sect1>
1713 </indexterm>
1716 </indexterm>
1719 </indexterm>
1722 </indexterm>
1725 </indexterm>
1728 </indexterm>
1731 </indexterm>
1734 </indexterm>
1737 </indexterm>
1740 </indexterm>
1742 <para>
1746 on these data types are described in
1748 Dates are counted according to the Gregorian calendar, even in
1749 years before that calendar was introduced (see <xref
1751 </para>
1756 <thead>
1757 <row>
1764 </row>
1765 </thead>
1766 <tbody>
1767 <row>
1774 </row>
1775 <row>
1782 </row>
1783 <row>
1790 </row>
1791 <row>
1798 </row>
1799 <row>
1803 <!-- see MAX_TZDISP_HOUR in datatype/timestamp.h -->
1807 </row>
1808 <row>
1809 <entry><type>interval [ <replaceable>fields</replaceable> ] [ (<replaceable>p</replaceable>) ]</type></entry>
1815 </row>
1816 </tbody>
1817 </tgroup>
1818 </table>
1820 <note>
1821 <para>
1823 be equivalent to <type>timestamp without time
1828 </para>
1829 </note>
1831 <para>
1835 fractional digits retained in the seconds field. By default, there
1836 is no explicit bound on precision. The allowed range of
1838 </para>
1840 <para>
1842 to restrict the set of stored fields by writing one of these phrases:
1844 YEAR
1845 MONTH
1846 DAY
1847 HOUR
1848 MINUTE
1849 SECOND
1850 YEAR TO MONTH
1851 DAY TO HOUR
1852 DAY TO MINUTE
1853 DAY TO SECOND
1854 HOUR TO MINUTE
1855 HOUR TO SECOND
1856 MINUTE TO SECOND
1857 </literallayout>
1861 since the precision applies only to the seconds.
1862 </para>
1864 <para>
1866 standard, but the definition exhibits properties which lead to
1867 questionable usefulness. In most cases, a combination of
1870 provide a complete range of date/time functionality required by
1871 any application.
1872 </para>
1877 <para>
1878 Date and time input is accepted in almost any reasonable format, including
1881 For some formats, ordering of day, month, and year in date input is
1882 ambiguous and there is support for specifying the expected
1887 </para>
1889 <para>
1891 handling date/time input than the
1894 for the exact parsing rules of date/time input and for the
1895 recognized text fields including months, days of the week, and
1896 time zones.
1897 </para>
1899 <para>
1900 Remember that any date or time literal input needs to be enclosed
1901 in single quotes, like text strings. Refer to
1903 information.
1905 <synopsis>
1906 <replaceable>type</replaceable> [ (<replaceable>p</replaceable>) ] '<replaceable>value</replaceable>'
1907 </synopsis>
1909 specification giving the number of
1910 fractional digits in the seconds field. Precision can be
1913 If no precision is specified in a constant specification,
1914 it defaults to the precision of the literal value (but not
1915 more than 6 digits).
1916 </para>
1921 <indexterm>
1923 </indexterm>
1925 <para>
1928 </para>
1935 <thead>
1936 <row>
1939 </row>
1940 </thead>
1941 <tbody>
1942 <row>
1945 (recommended format)</entry>
1946 </row>
1947 <row>
1950 </row>
1951 <row>
1955 </row>
1956 <row>
1959 rejected in other modes</entry>
1960 </row>
1961 <row>
1966 </entry>
1967 </row>
1968 <row>
1971 </row>
1972 <row>
1975 </row>
1976 <row>
1979 </row>
1980 <row>
1983 </row>
1984 <row>
1987 </row>
1988 <row>
1991 </row>
1992 <row>
1995 </row>
1996 <row>
1999 </row>
2000 <row>
2003 </row>
2004 <row>
2007 </row>
2008 <row>
2011 </row>
2012 </tbody>
2013 </tgroup>
2014 </table>
2015 </sect3>
2020 <indexterm>
2022 </indexterm>
2023 <indexterm>
2025 </indexterm>
2026 <indexterm>
2028 </indexterm>
2030 <para>
2031 The time-of-day types are <type>time [
2036 </para>
2038 <para>
2039 Valid input for these types consists of a time of day followed
2040 by an optional time zone. (See <xref
2044 it is silently ignored. You can also specify a date but it will
2045 be ignored, except when you use a time zone name that involves a
2046 daylight-savings rule, such as
2048 is required in order to determine whether standard or daylight-savings
2049 time applies. The appropriate time zone offset is recorded in the
2051 it is not adjusted to the active time zone.
2052 </para>
2059 <thead>
2060 <row>
2063 </row>
2064 </thead>
2065 <tbody>
2066 <row>
2069 </row>
2070 <row>
2073 </row>
2074 <row>
2077 </row>
2078 <row>
2081 </row>
2082 <row>
2085 </row>
2086 <row>
2089 </row>
2090 <row>
2093 </row>
2094 <row>
2097 </row>
2098 <row>
2101 </row>
2102 <row>
2105 </row>
2106 <row>
2109 </row>
2110 <row>
2113 </row>
2114 <row>
2117 </row>
2118 <row>
2121 </row>
2122 </tbody>
2123 </tgroup>
2124 </table>
2129 <thead>
2130 <row>
2133 </row>
2134 </thead>
2135 <tbody>
2136 <row>
2139 </row>
2140 <row>
2143 </row>
2144 <row>
2147 </row>
2148 <row>
2151 </row>
2152 <row>
2155 </row>
2156 <row>
2159 </row>
2160 <row>
2163 </row>
2164 <row>
2167 </row>
2168 <row>
2171 </row>
2172 </tbody>
2173 </tgroup>
2174 </table>
2176 <para>
2178 to specify time zones.
2179 </para>
2180 </sect3>
2185 <indexterm>
2187 </indexterm>
2189 <indexterm>
2191 </indexterm>
2193 <indexterm>
2195 </indexterm>
2197 <para>
2198 Valid input for the time stamp types consists of the concatenation
2199 of a date and a time, followed by an optional time zone,
2202 before the time zone, but this is not the preferred ordering.)
2203 Thus:
2205 <programlisting>
2207 </programlisting>
2208 and:
2209 <programlisting>
2211 </programlisting>
2214 standard. In addition, the common format:
2215 <programlisting>
2217 </programlisting>
2218 is supported.
2219 </para>
2221 <para>
2226 the time. Hence, according to the standard,
2228 <programlisting>
2230 </programlisting>
2234 <programlisting>
2236 </programlisting>
2240 literal string before determining its type, and therefore will treat
2242 ensure that a literal is treated as <type>timestamp with time
2243 zone</type>, give it the correct explicit type:
2245 <programlisting>
2247 </programlisting>
2249 In a literal that has been determined to be <type>timestamp without time
2251 any time zone indication.
2252 That is, the resulting value is derived from the date/time
2253 fields in the input value, and is not adjusted for time zone.
2254 </para>
2256 <para>
2258 value is always in UTC (Universal
2259 Coordinated Time, traditionally known as Greenwich Mean Time,
2261 time zone specified is converted to UTC using the appropriate offset
2262 for that time zone. If no time zone is stated in the input string,
2263 then it is assumed to be in the time zone indicated by the system's
2266 </para>
2268 <para>
2269 When a <type>timestamp with time
2270 zone</type> value is output, it is always converted from UTC to the
2272 zone. To see the time in another time zone, either change
2275 </para>
2277 <para>
2283 </para>
2284 </sect3>
2289 <indexterm>
2292 </indexterm>
2294 <indexterm>
2297 </indexterm>
2299 <para>
2301 special date/time input values for convenience, as shown in <xref
2304 are specially represented inside the system and will be displayed
2305 unchanged; but the others are simply notational shorthands
2306 that will be converted to ordinary date/time values when read.
2308 to a specific time value as soon as they are read.)
2309 All of these values need to be enclosed in single quotes when used
2310 as constants in SQL commands.
2311 </para>
2316 <thead>
2317 <row>
2321 </row>
2322 </thead>
2323 <tbody>
2324 <row>
2328 </row>
2329 <row>
2333 </row>
2334 <row>
2338 </row>
2339 <row>
2343 </row>
2344 <row>
2348 </row>
2349 <row>
2353 </row>
2354 <row>
2358 </row>
2359 <row>
2363 </row>
2364 </tbody>
2365 </tgroup>
2366 </table>
2368 <para>
2370 be used to obtain the current time value for the corresponding data
2371 type:
2377 </para>
2379 <caution>
2380 <para>
2384 commands, they can have surprising behavior when the command is
2385 saved to be executed later, for example in prepared statements,
2386 views, and function definitions. The string can be converted to a
2387 specific time value that continues to be used long after it becomes
2388 stale. Use one of the SQL functions instead in such contexts.
2391 </para>
2392 </caution>
2394 </sect3>
2395 </sect2>
2400 <indexterm>
2404 </indexterm>
2406 <indexterm>
2410 </indexterm>
2412 <para>
2413 The output format of the date/time types can be set to one of the four
2414 styles ISO 8601,
2417 German. The default
2421 historical accident.) <xref
2425 in accordance with the given examples. However, the
2428 </para>
2436 <thead>
2437 <row>
2441 </row>
2442 </thead>
2443 <tbody>
2444 <row>
2448 </row>
2449 <row>
2453 </row>
2454 <row>
2458 </row>
2459 <row>
2463 </row>
2464 </tbody>
2465 </tgroup>
2466 </table>
2468 <note>
2469 <para>
2473 above. This is for readability and for consistency with
2475 well as some other database systems.
2476 </para>
2477 </note>
2479 <para>
2481 month if DMY field ordering has been specified, otherwise month appears
2482 before day.
2484 for how this setting also affects interpretation of input values.)
2486 </para>
2494 <thead>
2495 <row>
2499 </row>
2500 </thead>
2501 <tbody>
2502 <row>
2504 <entry><replaceable>day</replaceable>/<replaceable>month</replaceable>/<replaceable>year</replaceable></entry>
2506 </row>
2507 <row>
2509 <entry><replaceable>month</replaceable>/<replaceable>day</replaceable>/<replaceable>year</replaceable></entry>
2511 </row>
2512 <row>
2514 <entry><replaceable>day</replaceable>/<replaceable>month</replaceable>/<replaceable>year</replaceable></entry>
2516 </row>
2517 </tbody>
2518 </tgroup>
2519 </table>
2521 <para>
2523 a signed numeric offset from UTC, with positive sign used for zones
2524 east of Greenwich. The offset will be shown
2526 number of hours, else
2528 is an integral number of minutes, else as
2530 (The third case is not possible with any modern time zone standard,
2531 but it can appear when working with timestamps that predate the
2532 adoption of standardized time zones.)
2533 In the other date styles, the time zone is shown as an alphabetic
2534 abbreviation if one is in common use in the current zone. Otherwise
2535 it appears as a signed numeric offset in ISO 8601 basic format
2537 </para>
2539 <para>
2540 The date/time style can be selected by the user using the
2545 client.
2546 </para>
2548 <para>
2551 a more flexible way to format date/time output.
2552 </para>
2553 </sect2>
2560 </indexterm>
2562 <para>
2563 Time zones, and time-zone conventions, are influenced by
2564 political decisions, not just earth geometry. Time zones around the
2565 world became somewhat standardized during the 1900s,
2566 but continue to be prone to arbitrary changes, particularly with
2567 respect to daylight-savings rules.
2569 IANA (Olson) time zone database for information about
2570 historical time zone rules. For times in the future, the assumption
2571 is that the latest known rules for a given time zone will
2572 continue to be observed indefinitely far into the future.
2573 </para>
2575 <para>
2579 time types and capabilities. Two obvious problems are:
2581 <itemizedlist>
2582 <listitem>
2583 <para>
2585 cannot have an associated time zone, the
2587 Time zones in the real world have little meaning unless
2588 associated with a date as well as a time,
2589 since the offset can vary through the year with daylight-saving
2590 time boundaries.
2591 </para>
2592 </listitem>
2594 <listitem>
2595 <para>
2596 The default time zone is specified as a constant numeric offset
2598 daylight-saving time when doing date/time arithmetic across
2600 </para>
2601 </listitem>
2603 </itemizedlist>
2604 </para>
2606 <para>
2607 To address these difficulties, we recommend using date/time types
2608 that contain both date and time when using time zones. We
2610 time zone</type> (though it is supported by
2614 your local time zone for any type containing only date or time.
2615 </para>
2617 <para>
2618 All timezone-aware dates and times are stored internally in
2621 parameter before being displayed to the client.
2622 </para>
2624 <para>
2626 three different forms:
2627 <itemizedlist>
2628 <listitem>
2629 <para>
2631 The recognized time zone names are listed in the
2635 time zone data for this purpose, so the same time zone
2636 names are also recognized by other software.
2637 </para>
2638 </listitem>
2639 <listitem>
2640 <para>
2642 specification merely defines a particular offset from UTC, in
2643 contrast to full time zone names which can imply a set of daylight
2644 savings transition rules as well. The recognized abbreviations
2649 zone abbreviation, but you can use abbreviations in
2651 operator.
2652 </para>
2653 </listitem>
2654 <listitem>
2655 <para>
2656 In addition to the timezone names and abbreviations,
2658 specifications, as described in
2660 normally preferable to using a named time zone, but it may be
2661 necessary if no suitable IANA time zone entry is available.
2662 </para>
2663 </listitem>
2664 </itemizedlist>
2666 In short, this is the difference between abbreviations
2667 and full names: abbreviations represent a specific offset from UTC,
2668 whereas many of the full names imply a local daylight-savings time
2669 rule, and so have two possible UTC offsets. As an example,
2671 time in New York, which for this particular date was Eastern Daylight
2674 noon Eastern Standard Time (UTC-5), regardless of whether daylight
2675 savings was nominally in effect on that date.
2676 </para>
2678 <para>
2679 To complicate matters, some jurisdictions have used the same timezone
2680 abbreviation to mean different UTC offsets at different times; for
2683 abbreviations according to whatever they meant (or had most recently
2685 above, this is not necessarily the same as local civil time on that date.
2686 </para>
2688 <para>
2689 In all cases, timezone names and abbreviations are recognized
2691 versions prior to 8.2, which were case-sensitive in some contexts but
2692 not others.)
2693 </para>
2695 <para>
2696 Neither timezone names nor abbreviations are hard-wired into the server;
2697 they are obtained from configuration files stored under
2699 of the installation directory
2701 </para>
2703 <para>
2707 There are also some special ways to set it:
2709 <itemizedlist>
2710 <listitem>
2711 <para>
2713 sets the time zone for the session. This is an alternative spelling
2715 </para>
2716 </listitem>
2718 <listitem>
2719 <para>
2723 command to the server upon connection.
2724 </para>
2725 </listitem>
2726 </itemizedlist>
2727 </para>
2728 </sect2>
2733 <indexterm>
2735 </indexterm>
2737 <para>
2739 verbose syntax:
2741 <synopsis>
2742 <optional>@</optional> <replaceable>quantity</replaceable> <replaceable>unit</replaceable> <optional><replaceable>quantity</replaceable> <replaceable>unit</replaceable>...</optional> <optional><replaceable>direction</replaceable></optional>
2743 </synopsis>
2751 or abbreviations or plurals of these units;
2754 of the different units are implicitly added with appropriate
2756 This syntax is also used for interval output, if
2759 </para>
2761 <para>
2762 Quantities of days, hours, minutes, and seconds can be specified without
2765 a combination of years and months can be specified with a dash;
2770 </para>
2772 <para>
2773 Interval values can also be written as ISO 8601 time intervals, using
2776 format with designators looks like this:
2777 <synopsis>
2778 P <replaceable>quantity</replaceable> <replaceable>unit</replaceable> <optional> <replaceable>quantity</replaceable> <replaceable>unit</replaceable> ...</optional> <optional> T <optional> <replaceable>quantity</replaceable> <replaceable>unit</replaceable> ...</optional></optional>
2779 </synopsis>
2782 available unit abbreviations are given in <xref
2784 omitted, and may be specified in any order, but units smaller than
2788 </para>
2793 <thead>
2794 <row>
2797 </row>
2798 </thead>
2799 <tbody>
2800 <row>
2803 </row>
2804 <row>
2807 </row>
2808 <row>
2811 </row>
2812 <row>
2815 </row>
2816 <row>
2819 </row>
2820 <row>
2823 </row>
2824 <row>
2827 </row>
2828 </tbody>
2829 </tgroup>
2830 </table>
2832 <para>
2833 In the alternative format:
2834 <synopsis>
2835 P <optional> <replaceable>years</replaceable>-<replaceable>months</replaceable>-<replaceable>days</replaceable> </optional> <optional> T <replaceable>hours</replaceable>:<replaceable>minutes</replaceable>:<replaceable>seconds</replaceable> </optional>
2836 </synopsis>
2839 The values are given as numbers similar to ISO 8601 dates.
2840 </para>
2842 <para>
2844 specification, or when assigning a string to an interval column that was
2852 results in dropping the seconds field, but not the day field.
2853 </para>
2855 <para>
2857 value must have the same sign, so a leading negative sign applies to all
2858 fields; for example the negative sign in the interval literal
2861 signs, and traditionally treats each field in the textual representation
2862 as independently signed, so that the hour/minute/second part is
2865 to apply to all fields (but only if no additional signs appear).
2867 used. To avoid ambiguity, it's recommended to attach an explicit sign
2868 to each field if any field is negative.
2869 </para>
2871 <para>
2873 fields: months, days, and microseconds. These fields are kept
2874 separate because the number of days in a month varies, while a day
2876 involved. An interval input string that uses other units is
2877 normalized into this format, and then reconstructed in a standardized
2878 way for output, for example:
2880 <programlisting>
2882 interval
2883 ---------------------------------------
2885 </programlisting>
2888 kept separate, while the smaller and larger time units were
2889 combined and normalized.
2890 </para>
2892 <para>
2896 fractional values must be converted into smaller
2897 units. Fractional parts of units greater than months are rounded to
2900 weeks and days are computed to be an integer number of days and
2904 on output.
2905 </para>
2907 <para>
2910 </para>
2915 <thead>
2916 <row>
2919 </row>
2920 </thead>
2921 <tbody>
2922 <row>
2925 </row>
2926 <row>
2929 </row>
2930 <row>
2933 </row>
2934 <row>
2937 </row>
2938 <row>
2941 </row>
2942 </tbody>
2943 </tgroup>
2944 </table>
2946 </sect2>
2951 <indexterm>
2955 </indexterm>
2957 <para>
2960 microseconds. For output, the months field is converted to years and
2961 months by dividing by 12. The days field is shown as-is. The
2962 microseconds field is converted to hours, minutes, seconds, and
2963 fractional seconds. Thus months, minutes, and seconds will never be
2965 respectively, while the displayed years, days, and hours fields can
2966 be quite large. (The <link
2968 and <link
2970 functions can be used if it is desirable to transpose large days or
2971 hours values into the next higher field.)
2972 </para>
2974 <para>
2975 The output format of the interval type can be set to one of the
2981 output style.
2982 </para>
2984 <para>
2986 the SQL standard's specification for interval literal strings, if
2987 the interval value meets the standard's restrictions (either year-month
2988 only or day-time only, with no mixing of positive
2989 and negative components). Otherwise the output looks like a standard
2990 year-month literal string followed by a day-time literal string,
2991 with explicit signs added to disambiguate mixed-sign intervals.
2992 </para>
2994 <para>
2998 </para>
3000 <para>
3004 </para>
3006 <para>
3009 ISO 8601 standard.
3010 </para>
3015 <thead>
3016 <row>
3021 </row>
3022 </thead>
3023 <tbody>
3024 <row>
3029 </row>
3030 <row>
3035 </row>
3036 <row>
3041 </row>
3042 <row>
3047 </row>
3048 </tbody>
3049 </tgroup>
3050 </table>
3052 </sect2>
3054 </sect1>
3062 </indexterm>
3066 </indexterm>
3070 </indexterm>
3072 <para>
3080 </para>
3085 <thead>
3086 <row>
3090 </row>
3091 </thead>
3092 <tbody>
3093 <row>
3097 </row>
3098 </tbody>
3099 </tgroup>
3100 </table>
3102 <para>
3103 Boolean constants can be represented in SQL queries by the SQL
3106 </para>
3108 <para>
3111 <simplelist>
3116 </simplelist>
3118 <simplelist>
3123 </simplelist>
3124 Unique prefixes of these strings are also accepted, for
3126 Leading or trailing whitespace is ignored, and case does not matter.
3127 </para>
3129 <para>
3133 </para>
3138 <programlisting>
3139 CREATE TABLE test1 (a boolean, b text);
3140 INSERT INTO test1 VALUES (TRUE, 'sic est');
3141 INSERT INTO test1 VALUES (FALSE, 'non est');
3142 SELECT * FROM test1;
3143 a | b
3144 ---+---------
3145 t | sic est
3146 f | non est
3148 SELECT * FROM test1 WHERE a;
3149 a | b
3150 ---+---------
3151 t | sic est
3152 </programlisting>
3153 </example>
3155 <para>
3158 Boolean constants in SQL queries. But you can also use the string
3159 representations by following the generic string-literal constant syntax
3162 </para>
3164 <para>
3165 Note that the parser automatically understands
3172 omitted from a string-literal Boolean value in contexts where the parser
3174 </para>
3175 </sect1>
3183 </indexterm>
3187 </indexterm>
3189 <para>
3190 Enumerated (enum) types are data types that
3191 comprise a static, ordered set of values.
3193 types supported in a number of programming languages. An example of an enum
3194 type might be the days of the week, or a set of status values for
3195 a piece of data.
3196 </para>
3201 <para>
3202 Enum types are created using the <xref
3204 for example:
3206 <programlisting>
3207 CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
3208 </programlisting>
3210 Once created, the enum type can be used in table and function
3211 definitions much like any other type:
3212 <programlisting>
3213 CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
3214 CREATE TABLE person (
3215 name text,
3216 current_mood mood
3217 );
3218 INSERT INTO person VALUES ('Moe', 'happy');
3219 SELECT * FROM person WHERE current_mood = 'happy';
3220 name | current_mood
3221 ------+--------------
3222 Moe | happy
3223 (1 row)
3224 </programlisting>
3225 </para>
3226 </sect2>
3231 <para>
3232 The ordering of the values in an enum type is the
3233 order in which the values were listed when the type was created.
3234 All standard comparison operators and related
3235 aggregate functions are supported for enums. For example:
3237 <programlisting>
3238 INSERT INTO person VALUES ('Larry', 'sad');
3239 INSERT INTO person VALUES ('Curly', 'ok');
3240 SELECT * FROM person WHERE current_mood > 'sad';
3241 name | current_mood
3242 -------+--------------
3243 Moe | happy
3244 Curly | ok
3245 (2 rows)
3247 SELECT * FROM person WHERE current_mood > 'sad' ORDER BY current_mood;
3248 name | current_mood
3249 -------+--------------
3250 Curly | ok
3251 Moe | happy
3252 (2 rows)
3254 SELECT name
3255 FROM person
3256 WHERE current_mood = (SELECT MIN(current_mood) FROM person);
3257 name
3258 -------
3259 Larry
3260 (1 row)
3261 </programlisting>
3262 </para>
3263 </sect2>
3268 <para>
3269 Each enumerated data type is separate and cannot
3270 be compared with other enumerated types. See this example:
3272 <programlisting>
3273 CREATE TYPE happiness AS ENUM ('happy', 'very happy', 'ecstatic');
3274 CREATE TABLE holidays (
3275 num_weeks integer,
3276 happiness happiness
3277 );
3278 INSERT INTO holidays(num_weeks,happiness) VALUES (4, 'happy');
3279 INSERT INTO holidays(num_weeks,happiness) VALUES (6, 'very happy');
3280 INSERT INTO holidays(num_weeks,happiness) VALUES (8, 'ecstatic');
3281 INSERT INTO holidays(num_weeks,happiness) VALUES (2, 'sad');
3282 ERROR: invalid input value for enum happiness: "sad"
3283 SELECT person.name, holidays.num_weeks FROM person, holidays
3284 WHERE person.current_mood = holidays.happiness;
3285 ERROR: operator does not exist: mood = happiness
3286 </programlisting>
3287 </para>
3289 <para>
3290 If you really need to do something like that, you can either
3291 write a custom operator or add explicit casts to your query:
3293 <programlisting>
3294 SELECT person.name, holidays.num_weeks FROM person, holidays
3295 WHERE person.current_mood::text = holidays.happiness::text;
3296 name | num_weeks
3297 ------+-----------
3298 Moe | 4
3299 (1 row)
3301 </programlisting>
3302 </para>
3303 </sect2>
3308 <para>
3309 Enum labels are case sensitive, so
3311 White space in the labels is significant too.
3312 </para>
3314 <para>
3315 Although enum types are primarily intended for static sets of values,
3316 there is support for adding new values to an existing enum type, and for
3318 cannot be removed from an enum type, nor can the sort ordering of such
3319 values be changed, short of dropping and re-creating the enum type.
3320 </para>
3322 <para>
3323 An enum value occupies four bytes on disk. The length of an enum
3326 builds this means at most 63 bytes.
3327 </para>
3329 <para>
3330 The translations from internal enum values to textual labels are
3331 kept in the system catalog
3333 Querying this catalog directly can be useful.
3334 </para>
3336 </sect2>
3337 </sect1>
3342 <para>
3343 Geometric data types represent two-dimensional spatial
3346 </para>
3355 <thead>
3356 <row>
3361 </row>
3362 </thead>
3363 <tbody>
3364 <row>
3369 </row>
3370 <row>
3375 </row>
3376 <row>
3381 </row>
3382 <row>
3387 </row>
3388 <row>
3393 </row>
3394 <row>
3399 </row>
3400 <row>
3405 </row>
3406 <row>
3411 </row>
3412 </tbody>
3413 </tgroup>
3414 </table>
3416 <para>
3417 In all these types, the individual coordinates are stored as
3419 </para>
3421 <para>
3422 A rich set of functions and operators is available to perform various geometric
3423 operations such as scaling, translation, rotation, and determining
3425 </para>
3430 <indexterm>
3432 </indexterm>
3434 <para>
3435 Points are the fundamental two-dimensional building block for geometric
3437 the following syntaxes:
3439 <synopsis>
3442 </synopsis>
3445 coordinates, as floating-point numbers.
3446 </para>
3448 <para>
3449 Points are output using the first syntax.
3450 </para>
3451 </sect2>
3456 <indexterm>
3458 </indexterm>
3460 <para>
3461 Lines are represented by the linear
3462 equation <replaceable>A</replaceable>x + <replaceable>B</replaceable>y + <replaceable>C</replaceable> = 0,
3465 <synopsis>
3467 </synopsis>
3469 Alternatively, any of the following forms can be used for input:
3471 <synopsis>
3472 [ ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ( <replaceable>x2</replaceable> , <replaceable>y2</replaceable> ) ]
3473 ( ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ( <replaceable>x2</replaceable> , <replaceable>y2</replaceable> ) )
3474 ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ( <replaceable>x2</replaceable> , <replaceable>y2</replaceable> )
3475 <replaceable>x1</replaceable> , <replaceable>y1</replaceable> , <replaceable>x2</replaceable> , <replaceable>y2</replaceable>
3476 </synopsis>
3478 where
3480 and
3482 are two different points on the line.
3483 </para>
3484 </sect2>
3489 <indexterm>
3491 </indexterm>
3493 <indexterm>
3495 </indexterm>
3497 <para>
3498 Line segments are represented by pairs of points that are the endpoints
3500 of the following syntaxes:
3502 <synopsis>
3503 [ ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ( <replaceable>x2</replaceable> , <replaceable>y2</replaceable> ) ]
3504 ( ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ( <replaceable>x2</replaceable> , <replaceable>y2</replaceable> ) )
3505 ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ( <replaceable>x2</replaceable> , <replaceable>y2</replaceable> )
3506 <replaceable>x1</replaceable> , <replaceable>y1</replaceable> , <replaceable>x2</replaceable> , <replaceable>y2</replaceable>
3507 </synopsis>
3509 where
3511 and
3513 are the end points of the line segment.
3514 </para>
3516 <para>
3517 Line segments are output using the first syntax.
3518 </para>
3519 </sect2>
3524 <indexterm>
3526 </indexterm>
3528 <indexterm>
3530 </indexterm>
3532 <para>
3533 Boxes are represented by pairs of points that are opposite
3534 corners of the box.
3536 syntaxes:
3538 <synopsis>
3539 ( ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ( <replaceable>x2</replaceable> , <replaceable>y2</replaceable> ) )
3540 ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ( <replaceable>x2</replaceable> , <replaceable>y2</replaceable> )
3541 <replaceable>x1</replaceable> , <replaceable>y1</replaceable> , <replaceable>x2</replaceable> , <replaceable>y2</replaceable>
3542 </synopsis>
3544 where
3546 and
3548 are any two opposite corners of the box.
3549 </para>
3551 <para>
3552 Boxes are output using the second syntax.
3553 </para>
3555 <para>
3556 Any two opposite corners can be supplied on input, but the values
3557 will be reordered as needed to store the
3558 upper right and lower left corners, in that order.
3559 </para>
3560 </sect2>
3565 <indexterm>
3567 </indexterm>
3569 <para>
3570 Paths are represented by lists of connected points. Paths can be
3572 the first and last points in the list are considered not connected, or
3574 where the first and last points are considered connected.
3575 </para>
3577 <para>
3579 syntaxes:
3581 <synopsis>
3582 [ ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ... , ( <replaceable>xn</replaceable> , <replaceable>yn</replaceable> ) ]
3583 ( ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ... , ( <replaceable>xn</replaceable> , <replaceable>yn</replaceable> ) )
3584 ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ... , ( <replaceable>xn</replaceable> , <replaceable>yn</replaceable> )
3585 ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> , ... , <replaceable>xn</replaceable> , <replaceable>yn</replaceable> )
3586 <replaceable>x1</replaceable> , <replaceable>y1</replaceable> , ... , <replaceable>xn</replaceable> , <replaceable>yn</replaceable>
3587 </synopsis>
3589 where the points are the end points of the line segments
3592 closed path. When the outermost parentheses are omitted, as
3593 in the third through fifth syntaxes, a closed path is assumed.
3594 </para>
3596 <para>
3597 Paths are output using the first or second syntax, as appropriate.
3598 </para>
3599 </sect2>
3604 <indexterm>
3606 </indexterm>
3608 <para>
3609 Polygons are represented by lists of points (the vertices of the
3610 polygon). Polygons are very similar to closed paths; the essential
3611 semantic difference is that a polygon is considered to include the
3612 area within it, while a path is not.
3613 </para>
3615 <para>
3616 An important implementation difference between polygons and
3617 paths is that the stored representation of a polygon includes its
3618 smallest bounding box. This speeds up certain search operations,
3619 although computing the bounding box adds overhead while constructing
3620 new polygons.
3621 </para>
3623 <para>
3625 following syntaxes:
3627 <synopsis>
3628 ( ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ... , ( <replaceable>xn</replaceable> , <replaceable>yn</replaceable> ) )
3629 ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ... , ( <replaceable>xn</replaceable> , <replaceable>yn</replaceable> )
3630 ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> , ... , <replaceable>xn</replaceable> , <replaceable>yn</replaceable> )
3631 <replaceable>x1</replaceable> , <replaceable>y1</replaceable> , ... , <replaceable>xn</replaceable> , <replaceable>yn</replaceable>
3632 </synopsis>
3634 where the points are the end points of the line segments
3635 comprising the boundary of the polygon.
3636 </para>
3638 <para>
3639 Polygons are output using the first syntax.
3640 </para>
3641 </sect2>
3646 <indexterm>
3648 </indexterm>
3650 <para>
3651 Circles are represented by a center point and radius.
3653 following syntaxes:
3655 <synopsis>
3656 < ( <replaceable>x</replaceable> , <replaceable>y</replaceable> ) , <replaceable>r</replaceable> >
3657 ( ( <replaceable>x</replaceable> , <replaceable>y</replaceable> ) , <replaceable>r</replaceable> )
3660 </synopsis>
3662 where
3665 circle.
3666 </para>
3668 <para>
3669 Circles are output using the first syntax.
3670 </para>
3671 </sect2>
3673 </sect1>
3681 </indexterm>
3683 <para>
3686 is better to use these types instead of plain text types to store
3687 network addresses, because
3688 these types offer input error checking and specialized
3690 </para>
3698 <thead>
3699 <row>
3703 </row>
3704 </thead>
3705 <tbody>
3707 <row>
3711 </row>
3713 <row>
3717 </row>
3719 <row>
3723 </row>
3725 <row>
3729 </row>
3731 </tbody>
3732 </tgroup>
3733 </table>
3735 <para>
3737 IPv4 addresses will always sort before IPv6 addresses, including
3738 IPv4 addresses encapsulated or mapped to IPv6 addresses, such as
3740 </para>
3746 <indexterm>
3748 </indexterm>
3750 <para>
3752 optionally its subnet, all in one field.
3753 The subnet is represented by the number of network address bits
3754 present in the host address (the
3756 then the value does not indicate a subnet, only a single host.
3758 unique host address. Note that if you
3759 want to accept only networks, you should use the
3761 </para>
3763 <para>
3764 The input format for this type is
3766 where
3768 is an IPv4 or IPv6 address and
3770 is the number of bits in the netmask. If the
3772 portion is omitted, the
3774 so the value represents
3775 just a single host. On display, the
3777 portion is suppressed if the netmask specifies a single host.
3778 </para>
3779 </sect2>
3784 <indexterm>
3786 </indexterm>
3788 <para>
3790 Input and output formats follow Classless Internet Domain Routing
3791 conventions.
3792 The format for specifying networks is <replaceable
3795 address represented as an
3796 IPv4 or IPv6 address, and <replaceable
3799 using assumptions from the older classful network numbering system, except
3800 it will be at least large enough to include all of the octets
3801 written in the input. It is an error to specify a network address
3802 that has bits set to the right of the specified netmask.
3803 </para>
3805 <para>
3807 </para>
3812 <thead>
3813 <row>
3817 </row>
3818 </thead>
3819 <tbody>
3820 <row>
3824 </row>
3825 <row>
3829 </row>
3830 <row>
3834 </row>
3835 <row>
3839 </row>
3840 <row>
3844 </row>
3845 <row>
3849 </row>
3850 <row>
3854 </row>
3855 <row>
3859 </row>
3860 <row>
3864 </row>
3865 <row>
3869 </row>
3870 <row>
3874 </row>
3875 <row>
3879 </row>
3880 <row>
3884 </row>
3885 <row>
3889 </row>
3890 <row>
3894 </row>
3895 <row>
3899 </row>
3900 </tbody>
3901 </tgroup>
3902 </table>
3903 </sect2>
3908 <para>
3914 </para>
3916 <tip>
3917 <para>
3921 </para>
3922 </tip>
3923 </sect2>
3928 <indexterm>
3930 </indexterm>
3932 <indexterm>
3935 </indexterm>
3937 <para>
3939 from Ethernet card hardware addresses (although MAC addresses are
3940 used for other purposes as well). Input is accepted in the
3941 following formats:
3943 <simplelist>
3951 </simplelist>
3953 These examples all specify the same address. Upper and
3954 lower case is accepted for the digits
3956 first of the forms shown.
3957 </para>
3959 <para>
3961 as the canonical form for MAC addresses, and specifies the first
3962 form (with colons) as used with bit-reversed, MSB-first notation, so that
3964 ignored nowadays, and it is relevant only for obsolete network
3965 protocols (such as Token Ring). PostgreSQL makes no provisions
3966 for bit reversal; all accepted formats use the canonical LSB
3967 order.
3968 </para>
3970 <para>
3971 The remaining five input formats are not part of any standard.
3972 </para>
3973 </sect2>
3978 <indexterm>
3980 </indexterm>
3982 <indexterm>
3985 </indexterm>
3987 <para>
3989 format, known for example from Ethernet card hardware addresses
3990 (although MAC addresses are used for other purposes as well).
3992 and stores them in 8 byte length format. MAC addresses given
3997 should be set to one after the conversion from EUI-48. The
3999 change.
4001 Generally speaking, any input which is comprised of pairs of hex
4002 digits (on byte boundaries), optionally separated consistently by
4007 The following are examples of input formats that are accepted:
4009 <simplelist>
4018 </simplelist>
4020 These examples all specify the same address. Upper and
4021 lower case is accepted for the digits
4023 first of the forms shown.
4024 </para>
4026 <para>
4027 The last six input formats shown above are not part of any standard.
4028 </para>
4030 <para>
4032 modified EUI-64 format to be included as the host portion of an
4035 <programlisting>
4037 <computeroutput>
4038 macaddr8_set7bit
4039 -------------------------
4041 (1 row)
4042 </computeroutput>
4043 </programlisting>
4045 </para>
4047 </sect2>
4049 </sect1>
4057 </indexterm>
4059 <para>
4061 or visualize bit masks. There are two SQL bit types:
4065 </para>
4067 <para>
4071 of variable length up to the maximum length
4075 specification means unlimited length.
4076 </para>
4078 <note>
4079 <para>
4080 If one explicitly casts a bit-string value to
4083 without raising an error. Similarly,
4084 if one explicitly casts a bit-string value to
4087 </para>
4088 </note>
4090 <para>
4091 Refer to <xref
4093 of bit string constants. Bit-logical operators and string
4094 manipulation functions are available; see <xref
4096 </para>
4098 <example>
4101 <programlisting>
4105 <computeroutput>
4107 </computeroutput>
4109 SELECT * FROM test;
4110 <computeroutput>
4111 a | b
4112 -----+-----
4115 </computeroutput>
4116 </programlisting>
4117 </example>
4119 <para>
4122 (but long values may be compressed or moved out-of-line, as explained
4124 </para>
4125 </sect1>
4133 </indexterm>
4138 </indexterm>
4140 <para>
4142 are designed to support full text search, which is the activity of
4147 a text query.
4150 related functions and operators.
4151 </para>
4156 <indexterm>
4158 </indexterm>
4160 <para>
4165 duplicate-elimination are done automatically during input, as shown in
4166 this example:
4168 <programlisting>
4169 SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector;
4170 tsvector
4171 ----------------------------------------------------
4172 'a' 'and' 'ate' 'cat' 'fat' 'mat' 'on' 'rat' 'sat'
4173 </programlisting>
4175 To represent
4176 lexemes containing whitespace or punctuation, surround them with quotes:
4178 <programlisting>
4179 SELECT $$the lexeme ' ' contains spaces$$::tsvector;
4180 tsvector
4181 -------------------------------------------
4182 ' ' 'contains' 'lexeme' 'spaces' 'the'
4183 </programlisting>
4185 (We use dollar-quoted string literals in this example and the next one
4186 to avoid the confusion of having to double quote marks within the
4187 literals.) Embedded quotes and backslashes must be doubled:
4189 <programlisting>
4190 SELECT $$the lexeme 'Joe''s' contains a quote$$::tsvector;
4191 tsvector
4192 ------------------------------------------------
4193 'Joe''s' 'a' 'contains' 'lexeme' 'quote' 'the'
4194 </programlisting>
4197 can be attached to lexemes:
4199 <programlisting>
4201 tsvector
4202 -------------------------------------------------------------------&zwsp;------------
4204 </programlisting>
4206 A position normally indicates the source word's location in the
4207 document. Positional information can be used for
4210 Duplicate positions for the same lexeme are discarded.
4211 </para>
4213 <para>
4214 Lexemes that have positions can further be labeled with a
4219 <programlisting>
4221 tsvector
4222 ----------------------------
4224 </programlisting>
4226 Weights are typically used to reflect document structure, for example
4227 by marking title words differently from body words. Text search
4228 ranking functions can assign different priorities to the different
4229 weight markers.
4230 </para>
4232 <para>
4233 It is important to understand that the
4235 normalization; it assumes the words it is given are normalized
4236 appropriately for the application. For example,
4238 <programlisting>
4239 SELECT 'The Fat Rats'::tsvector;
4240 tsvector
4241 --------------------
4242 'Fat' 'Rats' 'The'
4243 </programlisting>
4245 For most English-text-searching applications the above words would
4247 Raw document text should usually be passed through
4249 for searching:
4251 <programlisting>
4252 SELECT to_tsvector('english', 'The Fat Rats');
4253 to_tsvector
4254 -----------------
4256 </programlisting>
4259 </para>
4261 </sect2>
4266 <indexterm>
4268 </indexterm>
4270 <para>
4272 searched for, and can combine them using the Boolean operators
4278 specifies the distance between the two lexemes being searched
4280 </para>
4282 <para>
4283 Parentheses can be used to enforce grouping of these operators.
4287 the least tightly.
4288 </para>
4290 <para>
4291 Here are some examples:
4293 <programlisting>
4294 SELECT 'fat & rat'::tsquery;
4295 tsquery
4296 ---------------
4297 'fat' & 'rat'
4299 SELECT 'fat & (rat | cat)'::tsquery;
4300 tsquery
4301 ---------------------------
4302 'fat' & ( 'rat' | 'cat' )
4305 tsquery
4306 ------------------------
4308 </programlisting>
4309 </para>
4311 <para>
4313 one or more weight letters, which restricts them to match only
4316 <programlisting>
4317 SELECT 'fat:ab & cat'::tsquery;
4318 tsquery
4319 ------------------
4320 'fat':AB & 'cat'
4321 </programlisting>
4322 </para>
4324 <para>
4326 to specify prefix matching:
4327 <programlisting>
4328 SELECT 'super:*'::tsquery;
4329 tsquery
4330 -----------
4331 'super':*
4332 </programlisting>
4335 </para>
4337 <para>
4338 Quoting rules for lexemes are the same as described previously for
4340 any required normalization of words must be done before converting
4342 function is convenient for performing such normalization:
4344 <programlisting>
4345 SELECT to_tsquery('Fat:ab & Cats');
4346 to_tsquery
4347 ------------------
4348 'fat':AB & 'cat'
4349 </programlisting>
4352 as other words, which means this comparison returns true:
4354 <programlisting>
4355 SELECT to_tsvector( 'postgraduate' ) @@ to_tsquery( 'postgres:*' );
4356 ?column?
4357 ----------
4358 t
4359 </programlisting>
4361 <programlisting>
4362 SELECT to_tsvector( 'postgraduate' ), to_tsquery( 'postgres:*' );
4363 to_tsvector | to_tsquery
4364 ---------------+------------
4365 'postgradu':1 | 'postgr':*
4366 </programlisting>
4368 </para>
4370 </sect2>
4372 </sect1>
4379 </indexterm>
4381 <para>
4383 (UUID) as defined by <ulink url="https://datatracker.ietf.org/doc/html/rfc9562">RFC 9562</ulink>,
4385 (Some systems refer to this data type as a globally unique identifier, or
4387 identifier is a 128-bit quantity that is generated by an algorithm chosen
4388 to make it very unlikely that the same identifier will be generated by
4389 anyone else in the known universe using the same algorithm. Therefore,
4390 for distributed systems, these identifiers provide a better uniqueness
4391 guarantee than sequence generators, which
4392 are only unique within a single database.
4393 </para>
4395 <para>
4396 A UUID is written as a sequence of lower-case hexadecimal digits,
4397 in several groups separated by hyphens, specifically a group of 8
4398 digits followed by three groups of 4 digits followed by a group of
4400 example of a UUID in this standard form is:
4401 <programlisting>
4403 </programlisting>
4405 alternative forms for input:
4406 use of upper-case digits, the standard format surrounded by
4407 braces, omitting some or all hyphens, adding a hyphen after any
4408 group of four digits. Examples are:
4409 <programlisting>
4412 a0eebc999c0b4ef8bb6d6bb9bd380a11
4414 {a0eebc99-9c0b4ef8-bb6d6bb9-bd380a11}
4415 </programlisting>
4416 Output is always in the standard form.
4417 </para>
4419 <para>
4422 </para>
4423 </sect1>
4430 </indexterm>
4432 <para>
4435 checks the input values for well-formedness, and there are support
4436 functions to perform type-safe operations on it; see <xref
4438 installation to have been built with <command>configure
4439 --with-libxml</command>.
4440 </para>
4442 <para>
4446 to the more permissive
4447 <ulink url="https://www.w3.org/TR/2010/REC-xpath-datamodel-20101214/#DocumentNode"><quote>document node</quote></ulink>
4448 of the XQuery and XPath data model.
4449 Roughly, this means that content fragments can have
4450 more than one top-level element or character node. The expression
4453 value is a full document or only a content fragment.
4454 </para>
4456 <para>
4459 </para>
4463 <para>
4465 use the function
4467 <synopsis>
4469 </synopsis>
4470 Examples:
4471 <programlisting><![CDATA[
4472 XMLPARSE (DOCUMENT '<?xml version="1.0"?><book><title>Manual</title><chapter>...</chapter></book>')
4474 ]]></programlisting>
4475 While this is the only way to convert character strings into XML
4476 values according to the SQL standard, the PostgreSQL-specific
4477 syntaxes:
4478 <programlisting><![CDATA[
4481 ]]></programlisting>
4482 can also be used.
4483 </para>
4485 <para>
4487 against a document type declaration
4489 even when the input value specifies a DTD.
4490 There is also currently no built-in support for validating against
4491 other XML schema languages such as XML Schema.
4492 </para>
4494 <para>
4495 The inverse operation, producing a character string value from
4498 <synopsis>
4499 XMLSERIALIZE ( { DOCUMENT | CONTENT } <replaceable>value</replaceable> AS <replaceable>type</replaceable> [ [ NO ] INDENT ] )
4500 </synopsis>
4504 to the SQL standard, this is the only way to convert between type
4506 you to simply cast the value.
4507 </para>
4509 <para>
4512 default) just emits the original input string. Casting to a character
4513 type likewise produces the original string.
4514 </para>
4516 <para>
4517 When a character string value is cast to or from type
4523 session configuration parameter, which can be set using the
4524 standard command:
4525 <synopsis>
4526 SET XML OPTION { DOCUMENT | CONTENT };
4527 </synopsis>
4528 or the more PostgreSQL-like syntax
4529 <synopsis>
4530 SET xmloption TO { DOCUMENT | CONTENT };
4531 </synopsis>
4533 data are allowed.
4534 </para>
4536 </sect2>
4540 <para>
4541 Care must be taken when dealing with multiple character encodings
4542 on the client, server, and in the XML data passed through them.
4543 When using the text mode to pass queries to the server and query
4544 results to the client (which is the normal mode), PostgreSQL
4545 converts all character data passed between the client and the
4546 server and vice versa to the character encoding of the respective
4548 representations of XML values, such as in the above examples.
4549 This would ordinarily mean that encoding declarations contained in
4550 XML data can become invalid as the character data is converted
4551 to other encodings while traveling between client and server,
4552 because the embedded encoding declaration is not changed. To cope
4553 with this behavior, encoding declarations contained in
4556 to be in the current server encoding. Consequently, for correct
4557 processing, character strings of XML data must be sent
4558 from the client in the current client encoding. It is the
4559 responsibility of the client to either convert documents to the
4560 current client encoding before sending them to the server, or to
4561 adjust the client encoding appropriately. On output, values of
4563 clients should assume all data is in the current client
4564 encoding.
4565 </para>
4567 <para>
4568 When using binary mode to pass query parameters to the server
4569 and query results back to the client, no encoding conversion
4570 is performed, so the situation is different. In this case, an
4571 encoding declaration in the XML data will be observed, and if it
4572 is absent, the data will be assumed to be in UTF-8 (as required by
4573 the XML standard; note that PostgreSQL does not support UTF-16).
4574 On output, data will have an encoding declaration
4575 specifying the client encoding, unless the client encoding is
4576 UTF-8, in which case it will be omitted.
4577 </para>
4579 <para>
4580 Needless to say, processing XML data with PostgreSQL will be less
4581 error-prone and more efficient if the XML data encoding, client encoding,
4582 and server encoding are the same. Since XML data is internally
4583 processed in UTF-8, computations will be most efficient if the
4584 server encoding is also UTF-8.
4585 </para>
4587 <caution>
4588 <para>
4589 Some XML-related functions may not work at all on non-ASCII data
4590 when the server encoding is not UTF-8. This is known to be an
4592 </para>
4593 </caution>
4594 </sect2>
4599 <para>
4601 provide any comparison operators. This is because there is no
4602 well-defined and universally useful comparison algorithm for XML
4603 data. One consequence of this is that you cannot retrieve rows by
4605 values should therefore typically be accompanied by a separate key
4606 field such as an ID. An alternative solution for comparing XML
4607 values is to convert them to character strings first, but note
4608 that character string comparison has little to do with a useful
4609 XML comparison method.
4610 </para>
4612 <para>
4614 data type, it is not possible to create an index directly on a
4615 column of this type. If speedy searches in XML data are desired,
4616 possible workarounds include casting the expression to a
4617 character string type and indexing that, or indexing an XPath
4618 expression. Of course, the actual query would have to be adjusted
4619 to search by the indexed expression.
4620 </para>
4622 <para>
4623 The text-search functionality in PostgreSQL can also be used to speed
4624 up full-document searches of XML data. The necessary
4625 preprocessing support is, however, not yet available in the PostgreSQL
4626 distribution.
4627 </para>
4628 </sect2>
4629 </sect1>
4631 &json;
4633 &array;
4635 &rowtypes;
4637 &rangetypes;
4644 </indexterm>
4649 </indexterm>
4651 <para>
4654 it can have constraints that restrict its valid values to a subset of
4655 what the underlying type would allow. Otherwise it behaves like the
4656 underlying type — for example, any operator or function that
4657 can be applied to the underlying type will work on the domain type.
4658 The underlying type can be any built-in or user-defined base type,
4659 enum type, array type, composite type, range type, or another domain.
4660 </para>
4662 <para>
4663 For example, we could create a domain over integers that accepts only
4664 positive integers:
4665 <programlisting>
4667 CREATE TABLE mytable (id posint);
4668 INSERT INTO mytable VALUES(1); -- works
4669 INSERT INTO mytable VALUES(-1); -- fails
4670 </programlisting>
4671 </para>
4673 <para>
4674 When an operator or function of the underlying type is applied to a
4675 domain value, the domain is automatically down-cast to the underlying
4680 to be rechecked. In this case, that would result in an error if the
4682 1. Assigning a value of the underlying type to a field or variable of
4683 the domain type is allowed without writing an explicit cast, but the
4684 domain's constraints will be checked.
4685 </para>
4687 <para>
4689 </para>
4690 </sect1>
4698 </indexterm>
4702 </indexterm>
4706 </indexterm>
4710 </indexterm>
4714 </indexterm>
4718 </indexterm>
4722 </indexterm>
4726 </indexterm>
4730 </indexterm>
4734 </indexterm>
4738 </indexterm>
4742 </indexterm>
4746 </indexterm>
4750 </indexterm>
4754 </indexterm>
4758 </indexterm>
4762 </indexterm>
4764 <para>
4765 Object identifiers (OIDs) are used internally by
4767 system tables.
4772 overview.
4773 </para>
4775 <para>
4777 four-byte integer. Therefore, it is not large enough to provide
4778 database-wide uniqueness in large databases, or even in large
4779 individual tables.
4780 </para>
4782 <para>
4784 It can be cast to integer, however, and then manipulated using the
4785 standard integer operators. (Beware of possible
4786 signed-versus-unsigned confusion if you do this.)
4787 </para>
4789 <para>
4790 The OID alias types have no operations of their own except
4791 for specialized input and output routines. These routines are able
4792 to accept and display symbolic names for system objects, rather than
4794 types allow simplified lookup of OID values for objects. For example,
4797 <programlisting>
4798 SELECT * FROM pg_attribute WHERE attrelid = 'mytable'::regclass;
4799 </programlisting>
4800 rather than:
4801 <programlisting>
4802 SELECT * FROM pg_attribute
4803 WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'mytable');
4804 </programlisting>
4805 While that doesn't look all that bad by itself, it's still oversimplified.
4806 A far more complicated sub-select would be needed to
4807 select the right OID if there are multiple tables named
4811 automatically. Similarly, casting a table's OID to
4813 </para>
4818 <thead>
4819 <row>
4824 </row>
4825 </thead>
4827 <tbody>
4829 <row>
4834 </row>
4836 <row>
4841 </row>
4843 <row>
4848 </row>
4850 <row>
4855 </row>
4857 <row>
4862 </row>
4864 <row>
4869 </row>
4871 <row>
4876 </row>
4878 <row>
4884 </row>
4886 <row>
4891 </row>
4893 <row>
4898 </row>
4900 <row>
4905 </row>
4907 <row>
4912 </row>
4913 </tbody>
4914 </tgroup>
4915 </table>
4917 <para>
4918 All of the OID alias types for objects that are grouped by namespace
4919 accept schema-qualified names, and will
4920 display schema-qualified names on output if the object would not
4921 be found in the current search path without being qualified.
4927 accept input names that are unique (not overloaded), so they are
4931 operand.
4932 </para>
4934 <para>
4935 The input functions for these types allow whitespace between tokens,
4936 and will fold upper-case letters to lower case, except within double
4937 quotes; this is done to make the syntax rules similar to the way
4938 object names are written in SQL. Conversely, the output functions
4939 will use double quotes if needed to make the output be a valid SQL
4940 identifier. For example, the OID of a function
4942 taking two integer arguments could be entered as
4945 Both the function name and the argument type names could be
4946 schema-qualified, too.
4947 </para>
4949 <para>
4951 the OID of a table, or another kind of database object, and for
4953 appropriate OID alias type). This means you do not have to look up
4954 the object's OID by hand, but can just enter its name as a string
4956 takes a sequence relation's OID, so you could call it like this:
4957 <programlisting>
4961 nextval('myschema.foo') <lineannotation>operates on <literal>myschema.foo</literal></lineannotation>
4963 nextval('foo') <lineannotation>searches search path for <literal>foo</literal></lineannotation>
4964 </programlisting>
4965 </para>
4967 <note>
4968 <para>
4969 When you write the argument of such a function as an unadorned
4971 (or the appropriate type).
4972 Since this is really just an OID, it will track the originally
4973 identified object despite later renaming, schema reassignment,
4975 object references in column defaults and views. But sometimes you might
4977 at run time. To get late-binding behavior, force the constant to be
4979 <programlisting>
4980 nextval('foo'::text) <lineannotation><literal>foo</literal> is looked up at runtime</lineannotation>
4981 </programlisting>
4983 can also be used to perform run-time lookups. See
4985 </para>
4986 </note>
4988 <para>
4990 is to look up the OID of a table listed in
4992 such OIDs directly. One might for example wish to call
4994 the table OID. Taking the above rules into account, the correct way
4995 to do that is
4996 <programlisting>
4997 SELECT table_schema, table_name,
4998 pg_relation_size((quote_ident(table_schema) || '.' ||
4999 quote_ident(table_name))::regclass)
5000 FROM information_schema.tables
5001 WHERE ...
5002 </programlisting>
5004 double-quoting the identifiers where needed. The seemingly easier
5005 <programlisting>
5006 SELECT pg_relation_size(table_name)
5007 FROM information_schema.tables
5008 WHERE ...
5009 </programlisting>
5011 tables that are outside your search path or have names that require
5012 quoting.
5013 </para>
5015 <para>
5016 An additional property of most of the OID alias types is the creation of
5017 dependencies. If a
5018 constant of one of these types appears in a stored expression
5019 (such as a column default expression or view), it creates a dependency
5020 on the referenced object. For example, if a column has a default
5023 understands that the default expression depends on the sequence
5025 be dropped without first removing the default expression. The
5027 create a dependency.
5029 type are not allowed in stored expressions.)
5030 </para>
5032 <para>
5035 <structfield>xmin</structfield> and <structfield>xmax</structfield>. Transaction identifiers are 32-bit quantities.
5038 monotonically and cannot be reused in the lifetime of a database
5040 </para>
5042 <para>
5044 command identifier. This is the data type of the system columns
5045 <structfield>cmin</structfield> and <structfield>cmax</structfield>. Command identifiers are also 32-bit quantities.
5046 </para>
5048 <para>
5050 identifier (row identifier). This is the data type of the system column
5052 (block number, tuple index within block) that identifies the
5053 physical location of the row within its table.
5054 </para>
5056 <para>
5057 (The system columns are further explained in <xref
5059 </para>
5060 </sect1>
5067 </indexterm>
5069 <para>
5071 Number) data which is a pointer to a location in the WAL. This type is a
5074 </para>
5076 <para>
5077 Internally, an LSN is a 64-bit integer, representing a byte position in
5078 the write-ahead log stream. It is printed as two hexadecimal numbers of
5079 up to 8 digits each, separated by a slash; for example,
5084 those write-ahead log locations. Also the number of bytes can be
5085 added into and subtracted from LSN using the
5091 </para>
5092 </sect1>
5099 </indexterm>
5103 </indexterm>
5107 </indexterm>
5111 </indexterm>
5115 </indexterm>
5119 </indexterm>
5123 </indexterm>
5127 </indexterm>
5131 </indexterm>
5135 </indexterm>
5139 </indexterm>
5143 </indexterm>
5147 </indexterm>
5151 </indexterm>
5155 </indexterm>
5159 </indexterm>
5163 </indexterm>
5167 </indexterm>
5171 </indexterm>
5175 </indexterm>
5179 </indexterm>
5183 </indexterm>
5187 </indexterm>
5191 </indexterm>
5195 </indexterm>
5197 <para>
5199 number of special-purpose entries that are collectively called
5201 column data type, but it can be used to declare a function's
5202 argument or result type. Each of the available pseudo-types is
5203 useful in situations where a function's behavior does not
5204 correspond to simply taking or returning a value of a specific
5207 pseudo-types.
5208 </para>
5215 <thead>
5216 <row>
5219 </row>
5220 </thead>
5222 <tbody>
5223 <row>
5226 </row>
5228 <row>
5230 <entry>Indicates that a function accepts any data type
5232 </row>
5234 <row>
5236 <entry>Indicates that a function accepts any array data type
5238 </row>
5240 <row>
5242 <entry>Indicates that a function accepts any non-array data type
5244 </row>
5246 <row>
5248 <entry>Indicates that a function accepts any enum data type
5251 </row>
5253 <row>
5255 <entry>Indicates that a function accepts any range data type
5258 </row>
5260 <row>
5262 <entry>Indicates that a function accepts any multirange data type
5265 </row>
5267 <row>
5269 <entry>Indicates that a function accepts any data type,
5270 with automatic promotion of multiple arguments to a common data type
5272 </row>
5274 <row>
5276 <entry>Indicates that a function accepts any array data type,
5277 with automatic promotion of multiple arguments to a common data type
5279 </row>
5281 <row>
5283 <entry>Indicates that a function accepts any non-array data type,
5284 with automatic promotion of multiple arguments to a common data type
5286 </row>
5288 <row>
5290 <entry>Indicates that a function accepts any range data type,
5291 with automatic promotion of multiple arguments to a common data type
5294 </row>
5296 <row>
5298 <entry>Indicates that a function accepts any multirange data type,
5299 with automatic promotion of multiple arguments to a common data type
5302 </row>
5304 <row>
5307 </row>
5309 <row>
5311 <entry>Indicates that a function accepts or returns a server-internal
5312 data type.</entry>
5313 </row>
5315 <row>
5317 <entry>A procedural language call handler is declared to return <type>language_handler</type>.</entry>
5318 </row>
5320 <row>
5323 </row>
5325 <row>
5327 <entry>A table access method handler is declared to return <type>table_am_handler</type>.</entry>
5328 </row>
5330 <row>
5332 <entry>An index access method handler is declared to return <type>index_am_handler</type>.</entry>
5333 </row>
5335 <row>
5338 </row>
5340 <row>
5343 </row>
5345 <row>
5348 </row>
5350 <row>
5353 </row>
5355 <row>
5357 <entry>Identifies a representation of DDL commands that is available to event triggers.</entry>
5358 </row>
5360 <row>
5363 </row>
5365 <row>
5367 <entry>Identifies a not-yet-resolved type, e.g., of an undecorated
5368 string literal.</entry>
5369 </row>
5370 </tbody>
5371 </tgroup>
5372 </table>
5374 <para>
5375 Functions coded in C (whether built-in or dynamically loaded) can be
5376 declared to accept or return any of these pseudo-types. It is up to
5377 the function author to ensure that the function will behave safely
5378 when a pseudo-type is used as an argument type.
5379 </para>
5381 <para>
5382 Functions coded in procedural languages can use pseudo-types only as
5383 allowed by their implementation languages. At present most procedural
5384 languages forbid use of a pseudo-type as an argument type, and allow
5387 as a trigger or event trigger). Some also support polymorphic functions
5388 using the polymorphic pseudo-types, which are shown above and discussed
5390 </para>
5392 <para>
5394 that are meant only to be called internally by the database
5398 preserve the type safety of this restriction it is important to
5399 follow this coding rule: do not create any function that is
5402 </para>
5404 </sect1>
5406 </chapter>