Debian Codegraph @

doc/src/sgml/datatype.sgml

raw
1 <!-- doc/src/sgml/datatype.sgml -->
2
3 <chapter id="datatype">
4 <title>Data Types</title>
5
6 <indexterm zone="datatype">
7 <primary>data type</primary>
8 </indexterm>
9
10 <indexterm>
11 <primary>type</primary>
12 <see>data type</see>
13 </indexterm>
14
15 <para>
16 <productname>PostgreSQL</productname> has a rich set of native data
17 types available to users. Users can add new types to
18 <productname>PostgreSQL</productname> using the <xref
19 linkend="sql-createtype"/> command.
20 </para>
21
22 <para>
23 <xref linkend="datatype-table"/> shows all the built-in general-purpose data
24 types. Most of the alternative names listed in the
25 <quote>Aliases</quote> column are the names used internally by
26 <productname>PostgreSQL</productname> for historical reasons. In
27 addition, some internally used or deprecated types are available,
28 but are not listed here.
29 </para>
30
31 <table id="datatype-table">
32 <title>Data Types</title>
33 <tgroup cols="3">
34 <colspec colname="col1" colwidth="2*"/>
35 <colspec colname="col2" colwidth="1*"/>
36 <colspec colname="col3" colwidth="2*"/>
37 <thead>
38 <row>
39 <entry>Name</entry>
40 <entry>Aliases</entry>
41 <entry>Description</entry>
42 </row>
43 </thead>
44
45 <tbody>
46 <row>
47 <entry><type>bigint</type></entry>
48 <entry><type>int8</type></entry>
49 <entry>signed eight-byte integer</entry>
50 </row>
51
52 <row>
53 <entry><type>bigserial</type></entry>
54 <entry><type>serial8</type></entry>
55 <entry>autoincrementing eight-byte integer</entry>
56 </row>
57
58 <row>
59 <entry><type>bit [ (<replaceable>n</replaceable>) ]</type></entry>
60 <entry></entry>
61 <entry>fixed-length bit string</entry>
62 </row>
63
64 <row>
65 <entry><type>bit varying [ (<replaceable>n</replaceable>) ]</type></entry>
66 <entry><type>varbit [ (<replaceable>n</replaceable>) ]</type></entry>
67 <entry>variable-length bit string</entry>
68 </row>
69
70 <row>
71 <entry><type>boolean</type></entry>
72 <entry><type>bool</type></entry>
73 <entry>logical Boolean (true/false)</entry>
74 </row>
75
76 <row>
77 <entry><type>box</type></entry>
78 <entry></entry>
79 <entry>rectangular box on a plane</entry>
80 </row>
81
82 <row>
83 <entry><type>bytea</type></entry>
84 <entry></entry>
85 <entry>binary data (<quote>byte array</quote>)</entry>
86 </row>
87
88 <row>
89 <entry><type>character [ (<replaceable>n</replaceable>) ]</type></entry>
90 <entry><type>char [ (<replaceable>n</replaceable>) ]</type></entry>
91 <entry>fixed-length character string</entry>
92 </row>
93
94 <row>
95 <entry><type>character varying [ (<replaceable>n</replaceable>) ]</type></entry>
96 <entry><type>varchar [ (<replaceable>n</replaceable>) ]</type></entry>
97 <entry>variable-length character string</entry>
98 </row>
99
100 <row>
101 <entry><type>cidr</type></entry>
102 <entry></entry>
103 <entry>IPv4 or IPv6 network address</entry>
104 </row>
105
106 <row>
107 <entry><type>circle</type></entry>
108 <entry></entry>
109 <entry>circle on a plane</entry>
110 </row>
111
112 <row>
113 <entry><type>date</type></entry>
114 <entry></entry>
115 <entry>calendar date (year, month, day)</entry>
116 </row>
117
118 <row>
119 <entry><type>double precision</type></entry>
120 <entry><type>float</type>, <type>float8</type></entry>
121 <entry>double precision floating-point number (8 bytes)</entry>
122 </row>
123
124 <row>
125 <entry><type>inet</type></entry>
126 <entry></entry>
127 <entry>IPv4 or IPv6 host address</entry>
128 </row>
129
130 <row>
131 <entry><type>integer</type></entry>
132 <entry><type>int</type>, <type>int4</type></entry>
133 <entry>signed four-byte integer</entry>
134 </row>
135
136 <row>
137 <entry><type>interval [ <replaceable>fields</replaceable> ] [ (<replaceable>p</replaceable>) ]</type></entry>
138 <entry></entry>
139 <entry>time span</entry>
140 </row>
141
142 <row>
143 <entry><type>json</type></entry>
144 <entry></entry>
145 <entry>textual JSON data</entry>
146 </row>
147
148 <row>
149 <entry><type>jsonb</type></entry>
150 <entry></entry>
151 <entry>binary JSON data, decomposed</entry>
152 </row>
153
154 <row>
155 <entry><type>line</type></entry>
156 <entry></entry>
157 <entry>infinite line on a plane</entry>
158 </row>
159
160 <row>
161 <entry><type>lseg</type></entry>
162 <entry></entry>
163 <entry>line segment on a plane</entry>
164 </row>
165
166 <row>
167 <entry><type>macaddr</type></entry>
168 <entry></entry>
169 <entry>MAC (Media Access Control) address</entry>
170 </row>
171
172 <row>
173 <entry><type>macaddr8</type></entry>
174 <entry></entry>
175 <entry>MAC (Media Access Control) address (EUI-64 format)</entry>
176 </row>
177
178 <row>
179 <entry><type>money</type></entry>
180 <entry></entry>
181 <entry>currency amount</entry>
182 </row>
183
184 <row>
185 <entry><type>numeric [ (<replaceable>p</replaceable>,
186 <replaceable>s</replaceable>) ]</type></entry>
187 <entry><type>decimal [ (<replaceable>p</replaceable>,
188 <replaceable>s</replaceable>) ]</type></entry>
189 <entry>exact numeric of selectable precision</entry>
190 </row>
191
192 <row>
193 <entry><type>path</type></entry>
194 <entry></entry>
195 <entry>geometric path on a plane</entry>
196 </row>
197
198 <row>
199 <entry><type>pg_lsn</type></entry>
200 <entry></entry>
201 <entry><productname>PostgreSQL</productname> Log Sequence Number</entry>
202 </row>
203
204 <row>
205 <entry><type>pg_snapshot</type></entry>
206 <entry></entry>
207 <entry>user-level transaction ID snapshot</entry>
208 </row>
209
210 <row>
211 <entry><type>point</type></entry>
212 <entry></entry>
213 <entry>geometric point on a plane</entry>
214 </row>
215
216 <row>
217 <entry><type>polygon</type></entry>
218 <entry></entry>
219 <entry>closed geometric path on a plane</entry>
220 </row>
221
222 <row>
223 <entry><type>real</type></entry>
224 <entry><type>float4</type></entry>
225 <entry>single precision floating-point number (4 bytes)</entry>
226 </row>
227
228 <row>
229 <entry><type>smallint</type></entry>
230 <entry><type>int2</type></entry>
231 <entry>signed two-byte integer</entry>
232 </row>
233
234 <row>
235 <entry><type>smallserial</type></entry>
236 <entry><type>serial2</type></entry>
237 <entry>autoincrementing two-byte integer</entry>
238 </row>
239
240 <row>
241 <entry><type>serial</type></entry>
242 <entry><type>serial4</type></entry>
243 <entry>autoincrementing four-byte integer</entry>
244 </row>
245
246 <row>
247 <entry><type>text</type></entry>
248 <entry></entry>
249 <entry>variable-length character string</entry>
250 </row>
251
252 <row>
253 <entry><type>time [ (<replaceable>p</replaceable>) ] [ without time zone ]</type></entry>
254 <entry></entry>
255 <entry>time of day (no time zone)</entry>
256 </row>
257
258 <row>
259 <entry><type>time [ (<replaceable>p</replaceable>) ] with time zone</type></entry>
260 <entry><type>timetz</type></entry>
261 <entry>time of day, including time zone</entry>
262 </row>
263
264 <row>
265 <entry><type>timestamp [ (<replaceable>p</replaceable>) ] [ without time zone ]</type></entry>
266 <entry></entry>
267 <entry>date and time (no time zone)</entry>
268 </row>
269
270 <row>
271 <entry><type>timestamp [ (<replaceable>p</replaceable>) ] with time zone</type></entry>
272 <entry><type>timestamptz</type></entry>
273 <entry>date and time, including time zone</entry>
274 </row>
275
276 <row>
277 <entry><type>tsquery</type></entry>
278 <entry></entry>
279 <entry>text search query</entry>
280 </row>
281
282 <row>
283 <entry><type>tsvector</type></entry>
284 <entry></entry>
285 <entry>text search document</entry>
286 </row>
287
288 <row>
289 <entry><type>txid_snapshot</type></entry>
290 <entry></entry>
291 <entry>user-level transaction ID snapshot (deprecated; see <type>pg_snapshot</type>)</entry>
292 </row>
293
294 <row>
295 <entry><type>uuid</type></entry>
296 <entry></entry>
297 <entry>universally unique identifier</entry>
298 </row>
299
300 <row>
301 <entry><type>xml</type></entry>
302 <entry></entry>
303 <entry>XML data</entry>
304 </row>
305 </tbody>
306 </tgroup>
307 </table>
308
309 <note>
310 <title>Compatibility</title>
311 <para>
312 The following types (or spellings thereof) are specified by
313 <acronym>SQL</acronym>: <type>bigint</type>, <type>bit</type>, <type>bit
314 varying</type>, <type>boolean</type>, <type>char</type>,
315 <type>character varying</type>, <type>character</type>,
316 <type>varchar</type>, <type>date</type>, <type>double
317 precision</type>, <type>integer</type>, <type>interval</type>,
318 <type>numeric</type>, <type>decimal</type>, <type>real</type>,
319 <type>smallint</type>, <type>time</type> (with or without time zone),
320 <type>timestamp</type> (with or without time zone),
321 <type>xml</type>.
322 </para>
323 </note>
324
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
329 to <productname>PostgreSQL</productname>, such as geometric
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>
336
337 <sect1 id="datatype-numeric">
338 <title>Numeric Types</title>
339
340 <indexterm zone="datatype-numeric">
341 <primary>data type</primary>
342 <secondary>numeric</secondary>
343 </indexterm>
344
345 <para>
346 Numeric types consist of two-, four-, and eight-byte integers,
347 four- and eight-byte floating-point numbers, and selectable-precision
348 decimals. <xref linkend="datatype-numeric-table"/> lists the
349 available types.
350 </para>
351
352 <table id="datatype-numeric-table">
353 <title>Numeric Types</title>
354 <tgroup cols="4">
355 <colspec colname="col1" colwidth="2*"/>
356 <colspec colname="col2" colwidth="1*"/>
357 <colspec colname="col3" colwidth="2*"/>
358 <colspec colname="col4" colwidth="2*"/>
359 <thead>
360 <row>
361 <entry>Name</entry>
362 <entry>Storage Size</entry>
363 <entry>Description</entry>
364 <entry>Range</entry>
365 </row>
366 </thead>
367
368 <tbody>
369 <row>
370 <entry><type>smallint</type></entry>
371 <entry>2 bytes</entry>
372 <entry>small-range integer</entry>
373 <entry>-32768 to +32767</entry>
374 </row>
375 <row>
376 <entry><type>integer</type></entry>
377 <entry>4 bytes</entry>
378 <entry>typical choice for integer</entry>
379 <entry>-2147483648 to +2147483647</entry>
380 </row>
381 <row>
382 <entry><type>bigint</type></entry>
383 <entry>8 bytes</entry>
384 <entry>large-range integer</entry>
385 <entry>-9223372036854775808 to +9223372036854775807</entry>
386 </row>
387
388 <row>
389 <entry><type>decimal</type></entry>
390 <entry>variable</entry>
391 <entry>user-specified precision, exact</entry>
392 <entry>up to 131072 digits before the decimal point; up to 16383 digits after the decimal point</entry>
393 </row>
394 <row>
395 <entry><type>numeric</type></entry>
396 <entry>variable</entry>
397 <entry>user-specified precision, exact</entry>
398 <entry>up to 131072 digits before the decimal point; up to 16383 digits after the decimal point</entry>
399 </row>
400
401 <row>
402 <entry><type>real</type></entry>
403 <entry>4 bytes</entry>
404 <entry>variable-precision, inexact</entry>
405 <entry>6 decimal digits precision</entry>
406 </row>
407 <row>
408 <entry><type>double precision</type></entry>
409 <entry>8 bytes</entry>
410 <entry>variable-precision, inexact</entry>
411 <entry>15 decimal digits precision</entry>
412 </row>
413
414 <row>
415 <entry><type>smallserial</type></entry>
416 <entry>2 bytes</entry>
417 <entry>small autoincrementing integer</entry>
418 <entry>1 to 32767</entry>
419 </row>
420
421 <row>
422 <entry><type>serial</type></entry>
423 <entry>4 bytes</entry>
424 <entry>autoincrementing integer</entry>
425 <entry>1 to 2147483647</entry>
426 </row>
427
428 <row>
429 <entry><type>bigserial</type></entry>
430 <entry>8 bytes</entry>
431 <entry>large autoincrementing integer</entry>
432 <entry>1 to 9223372036854775807</entry>
433 </row>
434 </tbody>
435 </tgroup>
436 </table>
437
438 <para>
439 The syntax of constants for the numeric types is described in
440 <xref linkend="sql-syntax-constants"/>. The numeric types have a
441 full set of corresponding arithmetic operators and
442 functions. Refer to <xref linkend="functions"/> for more
443 information. The following sections describe the types in detail.
444 </para>
445
446 <sect2 id="datatype-int">
447 <title>Integer Types</title>
448
449 <indexterm zone="datatype-int">
450 <primary>integer</primary>
451 </indexterm>
452
453 <indexterm zone="datatype-int">
454 <primary>smallint</primary>
455 </indexterm>
456
457 <indexterm zone="datatype-int">
458 <primary>bigint</primary>
459 </indexterm>
460
461 <indexterm>
462 <primary>int4</primary>
463 <see>integer</see>
464 </indexterm>
465
466 <indexterm>
467 <primary>int2</primary>
468 <see>smallint</see>
469 </indexterm>
470
471 <indexterm>
472 <primary>int8</primary>
473 <see>bigint</see>
474 </indexterm>
475
476 <para>
477 The types <type>smallint</type>, <type>integer</type>, and
478 <type>bigint</type> store whole numbers, that is, numbers without
479 fractional components, of various ranges. Attempts to store
480 values outside of the allowed range will result in an error.
481 </para>
482
483 <para>
484 The type <type>integer</type> is the common choice, as it offers
485 the best balance between range, storage size, and performance.
486 The <type>smallint</type> type is generally only used if disk
487 space is at a premium. The <type>bigint</type> type is designed to be
488 used when the range of the <type>integer</type> type is insufficient.
489 </para>
490
491 <para>
492 <acronym>SQL</acronym> only specifies the integer types
493 <type>integer</type> (or <type>int</type>),
494 <type>smallint</type>, and <type>bigint</type>. The
495 type names <type>int2</type>, <type>int4</type>, and
496 <type>int8</type> are extensions, which are also used by some
497 other <acronym>SQL</acronym> database systems.
498 </para>
499
500 </sect2>
501
502 <sect2 id="datatype-numeric-decimal">
503 <title>Arbitrary Precision Numbers</title>
504
505 <indexterm>
506 <primary>numeric (data type)</primary>
507 </indexterm>
508
509 <indexterm>
510 <primary>arbitrary precision numbers</primary>
511 </indexterm>
512
513 <indexterm>
514 <primary>decimal</primary>
515 <see>numeric</see>
516 </indexterm>
517
518 <para>
519 The type <type>numeric</type> can store numbers with a
520 very large number of digits. It is especially recommended for
521 storing monetary amounts and other quantities where exactness is
522 required. Calculations with <type>numeric</type> values yield exact
523 results where possible, e.g., addition, subtraction, multiplication.
524 However, calculations on <type>numeric</type> values are very slow
525 compared to the integer types, or to the floating-point types
526 described in the next section.
527 </para>
528
529 <para>
530 We use the following terms below: The
531 <firstterm>precision</firstterm> of a <type>numeric</type>
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.
534 The <firstterm>scale</firstterm> of a <type>numeric</type> is the
535 count of decimal digits in the fractional part, to the right of the
536 decimal point. So the number 23.5141 has a precision of 6 and a
537 scale of 4. Integers can be considered to have a scale of zero.
538 </para>
539
540 <para>
541 Both the maximum precision and the maximum scale of a
542 <type>numeric</type> column can be
543 configured. To declare a column of type <type>numeric</type> use
544 the syntax:
545 <programlisting>
546 NUMERIC(<replaceable>precision</replaceable>, <replaceable>scale</replaceable>)
547 </programlisting>
548 The precision must be positive, while the scale may be positive or
549 negative (see below). Alternatively:
550 <programlisting>
551 NUMERIC(<replaceable>precision</replaceable>)
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
561 <type>numeric</type> columns with a declared scale will coerce
562 input values to that scale. (The <acronym>SQL</acronym> standard
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>
568
569 <note>
570 <para>
571 The maximum precision that can be explicitly specified in
572 a <type>numeric</type> type declaration is 1000. An
573 unconstrained <type>numeric</type> column is subject to the limits
574 described in <xref linkend="datatype-numeric-table"/>.
575 </para>
576 </note>
577
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>
586 NUMERIC(3, 1)
587 </programlisting>
588 will round values to 1 decimal place and can store values between
589 -99.9 and 99.9, inclusive.
590 </para>
591
592 <para>
593 Beginning in <productname>PostgreSQL</productname> 15, it is allowed
594 to declare a <type>numeric</type> column with a negative scale. Then
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>
599 NUMERIC(2, -3)
600 </programlisting>
601 will round values to the nearest thousand and can store values
602 between -99000 and 99000, inclusive.
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>
609 NUMERIC(3, 5)
610 </programlisting>
611 will round values to 5 decimal places and can store values between
612 -0.00999 and 0.00999, inclusive.
613 </para>
614
615 <note>
616 <para>
617 <productname>PostgreSQL</productname> permits the scale in a
618 <type>numeric</type> type declaration to be any value in the range
619 -1000 to 1000. However, the <acronym>SQL</acronym> standard requires
620 the scale to be in the range 0 to <replaceable>precision</replaceable>.
621 Using scales outside that range may not be portable to other database
622 systems.
623 </para>
624 </note>
625
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
629 are maximums, not fixed allocations. (In this sense the <type>numeric</type>
630 type is more akin to <type>varchar(<replaceable>n</replaceable>)</type>
631 than to <type>char(<replaceable>n</replaceable>)</type>.) The actual storage
632 requirement is two bytes for each group of four decimal digits,
633 plus three to eight bytes overhead.
634 </para>
635
636 <indexterm>
637 <primary>infinity</primary>
638 <secondary>numeric (data type)</secondary>
639 </indexterm>
640
641 <indexterm>
642 <primary>NaN</primary>
643 <see>not a number</see>
644 </indexterm>
645
646 <indexterm>
647 <primary>not a number</primary>
648 <secondary>numeric (data type)</secondary>
649 </indexterm>
650
651 <para>
652 In addition to ordinary numeric values, the <type>numeric</type> type
653 has several special values:
654 <literallayout>
655 <literal>Infinity</literal>
656 <literal>-Infinity</literal>
657 <literal>NaN</literal>
658 </literallayout>
659 These are adapted from the IEEE 754 standard, and represent
660 <quote>infinity</quote>, <quote>negative infinity</quote>, and
661 <quote>not-a-number</quote>, respectively. When writing these values
662 as constants in an SQL command, you must put quotes around them,
663 for example <literal>UPDATE table SET x = '-Infinity'</literal>.
664 On input, these strings are recognized in a case-insensitive manner.
665 The infinity values can alternatively be spelled <literal>inf</literal>
666 and <literal>-inf</literal>.
667 </para>
668
669 <para>
670 The infinity values behave as per mathematical expectations. For
671 example, <literal>Infinity</literal> plus any finite value equals
672 <literal>Infinity</literal>, as does <literal>Infinity</literal>
673 plus <literal>Infinity</literal>; but <literal>Infinity</literal>
674 minus <literal>Infinity</literal> yields <literal>NaN</literal> (not a
675 number), because it has no well-defined interpretation. Note that an
676 infinity can only be stored in an unconstrained <type>numeric</type>
677 column, because it notionally exceeds any finite precision limit.
678 </para>
679
680 <para>
681 The <literal>NaN</literal> (not a number) value is used to represent
682 undefined calculational results. In general, any operation with
683 a <literal>NaN</literal> input yields another <literal>NaN</literal>.
684 The only exception is when the operation's other inputs are such that
685 the same output would be obtained if the <literal>NaN</literal> were to
686 be replaced by any finite or infinite numeric value; then, that output
687 value is used for <literal>NaN</literal> too. (An example of this
688 principle is that <literal>NaN</literal> raised to the zero power
689 yields one.)
690 </para>
691
692 <note>
693 <para>
694 In most implementations of the <quote>not-a-number</quote> concept,
695 <literal>NaN</literal> is not considered equal to any other numeric
696 value (including <literal>NaN</literal>). In order to allow
697 <type>numeric</type> values to be sorted and used in tree-based
698 indexes, <productname>PostgreSQL</productname> treats <literal>NaN</literal>
699 values as equal, and greater than all non-<literal>NaN</literal>
700 values.
701 </para>
702 </note>
703
704 <para>
705 The types <type>decimal</type> and <type>numeric</type> are
706 equivalent. Both types are part of the <acronym>SQL</acronym>
707 standard.
708 </para>
709
710 <para>
711 When rounding values, the <type>numeric</type> type rounds ties away
712 from zero, while (on most machines) the <type>real</type>
713 and <type>double precision</type> types round ties to the nearest even
714 number. For example:
715
716 <programlisting>
717 SELECT x,
718 round(x::numeric) AS num_round,
719 round(x::double precision) AS dbl_round
720 FROM generate_series(-3.5, 3.5, 1) as x;
721 x | num_round | dbl_round
722 ------+-----------+-----------
723 -3.5 | -4 | -4
724 -2.5 | -3 | -2
725 -1.5 | -2 | -2
726 -0.5 | -1 | -0
727 0.5 | 1 | 0
728 1.5 | 2 | 2
729 2.5 | 3 | 2
730 3.5 | 4 | 4
731 (8 rows)
732 </programlisting>
733 </para>
734 </sect2>
735
736
737 <sect2 id="datatype-float">
738 <title>Floating-Point Types</title>
739
740 <indexterm zone="datatype-float">
741 <primary>real</primary>
742 </indexterm>
743
744 <indexterm zone="datatype-float">
745 <primary>double precision</primary>
746 </indexterm>
747
748 <indexterm>
749 <primary>float4</primary>
750 <see>real</see>
751 </indexterm>
752
753 <indexterm>
754 <primary>float8</primary>
755 <see>double precision</see>
756 </indexterm>
757
758 <indexterm zone="datatype-float">
759 <primary>floating point</primary>
760 </indexterm>
761
762 <para>
763 The data types <type>real</type> and <type>double precision</type> are
764 inexact, variable-precision numeric types. On all currently supported
765 platforms, these types are implementations of <acronym>IEEE</acronym>
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>
770
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
783 monetary amounts), use the <type>numeric</type> type instead.
784 </para>
785 </listitem>
786
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>
795
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>
804
805 <para>
806 On all currently supported platforms, the <type>real</type> type has a
807 range of around 1E-37 to 1E+37 with a precision of at least 6 decimal
808 digits. The <type>double precision</type> type has a range of around
809 1E-307 to 1E+308 with a precision of at least 15 digits. Values that are
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>
815
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
821 currently never <emphasis>exactly</emphasis> midway between two
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
824 use at most 17 significant decimal digits for <type>float8</type>
825 values, and at most 9 digits for <type>float4</type> values.
826 </para>
827
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>
834
835 <para>
836 For compatibility with output generated by older versions
837 of <productname>PostgreSQL</productname>, and to allow the output
838 precision to be reduced, the <xref linkend="guc-extra-float-digits"/>
839 parameter can be used to select rounded decimal output instead. Setting a
840 value of 0 restores the previous default of rounding the value to 6
841 (for <type>float4</type>) or 15 (for <type>float8</type>)
842 significant decimal digits. Setting a negative value reduces the number
843 of digits further; for example -2 would round output to 4 or 13 digits
844 respectively.
845 </para>
846
847 <para>
848 Any value of <xref linkend="guc-extra-float-digits"/> greater than 0
849 selects the shortest-precise format.
850 </para>
851
852 <note>
853 <para>
854 Applications that wanted precise values have historically had to set
855 <xref linkend="guc-extra-float-digits"/> to 3 to obtain them. For
856 maximum compatibility between versions, they should continue to do so.
857 </para>
858 </note>
859
860 <indexterm>
861 <primary>infinity</primary>
862 <secondary>floating point</secondary>
863 </indexterm>
864
865 <indexterm>
866 <primary>not a number</primary>
867 <secondary>floating point</secondary>
868 </indexterm>
869
870 <para>
871 In addition to ordinary numeric values, the floating-point types
872 have several special values:
873 <literallayout>
874 <literal>Infinity</literal>
875 <literal>-Infinity</literal>
876 <literal>NaN</literal>
877 </literallayout>
878 These represent the IEEE 754 special values
879 <quote>infinity</quote>, <quote>negative infinity</quote>, and
880 <quote>not-a-number</quote>, respectively. When writing these values
881 as constants in an SQL command, you must put quotes around them,
882 for example <literal>UPDATE table SET x = '-Infinity'</literal>. On input,
883 these strings are recognized in a case-insensitive manner.
884 The infinity values can alternatively be spelled <literal>inf</literal>
885 and <literal>-inf</literal>.
886 </para>
887
888 <note>
889 <para>
890 IEEE 754 specifies that <literal>NaN</literal> should not compare equal
891 to any other floating-point value (including <literal>NaN</literal>).
892 In order to allow floating-point values to be sorted and used
893 in tree-based indexes, <productname>PostgreSQL</productname> treats
894 <literal>NaN</literal> values as equal, and greater than all
895 non-<literal>NaN</literal> values.
896 </para>
897 </note>
898
899 <para>
900 <productname>PostgreSQL</productname> also supports the SQL-standard
901 notations <type>float</type> and
902 <type>float(<replaceable>p</replaceable>)</type> for specifying
903 inexact numeric types. Here, <replaceable>p</replaceable> specifies
904 the minimum acceptable precision in <emphasis>binary</emphasis> digits.
905 <productname>PostgreSQL</productname> accepts
906 <type>float(1)</type> to <type>float(24)</type> as selecting the
907 <type>real</type> type, while
908 <type>float(25)</type> to <type>float(53)</type> select
909 <type>double precision</type>. Values of <replaceable>p</replaceable>
910 outside the allowed range draw an error.
911 <type>float</type> with no precision specified is taken to mean
912 <type>double precision</type>.
913 </para>
914
915 </sect2>
916
917 <sect2 id="datatype-serial">
918 <title>Serial Types</title>
919
920 <indexterm zone="datatype-serial">
921 <primary>smallserial</primary>
922 </indexterm>
923
924 <indexterm zone="datatype-serial">
925 <primary>serial</primary>
926 </indexterm>
927
928 <indexterm zone="datatype-serial">
929 <primary>bigserial</primary>
930 </indexterm>
931
932 <indexterm zone="datatype-serial">
933 <primary>serial2</primary>
934 </indexterm>
935
936 <indexterm zone="datatype-serial">
937 <primary>serial4</primary>
938 </indexterm>
939
940 <indexterm zone="datatype-serial">
941 <primary>serial8</primary>
942 </indexterm>
943
944 <indexterm>
945 <primary>auto-increment</primary>
946 <see>serial</see>
947 </indexterm>
948
949 <indexterm>
950 <primary>sequence</primary>
951 <secondary>and serial type</secondary>
952 </indexterm>
953
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
958 identity column feature, described at <xref linkend="ddl-identity-columns"/>.
959 </para>
960 </note>
961
962 <para>
963 The data types <type>smallserial</type>, <type>serial</type> and
964 <type>bigserial</type> are not true types, but merely
965 a notational convenience for creating unique identifier columns
966 (similar to the <literal>AUTO_INCREMENT</literal> property
967 supported by some other databases). In the current
968 implementation, specifying:
969
970 <programlisting>
971 CREATE TABLE <replaceable class="parameter">tablename</replaceable> (
972 <replaceable class="parameter">colname</replaceable> SERIAL
973 );
974 </programlisting>
975
976 is equivalent to specifying:
977
978 <programlisting>
979 CREATE SEQUENCE <replaceable class="parameter">tablename</replaceable>_<replaceable class="parameter">colname</replaceable>_seq AS integer;
980 CREATE TABLE <replaceable class="parameter">tablename</replaceable> (
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>
985
986 Thus, we have created an integer column and arranged for its default
987 values to be assigned from a sequence generator. A <literal>NOT NULL</literal>
988 constraint is applied to ensure that a null value cannot be
989 inserted. (In most cases you would also want to attach a
990 <literal>UNIQUE</literal> or <literal>PRIMARY KEY</literal> constraint to prevent
991 duplicate values from being inserted by accident, but this is
992 not automatic.) Lastly, the sequence is marked as <quote>owned by</quote>
993 the column, so that it will be dropped if the column or table is dropped.
994 </para>
995
996 <note>
997 <para>
998 Because <type>smallserial</type>, <type>serial</type> and
999 <type>bigserial</type> are implemented using sequences, there may
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.
1005 See <literal>nextval()</literal> in <xref linkend="functions-sequence"/>
1006 for details.
1007 </para>
1008 </note>
1009
1010 <para>
1011 To insert the next value of the sequence into the <type>serial</type>
1012 column, specify that the <type>serial</type>
1013 column should be assigned its default value. This can be done
1014 either by excluding the column from the list of columns in
1015 the <command>INSERT</command> statement, or through the use of
1016 the <literal>DEFAULT</literal> key word.
1017 </para>
1018
1019 <para>
1020 The type names <type>serial</type> and <type>serial4</type> are
1021 equivalent: both create <type>integer</type> columns. The type
1022 names <type>bigserial</type> and <type>serial8</type> work
1023 the same way, except that they create a <type>bigint</type>
1024 column. <type>bigserial</type> should be used if you anticipate
1025 the use of more than 2<superscript>31</superscript> identifiers over the
1026 lifetime of the table. The type names <type>smallserial</type> and
1027 <type>serial2</type> also work the same way, except that they
1028 create a <type>smallint</type> column.
1029 </para>
1030
1031 <para>
1032 The sequence created for a <type>serial</type> column is
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>
1039
1040 <sect1 id="datatype-money">
1041 <title>Monetary Types</title>
1042
1043 <para>
1044 The <type>money</type> type stores a currency amount with a fixed
1045 fractional precision; see <xref
1046 linkend="datatype-money-table"/>. The fractional precision is
1047 determined by the database's <xref linkend="guc-lc-monetary"/> setting.
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
1051 currency formatting, such as <literal>'$1,000.00'</literal>.
1052 Output is generally in the latter form but depends on the locale.
1053 </para>
1054
1055 <table id="datatype-money-table">
1056 <title>Monetary Types</title>
1057 <tgroup cols="4">
1058 <colspec colname="col1" colwidth="2*"/>
1059 <colspec colname="col2" colwidth="1*"/>
1060 <colspec colname="col3" colwidth="2*"/>
1061 <colspec colname="col4" colwidth="2*"/>
1062 <thead>
1063 <row>
1064 <entry>Name</entry>
1065 <entry>Storage Size</entry>
1066 <entry>Description</entry>
1067 <entry>Range</entry>
1068 </row>
1069 </thead>
1070 <tbody>
1071 <row>
1072 <entry><type>money</type></entry>
1073 <entry>8 bytes</entry>
1074 <entry>currency amount</entry>
1075 <entry>-92233720368547758.08 to +92233720368547758.07</entry>
1076 </row>
1077 </tbody>
1078 </tgroup>
1079 </table>
1080
1081 <para>
1082 Since the output of this data type is locale-sensitive, it might not
1083 work to load <type>money</type> data into a database that has a different
1084 setting of <varname>lc_monetary</varname>. To avoid problems, before
1085 restoring a dump into a new database make sure <varname>lc_monetary</varname> has
1086 the same or equivalent value as in the database that was dumped.
1087 </para>
1088
1089 <para>
1090 Values of the <type>numeric</type>, <type>int</type>, and
1091 <type>bigint</type> data types can be cast to <type>money</type>.
1092 Conversion from the <type>real</type> and <type>double precision</type>
1093 data types can be done by casting to <type>numeric</type> first, for
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>
1101
1102 <para>
1103 A <type>money</type> value can be cast to <type>numeric</type> without
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>
1110
1111 <para>
1112 Division of a <type>money</type> value by an integer value is performed
1113 with truncation of the fractional part towards zero. To get a rounded
1114 result, divide by a floating-point value, or cast the <type>money</type>
1115 value to <type>numeric</type> before dividing and back to <type>money</type>
1116 afterwards. (The latter is preferable to avoid risking precision loss.)
1117 When a <type>money</type> value is divided by another <type>money</type>
1118 value, the result is <type>double precision</type> (i.e., a pure number,
1119 not money); the currency units cancel each other out in the division.
1120 </para>
1121 </sect1>
1122
1123
1124 <sect1 id="datatype-character">
1125 <title>Character Types</title>
1126
1127 <indexterm zone="datatype-character">
1128 <primary>character string</primary>
1129 <secondary>data types</secondary>
1130 </indexterm>
1131
1132 <indexterm>
1133 <primary>string</primary>
1134 <see>character string</see>
1135 </indexterm>
1136
1137 <indexterm zone="datatype-character">
1138 <primary>character</primary>
1139 </indexterm>
1140
1141 <indexterm zone="datatype-character">
1142 <primary>character varying</primary>
1143 </indexterm>
1144
1145 <indexterm zone="datatype-character">
1146 <primary>text</primary>
1147 </indexterm>
1148
1149 <indexterm zone="datatype-character">
1150 <primary>char</primary>
1151 </indexterm>
1152
1153 <indexterm zone="datatype-character">
1154 <primary>varchar</primary>
1155 </indexterm>
1156
1157 <indexterm zone="datatype-character">
1158 <primary>bpchar</primary>
1159 </indexterm>
1160
1161 <table id="datatype-character-table">
1162 <title>Character Types</title>
1163 <tgroup cols="2">
1164 <thead>
1165 <row>
1166 <entry>Name</entry>
1167 <entry>Description</entry>
1168 </row>
1169 </thead>
1170 <tbody>
1171 <row>
1172 <entry><type>character varying(<replaceable>n</replaceable>)</type>, <type>varchar(<replaceable>n</replaceable>)</type></entry>
1173 <entry>variable-length with limit</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>
1177 <entry>fixed-length, blank-padded</entry>
1178 </row>
1179 <row>
1180 <entry><type>bpchar</type></entry>
1181 <entry>variable unlimited length, blank-trimmed</entry>
1182 </row>
1183 <row>
1184 <entry><type>text</type></entry>
1185 <entry>variable unlimited length</entry>
1186 </row>
1187 </tbody>
1188 </tgroup>
1189 </table>
1190
1191 <para>
1192 <xref linkend="datatype-character-table"/> shows the
1193 general-purpose character types available in
1194 <productname>PostgreSQL</productname>.
1195 </para>
1196
1197 <para>
1198 <acronym>SQL</acronym> defines two primary character types:
1199 <type>character varying(<replaceable>n</replaceable>)</type> and
1200 <type>character(<replaceable>n</replaceable>)</type>, where <replaceable>n</replaceable>
1201 is a positive integer. Both of these types can store strings up to
1202 <replaceable>n</replaceable> characters (not bytes) in length. An attempt to store a
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
1206 bizarre exception is required by the <acronym>SQL</acronym>
1207 standard.)
1208 However, if one explicitly casts a value to <type>character
1209 varying(<replaceable>n</replaceable>)</type> or
1210 <type>character(<replaceable>n</replaceable>)</type>, then an over-length
1211 value will be truncated to <replaceable>n</replaceable> characters without
1212 raising an error. (This too is required by the
1213 <acronym>SQL</acronym> standard.)
1214 If the string to be stored is shorter than the declared
1215 length, values of type <type>character</type> will be space-padded;
1216 values of type <type>character varying</type> will simply store the
1217 shorter
1218 string.
1219 </para>
1220
1221 <para>
1222 In addition, <productname>PostgreSQL</productname> provides the
1223 <type>text</type> type, which stores strings of any length.
1224 Although the <type>text</type> type is not in the
1225 <acronym>SQL</acronym> standard, several other SQL database
1226 management systems have it as well.
1227 <type>text</type> is <productname>PostgreSQL</productname>'s native
1228 string data type, in that most built-in functions operating on strings
1229 are declared to take or return <type>text</type> not <type>character
1230 varying</type>. For many purposes, <type>character varying</type>
1231 acts as though it were a <link linkend="domains">domain</link>
1232 over <type>text</type>.
1233 </para>
1234
1235 <para>
1236 The type name <type>varchar</type> is an alias for <type>character
1237 varying</type>, while <type>bpchar</type> (with length specifier) and
1238 <type>char</type> are aliases for <type>character</type>. The
1239 <type>varchar</type> and <type>char</type> aliases are defined in the
1240 <acronym>SQL</acronym> standard; <type>bpchar</type> is a
1241 <productname>PostgreSQL</productname> extension.
1242 </para>
1243
1244 <para>
1245 If specified, the length <replaceable>n</replaceable> must be greater
1246 than zero and cannot exceed 10,485,760. If <type>character
1247 varying</type> (or <type>varchar</type>) is used without
1248 length specifier, the type accepts strings of any length. If
1249 <type>bpchar</type> lacks a length specifier, it also accepts strings
1250 of any length, but trailing spaces are semantically insignificant.
1251 If <type>character</type> (or <type>char</type>) lacks a specifier,
1252 it is equivalent to <type>character(1)</type>.
1253 </para>
1254
1255 <para>
1256 Values of type <type>character</type> are physically padded
1257 with spaces to the specified width <replaceable>n</replaceable>, and are
1258 stored and displayed that way. However, trailing spaces are treated as
1259 semantically insignificant and disregarded when comparing two values
1260 of type <type>character</type>. In collations where whitespace
1261 is significant, this behavior can produce unexpected results;
1262 for example <command>SELECT 'a '::CHAR(2) collate "C" &lt;
1263 E'a\n'::CHAR(2)</command> returns true, even though <literal>C</literal>
1264 locale would consider a space to be greater than a newline.
1265 Trailing spaces are removed when converting a <type>character</type> value
1266 to one of the other string types. Note that trailing spaces
1267 <emphasis>are</emphasis> semantically significant in
1268 <type>character varying</type> and <type>text</type> values, and
1269 when using pattern matching, that is <literal>LIKE</literal> and
1270 regular expressions.
1271 </para>
1272
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.
1278 For more information refer to <xref linkend="multibyte"/>.
1279 </para>
1280
1281 <para>
1282 The storage requirement for a short string (up to 126 bytes) is 1 byte
1283 plus the actual string, which includes the space padding in the case of
1284 <type>character</type>. Longer strings have 4 bytes of overhead instead
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
1290 maximum value that will be allowed for <replaceable>n</replaceable> in the data
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
1295 <type>text</type> or <type>character varying</type> without a length
1296 specifier, rather than making up an arbitrary length limit.)
1297 </para>
1298
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
1305 <type>character(<replaceable>n</replaceable>)</type> has performance
1306 advantages in some other database systems, there is no such advantage in
1307 <productname>PostgreSQL</productname>; in fact
1308 <type>character(<replaceable>n</replaceable>)</type> is usually the slowest of
1309 the three because of its additional storage costs. In most situations
1310 <type>text</type> or <type>character varying</type> should be used
1311 instead.
1312 </para>
1313 </tip>
1314
1315 <para>
1316 Refer to <xref linkend="sql-syntax-strings"/> for information about
1317 the syntax of string literals, and to <xref linkend="functions"/>
1318 for information about available operators and functions.
1319 </para>
1320
1321 <example>
1322 <title>Using the Character Types</title>
1323
1324 <programlisting>
1325 CREATE TABLE test1 (a character(4));
1326 INSERT INTO test1 VALUES ('ok');
1327 SELECT a, char_length(a) FROM test1; -- <co id="co.datatype-char"/>
1328 <computeroutput>
1329 a | char_length
1330 ------+-------------
1331 ok | 2
1332 </computeroutput>
1333
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');
1338 <computeroutput>ERROR: value too long for type character varying(5)</computeroutput>
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>
1350 <callout arearefs="co.datatype-char">
1351 <para>
1352 The <function>char_length</function> function is discussed in
1353 <xref linkend="functions-string"/>.
1354 </para>
1355 </callout>
1356 </calloutlist>
1357 </example>
1358
1359 <para>
1360 There are two other fixed-length character types in
1361 <productname>PostgreSQL</productname>, shown in <xref
1362 linkend="datatype-character-special-table"/>.
1363 These are not intended for general-purpose use, only for use
1364 in the internal system catalogs.
1365 The <type>name</type> type is used to store identifiers. Its
1366 length is currently defined as 64 bytes (63 usable characters plus
1367 terminator) but should be referenced using the constant
1368 <symbol>NAMEDATALEN</symbol> in <literal>C</literal> source code.
1369 The length is set at compile time (and
1370 is therefore adjustable for special uses); the default maximum
1371 length might change in a future release. The type <type>"char"</type>
1372 (note the quotes) is different from <type>char(1)</type> in that it
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>
1377
1378 <table id="datatype-character-special-table">
1379 <title>Special Character Types</title>
1380 <tgroup cols="3">
1381 <thead>
1382 <row>
1383 <entry>Name</entry>
1384 <entry>Storage Size</entry>
1385 <entry>Description</entry>
1386 </row>
1387 </thead>
1388 <tbody>
1389 <row>
1390 <entry><type>"char"</type></entry>
1391 <entry>1 byte</entry>
1392 <entry>single-byte internal type</entry>
1393 </row>
1394 <row>
1395 <entry><type>name</type></entry>
1396 <entry>64 bytes</entry>
1397 <entry>internal type for object names</entry>
1398 </row>
1399 </tbody>
1400 </tgroup>
1401 </table>
1402
1403 </sect1>
1404
1405 <sect1 id="datatype-binary">
1406 <title>Binary Data Types</title>
1407
1408 <indexterm zone="datatype-binary">
1409 <primary>binary data</primary>
1410 </indexterm>
1411
1412 <indexterm zone="datatype-binary">
1413 <primary>bytea</primary>
1414 </indexterm>
1415
1416 <para>
1417 The <type>bytea</type> data type allows storage of binary strings;
1418 see <xref linkend="datatype-binary-table"/>.
1419 </para>
1420
1421 <table id="datatype-binary-table">
1422 <title>Binary Data Types</title>
1423 <tgroup cols="3">
1424 <colspec colname="col1" colwidth="1*"/>
1425 <colspec colname="col2" colwidth="3*"/>
1426 <colspec colname="col3" colwidth="2*"/>
1427 <thead>
1428 <row>
1429 <entry>Name</entry>
1430 <entry>Storage Size</entry>
1431 <entry>Description</entry>
1432 </row>
1433 </thead>
1434 <tbody>
1435 <row>
1436 <entry><type>bytea</type></entry>
1437 <entry>1 or 4 bytes plus the actual binary string</entry>
1438 <entry>variable-length binary string</entry>
1439 </row>
1440 </tbody>
1441 </tgroup>
1442 </table>
1443
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
1448 octets of value zero and other <quote>non-printable</quote>
1449 octets (usually, octets outside the decimal range 32 to 126).
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
1456 programmer thinks of as <quote>raw bytes</quote>, whereas character
1457 strings are appropriate for storing text.
1458 </para>
1459
1460 <para>
1461 The <type>bytea</type> type supports two
1462 formats for input and output: <quote>hex</quote> format
1463 and <productname>PostgreSQL</productname>'s historical
1464 <quote>escape</quote> format. Both
1465 of these are always accepted on input. The output format depends
1466 on the configuration parameter <xref linkend="guc-bytea-output"/>;
1467 the default is hex. (Note that the hex format was introduced in
1468 <productname>PostgreSQL</productname> 9.0; earlier versions and some
1469 tools don't understand it.)
1470 </para>
1471
1472 <para>
1473 The <acronym>SQL</acronym> standard defines a different binary
1474 string type, called <type>BLOB</type> or <type>BINARY LARGE
1475 OBJECT</type>. The input format is different from
1476 <type>bytea</type>, but the provided functions and operators are
1477 mostly the same.
1478 </para>
1479
1480 <sect2 id="datatype-binary-bytea-hex-format">
1481 <title><type>bytea</type> Hex Format</title>
1482
1483 <para>
1484 The <quote>hex</quote> format encodes binary data as 2 hexadecimal digits
1485 per byte, most significant nibble first. The entire string is
1486 preceded by the sequence <literal>\x</literal> (to distinguish it
1487 from the escape format). In some contexts, the initial backslash may
1488 need to be escaped by doubling it
1489 (see <xref linkend="sql-syntax-strings"/>).
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
1493 <literal>\x</literal> sequence).
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>
1498
1499 <para>
1500 Example:
1501 <programlisting>
1502 SET bytea_output = 'hex';
1503
1504 SELECT '\xDEADBEEF'::bytea;
1505 bytea
1506 ------------
1507 \xdeadbeef
1508 </programlisting>
1509 </para>
1510 </sect2>
1511
1512 <sect2 id="datatype-binary-bytea-escape-format">
1513 <title><type>bytea</type> Escape Format</title>
1514
1515 <para>
1516 The <quote>escape</quote> format is the traditional
1517 <productname>PostgreSQL</productname> format for the <type>bytea</type>
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>
1530
1531 <para>
1532 When entering <type>bytea</type> values in escape format,
1533 octets of certain
1534 values <emphasis>must</emphasis> be escaped, while all octet
1535 values <emphasis>can</emphasis> be escaped. In
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.
1540 <xref linkend="datatype-binary-sqlesc"/>
1541 shows the characters that must be escaped, and gives the alternative
1542 escape sequences where applicable.
1543 </para>
1544
1545 <table id="datatype-binary-sqlesc">
1546 <title><type>bytea</type> Literal Escaped Octets</title>
1547 <tgroup cols="5">
1548 <colspec colname="col1" colwidth="1*"/>
1549 <colspec colname="col2" colwidth="1*"/>
1550 <colspec colname="col3" colwidth="1*"/>
1551 <colspec colname="col4" colwidth="1.25*"/>
1552 <colspec colname="col5" colwidth="1*"/>
1553 <thead>
1554 <row>
1555 <entry>Decimal Octet Value</entry>
1556 <entry>Description</entry>
1557 <entry>Escaped Input Representation</entry>
1558 <entry>Example</entry>
1559 <entry>Hex Representation</entry>
1560 </row>
1561 </thead>
1562
1563 <tbody>
1564 <row>
1565 <entry>0</entry>
1566 <entry>zero octet</entry>
1567 <entry><literal>'\000'</literal></entry>
1568 <entry><literal>'\000'::bytea</literal></entry>
1569 <entry><literal>\x00</literal></entry>
1570 </row>
1571
1572 <row>
1573 <entry>39</entry>
1574 <entry>single quote</entry>
1575 <entry><literal>''''</literal> or <literal>'\047'</literal></entry>
1576 <entry><literal>''''::bytea</literal></entry>
1577 <entry><literal>\x27</literal></entry>
1578 </row>
1579
1580 <row>
1581 <entry>92</entry>
1582 <entry>backslash</entry>
1583 <entry><literal>'\\'</literal> or <literal>'\134'</literal></entry>
1584 <entry><literal>'\\'::bytea</literal></entry>
1585 <entry><literal>\x5c</literal></entry>
1586 </row>
1587
1588 <row>
1589 <entry>0 to 31 and 127 to 255</entry>
1590 <entry><quote>non-printable</quote> octets</entry>
1591 <entry><literal>'\<replaceable>xxx'</replaceable></literal> (octal value)</entry>
1592 <entry><literal>'\001'::bytea</literal></entry>
1593 <entry><literal>\x01</literal></entry>
1594 </row>
1595
1596 </tbody>
1597 </tgroup>
1598 </table>
1599
1600 <para>
1601 The requirement to escape <emphasis>non-printable</emphasis> octets
1602 varies depending on locale settings. In some instances you can get away
1603 with leaving them unescaped.
1604 </para>
1605
1606 <para>
1607 The reason that single quotes must be doubled, as shown
1608 in <xref linkend="datatype-binary-sqlesc"/>, is that this
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.
1612 What the <type>bytea</type> input function sees is just one
1613 single quote, which it treats as a plain data character.
1614 However, the <type>bytea</type> input function treats
1615 backslashes as special, and the other behaviors shown in
1616 <xref linkend="datatype-binary-sqlesc"/> are implemented by
1617 that function.
1618 </para>
1619
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;
1624 see <xref linkend="sql-syntax-strings"/>.
1625 </para>
1626
1627 <para>
1628 <type>Bytea</type> octets are output in <literal>hex</literal>
1629 format by default. If you change <xref linkend="guc-bytea-output"/>
1630 to <literal>escape</literal>,
1631 <quote>non-printable</quote> octets are converted to their
1632 equivalent three-digit octal value and preceded by one backslash.
1633 Most <quote>printable</quote> octets are output by their standard
1634 representation in the client character set, e.g.:
1635
1636 <programlisting>
1637 SET bytea_output = 'escape';
1638
1639 SELECT 'abc \153\154\155 \052\251\124'::bytea;
1640 bytea
1641 ----------------
1642 abc klm *\251T
1643 </programlisting>
1644
1645 The octet with decimal value 92 (backslash) is doubled in the output.
1646 Details are in <xref linkend="datatype-binary-resesc"/>.
1647 </para>
1648
1649 <table id="datatype-binary-resesc">
1650 <title><type>bytea</type> Output Escaped Octets</title>
1651 <tgroup cols="5">
1652 <colspec colname="col1" colwidth="1*"/>
1653 <colspec colname="col2" colwidth="1*"/>
1654 <colspec colname="col3" colwidth="1*"/>
1655 <colspec colname="col4" colwidth="1.25*"/>
1656 <colspec colname="col5" colwidth="1*"/>
1657 <thead>
1658 <row>
1659 <entry>Decimal Octet Value</entry>
1660 <entry>Description</entry>
1661 <entry>Escaped Output Representation</entry>
1662 <entry>Example</entry>
1663 <entry>Output Result</entry>
1664 </row>
1665 </thead>
1666
1667 <tbody>
1668
1669 <row>
1670 <entry>92</entry>
1671 <entry>backslash</entry>
1672 <entry><literal>\\</literal></entry>
1673 <entry><literal>'\134'::bytea</literal></entry>
1674 <entry><literal>\\</literal></entry>
1675 </row>
1676
1677 <row>
1678 <entry>0 to 31 and 127 to 255</entry>
1679 <entry><quote>non-printable</quote> octets</entry>
1680 <entry><literal>\<replaceable>xxx</replaceable></literal> (octal value)</entry>
1681 <entry><literal>'\001'::bytea</literal></entry>
1682 <entry><literal>\001</literal></entry>
1683 </row>
1684
1685 <row>
1686 <entry>32 to 126</entry>
1687 <entry><quote>printable</quote> octets</entry>
1688 <entry>client character set representation</entry>
1689 <entry><literal>'\176'::bytea</literal></entry>
1690 <entry><literal>~</literal></entry>
1691 </row>
1692
1693 </tbody>
1694 </tgroup>
1695 </table>
1696
1697 <para>
1698 Depending on the front end to <productname>PostgreSQL</productname> you use,
1699 you might have additional work to do in terms of escaping and
1700 unescaping <type>bytea</type> strings. For example, you might also
1701 have to escape line feeds and carriage returns if your interface
1702 automatically translates these.
1703 </para>
1704 </sect2>
1705 </sect1>
1706
1707
1708 <sect1 id="datatype-datetime">
1709 <title>Date/Time Types</title>
1710
1711 <indexterm zone="datatype-datetime">
1712 <primary>date</primary>
1713 </indexterm>
1714 <indexterm zone="datatype-datetime">
1715 <primary>time</primary>
1716 </indexterm>
1717 <indexterm zone="datatype-datetime">
1718 <primary>time without time zone</primary>
1719 </indexterm>
1720 <indexterm zone="datatype-datetime">
1721 <primary>time with time zone</primary>
1722 </indexterm>
1723 <indexterm zone="datatype-datetime">
1724 <primary>timestamp</primary>
1725 </indexterm>
1726 <indexterm zone="datatype-datetime">
1727 <primary>timestamptz</primary>
1728 </indexterm>
1729 <indexterm zone="datatype-datetime">
1730 <primary>timestamp with time zone</primary>
1731 </indexterm>
1732 <indexterm zone="datatype-datetime">
1733 <primary>timestamp without time zone</primary>
1734 </indexterm>
1735 <indexterm zone="datatype-datetime">
1736 <primary>interval</primary>
1737 </indexterm>
1738 <indexterm zone="datatype-datetime">
1739 <primary>time span</primary>
1740 </indexterm>
1741
1742 <para>
1743 <productname>PostgreSQL</productname> supports the full set of
1744 <acronym>SQL</acronym> date and time types, shown in <xref
1745 linkend="datatype-datetime-table"/>. The operations available
1746 on these data types are described in
1747 <xref linkend="functions-datetime"/>.
1748 Dates are counted according to the Gregorian calendar, even in
1749 years before that calendar was introduced (see <xref
1750 linkend="datetime-units-history"/> for more information).
1751 </para>
1752
1753 <table id="datatype-datetime-table">
1754 <title>Date/Time Types</title>
1755 <tgroup cols="6">
1756 <thead>
1757 <row>
1758 <entry>Name</entry>
1759 <entry>Storage Size</entry>
1760 <entry>Description</entry>
1761 <entry>Low Value</entry>
1762 <entry>High Value</entry>
1763 <entry>Resolution</entry>
1764 </row>
1765 </thead>
1766 <tbody>
1767 <row>
1768 <entry><type>timestamp [ (<replaceable>p</replaceable>) ] [ without time zone ]</type></entry>
1769 <entry>8 bytes</entry>
1770 <entry>both date and time (no time zone)</entry>
1771 <entry>4713 BC</entry>
1772 <entry>294276 AD</entry>
1773 <entry>1 microsecond</entry>
1774 </row>
1775 <row>
1776 <entry><type>timestamp [ (<replaceable>p</replaceable>) ] with time zone</type></entry>
1777 <entry>8 bytes</entry>
1778 <entry>both date and time, with time zone</entry>
1779 <entry>4713 BC</entry>
1780 <entry>294276 AD</entry>
1781 <entry>1 microsecond</entry>
1782 </row>
1783 <row>
1784 <entry><type>date</type></entry>
1785 <entry>4 bytes</entry>
1786 <entry>date (no time of day)</entry>
1787 <entry>4713 BC</entry>
1788 <entry>5874897 AD</entry>
1789 <entry>1 day</entry>
1790 </row>
1791 <row>
1792 <entry><type>time [ (<replaceable>p</replaceable>) ] [ without time zone ]</type></entry>
1793 <entry>8 bytes</entry>
1794 <entry>time of day (no date)</entry>
1795 <entry>00:00:00</entry>
1796 <entry>24:00:00</entry>
1797 <entry>1 microsecond</entry>
1798 </row>
1799 <row>
1800 <entry><type>time [ (<replaceable>p</replaceable>) ] with time zone</type></entry>
1801 <entry>12 bytes</entry>
1802 <entry>time of day (no date), with time zone</entry>
1803 <!-- see MAX_TZDISP_HOUR in datatype/timestamp.h -->
1804 <entry>00:00:00+1559</entry>
1805 <entry>24:00:00-1559</entry>
1806 <entry>1 microsecond</entry>
1807 </row>
1808 <row>
1809 <entry><type>interval [ <replaceable>fields</replaceable> ] [ (<replaceable>p</replaceable>) ]</type></entry>
1810 <entry>16 bytes</entry>
1811 <entry>time interval</entry>
1812 <entry>-178000000 years</entry>
1813 <entry>178000000 years</entry>
1814 <entry>1 microsecond</entry>
1815 </row>
1816 </tbody>
1817 </tgroup>
1818 </table>
1819
1820 <note>
1821 <para>
1822 The SQL standard requires that writing just <type>timestamp</type>
1823 be equivalent to <type>timestamp without time
1824 zone</type>, and <productname>PostgreSQL</productname> honors that
1825 behavior. <type>timestamptz</type> is accepted as an
1826 abbreviation for <type>timestamp with time zone</type>; this is a
1827 <productname>PostgreSQL</productname> extension.
1828 </para>
1829 </note>
1830
1831 <para>
1832 <type>time</type>, <type>timestamp</type>, and
1833 <type>interval</type> accept an optional precision value
1834 <replaceable>p</replaceable> which specifies the number of
1835 fractional digits retained in the seconds field. By default, there
1836 is no explicit bound on precision. The allowed range of
1837 <replaceable>p</replaceable> is from 0 to 6.
1838 </para>
1839
1840 <para>
1841 The <type>interval</type> type has an additional option, which is
1842 to restrict the set of stored fields by writing one of these phrases:
1843 <literallayout class="monospaced">
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>
1858 Note that if both <replaceable>fields</replaceable> and
1859 <replaceable>p</replaceable> are specified, the
1860 <replaceable>fields</replaceable> must include <literal>SECOND</literal>,
1861 since the precision applies only to the seconds.
1862 </para>
1863
1864 <para>
1865 The type <type>time with time zone</type> is defined by the SQL
1866 standard, but the definition exhibits properties which lead to
1867 questionable usefulness. In most cases, a combination of
1868 <type>date</type>, <type>time</type>, <type>timestamp without time
1869 zone</type>, and <type>timestamp with time zone</type> should
1870 provide a complete range of date/time functionality required by
1871 any application.
1872 </para>
1873
1874 <sect2 id="datatype-datetime-input">
1875 <title>Date/Time Input</title>
1876
1877 <para>
1878 Date and time input is accepted in almost any reasonable format, including
1879 ISO 8601, <acronym>SQL</acronym>-compatible,
1880 traditional <productname>POSTGRES</productname>, and others.
1881 For some formats, ordering of day, month, and year in date input is
1882 ambiguous and there is support for specifying the expected
1883 ordering of these fields. Set the <xref linkend="guc-datestyle"/> parameter
1884 to <literal>MDY</literal> to select month-day-year interpretation,
1885 <literal>DMY</literal> to select day-month-year interpretation, or
1886 <literal>YMD</literal> to select year-month-day interpretation.
1887 </para>
1888
1889 <para>
1890 <productname>PostgreSQL</productname> is more flexible in
1891 handling date/time input than the
1892 <acronym>SQL</acronym> standard requires.
1893 See <xref linkend="datetime-appendix"/>
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>
1898
1899 <para>
1900 Remember that any date or time literal input needs to be enclosed
1901 in single quotes, like text strings. Refer to
1902 <xref linkend="sql-syntax-constants-generic"/> for more
1903 information.
1904 <acronym>SQL</acronym> requires the following syntax
1905 <synopsis>
1906 <replaceable>type</replaceable> [ (<replaceable>p</replaceable>) ] '<replaceable>value</replaceable>'
1907 </synopsis>
1908 where <replaceable>p</replaceable> is an optional precision
1909 specification giving the number of
1910 fractional digits in the seconds field. Precision can be
1911 specified for <type>time</type>, <type>timestamp</type>, and
1912 <type>interval</type> types, and can range from 0 to 6.
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>
1917
1918 <sect3 id="datatype-datetime-input-dates">
1919 <title>Dates</title>
1920
1921 <indexterm>
1922 <primary>date</primary>
1923 </indexterm>
1924
1925 <para>
1926 <xref linkend="datatype-datetime-date-table"/> shows some possible
1927 inputs for the <type>date</type> type.
1928 </para>
1929
1930 <table id="datatype-datetime-date-table">
1931 <title>Date Input</title>
1932 <tgroup cols="2">
1933 <colspec colname="col1" colwidth="1*"/>
1934 <colspec colname="col2" colwidth="2*"/>
1935 <thead>
1936 <row>
1937 <entry>Example</entry>
1938 <entry>Description</entry>
1939 </row>
1940 </thead>
1941 <tbody>
1942 <row>
1943 <entry>1999-01-08</entry>
1944 <entry>ISO 8601; January 8 in any mode
1945 (recommended format)</entry>
1946 </row>
1947 <row>
1948 <entry>January 8, 1999</entry>
1949 <entry>unambiguous in any <varname>datestyle</varname> input mode</entry>
1950 </row>
1951 <row>
1952 <entry>1/8/1999</entry>
1953 <entry>January 8 in <literal>MDY</literal> mode;
1954 August 1 in <literal>DMY</literal> mode</entry>
1955 </row>
1956 <row>
1957 <entry>1/18/1999</entry>
1958 <entry>January 18 in <literal>MDY</literal> mode;
1959 rejected in other modes</entry>
1960 </row>
1961 <row>
1962 <entry>01/02/03</entry>
1963 <entry>January 2, 2003 in <literal>MDY</literal> mode;
1964 February 1, 2003 in <literal>DMY</literal> mode;
1965 February 3, 2001 in <literal>YMD</literal> mode
1966 </entry>
1967 </row>
1968 <row>
1969 <entry>1999-Jan-08</entry>
1970 <entry>January 8 in any mode</entry>
1971 </row>
1972 <row>
1973 <entry>Jan-08-1999</entry>
1974 <entry>January 8 in any mode</entry>
1975 </row>
1976 <row>
1977 <entry>08-Jan-1999</entry>
1978 <entry>January 8 in any mode</entry>
1979 </row>
1980 <row>
1981 <entry>99-Jan-08</entry>
1982 <entry>January 8 in <literal>YMD</literal> mode, else error</entry>
1983 </row>
1984 <row>
1985 <entry>08-Jan-99</entry>
1986 <entry>January 8, except error in <literal>YMD</literal> mode</entry>
1987 </row>
1988 <row>
1989 <entry>Jan-08-99</entry>
1990 <entry>January 8, except error in <literal>YMD</literal> mode</entry>
1991 </row>
1992 <row>
1993 <entry>19990108</entry>
1994 <entry>ISO 8601; January 8, 1999 in any mode</entry>
1995 </row>
1996 <row>
1997 <entry>990108</entry>
1998 <entry>ISO 8601; January 8, 1999 in any mode</entry>
1999 </row>
2000 <row>
2001 <entry>1999.008</entry>
2002 <entry>year and day of year</entry>
2003 </row>
2004 <row>
2005 <entry>J2451187</entry>
2006 <entry>Julian date</entry>
2007 </row>
2008 <row>
2009 <entry>January 8, 99 BC</entry>
2010 <entry>year 99 BC</entry>
2011 </row>
2012 </tbody>
2013 </tgroup>
2014 </table>
2015 </sect3>
2016
2017 <sect3 id="datatype-datetime-input-times">
2018 <title>Times</title>
2019
2020 <indexterm>
2021 <primary>time</primary>
2022 </indexterm>
2023 <indexterm>
2024 <primary>time without time zone</primary>
2025 </indexterm>
2026 <indexterm>
2027 <primary>time with time zone</primary>
2028 </indexterm>
2029
2030 <para>
2031 The time-of-day types are <type>time [
2032 (<replaceable>p</replaceable>) ] without time zone</type> and
2033 <type>time [ (<replaceable>p</replaceable>) ] with time
2034 zone</type>. <type>time</type> alone is equivalent to
2035 <type>time without time zone</type>.
2036 </para>
2037
2038 <para>
2039 Valid input for these types consists of a time of day followed
2040 by an optional time zone. (See <xref
2041 linkend="datatype-datetime-time-table"/>
2042 and <xref linkend="datatype-timezone-table"/>.) If a time zone is
2043 specified in the input for <type>time without time zone</type>,
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
2047 <literal>America/New_York</literal>. In this case specifying the date
2048 is required in order to determine whether standard or daylight-savings
2049 time applies. The appropriate time zone offset is recorded in the
2050 <type>time with time zone</type> value and is output as stored;
2051 it is not adjusted to the active time zone.
2052 </para>
2053
2054 <table id="datatype-datetime-time-table">
2055 <title>Time Input</title>
2056 <tgroup cols="2">
2057 <colspec colname="col1" colwidth="3*"/>
2058 <colspec colname="col2" colwidth="2*"/>
2059 <thead>
2060 <row>
2061 <entry>Example</entry>
2062 <entry>Description</entry>
2063 </row>
2064 </thead>
2065 <tbody>
2066 <row>
2067 <entry><literal>04:05:06.789</literal></entry>
2068 <entry>ISO 8601</entry>
2069 </row>
2070 <row>
2071 <entry><literal>04:05:06</literal></entry>
2072 <entry>ISO 8601</entry>
2073 </row>
2074 <row>
2075 <entry><literal>04:05</literal></entry>
2076 <entry>ISO 8601</entry>
2077 </row>
2078 <row>
2079 <entry><literal>040506</literal></entry>
2080 <entry>ISO 8601</entry>
2081 </row>
2082 <row>
2083 <entry><literal>04:05 AM</literal></entry>
2084 <entry>same as 04:05; AM does not affect value</entry>
2085 </row>
2086 <row>
2087 <entry><literal>04:05 PM</literal></entry>
2088 <entry>same as 16:05; input hour must be &lt;= 12</entry>
2089 </row>
2090 <row>
2091 <entry><literal>04:05:06.789-8</literal></entry>
2092 <entry>ISO 8601, with time zone as UTC offset</entry>
2093 </row>
2094 <row>
2095 <entry><literal>04:05:06-08:00</literal></entry>
2096 <entry>ISO 8601, with time zone as UTC offset</entry>
2097 </row>
2098 <row>
2099 <entry><literal>04:05-08:00</literal></entry>
2100 <entry>ISO 8601, with time zone as UTC offset</entry>
2101 </row>
2102 <row>
2103 <entry><literal>040506-08</literal></entry>
2104 <entry>ISO 8601, with time zone as UTC offset</entry>
2105 </row>
2106 <row>
2107 <entry><literal>040506+0730</literal></entry>
2108 <entry>ISO 8601, with fractional-hour time zone as UTC offset</entry>
2109 </row>
2110 <row>
2111 <entry><literal>040506+07:30:00</literal></entry>
2112 <entry>UTC offset specified to seconds (not allowed in ISO 8601)</entry>
2113 </row>
2114 <row>
2115 <entry><literal>04:05:06 PST</literal></entry>
2116 <entry>time zone specified by abbreviation</entry>
2117 </row>
2118 <row>
2119 <entry><literal>2003-04-12 04:05:06 America/New_York</literal></entry>
2120 <entry>time zone specified by full name</entry>
2121 </row>
2122 </tbody>
2123 </tgroup>
2124 </table>
2125
2126 <table tocentry="1" id="datatype-timezone-table">
2127 <title>Time Zone Input</title>
2128 <tgroup cols="2">
2129 <thead>
2130 <row>
2131 <entry>Example</entry>
2132 <entry>Description</entry>
2133 </row>
2134 </thead>
2135 <tbody>
2136 <row>
2137 <entry><literal>PST</literal></entry>
2138 <entry>Abbreviation (for Pacific Standard Time)</entry>
2139 </row>
2140 <row>
2141 <entry><literal>America/New_York</literal></entry>
2142 <entry>Full time zone name</entry>
2143 </row>
2144 <row>
2145 <entry><literal>PST8PDT</literal></entry>
2146 <entry>POSIX-style time zone specification</entry>
2147 </row>
2148 <row>
2149 <entry><literal>-8:00:00</literal></entry>
2150 <entry>UTC offset for PST</entry>
2151 </row>
2152 <row>
2153 <entry><literal>-8:00</literal></entry>
2154 <entry>UTC offset for PST (ISO 8601 extended format)</entry>
2155 </row>
2156 <row>
2157 <entry><literal>-800</literal></entry>
2158 <entry>UTC offset for PST (ISO 8601 basic format)</entry>
2159 </row>
2160 <row>
2161 <entry><literal>-8</literal></entry>
2162 <entry>UTC offset for PST (ISO 8601 basic format)</entry>
2163 </row>
2164 <row>
2165 <entry><literal>zulu</literal></entry>
2166 <entry>Military abbreviation for UTC</entry>
2167 </row>
2168 <row>
2169 <entry><literal>z</literal></entry>
2170 <entry>Short form of <literal>zulu</literal> (also in ISO 8601)</entry>
2171 </row>
2172 </tbody>
2173 </tgroup>
2174 </table>
2175
2176 <para>
2177 Refer to <xref linkend="datatype-timezones"/> for more information on how
2178 to specify time zones.
2179 </para>
2180 </sect3>
2181
2182 <sect3 id="datatype-datetime-input-time-stamps">
2183 <title>Time Stamps</title>
2184
2185 <indexterm>
2186 <primary>timestamp</primary>
2187 </indexterm>
2188
2189 <indexterm>
2190 <primary>timestamp with time zone</primary>
2191 </indexterm>
2192
2193 <indexterm>
2194 <primary>timestamp without time zone</primary>
2195 </indexterm>
2196
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,
2200 followed by an optional <literal>AD</literal> or <literal>BC</literal>.
2201 (Alternatively, <literal>AD</literal>/<literal>BC</literal> can appear
2202 before the time zone, but this is not the preferred ordering.)
2203 Thus:
2204
2205 <programlisting>
2206 1999-01-08 04:05:06
2207 </programlisting>
2208 and:
2209 <programlisting>
2210 1999-01-08 04:05:06 -8:00
2211 </programlisting>
2212
2213 are valid values, which follow the <acronym>ISO</acronym> 8601
2214 standard. In addition, the common format:
2215 <programlisting>
2216 January 8 04:05:06 1999 PST
2217 </programlisting>
2218 is supported.
2219 </para>
2220
2221 <para>
2222 The <acronym>SQL</acronym> standard differentiates
2223 <type>timestamp without time zone</type>
2224 and <type>timestamp with time zone</type> literals by the presence of a
2225 <quote>+</quote> or <quote>-</quote> symbol and time zone offset after
2226 the time. Hence, according to the standard,
2227
2228 <programlisting>
2229 TIMESTAMP '2004-10-19 10:23:54'
2230 </programlisting>
2231
2232 is a <type>timestamp without time zone</type>, while
2233
2234 <programlisting>
2235 TIMESTAMP '2004-10-19 10:23:54+02'
2236 </programlisting>
2237
2238 is a <type>timestamp with time zone</type>.
2239 <productname>PostgreSQL</productname> never examines the content of a
2240 literal string before determining its type, and therefore will treat
2241 both of the above as <type>timestamp without time zone</type>. To
2242 ensure that a literal is treated as <type>timestamp with time
2243 zone</type>, give it the correct explicit type:
2244
2245 <programlisting>
2246 TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'
2247 </programlisting>
2248 </para>
2249
2250 <para>
2251 In a value that has been determined to be <type>timestamp without time
2252 zone</type>, <productname>PostgreSQL</productname> will silently ignore
2253 any time zone indication.
2254 That is, the resulting value is derived from the date/time
2255 fields in the input string, and is not adjusted for time zone.
2256 </para>
2257
2258 <para>
2259 For <type>timestamp with time zone</type> values, an input string
2260 that includes an explicit time zone will be converted to UTC
2261 (<glossterm linkend="glossary-utc">Universal Coordinated
2262 Time</glossterm>) using the appropriate offset
2263 for that time zone. If no time zone is stated in the input string,
2264 then it is assumed to be in the time zone indicated by the system's
2265 <xref linkend="guc-timezone"/> parameter, and is converted to UTC using the
2266 offset for the <varname>timezone</varname> zone.
2267 In either case, the value is stored internally as UTC, and the
2268 originally stated or assumed time zone is not retained.
2269 </para>
2270
2271 <para>
2272 When a <type>timestamp with time
2273 zone</type> value is output, it is always converted from UTC to the
2274 current <varname>timezone</varname> zone, and displayed as local time in that
2275 zone. To see the time in another time zone, either change
2276 <varname>timezone</varname> or use the <literal>AT TIME ZONE</literal> construct
2277 (see <xref linkend="functions-datetime-zoneconvert"/>).
2278 </para>
2279
2280 <para>
2281 Conversions between <type>timestamp without time zone</type> and
2282 <type>timestamp with time zone</type> normally assume that the
2283 <type>timestamp without time zone</type> value should be taken or given
2284 as <varname>timezone</varname> local time. A different time zone can
2285 be specified for the conversion using <literal>AT TIME ZONE</literal>.
2286 </para>
2287 </sect3>
2288
2289 <sect3 id="datatype-datetime-special-values">
2290 <title>Special Values</title>
2291
2292 <indexterm>
2293 <primary>time</primary>
2294 <secondary>constants</secondary>
2295 </indexterm>
2296
2297 <indexterm>
2298 <primary>date</primary>
2299 <secondary>constants</secondary>
2300 </indexterm>
2301
2302 <para>
2303 <productname>PostgreSQL</productname> supports several
2304 special date/time input values for convenience, as shown in <xref
2305 linkend="datatype-datetime-special-table"/>. The values
2306 <literal>infinity</literal> and <literal>-infinity</literal>
2307 are specially represented inside the system and will be displayed
2308 unchanged; but the others are simply notational shorthands
2309 that will be converted to ordinary date/time values when read.
2310 (In particular, <literal>now</literal> and related strings are converted
2311 to a specific time value as soon as they are read.)
2312 All of these values need to be enclosed in single quotes when used
2313 as constants in SQL commands.
2314 </para>
2315
2316 <table id="datatype-datetime-special-table">
2317 <title>Special Date/Time Inputs</title>
2318 <tgroup cols="3">
2319 <thead>
2320 <row>
2321 <entry>Input String</entry>
2322 <entry>Valid Types</entry>
2323 <entry>Description</entry>
2324 </row>
2325 </thead>
2326 <tbody>
2327 <row>
2328 <entry><literal>epoch</literal></entry>
2329 <entry><type>date</type>, <type>timestamp</type></entry>
2330 <entry>1970-01-01 00:00:00+00 (Unix system time zero)</entry>
2331 </row>
2332 <row>
2333 <entry><literal>infinity</literal></entry>
2334 <entry><type>date</type>, <type>timestamp</type>, <type>interval</type></entry>
2335 <entry>later than all other time stamps</entry>
2336 </row>
2337 <row>
2338 <entry><literal>-infinity</literal></entry>
2339 <entry><type>date</type>, <type>timestamp</type>, <type>interval</type></entry>
2340 <entry>earlier than all other time stamps</entry>
2341 </row>
2342 <row>
2343 <entry><literal>now</literal></entry>
2344 <entry><type>date</type>, <type>time</type>, <type>timestamp</type></entry>
2345 <entry>current transaction's start time</entry>
2346 </row>
2347 <row>
2348 <entry><literal>today</literal></entry>
2349 <entry><type>date</type>, <type>timestamp</type></entry>
2350 <entry>midnight (<literal>00:00</literal>) today</entry>
2351 </row>
2352 <row>
2353 <entry><literal>tomorrow</literal></entry>
2354 <entry><type>date</type>, <type>timestamp</type></entry>
2355 <entry>midnight (<literal>00:00</literal>) tomorrow</entry>
2356 </row>
2357 <row>
2358 <entry><literal>yesterday</literal></entry>
2359 <entry><type>date</type>, <type>timestamp</type></entry>
2360 <entry>midnight (<literal>00:00</literal>) yesterday</entry>
2361 </row>
2362 <row>
2363 <entry><literal>allballs</literal></entry>
2364 <entry><type>time</type></entry>
2365 <entry>00:00:00.00 UTC</entry>
2366 </row>
2367 </tbody>
2368 </tgroup>
2369 </table>
2370
2371 <para>
2372 The following <acronym>SQL</acronym>-compatible functions can also
2373 be used to obtain the current time value for the corresponding data
2374 type:
2375 <literal>CURRENT_DATE</literal>, <literal>CURRENT_TIME</literal>,
2376 <literal>CURRENT_TIMESTAMP</literal>, <literal>LOCALTIME</literal>,
2377 <literal>LOCALTIMESTAMP</literal>. (See <xref
2378 linkend="functions-datetime-current"/>.) Note that these are
2379 SQL functions and are <emphasis>not</emphasis> recognized in data input strings.
2380 </para>
2381
2382 <caution>
2383 <para>
2384 While the input strings <literal>now</literal>,
2385 <literal>today</literal>, <literal>tomorrow</literal>,
2386 and <literal>yesterday</literal> are fine to use in interactive SQL
2387 commands, they can have surprising behavior when the command is
2388 saved to be executed later, for example in prepared statements,
2389 views, and function definitions. The string can be converted to a
2390 specific time value that continues to be used long after it becomes
2391 stale. Use one of the SQL functions instead in such contexts.
2392 For example, <literal>CURRENT_DATE + 1</literal> is safer than
2393 <literal>'tomorrow'::date</literal>.
2394 </para>
2395 </caution>
2396
2397 </sect3>
2398 </sect2>
2399
2400 <sect2 id="datatype-datetime-output">
2401 <title>Date/Time Output</title>
2402
2403 <indexterm>
2404 <primary>date</primary>
2405 <secondary>output format</secondary>
2406 <seealso>formatting</seealso>
2407 </indexterm>
2408
2409 <indexterm>
2410 <primary>time</primary>
2411 <secondary>output format</secondary>
2412 <seealso>formatting</seealso>
2413 </indexterm>
2414
2415 <para>
2416 The output format of the date/time types can be set to one of the four
2417 styles ISO 8601,
2418 <acronym>SQL</acronym> (Ingres), traditional <productname>POSTGRES</productname>
2419 (Unix <application>date</application> format), or
2420 German. The default
2421 is the <acronym>ISO</acronym> format. (The
2422 <acronym>SQL</acronym> standard requires the use of the ISO 8601
2423 format. The name of the <quote>SQL</quote> output format is a
2424 historical accident.) <xref
2425 linkend="datatype-datetime-output-table"/> shows examples of each
2426 output style. The output of the <type>date</type> and
2427 <type>time</type> types is generally only the date or time part
2428 in accordance with the given examples. However, the
2429 <productname>POSTGRES</productname> style outputs date-only values in
2430 <acronym>ISO</acronym> format.
2431 </para>
2432
2433 <table id="datatype-datetime-output-table">
2434 <title>Date/Time Output Styles</title>
2435 <tgroup cols="3">
2436 <colspec colname="col1" colwidth="1*"/>
2437 <colspec colname="col2" colwidth="1*"/>
2438 <colspec colname="col3" colwidth="2*"/>
2439 <thead>
2440 <row>
2441 <entry>Style Specification</entry>
2442 <entry>Description</entry>
2443 <entry>Example</entry>
2444 </row>
2445 </thead>
2446 <tbody>
2447 <row>
2448 <entry><literal>ISO</literal></entry>
2449 <entry>ISO 8601, SQL standard</entry>
2450 <entry><literal>1997-12-17 07:37:16-08</literal></entry>
2451 </row>
2452 <row>
2453 <entry><literal>SQL</literal></entry>
2454 <entry>traditional style</entry>
2455 <entry><literal>12/17/1997 07:37:16.00 PST</literal></entry>
2456 </row>
2457 <row>
2458 <entry><literal>Postgres</literal></entry>
2459 <entry>original style</entry>
2460 <entry><literal>Wed Dec 17 07:37:16 1997 PST</literal></entry>
2461 </row>
2462 <row>
2463 <entry><literal>German</literal></entry>
2464 <entry>regional style</entry>
2465 <entry><literal>17.12.1997 07:37:16.00 PST</literal></entry>
2466 </row>
2467 </tbody>
2468 </tgroup>
2469 </table>
2470
2471 <note>
2472 <para>
2473 ISO 8601 specifies the use of uppercase letter <literal>T</literal> to separate
2474 the date and time. <productname>PostgreSQL</productname> accepts that format on
2475 input, but on output it uses a space rather than <literal>T</literal>, as shown
2476 above. This is for readability and for consistency with
2477 <ulink url="https://datatracker.ietf.org/doc/html/rfc3339">RFC 3339</ulink> as
2478 well as some other database systems.
2479 </para>
2480 </note>
2481
2482 <para>
2483 In the <acronym>SQL</acronym> and POSTGRES styles, day appears before
2484 month if DMY field ordering has been specified, otherwise month appears
2485 before day.
2486 (See <xref linkend="datatype-datetime-input"/>
2487 for how this setting also affects interpretation of input values.)
2488 <xref linkend="datatype-datetime-output2-table"/> shows examples.
2489 </para>
2490
2491 <table id="datatype-datetime-output2-table">
2492 <title>Date Order Conventions</title>
2493 <tgroup cols="3">
2494 <colspec colname="col1" colwidth="1*"/>
2495 <colspec colname="col2" colwidth="1*"/>
2496 <colspec colname="col3" colwidth="2*"/>
2497 <thead>
2498 <row>
2499 <entry><varname>datestyle</varname> Setting</entry>
2500 <entry>Input Ordering</entry>
2501 <entry>Example Output</entry>
2502 </row>
2503 </thead>
2504 <tbody>
2505 <row>
2506 <entry><literal>SQL, DMY</literal></entry>
2507 <entry><replaceable>day</replaceable>/<replaceable>month</replaceable>/<replaceable>year</replaceable></entry>
2508 <entry><literal>17/12/1997 15:37:16.00 CET</literal></entry>
2509 </row>
2510 <row>
2511 <entry><literal>SQL, MDY</literal></entry>
2512 <entry><replaceable>month</replaceable>/<replaceable>day</replaceable>/<replaceable>year</replaceable></entry>
2513 <entry><literal>12/17/1997 07:37:16.00 PST</literal></entry>
2514 </row>
2515 <row>
2516 <entry><literal>Postgres, DMY</literal></entry>
2517 <entry><replaceable>day</replaceable>/<replaceable>month</replaceable>/<replaceable>year</replaceable></entry>
2518 <entry><literal>Wed 17 Dec 07:37:16 1997 PST</literal></entry>
2519 </row>
2520 </tbody>
2521 </tgroup>
2522 </table>
2523
2524 <para>
2525 In the <acronym>ISO</acronym> style, the time zone is always shown as
2526 a signed numeric offset from UTC, with positive sign used for zones
2527 east of Greenwich. The offset will be shown
2528 as <replaceable>hh</replaceable> (hours only) if it is an integral
2529 number of hours, else
2530 as <replaceable>hh</replaceable>:<replaceable>mm</replaceable> if it
2531 is an integral number of minutes, else as
2532 <replaceable>hh</replaceable>:<replaceable>mm</replaceable>:<replaceable>ss</replaceable>.
2533 (The third case is not possible with any modern time zone standard,
2534 but it can appear when working with timestamps that predate the
2535 adoption of standardized time zones.)
2536 In the other date styles, the time zone is shown as an alphabetic
2537 abbreviation if one is in common use in the current zone. Otherwise
2538 it appears as a signed numeric offset in ISO 8601 basic format
2539 (<replaceable>hh</replaceable> or <replaceable>hhmm</replaceable>).
2540 The alphabetic abbreviations shown in these styles are taken from the
2541 IANA time zone database entry currently selected by the
2542 <xref linkend="guc-timezone"/> run-time parameter; they are not
2543 affected by the <xref linkend="guc-timezone-abbreviations"/> setting.
2544 </para>
2545
2546 <para>
2547 The date/time style can be selected by the user using the
2548 <command>SET datestyle</command> command, the <xref
2549 linkend="guc-datestyle"/> parameter in the
2550 <filename>postgresql.conf</filename> configuration file, or the
2551 <envar>PGDATESTYLE</envar> environment variable on the server or
2552 client.
2553 </para>
2554
2555 <para>
2556 The formatting function <function>to_char</function>
2557 (see <xref linkend="functions-formatting"/>) is also available as
2558 a more flexible way to format date/time output.
2559 </para>
2560 </sect2>
2561
2562 <sect2 id="datatype-timezones">
2563 <title>Time Zones</title>
2564
2565 <indexterm zone="datatype-timezones">
2566 <primary>time zone</primary>
2567 </indexterm>
2568
2569 <para>
2570 Time zones, and time-zone conventions, are influenced by
2571 political decisions, not just earth geometry. Time zones around the
2572 world became somewhat standardized during the 1900s,
2573 but continue to be prone to arbitrary changes, particularly with
2574 respect to daylight-savings rules.
2575 <productname>PostgreSQL</productname> uses the widely-used
2576 IANA (Olson) time zone database for information about
2577 historical time zone rules. For times in the future, the assumption
2578 is that the latest known rules for a given time zone will
2579 continue to be observed indefinitely far into the future.
2580 </para>
2581
2582 <para>
2583 <productname>PostgreSQL</productname> endeavors to be compatible with
2584 the <acronym>SQL</acronym> standard definitions for typical usage.
2585 However, the <acronym>SQL</acronym> standard has an odd mix of date and
2586 time types and capabilities. Two obvious problems are:
2587
2588 <itemizedlist>
2589 <listitem>
2590 <para>
2591 Although the <type>date</type> type
2592 cannot have an associated time zone, the
2593 <type>time</type> type can.
2594 Time zones in the real world have little meaning unless
2595 associated with a date as well as a time,
2596 since the offset can vary through the year with daylight-saving
2597 time boundaries.
2598 </para>
2599 </listitem>
2600
2601 <listitem>
2602 <para>
2603 The default time zone is specified as a constant numeric offset
2604 from <acronym>UTC</acronym>. It is therefore impossible to adapt to
2605 daylight-saving time when doing date/time arithmetic across
2606 <acronym>DST</acronym> boundaries.
2607 </para>
2608 </listitem>
2609
2610 </itemizedlist>
2611 </para>
2612
2613 <para>
2614 To address these difficulties, we recommend using date/time types
2615 that contain both date and time when using time zones. We
2616 do <emphasis>not</emphasis> recommend using the type <type>time with
2617 time zone</type> (though it is supported by
2618 <productname>PostgreSQL</productname> for legacy applications and
2619 for compliance with the <acronym>SQL</acronym> standard).
2620 <productname>PostgreSQL</productname> assumes
2621 your local time zone for any type containing only date or time.
2622 </para>
2623
2624 <para>
2625 All timezone-aware dates and times are stored internally in
2626 <acronym>UTC</acronym>. They are converted to local time
2627 in the zone specified by the <xref linkend="guc-timezone"/> configuration
2628 parameter before being displayed to the client.
2629 </para>
2630
2631 <para>
2632 <productname>PostgreSQL</productname> allows you to specify time zones in
2633 three different forms:
2634 <itemizedlist>
2635 <listitem>
2636 <para>
2637 A full time zone name, for example <literal>America/New_York</literal>.
2638 The recognized time zone names are listed in the
2639 <literal>pg_timezone_names</literal> view (see <xref
2640 linkend="view-pg-timezone-names"/>).
2641 <productname>PostgreSQL</productname> uses the widely-used IANA
2642 time zone data for this purpose, so the same time zone
2643 names are also recognized by other software.
2644 </para>
2645 </listitem>
2646 <listitem>
2647 <para>
2648 A time zone abbreviation, for example <literal>PST</literal>. Such a
2649 specification merely defines a particular offset from UTC, in
2650 contrast to full time zone names which can imply a set of daylight
2651 savings transition rules as well. The recognized abbreviations
2652 are listed in the <literal>pg_timezone_abbrevs</literal> view (see <xref
2653 linkend="view-pg-timezone-abbrevs"/>). You cannot set the
2654 configuration parameters <xref linkend="guc-timezone"/> or
2655 <xref linkend="guc-log-timezone"/> to a time
2656 zone abbreviation, but you can use abbreviations in
2657 date/time input values and with the <literal>AT TIME ZONE</literal>
2658 operator.
2659 </para>
2660 </listitem>
2661 <listitem>
2662 <para>
2663 In addition to the timezone names and abbreviations,
2664 <productname>PostgreSQL</productname> will accept POSIX-style time zone
2665 specifications, as described in
2666 <xref linkend="datetime-posix-timezone-specs"/>. This option is not
2667 normally preferable to using a named time zone, but it may be
2668 necessary if no suitable IANA time zone entry is available.
2669 </para>
2670 </listitem>
2671 </itemizedlist>
2672
2673 In short, this is the difference between abbreviations
2674 and full names: abbreviations represent a specific offset from UTC,
2675 whereas many of the full names imply a local daylight-savings time
2676 rule, and so have two possible UTC offsets. As an example,
2677 <literal>2014-06-04 12:00 America/New_York</literal> represents noon local
2678 time in New York, which for this particular date was Eastern Daylight
2679 Time (UTC-4). So <literal>2014-06-04 12:00 EDT</literal> specifies that
2680 same time instant. But <literal>2014-06-04 12:00 EST</literal> specifies
2681 noon Eastern Standard Time (UTC-5), regardless of whether daylight
2682 savings was nominally in effect on that date.
2683 </para>
2684
2685 <note>
2686 <para>
2687 The sign in POSIX-style time zone specifications has the opposite meaning
2688 of the sign in ISO-8601 datetime values. For example, the POSIX time zone
2689 for <literal>2014-06-04 12:00+04</literal> would be UTC-4.
2690 </para>
2691 </note>
2692
2693 <para>
2694 To complicate matters, some jurisdictions have used the same timezone
2695 abbreviation to mean different UTC offsets at different times; for
2696 example, in Moscow <literal>MSK</literal> has meant UTC+3 in some years and
2697 UTC+4 in others. <productname>PostgreSQL</productname> interprets such
2698 abbreviations according to whatever they meant (or had most recently
2699 meant) on the specified date; but, as with the <literal>EST</literal> example
2700 above, this is not necessarily the same as local civil time on that date.
2701 </para>
2702
2703 <para>
2704 In all cases, timezone names and abbreviations are recognized
2705 case-insensitively. (This is a change from <productname>PostgreSQL</productname>
2706 versions prior to 8.2, which were case-sensitive in some contexts but
2707 not others.)
2708 </para>
2709
2710 <para>
2711 Neither timezone names nor abbreviations are hard-wired into the server;
2712 they are obtained from configuration files stored under
2713 <filename>.../share/timezone/</filename> and <filename>.../share/timezonesets/</filename>
2714 of the installation directory
2715 (see <xref linkend="datetime-config-files"/>).
2716 </para>
2717
2718 <para>
2719 The <xref linkend="guc-timezone"/> configuration parameter can
2720 be set in the file <filename>postgresql.conf</filename>, or in any of the
2721 other standard ways described in <xref linkend="runtime-config"/>.
2722 There are also some special ways to set it:
2723
2724 <itemizedlist>
2725 <listitem>
2726 <para>
2727 The <acronym>SQL</acronym> command <command>SET TIME ZONE</command>
2728 sets the time zone for the session. This is an alternative spelling
2729 of <command>SET TIMEZONE TO</command> with a more SQL-spec-compatible syntax.
2730 </para>
2731 </listitem>
2732
2733 <listitem>
2734 <para>
2735 The <envar>PGTZ</envar> environment variable is used by
2736 <application>libpq</application> clients
2737 to send a <command>SET TIME ZONE</command>
2738 command to the server upon connection.
2739 </para>
2740 </listitem>
2741 </itemizedlist>
2742 </para>
2743 </sect2>
2744
2745 <sect2 id="datatype-interval-input">
2746 <title>Interval Input</title>
2747
2748 <indexterm>
2749 <primary>interval</primary>
2750 </indexterm>
2751
2752 <para>
2753 <type>interval</type> values can be written using the following
2754 verbose syntax:
2755
2756 <synopsis>
2757 <optional>@</optional> <replaceable>quantity</replaceable> <replaceable>unit</replaceable> <optional><replaceable>quantity</replaceable> <replaceable>unit</replaceable>...</optional> <optional><replaceable>direction</replaceable></optional>
2758 </synopsis>
2759
2760 where <replaceable>quantity</replaceable> is a number (possibly signed);
2761 <replaceable>unit</replaceable> is <literal>microsecond</literal>,
2762 <literal>millisecond</literal>, <literal>second</literal>,
2763 <literal>minute</literal>, <literal>hour</literal>, <literal>day</literal>,
2764 <literal>week</literal>, <literal>month</literal>, <literal>year</literal>,
2765 <literal>decade</literal>, <literal>century</literal>, <literal>millennium</literal>,
2766 or abbreviations or plurals of these units;
2767 <replaceable>direction</replaceable> can be <literal>ago</literal> or
2768 empty. The at sign (<literal>@</literal>) is optional noise. The amounts
2769 of the different units are implicitly added with appropriate
2770 sign accounting. <literal>ago</literal> negates all the fields.
2771 This syntax is also used for interval output, if
2772 <xref linkend="guc-intervalstyle"/> is set to
2773 <literal>postgres_verbose</literal>.
2774 </para>
2775
2776 <para>
2777 Quantities of days, hours, minutes, and seconds can be specified without
2778 explicit unit markings. For example, <literal>'1 12:59:10'</literal> is read
2779 the same as <literal>'1 day 12 hours 59 min 10 sec'</literal>. Also,
2780 a combination of years and months can be specified with a dash;
2781 for example <literal>'200-10'</literal> is read the same as <literal>'200 years
2782 10 months'</literal>. (These shorter forms are in fact the only ones allowed
2783 by the <acronym>SQL</acronym> standard, and are used for output when
2784 <varname>IntervalStyle</varname> is set to <literal>sql_standard</literal>.)
2785 </para>
2786
2787 <para>
2788 Interval values can also be written as ISO 8601 time intervals, using
2789 either the <quote>format with designators</quote> of the standard's section
2790 4.4.3.2 or the <quote>alternative format</quote> of section 4.4.3.3. The
2791 format with designators looks like this:
2792 <synopsis>
2793 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>
2794 </synopsis>
2795 The string must start with a <literal>P</literal>, and may include a
2796 <literal>T</literal> that introduces the time-of-day units. The
2797 available unit abbreviations are given in <xref
2798 linkend="datatype-interval-iso8601-units"/>. Units may be
2799 omitted, and may be specified in any order, but units smaller than
2800 a day must appear after <literal>T</literal>. In particular, the meaning of
2801 <literal>M</literal> depends on whether it is before or after
2802 <literal>T</literal>.
2803 </para>
2804
2805 <table id="datatype-interval-iso8601-units">
2806 <title>ISO 8601 Interval Unit Abbreviations</title>
2807 <tgroup cols="2">
2808 <thead>
2809 <row>
2810 <entry>Abbreviation</entry>
2811 <entry>Meaning</entry>
2812 </row>
2813 </thead>
2814 <tbody>
2815 <row>
2816 <entry>Y</entry>
2817 <entry>Years</entry>
2818 </row>
2819 <row>
2820 <entry>M</entry>
2821 <entry>Months (in the date part)</entry>
2822 </row>
2823 <row>
2824 <entry>W</entry>
2825 <entry>Weeks</entry>
2826 </row>
2827 <row>
2828 <entry>D</entry>
2829 <entry>Days</entry>
2830 </row>
2831 <row>
2832 <entry>H</entry>
2833 <entry>Hours</entry>
2834 </row>
2835 <row>
2836 <entry>M</entry>
2837 <entry>Minutes (in the time part)</entry>
2838 </row>
2839 <row>
2840 <entry>S</entry>
2841 <entry>Seconds</entry>
2842 </row>
2843 </tbody>
2844 </tgroup>
2845 </table>
2846
2847 <para>
2848 In the alternative format:
2849 <synopsis>
2850 P <optional> <replaceable>years</replaceable>-<replaceable>months</replaceable>-<replaceable>days</replaceable> </optional> <optional> T <replaceable>hours</replaceable>:<replaceable>minutes</replaceable>:<replaceable>seconds</replaceable> </optional>
2851 </synopsis>
2852 the string must begin with <literal>P</literal>, and a
2853 <literal>T</literal> separates the date and time parts of the interval.
2854 The values are given as numbers similar to ISO 8601 dates.
2855 </para>
2856
2857 <para>
2858 When writing an interval constant with a <replaceable>fields</replaceable>
2859 specification, or when assigning a string to an interval column that was
2860 defined with a <replaceable>fields</replaceable> specification, the interpretation of
2861 unmarked quantities depends on the <replaceable>fields</replaceable>. For
2862 example <literal>INTERVAL '1' YEAR</literal> is read as 1 year, whereas
2863 <literal>INTERVAL '1'</literal> means 1 second. Also, field values
2864 <quote>to the right</quote> of the least significant field allowed by the
2865 <replaceable>fields</replaceable> specification are silently discarded. For
2866 example, writing <literal>INTERVAL '1 day 2:03:04' HOUR TO MINUTE</literal>
2867 results in dropping the seconds field, but not the day field.
2868 </para>
2869
2870 <para>
2871 According to the <acronym>SQL</acronym> standard all fields of an interval
2872 value must have the same sign, so a leading negative sign applies to all
2873 fields; for example the negative sign in the interval literal
2874 <literal>'-1 2:03:04'</literal> applies to both the days and hour/minute/second
2875 parts. <productname>PostgreSQL</productname> allows the fields to have different
2876 signs, and traditionally treats each field in the textual representation
2877 as independently signed, so that the hour/minute/second part is
2878 considered positive in this example. If <varname>IntervalStyle</varname> is
2879 set to <literal>sql_standard</literal> then a leading sign is considered
2880 to apply to all fields (but only if no additional signs appear).
2881 Otherwise the traditional <productname>PostgreSQL</productname> interpretation is
2882 used. To avoid ambiguity, it's recommended to attach an explicit sign
2883 to each field if any field is negative.
2884 </para>
2885
2886 <para>
2887 Internally, <type>interval</type> values are stored as three integral
2888 fields: months, days, and microseconds. These fields are kept
2889 separate because the number of days in a month varies, while a day
2890 can have 23 or 25 hours if a daylight savings time transition is
2891 involved. An interval input string that uses other units is
2892 normalized into this format, and then reconstructed in a standardized
2893 way for output, for example:
2894
2895 <programlisting>
2896 SELECT '2 years 15 months 100 weeks 99 hours 123456789 milliseconds'::interval;
2897 interval
2898 ---------------------------------------
2899 3 years 3 mons 700 days 133:17:36.789
2900 </programlisting>
2901
2902 Here weeks, which are understood as <quote>7 days</quote>, have been
2903 kept separate, while the smaller and larger time units were
2904 combined and normalized.
2905 </para>
2906
2907 <para>
2908 Input field values can have fractional parts, for example <literal>'1.5
2909 weeks'</literal> or <literal>'01:02:03.45'</literal>. However,
2910 because <type>interval</type> internally stores only integral fields,
2911 fractional values must be converted into smaller
2912 units. Fractional parts of units greater than months are rounded to
2913 be an integer number of months, e.g. <literal>'1.5 years'</literal>
2914 becomes <literal>'1 year 6 mons'</literal>. Fractional parts of
2915 weeks and days are computed to be an integer number of days and
2916 microseconds, assuming 30 days per month and 24 hours per day, e.g.,
2917 <literal>'1.75 months'</literal> becomes <literal>1 mon 22 days
2918 12:00:00</literal>. Only seconds will ever be shown as fractional
2919 on output.
2920 </para>
2921
2922 <para>
2923 <xref linkend="datatype-interval-input-examples"/> shows some examples
2924 of valid <type>interval</type> input.
2925 </para>
2926
2927 <table id="datatype-interval-input-examples">
2928 <title>Interval Input</title>
2929 <tgroup cols="2">
2930 <thead>
2931 <row>
2932 <entry>Example</entry>
2933 <entry>Description</entry>
2934 </row>
2935 </thead>
2936 <tbody>
2937 <row>
2938 <entry><literal>1-2</literal></entry>
2939 <entry>SQL standard format: 1 year 2 months</entry>
2940 </row>
2941 <row>
2942 <entry><literal>3 4:05:06</literal></entry>
2943 <entry>SQL standard format: 3 days 4 hours 5 minutes 6 seconds</entry>
2944 </row>
2945 <row>
2946 <entry><literal>1 year 2 months 3 days 4 hours 5 minutes 6 seconds</literal></entry>
2947 <entry>Traditional Postgres format: 1 year 2 months 3 days 4 hours 5 minutes 6 seconds</entry>
2948 </row>
2949 <row>
2950 <entry><literal>P1Y2M3DT4H5M6S</literal></entry>
2951 <entry>ISO 8601 <quote>format with designators</quote>: same meaning as above</entry>
2952 </row>
2953 <row>
2954 <entry><literal>P0001-02-03T04:05:06</literal></entry>
2955 <entry>ISO 8601 <quote>alternative format</quote>: same meaning as above</entry>
2956 </row>
2957 </tbody>
2958 </tgroup>
2959 </table>
2960
2961 </sect2>
2962
2963 <sect2 id="datatype-interval-output">
2964 <title>Interval Output</title>
2965
2966 <indexterm>
2967 <primary>interval</primary>
2968 <secondary>output format</secondary>
2969 <seealso>formatting</seealso>
2970 </indexterm>
2971
2972 <para>
2973 As previously explained, <productname>PostgreSQL</productname>
2974 stores <type>interval</type> values as months, days, and
2975 microseconds. For output, the months field is converted to years and
2976 months by dividing by 12. The days field is shown as-is. The
2977 microseconds field is converted to hours, minutes, seconds, and
2978 fractional seconds. Thus months, minutes, and seconds will never be
2979 shown as exceeding the ranges 0&ndash;11, 0&ndash;59, and 0&ndash;59
2980 respectively, while the displayed years, days, and hours fields can
2981 be quite large. (The <link
2982 linkend="function-justify-days"><function>justify_days</function></link>
2983 and <link
2984 linkend="function-justify-hours"><function>justify_hours</function></link>
2985 functions can be used if it is desirable to transpose large days or
2986 hours values into the next higher field.)
2987 </para>
2988
2989 <para>
2990 The output format of the interval type can be set to one of the
2991 four styles <literal>sql_standard</literal>, <literal>postgres</literal>,
2992 <literal>postgres_verbose</literal>, or <literal>iso_8601</literal>,
2993 using the command <literal>SET intervalstyle</literal>.
2994 The default is the <literal>postgres</literal> format.
2995 <xref linkend="interval-style-output-table"/> shows examples of each
2996 output style.
2997 </para>
2998
2999 <para>
3000 The <literal>sql_standard</literal> style produces output that conforms to
3001 the SQL standard's specification for interval literal strings, if
3002 the interval value meets the standard's restrictions (either year-month
3003 only or day-time only, with no mixing of positive
3004 and negative components). Otherwise the output looks like a standard
3005 year-month literal string followed by a day-time literal string,
3006 with explicit signs added to disambiguate mixed-sign intervals.
3007 </para>
3008
3009 <para>
3010 The output of the <literal>postgres</literal> style matches the output of
3011 <productname>PostgreSQL</productname> releases prior to 8.4 when the
3012 <xref linkend="guc-datestyle"/> parameter was set to <literal>ISO</literal>.
3013 </para>
3014
3015 <para>
3016 The output of the <literal>postgres_verbose</literal> style matches the output of
3017 <productname>PostgreSQL</productname> releases prior to 8.4 when the
3018 <varname>DateStyle</varname> parameter was set to non-<literal>ISO</literal> output.
3019 </para>
3020
3021 <para>
3022 The output of the <literal>iso_8601</literal> style matches the <quote>format
3023 with designators</quote> described in section 4.4.3.2 of the
3024 ISO 8601 standard.
3025 </para>
3026
3027 <table id="interval-style-output-table">
3028 <title>Interval Output Style Examples</title>
3029 <tgroup cols="4">
3030 <thead>
3031 <row>
3032 <entry>Style Specification</entry>
3033 <entry>Year-Month Interval</entry>
3034 <entry>Day-Time Interval</entry>
3035 <entry>Mixed Interval</entry>
3036 </row>
3037 </thead>
3038 <tbody>
3039 <row>
3040 <entry><literal>sql_standard</literal></entry>
3041 <entry>1-2</entry>
3042 <entry>3 4:05:06</entry>
3043 <entry>-1-2 +3 -4:05:06</entry>
3044 </row>
3045 <row>
3046 <entry><literal>postgres</literal></entry>
3047 <entry>1 year 2 mons</entry>
3048 <entry>3 days 04:05:06</entry>
3049 <entry>-1 year -2 mons +3 days -04:05:06</entry>
3050 </row>
3051 <row>
3052 <entry><literal>postgres_verbose</literal></entry>
3053 <entry>@ 1 year 2 mons</entry>
3054 <entry>@ 3 days 4 hours 5 mins 6 secs</entry>
3055 <entry>@ 1 year 2 mons -3 days 4 hours 5 mins 6 secs ago</entry>
3056 </row>
3057 <row>
3058 <entry><literal>iso_8601</literal></entry>
3059 <entry>P1Y2M</entry>
3060 <entry>P3DT4H5M6S</entry>
3061 <entry>P-1Y-2M3D&zwsp;T-4H-5M-6S</entry>
3062 </row>
3063 </tbody>
3064 </tgroup>
3065 </table>
3066
3067 </sect2>
3068
3069 </sect1>
3070
3071 <sect1 id="datatype-boolean">
3072 <title>Boolean Type</title>
3073
3074 <indexterm zone="datatype-boolean">
3075 <primary>Boolean</primary>
3076 <secondary>data type</secondary>
3077 </indexterm>
3078
3079 <indexterm zone="datatype-boolean">
3080 <primary>true</primary>
3081 </indexterm>
3082
3083 <indexterm zone="datatype-boolean">
3084 <primary>false</primary>
3085 </indexterm>
3086
3087 <para>
3088 <productname>PostgreSQL</productname> provides the
3089 standard <acronym>SQL</acronym> type <type>boolean</type>;
3090 see <xref linkend="datatype-boolean-table"/>.
3091 The <type>boolean</type> type can have several states:
3092 <quote>true</quote>, <quote>false</quote>, and a third state,
3093 <quote>unknown</quote>, which is represented by the
3094 <acronym>SQL</acronym> null value.
3095 </para>
3096
3097 <table id="datatype-boolean-table">
3098 <title>Boolean Data Type</title>
3099 <tgroup cols="3">
3100 <thead>
3101 <row>
3102 <entry>Name</entry>
3103 <entry>Storage Size</entry>
3104 <entry>Description</entry>
3105 </row>
3106 </thead>
3107 <tbody>
3108 <row>
3109 <entry><type>boolean</type></entry>
3110 <entry>1 byte</entry>
3111 <entry>state of true or false</entry>
3112 </row>
3113 </tbody>
3114 </tgroup>
3115 </table>
3116
3117 <para>
3118 Boolean constants can be represented in SQL queries by the SQL
3119 key words <literal>TRUE</literal>, <literal>FALSE</literal>,
3120 and <literal>NULL</literal>.
3121 </para>
3122
3123 <para>
3124 The datatype input function for type <type>boolean</type> accepts these
3125 string representations for the <quote>true</quote> state:
3126 <simplelist>
3127 <member><literal>true</literal></member>
3128 <member><literal>yes</literal></member>
3129 <member><literal>on</literal></member>
3130 <member><literal>1</literal></member>
3131 </simplelist>
3132 and these representations for the <quote>false</quote> state:
3133 <simplelist>
3134 <member><literal>false</literal></member>
3135 <member><literal>no</literal></member>
3136 <member><literal>off</literal></member>
3137 <member><literal>0</literal></member>
3138 </simplelist>
3139 Unique prefixes of these strings are also accepted, for
3140 example <literal>t</literal> or <literal>n</literal>.
3141 Leading or trailing whitespace is ignored, and case does not matter.
3142 </para>
3143
3144 <para>
3145 The datatype output function for type <type>boolean</type> always emits
3146 either <literal>t</literal> or <literal>f</literal>, as shown in
3147 <xref linkend="datatype-boolean-example"/>.
3148 </para>
3149
3150 <example id="datatype-boolean-example">
3151 <title>Using the <type>boolean</type> Type</title>
3152
3153 <programlisting>
3154 CREATE TABLE test1 (a boolean, b text);
3155 INSERT INTO test1 VALUES (TRUE, 'sic est');
3156 INSERT INTO test1 VALUES (FALSE, 'non est');
3157 SELECT * FROM test1;
3158 a | b
3159 ---+---------
3160 t | sic est
3161 f | non est
3162
3163 SELECT * FROM test1 WHERE a;
3164 a | b
3165 ---+---------
3166 t | sic est
3167 </programlisting>
3168 </example>
3169
3170 <para>
3171 The key words <literal>TRUE</literal> and <literal>FALSE</literal> are
3172 the preferred (<acronym>SQL</acronym>-compliant) method for writing
3173 Boolean constants in SQL queries. But you can also use the string
3174 representations by following the generic string-literal constant syntax
3175 described in <xref linkend="sql-syntax-constants-generic"/>, for
3176 example <literal>'yes'::boolean</literal>.
3177 </para>
3178
3179 <para>
3180 Note that the parser automatically understands
3181 that <literal>TRUE</literal> and <literal>FALSE</literal> are of
3182 type <type>boolean</type>, but this is not so
3183 for <literal>NULL</literal> because that can have any type.
3184 So in some contexts you might have to cast <literal>NULL</literal>
3185 to <type>boolean</type> explicitly, for
3186 example <literal>NULL::boolean</literal>. Conversely, the cast can be
3187 omitted from a string-literal Boolean value in contexts where the parser
3188 can deduce that the literal must be of type <type>boolean</type>.
3189 </para>
3190 </sect1>
3191
3192 <sect1 id="datatype-enum">
3193 <title>Enumerated Types</title>
3194
3195 <indexterm zone="datatype-enum">
3196 <primary>data type</primary>
3197 <secondary>enumerated (enum)</secondary>
3198 </indexterm>
3199
3200 <indexterm zone="datatype-enum">
3201 <primary>enumerated types</primary>
3202 </indexterm>
3203
3204 <para>
3205 Enumerated (enum) types are data types that
3206 comprise a static, ordered set of values.
3207 They are equivalent to the <type>enum</type>
3208 types supported in a number of programming languages. An example of an enum
3209 type might be the days of the week, or a set of status values for
3210 a piece of data.
3211 </para>
3212
3213 <sect2 id="datatype-enum-declaration">
3214 <title>Declaration of Enumerated Types</title>
3215
3216 <para>
3217 Enum types are created using the <xref
3218 linkend="sql-createtype"/> command,
3219 for example:
3220
3221 <programlisting>
3222 CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
3223 </programlisting>
3224
3225 Once created, the enum type can be used in table and function
3226 definitions much like any other type:
3227 <programlisting>
3228 CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
3229 CREATE TABLE person (
3230 name text,
3231 current_mood mood
3232 );
3233 INSERT INTO person VALUES ('Moe', 'happy');
3234 SELECT * FROM person WHERE current_mood = 'happy';
3235 name | current_mood
3236 ------+--------------
3237 Moe | happy
3238 (1 row)
3239 </programlisting>
3240 </para>
3241 </sect2>
3242
3243 <sect2 id="datatype-enum-ordering">
3244 <title>Ordering</title>
3245
3246 <para>
3247 The ordering of the values in an enum type is the
3248 order in which the values were listed when the type was created.
3249 All standard comparison operators and related
3250 aggregate functions are supported for enums. For example:
3251
3252 <programlisting>
3253 INSERT INTO person VALUES ('Larry', 'sad');
3254 INSERT INTO person VALUES ('Curly', 'ok');
3255 SELECT * FROM person WHERE current_mood > 'sad';
3256 name | current_mood
3257 -------+--------------
3258 Moe | happy
3259 Curly | ok
3260 (2 rows)
3261
3262 SELECT * FROM person WHERE current_mood > 'sad' ORDER BY current_mood;
3263 name | current_mood
3264 -------+--------------
3265 Curly | ok
3266 Moe | happy
3267 (2 rows)
3268
3269 SELECT name
3270 FROM person
3271 WHERE current_mood = (SELECT MIN(current_mood) FROM person);
3272 name
3273 -------
3274 Larry
3275 (1 row)
3276 </programlisting>
3277 </para>
3278 </sect2>
3279
3280 <sect2 id="datatype-enum-type-safety">
3281 <title>Type Safety</title>
3282
3283 <para>
3284 Each enumerated data type is separate and cannot
3285 be compared with other enumerated types. See this example:
3286
3287 <programlisting>
3288 CREATE TYPE happiness AS ENUM ('happy', 'very happy', 'ecstatic');
3289 CREATE TABLE holidays (
3290 num_weeks integer,
3291 happiness happiness
3292 );
3293 INSERT INTO holidays(num_weeks,happiness) VALUES (4, 'happy');
3294 INSERT INTO holidays(num_weeks,happiness) VALUES (6, 'very happy');
3295 INSERT INTO holidays(num_weeks,happiness) VALUES (8, 'ecstatic');
3296 INSERT INTO holidays(num_weeks,happiness) VALUES (2, 'sad');
3297 ERROR: invalid input value for enum happiness: "sad"
3298 SELECT person.name, holidays.num_weeks FROM person, holidays
3299 WHERE person.current_mood = holidays.happiness;
3300 ERROR: operator does not exist: mood = happiness
3301 </programlisting>
3302 </para>
3303
3304 <para>
3305 If you really need to do something like that, you can either
3306 write a custom operator or add explicit casts to your query:
3307
3308 <programlisting>
3309 SELECT person.name, holidays.num_weeks FROM person, holidays
3310 WHERE person.current_mood::text = holidays.happiness::text;
3311 name | num_weeks
3312 ------+-----------
3313 Moe | 4
3314 (1 row)
3315
3316 </programlisting>
3317 </para>
3318 </sect2>
3319
3320 <sect2 id="datatype-enum-implementation-details">
3321 <title>Implementation Details</title>
3322
3323 <para>
3324 Enum labels are case sensitive, so
3325 <type>'happy'</type> is not the same as <type>'HAPPY'</type>.
3326 White space in the labels is significant too.
3327 </para>
3328
3329 <para>
3330 Although enum types are primarily intended for static sets of values,
3331 there is support for adding new values to an existing enum type, and for
3332 renaming values (see <xref linkend="sql-altertype"/>). Existing values
3333 cannot be removed from an enum type, nor can the sort ordering of such
3334 values be changed, short of dropping and re-creating the enum type.
3335 </para>
3336
3337 <para>
3338 An enum value occupies four bytes on disk. The length of an enum
3339 value's textual label is limited by the <symbol>NAMEDATALEN</symbol>
3340 setting compiled into <productname>PostgreSQL</productname>; in standard
3341 builds this means at most 63 bytes.
3342 </para>
3343
3344 <para>
3345 The translations from internal enum values to textual labels are
3346 kept in the system catalog
3347 <link linkend="catalog-pg-enum"><structname>pg_enum</structname></link>.
3348 Querying this catalog directly can be useful.
3349 </para>
3350
3351 </sect2>
3352 </sect1>
3353
3354 <sect1 id="datatype-geometric">
3355 <title>Geometric Types</title>
3356
3357 <para>
3358 Geometric data types represent two-dimensional spatial
3359 objects. <xref linkend="datatype-geo-table"/> shows the geometric
3360 types available in <productname>PostgreSQL</productname>.
3361 </para>
3362
3363 <table id="datatype-geo-table">
3364 <title>Geometric Types</title>
3365 <tgroup cols="4">
3366 <colspec colname="col1" colwidth="1*"/>
3367 <colspec colname="col2" colwidth="1*"/>
3368 <colspec colname="col3" colwidth="2*"/>
3369 <colspec colname="col4" colwidth="1*"/>
3370 <thead>
3371 <row>
3372 <entry>Name</entry>
3373 <entry>Storage Size</entry>
3374 <entry>Description</entry>
3375 <entry>Representation</entry>
3376 </row>
3377 </thead>
3378 <tbody>
3379 <row>
3380 <entry><type>point</type></entry>
3381 <entry>16 bytes</entry>
3382 <entry>Point on a plane</entry>
3383 <entry>(x,y)</entry>
3384 </row>
3385 <row>
3386 <entry><type>line</type></entry>
3387 <entry>24 bytes</entry>
3388 <entry>Infinite line</entry>
3389 <entry>{A,B,C}</entry>
3390 </row>
3391 <row>
3392 <entry><type>lseg</type></entry>
3393 <entry>32 bytes</entry>
3394 <entry>Finite line segment</entry>
3395 <entry>[(x1,y1),(x2,y2)]</entry>
3396 </row>
3397 <row>
3398 <entry><type>box</type></entry>
3399 <entry>32 bytes</entry>
3400 <entry>Rectangular box</entry>
3401 <entry>(x1,y1),(x2,y2)</entry>
3402 </row>
3403 <row>
3404 <entry><type>path</type></entry>
3405 <entry>16+16n bytes</entry>
3406 <entry>Closed path (similar to polygon)</entry>
3407 <entry>((x1,y1),...)</entry>
3408 </row>
3409 <row>
3410 <entry><type>path</type></entry>
3411 <entry>16+16n bytes</entry>
3412 <entry>Open path</entry>
3413 <entry>[(x1,y1),...]</entry>
3414 </row>
3415 <row>
3416 <entry><type>polygon</type></entry>
3417 <entry>40+16n bytes</entry>
3418 <entry>Polygon (similar to closed path)</entry>
3419 <entry>((x1,y1),...)</entry>
3420 </row>
3421 <row>
3422 <entry><type>circle</type></entry>
3423 <entry>24 bytes</entry>
3424 <entry>Circle</entry>
3425 <entry>&lt;(x,y),r&gt; (center point and radius)</entry>
3426 </row>
3427 </tbody>
3428 </tgroup>
3429 </table>
3430
3431 <para>
3432 In all these types, the individual coordinates are stored as
3433 <type>double precision</type> (<type>float8</type>) numbers.
3434 </para>
3435
3436 <para>
3437 A rich set of functions and operators is available to perform various geometric
3438 operations such as scaling, translation, rotation, and determining
3439 intersections. They are explained in <xref linkend="functions-geometry"/>.
3440 </para>
3441
3442 <sect2 id="datatype-geometric-points">
3443 <title>Points</title>
3444
3445 <indexterm>
3446 <primary>point</primary>
3447 </indexterm>
3448
3449 <para>
3450 Points are the fundamental two-dimensional building block for geometric
3451 types. Values of type <type>point</type> are specified using either of
3452 the following syntaxes:
3453
3454 <synopsis>
3455 ( <replaceable>x</replaceable> , <replaceable>y</replaceable> )
3456 <replaceable>x</replaceable> , <replaceable>y</replaceable>
3457 </synopsis>
3458
3459 where <replaceable>x</replaceable> and <replaceable>y</replaceable> are the respective
3460 coordinates, as floating-point numbers.
3461 </para>
3462
3463 <para>
3464 Points are output using the first syntax.
3465 </para>
3466 </sect2>
3467
3468 <sect2 id="datatype-line">
3469 <title>Lines</title>
3470
3471 <indexterm>
3472 <primary>line</primary>
3473 </indexterm>
3474
3475 <para>
3476 Lines are represented by the linear
3477 equation <replaceable>A</replaceable>x + <replaceable>B</replaceable>y + <replaceable>C</replaceable> = 0,
3478 where <replaceable>A</replaceable> and <replaceable>B</replaceable> are not both zero. Values
3479 of type <type>line</type> are input and output in the following form:
3480 <synopsis>
3481 { <replaceable>A</replaceable>, <replaceable>B</replaceable>, <replaceable>C</replaceable> }
3482 </synopsis>
3483
3484 Alternatively, any of the following forms can be used for input:
3485
3486 <synopsis>
3487 [ ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ( <replaceable>x2</replaceable> , <replaceable>y2</replaceable> ) ]
3488 ( ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ( <replaceable>x2</replaceable> , <replaceable>y2</replaceable> ) )
3489 ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ( <replaceable>x2</replaceable> , <replaceable>y2</replaceable> )
3490 <replaceable>x1</replaceable> , <replaceable>y1</replaceable> , <replaceable>x2</replaceable> , <replaceable>y2</replaceable>
3491 </synopsis>
3492
3493 where
3494 <literal>(<replaceable>x1</replaceable>,<replaceable>y1</replaceable>)</literal>
3495 and
3496 <literal>(<replaceable>x2</replaceable>,<replaceable>y2</replaceable>)</literal>
3497 are two different points on the line.
3498 </para>
3499 </sect2>
3500
3501 <sect2 id="datatype-lseg">
3502 <title>Line Segments</title>
3503
3504 <indexterm>
3505 <primary>lseg</primary>
3506 </indexterm>
3507
3508 <indexterm>
3509 <primary>line segment</primary>
3510 </indexterm>
3511
3512 <para>
3513 Line segments are represented by pairs of points that are the endpoints
3514 of the segment. Values of type <type>lseg</type> are specified using any
3515 of the following syntaxes:
3516
3517 <synopsis>
3518 [ ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ( <replaceable>x2</replaceable> , <replaceable>y2</replaceable> ) ]
3519 ( ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ( <replaceable>x2</replaceable> , <replaceable>y2</replaceable> ) )
3520 ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ( <replaceable>x2</replaceable> , <replaceable>y2</replaceable> )
3521 <replaceable>x1</replaceable> , <replaceable>y1</replaceable> , <replaceable>x2</replaceable> , <replaceable>y2</replaceable>
3522 </synopsis>
3523
3524 where
3525 <literal>(<replaceable>x1</replaceable>,<replaceable>y1</replaceable>)</literal>
3526 and
3527 <literal>(<replaceable>x2</replaceable>,<replaceable>y2</replaceable>)</literal>
3528 are the end points of the line segment.
3529 </para>
3530
3531 <para>
3532 Line segments are output using the first syntax.
3533 </para>
3534 </sect2>
3535
3536 <sect2 id="datatype-geometric-boxes">
3537 <title>Boxes</title>
3538
3539 <indexterm>
3540 <primary>box (data type)</primary>
3541 </indexterm>
3542
3543 <indexterm>
3544 <primary>rectangle</primary>
3545 </indexterm>
3546
3547 <para>
3548 Boxes are represented by pairs of points that are opposite
3549 corners of the box.
3550 Values of type <type>box</type> are specified using any of the following
3551 syntaxes:
3552
3553 <synopsis>
3554 ( ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ( <replaceable>x2</replaceable> , <replaceable>y2</replaceable> ) )
3555 ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ( <replaceable>x2</replaceable> , <replaceable>y2</replaceable> )
3556 <replaceable>x1</replaceable> , <replaceable>y1</replaceable> , <replaceable>x2</replaceable> , <replaceable>y2</replaceable>
3557 </synopsis>
3558
3559 where
3560 <literal>(<replaceable>x1</replaceable>,<replaceable>y1</replaceable>)</literal>
3561 and
3562 <literal>(<replaceable>x2</replaceable>,<replaceable>y2</replaceable>)</literal>
3563 are any two opposite corners of the box.
3564 </para>
3565
3566 <para>
3567 Boxes are output using the second syntax.
3568 </para>
3569
3570 <para>
3571 Any two opposite corners can be supplied on input, but the values
3572 will be reordered as needed to store the
3573 upper right and lower left corners, in that order.
3574 </para>
3575 </sect2>
3576
3577 <sect2 id="datatype-geometric-paths">
3578 <title>Paths</title>
3579
3580 <indexterm>
3581 <primary>path (data type)</primary>
3582 </indexterm>
3583
3584 <para>
3585 Paths are represented by lists of connected points. Paths can be
3586 <firstterm>open</firstterm>, where
3587 the first and last points in the list are considered not connected, or
3588 <firstterm>closed</firstterm>,
3589 where the first and last points are considered connected.
3590 </para>
3591
3592 <para>
3593 Values of type <type>path</type> are specified using any of the following
3594 syntaxes:
3595
3596 <synopsis>
3597 [ ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ... , ( <replaceable>xn</replaceable> , <replaceable>yn</replaceable> ) ]
3598 ( ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ... , ( <replaceable>xn</replaceable> , <replaceable>yn</replaceable> ) )
3599 ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ... , ( <replaceable>xn</replaceable> , <replaceable>yn</replaceable> )
3600 ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> , ... , <replaceable>xn</replaceable> , <replaceable>yn</replaceable> )
3601 <replaceable>x1</replaceable> , <replaceable>y1</replaceable> , ... , <replaceable>xn</replaceable> , <replaceable>yn</replaceable>
3602 </synopsis>
3603
3604 where the points are the end points of the line segments
3605 comprising the path. Square brackets (<literal>[]</literal>) indicate
3606 an open path, while parentheses (<literal>()</literal>) indicate a
3607 closed path. When the outermost parentheses are omitted, as
3608 in the third through fifth syntaxes, a closed path is assumed.
3609 </para>
3610
3611 <para>
3612 Paths are output using the first or second syntax, as appropriate.
3613 </para>
3614 </sect2>
3615
3616 <sect2 id="datatype-polygon">
3617 <title>Polygons</title>
3618
3619 <indexterm>
3620 <primary>polygon</primary>
3621 </indexterm>
3622
3623 <para>
3624 Polygons are represented by lists of points (the vertices of the
3625 polygon). Polygons are very similar to closed paths; the essential
3626 semantic difference is that a polygon is considered to include the
3627 area within it, while a path is not.
3628 </para>
3629
3630 <para>
3631 An important implementation difference between polygons and
3632 paths is that the stored representation of a polygon includes its
3633 smallest bounding box. This speeds up certain search operations,
3634 although computing the bounding box adds overhead while constructing
3635 new polygons.
3636 </para>
3637
3638 <para>
3639 Values of type <type>polygon</type> are specified using any of the
3640 following syntaxes:
3641
3642 <synopsis>
3643 ( ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ... , ( <replaceable>xn</replaceable> , <replaceable>yn</replaceable> ) )
3644 ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ... , ( <replaceable>xn</replaceable> , <replaceable>yn</replaceable> )
3645 ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> , ... , <replaceable>xn</replaceable> , <replaceable>yn</replaceable> )
3646 <replaceable>x1</replaceable> , <replaceable>y1</replaceable> , ... , <replaceable>xn</replaceable> , <replaceable>yn</replaceable>
3647 </synopsis>
3648
3649 where the points are the end points of the line segments
3650 comprising the boundary of the polygon.
3651 </para>
3652
3653 <para>
3654 Polygons are output using the first syntax.
3655 </para>
3656 </sect2>
3657
3658 <sect2 id="datatype-circle">
3659 <title>Circles</title>
3660
3661 <indexterm>
3662 <primary>circle</primary>
3663 </indexterm>
3664
3665 <para>
3666 Circles are represented by a center point and radius.
3667 Values of type <type>circle</type> are specified using any of the
3668 following syntaxes:
3669
3670 <synopsis>
3671 &lt; ( <replaceable>x</replaceable> , <replaceable>y</replaceable> ) , <replaceable>r</replaceable> &gt;
3672 ( ( <replaceable>x</replaceable> , <replaceable>y</replaceable> ) , <replaceable>r</replaceable> )
3673 ( <replaceable>x</replaceable> , <replaceable>y</replaceable> ) , <replaceable>r</replaceable>
3674 <replaceable>x</replaceable> , <replaceable>y</replaceable> , <replaceable>r</replaceable>
3675 </synopsis>
3676
3677 where
3678 <literal>(<replaceable>x</replaceable>,<replaceable>y</replaceable>)</literal>
3679 is the center point and <replaceable>r</replaceable> is the radius of the
3680 circle.
3681 </para>
3682
3683 <para>
3684 Circles are output using the first syntax.
3685 </para>
3686 </sect2>
3687
3688 </sect1>
3689
3690 <sect1 id="datatype-net-types">
3691 <title>Network Address Types</title>
3692
3693 <indexterm zone="datatype-net-types">
3694 <primary>network</primary>
3695 <secondary>data types</secondary>
3696 </indexterm>
3697
3698 <para>
3699 <productname>PostgreSQL</productname> offers data types to store IPv4, IPv6, and MAC
3700 addresses, as shown in <xref linkend="datatype-net-types-table"/>. It
3701 is better to use these types instead of plain text types to store
3702 network addresses, because
3703 these types offer input error checking and specialized
3704 operators and functions (see <xref linkend="functions-net"/>).
3705 </para>
3706
3707 <table tocentry="1" id="datatype-net-types-table">
3708 <title>Network Address Types</title>
3709 <tgroup cols="3">
3710 <colspec colname="col1" colwidth="1*"/>
3711 <colspec colname="col2" colwidth="1*"/>
3712 <colspec colname="col3" colwidth="2*"/>
3713 <thead>
3714 <row>
3715 <entry>Name</entry>
3716 <entry>Storage Size</entry>
3717 <entry>Description</entry>
3718 </row>
3719 </thead>
3720 <tbody>
3721
3722 <row>
3723 <entry><type>cidr</type></entry>
3724 <entry>7 or 19 bytes</entry>
3725 <entry>IPv4 and IPv6 networks</entry>
3726 </row>
3727
3728 <row>
3729 <entry><type>inet</type></entry>
3730 <entry>7 or 19 bytes</entry>
3731 <entry>IPv4 and IPv6 hosts and networks</entry>
3732 </row>
3733
3734 <row>
3735 <entry><type>macaddr</type></entry>
3736 <entry>6 bytes</entry>
3737 <entry>MAC addresses</entry>
3738 </row>
3739
3740 <row>
3741 <entry><type>macaddr8</type></entry>
3742 <entry>8 bytes</entry>
3743 <entry>MAC addresses (EUI-64 format)</entry>
3744 </row>
3745
3746 </tbody>
3747 </tgroup>
3748 </table>
3749
3750 <para>
3751 When sorting <type>inet</type> or <type>cidr</type> data types,
3752 IPv4 addresses will always sort before IPv6 addresses, including
3753 IPv4 addresses encapsulated or mapped to IPv6 addresses, such as
3754 ::10.2.3.4 or ::ffff:10.4.3.2.
3755 </para>
3756
3757
3758 <sect2 id="datatype-inet">
3759 <title><type>inet</type></title>
3760
3761 <indexterm>
3762 <primary>inet (data type)</primary>
3763 </indexterm>
3764
3765 <para>
3766 The <type>inet</type> type holds an IPv4 or IPv6 host address, and
3767 optionally its subnet, all in one field.
3768 The subnet is represented by the number of network address bits
3769 present in the host address (the
3770 <quote>netmask</quote>). If the netmask is 32 and the address is IPv4,
3771 then the value does not indicate a subnet, only a single host.
3772 In IPv6, the address length is 128 bits, so 128 bits specify a
3773 unique host address. Note that if you
3774 want to accept only networks, you should use the
3775 <type>cidr</type> type rather than <type>inet</type>.
3776 </para>
3777
3778 <para>
3779 The input format for this type is
3780 <replaceable class="parameter">address/y</replaceable>
3781 where
3782 <replaceable class="parameter">address</replaceable>
3783 is an IPv4 or IPv6 address and
3784 <replaceable class="parameter">y</replaceable>
3785 is the number of bits in the netmask. If the
3786 <replaceable class="parameter">/y</replaceable>
3787 portion is omitted, the
3788 netmask is taken to be 32 for IPv4 or 128 for IPv6,
3789 so the value represents
3790 just a single host. On display, the
3791 <replaceable class="parameter">/y</replaceable>
3792 portion is suppressed if the netmask specifies a single host.
3793 </para>
3794 </sect2>
3795
3796 <sect2 id="datatype-cidr">
3797 <title><type>cidr</type></title>
3798
3799 <indexterm>
3800 <primary>cidr</primary>
3801 </indexterm>
3802
3803 <para>
3804 The <type>cidr</type> type holds an IPv4 or IPv6 network specification.
3805 Input and output formats follow Classless Internet Domain Routing
3806 conventions.
3807 The format for specifying networks is <replaceable
3808 class="parameter">address/y</replaceable> where <replaceable
3809 class="parameter">address</replaceable> is the network's lowest
3810 address represented as an
3811 IPv4 or IPv6 address, and <replaceable
3812 class="parameter">y</replaceable> is the number of bits in the netmask. If
3813 <replaceable class="parameter">y</replaceable> is omitted, it is calculated
3814 using assumptions from the older classful network numbering system, except
3815 it will be at least large enough to include all of the octets
3816 written in the input. It is an error to specify a network address
3817 that has bits set to the right of the specified netmask.
3818 </para>
3819
3820 <para>
3821 <xref linkend="datatype-net-cidr-table"/> shows some examples.
3822 </para>
3823
3824 <table id="datatype-net-cidr-table">
3825 <title><type>cidr</type> Type Input Examples</title>
3826 <tgroup cols="3">
3827 <thead>
3828 <row>
3829 <entry><type>cidr</type> Input</entry>
3830 <entry><type>cidr</type> Output</entry>
3831 <entry><literal><function>abbrev(<type>cidr</type>)</function></literal></entry>
3832 </row>
3833 </thead>
3834 <tbody>
3835 <row>
3836 <entry>192.168.100.128/25</entry>
3837 <entry>192.168.100.128/25</entry>
3838 <entry>192.168.100.128/25</entry>
3839 </row>
3840 <row>
3841 <entry>192.168/24</entry>
3842 <entry>192.168.0.0/24</entry>
3843 <entry>192.168.0/24</entry>
3844 </row>
3845 <row>
3846 <entry>192.168/25</entry>
3847 <entry>192.168.0.0/25</entry>
3848 <entry>192.168.0.0/25</entry>
3849 </row>
3850 <row>
3851 <entry>192.168.1</entry>
3852 <entry>192.168.1.0/24</entry>
3853 <entry>192.168.1/24</entry>
3854 </row>
3855 <row>
3856 <entry>192.168</entry>
3857 <entry>192.168.0.0/24</entry>
3858 <entry>192.168.0/24</entry>
3859 </row>
3860 <row>
3861 <entry>128.1</entry>
3862 <entry>128.1.0.0/16</entry>
3863 <entry>128.1/16</entry>
3864 </row>
3865 <row>
3866 <entry>128</entry>
3867 <entry>128.0.0.0/16</entry>
3868 <entry>128.0/16</entry>
3869 </row>
3870 <row>
3871 <entry>128.1.2</entry>
3872 <entry>128.1.2.0/24</entry>
3873 <entry>128.1.2/24</entry>
3874 </row>
3875 <row>
3876 <entry>10.1.2</entry>
3877 <entry>10.1.2.0/24</entry>
3878 <entry>10.1.2/24</entry>
3879 </row>
3880 <row>
3881 <entry>10.1</entry>
3882 <entry>10.1.0.0/16</entry>
3883 <entry>10.1/16</entry>
3884 </row>
3885 <row>
3886 <entry>10</entry>
3887 <entry>10.0.0.0/8</entry>
3888 <entry>10/8</entry>
3889 </row>
3890 <row>
3891 <entry>10.1.2.3/32</entry>
3892 <entry>10.1.2.3/32</entry>
3893 <entry>10.1.2.3/32</entry>
3894 </row>
3895 <row>
3896 <entry>2001:4f8:3:ba::/64</entry>
3897 <entry>2001:4f8:3:ba::/64</entry>
3898 <entry>2001:4f8:3:ba/64</entry>
3899 </row>
3900 <row>
3901 <entry>2001:4f8:3:ba:&zwsp;2e0:81ff:fe22:d1f1/128</entry>
3902 <entry>2001:4f8:3:ba:&zwsp;2e0:81ff:fe22:d1f1/128</entry>
3903 <entry>2001:4f8:3:ba:&zwsp;2e0:81ff:fe22:d1f1/128</entry>
3904 </row>
3905 <row>
3906 <entry>::ffff:1.2.3.0/120</entry>
3907 <entry>::ffff:1.2.3.0/120</entry>
3908 <entry>::ffff:1.2.3/120</entry>
3909 </row>
3910 <row>
3911 <entry>::ffff:1.2.3.0/128</entry>
3912 <entry>::ffff:1.2.3.0/128</entry>
3913 <entry>::ffff:1.2.3.0/128</entry>
3914 </row>
3915 </tbody>
3916 </tgroup>
3917 </table>
3918 </sect2>
3919
3920 <sect2 id="datatype-inet-vs-cidr">
3921 <title><type>inet</type> vs. <type>cidr</type></title>
3922
3923 <para>
3924 The essential difference between <type>inet</type> and <type>cidr</type>
3925 data types is that <type>inet</type> accepts values with nonzero bits to
3926 the right of the netmask, whereas <type>cidr</type> does not. For
3927 example, <literal>192.168.0.1/24</literal> is valid for <type>inet</type>
3928 but not for <type>cidr</type>.
3929 </para>
3930
3931 <tip>
3932 <para>
3933 If you do not like the output format for <type>inet</type> or
3934 <type>cidr</type> values, try the functions <function>host</function>,
3935 <function>text</function>, and <function>abbrev</function>.
3936 </para>
3937 </tip>
3938 </sect2>
3939
3940 <sect2 id="datatype-macaddr">
3941 <title><type>macaddr</type></title>
3942
3943 <indexterm>
3944 <primary>macaddr (data type)</primary>
3945 </indexterm>
3946
3947 <indexterm>
3948 <primary>MAC address</primary>
3949 <see>macaddr</see>
3950 </indexterm>
3951
3952 <para>
3953 The <type>macaddr</type> type stores MAC addresses, known for example
3954 from Ethernet card hardware addresses (although MAC addresses are
3955 used for other purposes as well). Input is accepted in the
3956 following formats:
3957
3958 <simplelist>
3959 <member><literal>'08:00:2b:01:02:03'</literal></member>
3960 <member><literal>'08-00-2b-01-02-03'</literal></member>
3961 <member><literal>'08002b:010203'</literal></member>
3962 <member><literal>'08002b-010203'</literal></member>
3963 <member><literal>'0800.2b01.0203'</literal></member>
3964 <member><literal>'0800-2b01-0203'</literal></member>
3965 <member><literal>'08002b010203'</literal></member>
3966 </simplelist>
3967
3968 These examples all specify the same address. Upper and
3969 lower case is accepted for the digits
3970 <literal>a</literal> through <literal>f</literal>. Output is always in the
3971 first of the forms shown.
3972 </para>
3973
3974 <para>
3975 IEEE Standard 802-2001 specifies the second form shown (with hyphens)
3976 as the canonical form for MAC addresses, and specifies the first
3977 form (with colons) as used with bit-reversed, MSB-first notation, so that
3978 08-00-2b-01-02-03 = 10:00:D4:80:40:C0. This convention is widely
3979 ignored nowadays, and it is relevant only for obsolete network
3980 protocols (such as Token Ring). PostgreSQL makes no provisions
3981 for bit reversal; all accepted formats use the canonical LSB
3982 order.
3983 </para>
3984
3985 <para>
3986 The remaining five input formats are not part of any standard.
3987 </para>
3988 </sect2>
3989
3990 <sect2 id="datatype-macaddr8">
3991 <title><type>macaddr8</type></title>
3992
3993 <indexterm>
3994 <primary>macaddr8 (data type)</primary>
3995 </indexterm>
3996
3997 <indexterm>
3998 <primary>MAC address (EUI-64 format)</primary>
3999 <see>macaddr</see>
4000 </indexterm>
4001
4002 <para>
4003 The <type>macaddr8</type> type stores MAC addresses in EUI-64
4004 format, known for example from Ethernet card hardware addresses
4005 (although MAC addresses are used for other purposes as well).
4006 This type can accept both 6 and 8 byte length MAC addresses
4007 and stores them in 8 byte length format. MAC addresses given
4008 in 6 byte format will be stored in 8 byte length format with the
4009 4th and 5th bytes set to FF and FE, respectively.
4010
4011 Note that IPv6 uses a modified EUI-64 format where the 7th bit
4012 should be set to one after the conversion from EUI-48. The
4013 function <function>macaddr8_set7bit</function> is provided to make this
4014 change.
4015
4016 Generally speaking, any input which is comprised of pairs of hex
4017 digits (on byte boundaries), optionally separated consistently by
4018 one of <literal>':'</literal>, <literal>'-'</literal> or <literal>'.'</literal>, is
4019 accepted. The number of hex digits must be either 16 (8 bytes) or
4020 12 (6 bytes). Leading and trailing whitespace is ignored.
4021
4022 The following are examples of input formats that are accepted:
4023
4024 <simplelist>
4025 <member><literal>'08:00:2b:01:02:03:04:05'</literal></member>
4026 <member><literal>'08-00-2b-01-02-03-04-05'</literal></member>
4027 <member><literal>'08002b:0102030405'</literal></member>
4028 <member><literal>'08002b-0102030405'</literal></member>
4029 <member><literal>'0800.2b01.0203.0405'</literal></member>
4030 <member><literal>'0800-2b01-0203-0405'</literal></member>
4031 <member><literal>'08002b01:02030405'</literal></member>
4032 <member><literal>'08002b0102030405'</literal></member>
4033 </simplelist>
4034
4035 These examples all specify the same address. Upper and
4036 lower case is accepted for the digits
4037 <literal>a</literal> through <literal>f</literal>. Output is always in the
4038 first of the forms shown.
4039 </para>
4040
4041 <para>
4042 The last six input formats shown above are not part of any standard.
4043 </para>
4044
4045 <para>
4046 To convert a traditional 48 bit MAC address in EUI-48 format to
4047 modified EUI-64 format to be included as the host portion of an
4048 IPv6 address, use <function>macaddr8_set7bit</function> as shown:
4049
4050 <programlisting>
4051 SELECT macaddr8_set7bit('08:00:2b:01:02:03');
4052 <computeroutput>
4053 macaddr8_set7bit
4054 -------------------------
4055 0a:00:2b:ff:fe:01:02:03
4056 (1 row)
4057 </computeroutput>
4058 </programlisting>
4059
4060 </para>
4061
4062 </sect2>
4063
4064 </sect1>
4065
4066 <sect1 id="datatype-bit">
4067 <title>Bit String Types</title>
4068
4069 <indexterm zone="datatype-bit">
4070 <primary>bit string</primary>
4071 <secondary>data type</secondary>
4072 </indexterm>
4073
4074 <para>
4075 Bit strings are strings of 1's and 0's. They can be used to store
4076 or visualize bit masks. There are two SQL bit types:
4077 <type>bit(<replaceable>n</replaceable>)</type> and <type>bit
4078 varying(<replaceable>n</replaceable>)</type>, where
4079 <replaceable>n</replaceable> is a positive integer.
4080 </para>
4081
4082 <para>
4083 <type>bit</type> type data must match the length
4084 <replaceable>n</replaceable> exactly; it is an error to attempt to
4085 store shorter or longer bit strings. <type>bit varying</type> data is
4086 of variable length up to the maximum length
4087 <replaceable>n</replaceable>; longer strings will be rejected.
4088 Writing <type>bit</type> without a length is equivalent to
4089 <literal>bit(1)</literal>, while <type>bit varying</type> without a length
4090 specification means unlimited length.
4091 </para>
4092
4093 <note>
4094 <para>
4095 If one explicitly casts a bit-string value to
4096 <type>bit(<replaceable>n</replaceable>)</type>, it will be truncated or
4097 zero-padded on the right to be exactly <replaceable>n</replaceable> bits,
4098 without raising an error. Similarly,
4099 if one explicitly casts a bit-string value to
4100 <type>bit varying(<replaceable>n</replaceable>)</type>, it will be truncated
4101 on the right if it is more than <replaceable>n</replaceable> bits.
4102 </para>
4103 </note>
4104
4105 <para>
4106 Refer to <xref
4107 linkend="sql-syntax-bit-strings"/> for information about the syntax
4108 of bit string constants. Bit-logical operators and string
4109 manipulation functions are available; see <xref
4110 linkend="functions-bitstring"/>.
4111 </para>
4112
4113 <example>
4114 <title>Using the Bit String Types</title>
4115
4116 <programlisting>
4117 CREATE TABLE test (a BIT(3), b BIT VARYING(5));
4118 INSERT INTO test VALUES (B'101', B'00');
4119 INSERT INTO test VALUES (B'10', B'101');
4120 <computeroutput>
4121 ERROR: bit string length 2 does not match type bit(3)
4122 </computeroutput>
4123 INSERT INTO test VALUES (B'10'::bit(3), B'101');
4124 SELECT * FROM test;
4125 <computeroutput>
4126 a | b
4127 -----+-----
4128 101 | 00
4129 100 | 101
4130 </computeroutput>
4131 </programlisting>
4132 </example>
4133
4134 <para>
4135 A bit string value requires 1 byte for each group of 8 bits, plus
4136 5 or 8 bytes overhead depending on the length of the string
4137 (but long values may be compressed or moved out-of-line, as explained
4138 in <xref linkend="datatype-character"/> for character strings).
4139 </para>
4140 </sect1>
4141
4142 <sect1 id="datatype-textsearch">
4143 <title>Text Search Types</title>
4144
4145 <indexterm zone="datatype-textsearch">
4146 <primary>full text search</primary>
4147 <secondary>data types</secondary>
4148 </indexterm>
4149
4150 <indexterm zone="datatype-textsearch">
4151 <primary>text search</primary>
4152 <secondary>data types</secondary>
4153 </indexterm>
4154
4155 <para>
4156 <productname>PostgreSQL</productname> provides two data types that
4157 are designed to support full text search, which is the activity of
4158 searching through a collection of natural-language <firstterm>documents</firstterm>
4159 to locate those that best match a <firstterm>query</firstterm>.
4160 The <type>tsvector</type> type represents a document in a form optimized
4161 for text search; the <type>tsquery</type> type similarly represents
4162 a text query.
4163 <xref linkend="textsearch"/> provides a detailed explanation of this
4164 facility, and <xref linkend="functions-textsearch"/> summarizes the
4165 related functions and operators.
4166 </para>
4167
4168 <sect2 id="datatype-tsvector">
4169 <title><type>tsvector</type></title>
4170
4171 <indexterm>
4172 <primary>tsvector (data type)</primary>
4173 </indexterm>
4174
4175 <para>
4176 A <type>tsvector</type> value is a sorted list of distinct
4177 <firstterm>lexemes</firstterm>, which are words that have been
4178 <firstterm>normalized</firstterm> to merge different variants of the same word
4179 (see <xref linkend="textsearch"/> for details). Sorting and
4180 duplicate-elimination are done automatically during input, as shown in
4181 this example:
4182
4183 <programlisting>
4184 SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector;
4185 tsvector
4186 ----------------------------------------------------
4187 'a' 'and' 'ate' 'cat' 'fat' 'mat' 'on' 'rat' 'sat'
4188 </programlisting>
4189
4190 To represent
4191 lexemes containing whitespace or punctuation, surround them with quotes:
4192
4193 <programlisting>
4194 SELECT $$the lexeme ' ' contains spaces$$::tsvector;
4195 tsvector
4196 -------------------------------------------
4197 ' ' 'contains' 'lexeme' 'spaces' 'the'
4198 </programlisting>
4199
4200 (We use dollar-quoted string literals in this example and the next one
4201 to avoid the confusion of having to double quote marks within the
4202 literals.) Embedded quotes and backslashes must be doubled:
4203
4204 <programlisting>
4205 SELECT $$the lexeme 'Joe''s' contains a quote$$::tsvector;
4206 tsvector
4207 ------------------------------------------------
4208 'Joe''s' 'a' 'contains' 'lexeme' 'quote' 'the'
4209 </programlisting>
4210
4211 Optionally, integer <firstterm>positions</firstterm>
4212 can be attached to lexemes:
4213
4214 <programlisting>
4215 SELECT 'a:1 fat:2 cat:3 sat:4 on:5 a:6 mat:7 and:8 ate:9 a:10 fat:11 rat:12'::tsvector;
4216 tsvector
4217 -------------------------------------------------------------------&zwsp;------------
4218 'a':1,6,10 'and':8 'ate':9 'cat':3 'fat':2,11 'mat':7 'on':5 'rat':12 'sat':4
4219 </programlisting>
4220
4221 A position normally indicates the source word's location in the
4222 document. Positional information can be used for
4223 <firstterm>proximity ranking</firstterm>. Position values can
4224 range from 1 to 16383; larger numbers are silently set to 16383.
4225 Duplicate positions for the same lexeme are discarded.
4226 </para>
4227
4228 <para>
4229 Lexemes that have positions can further be labeled with a
4230 <firstterm>weight</firstterm>, which can be <literal>A</literal>,
4231 <literal>B</literal>, <literal>C</literal>, or <literal>D</literal>.
4232 <literal>D</literal> is the default and hence is not shown on output:
4233
4234 <programlisting>
4235 SELECT 'a:1A fat:2B,4C cat:5D'::tsvector;
4236 tsvector
4237 ----------------------------
4238 'a':1A 'cat':5 'fat':2B,4C
4239 </programlisting>
4240
4241 Weights are typically used to reflect document structure, for example
4242 by marking title words differently from body words. Text search
4243 ranking functions can assign different priorities to the different
4244 weight markers.
4245 </para>
4246
4247 <para>
4248 It is important to understand that the
4249 <type>tsvector</type> type itself does not perform any word
4250 normalization; it assumes the words it is given are normalized
4251 appropriately for the application. For example,
4252
4253 <programlisting>
4254 SELECT 'The Fat Rats'::tsvector;
4255 tsvector
4256 --------------------
4257 'Fat' 'Rats' 'The'
4258 </programlisting>
4259
4260 For most English-text-searching applications the above words would
4261 be considered non-normalized, but <type>tsvector</type> doesn't care.
4262 Raw document text should usually be passed through
4263 <function>to_tsvector</function> to normalize the words appropriately
4264 for searching:
4265
4266 <programlisting>
4267 SELECT to_tsvector('english', 'The Fat Rats');
4268 to_tsvector
4269 -----------------
4270 'fat':2 'rat':3
4271 </programlisting>
4272
4273 Again, see <xref linkend="textsearch"/> for more detail.
4274 </para>
4275
4276 </sect2>
4277
4278 <sect2 id="datatype-tsquery">
4279 <title><type>tsquery</type></title>
4280
4281 <indexterm>
4282 <primary>tsquery (data type)</primary>
4283 </indexterm>
4284
4285 <para>
4286 A <type>tsquery</type> value stores lexemes that are to be
4287 searched for, and can combine them using the Boolean operators
4288 <literal>&amp;</literal> (AND), <literal>|</literal> (OR), and
4289 <literal>!</literal> (NOT), as well as the phrase search operator
4290 <literal>&lt;-&gt;</literal> (FOLLOWED BY). There is also a variant
4291 <literal>&lt;<replaceable>N</replaceable>&gt;</literal> of the FOLLOWED BY
4292 operator, where <replaceable>N</replaceable> is an integer constant that
4293 specifies the distance between the two lexemes being searched
4294 for. <literal>&lt;-&gt;</literal> is equivalent to <literal>&lt;1&gt;</literal>.
4295 </para>
4296
4297 <para>
4298 Parentheses can be used to enforce grouping of these operators.
4299 In the absence of parentheses, <literal>!</literal> (NOT) binds most tightly,
4300 <literal>&lt;-&gt;</literal> (FOLLOWED BY) next most tightly, then
4301 <literal>&amp;</literal> (AND), with <literal>|</literal> (OR) binding
4302 the least tightly.
4303 </para>
4304
4305 <para>
4306 Here are some examples:
4307
4308 <programlisting>
4309 SELECT 'fat &amp; rat'::tsquery;
4310 tsquery
4311 ---------------
4312 'fat' &amp; 'rat'
4313
4314 SELECT 'fat &amp; (rat | cat)'::tsquery;
4315 tsquery
4316 ---------------------------
4317 'fat' &amp; ( 'rat' | 'cat' )
4318
4319 SELECT 'fat &amp; rat &amp; ! cat'::tsquery;
4320 tsquery
4321 ------------------------
4322 'fat' &amp; 'rat' &amp; !'cat'
4323 </programlisting>
4324 </para>
4325
4326 <para>
4327 Optionally, lexemes in a <type>tsquery</type> can be labeled with
4328 one or more weight letters, which restricts them to match only
4329 <type>tsvector</type> lexemes with one of those weights:
4330
4331 <programlisting>
4332 SELECT 'fat:ab &amp; cat'::tsquery;
4333 tsquery
4334 ------------------
4335 'fat':AB &amp; 'cat'
4336 </programlisting>
4337 </para>
4338
4339 <para>
4340 Also, lexemes in a <type>tsquery</type> can be labeled with <literal>*</literal>
4341 to specify prefix matching:
4342 <programlisting>
4343 SELECT 'super:*'::tsquery;
4344 tsquery
4345 -----------
4346 'super':*
4347 </programlisting>
4348 This query will match any word in a <type>tsvector</type> that begins
4349 with <quote>super</quote>.
4350 </para>
4351
4352 <para>
4353 Quoting rules for lexemes are the same as described previously for
4354 lexemes in <type>tsvector</type>; and, as with <type>tsvector</type>,
4355 any required normalization of words must be done before converting
4356 to the <type>tsquery</type> type. The <function>to_tsquery</function>
4357 function is convenient for performing such normalization:
4358
4359 <programlisting>
4360 SELECT to_tsquery('Fat:ab &amp; Cats');
4361 to_tsquery
4362 ------------------
4363 'fat':AB &amp; 'cat'
4364 </programlisting>
4365
4366 Note that <function>to_tsquery</function> will process prefixes in the same way
4367 as other words, which means this comparison returns true:
4368
4369 <programlisting>
4370 SELECT to_tsvector( 'postgraduate' ) @@ to_tsquery( 'postgres:*' );
4371 ?column?
4372 ----------
4373 t
4374 </programlisting>
4375 because <literal>postgres</literal> gets stemmed to <literal>postgr</literal>:
4376 <programlisting>
4377 SELECT to_tsvector( 'postgraduate' ), to_tsquery( 'postgres:*' );
4378 to_tsvector | to_tsquery
4379 ---------------+------------
4380 'postgradu':1 | 'postgr':*
4381 </programlisting>
4382 which will match the stemmed form of <literal>postgraduate</literal>.
4383 </para>
4384
4385 </sect2>
4386
4387 </sect1>
4388
4389 <sect1 id="datatype-uuid">
4390 <title><acronym>UUID</acronym> Type</title>
4391
4392 <indexterm zone="datatype-uuid">
4393 <primary>UUID</primary>
4394 </indexterm>
4395
4396 <para>
4397 The data type <type>uuid</type> stores Universally Unique Identifiers
4398 (UUID) as defined by <ulink url="https://datatracker.ietf.org/doc/html/rfc9562">RFC 9562</ulink>,
4399 ISO/IEC 9834-8:2005, and related standards.
4400 (Some systems refer to this data type as a globally unique identifier, or
4401 GUID,<indexterm><primary>GUID</primary></indexterm> instead.) This
4402 identifier is a 128-bit quantity that is generated by an algorithm chosen
4403 to make it very unlikely that the same identifier will be generated by
4404 anyone else in the known universe using the same algorithm. Therefore,
4405 for distributed systems, these identifiers provide a better uniqueness
4406 guarantee than sequence generators, which
4407 are only unique within a single database.
4408 </para>
4409
4410 <para>
4411 RFC 9562 defines 8 different UUID versions. Each version has specific requirements
4412 for generating new UUID values, and each version provides distinct benefits and drawbacks.
4413 <productname>PostgreSQL</productname> provides native support for generating UUIDs
4414 using the UUIDv4 and UUIDv7 algorithms. Alternatively, UUID values can be generated
4415 outside of the database using any algorithm. The data type <type>uuid</type> can be used
4416 to store any UUID, regardless of the origin and the UUID version.
4417 </para>
4418
4419 <para>
4420 A UUID is written as a sequence of lower-case hexadecimal digits,
4421 in several groups separated by hyphens, specifically a group of 8
4422 digits followed by three groups of 4 digits followed by a group of
4423 12 digits, for a total of 32 digits representing the 128 bits. An
4424 example of a UUID in this standard form is:
4425 <programlisting>
4426 a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11
4427 </programlisting>
4428 <productname>PostgreSQL</productname> also accepts the following
4429 alternative forms for input:
4430 use of upper-case digits, the standard format surrounded by
4431 braces, omitting some or all hyphens, adding a hyphen after any
4432 group of four digits. Examples are:
4433 <programlisting>
4434 A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11
4435 {a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11}
4436 a0eebc999c0b4ef8bb6d6bb9bd380a11
4437 a0ee-bc99-9c0b-4ef8-bb6d-6bb9-bd38-0a11
4438 {a0eebc99-9c0b4ef8-bb6d6bb9-bd380a11}
4439 </programlisting>
4440 Output is always in the standard form.
4441 </para>
4442
4443 <para>
4444 See <xref linkend="functions-uuid"/> for how to generate a UUID in
4445 <productname>PostgreSQL</productname>.
4446 </para>
4447 </sect1>
4448
4449 <sect1 id="datatype-xml">
4450 <title><acronym>XML</acronym> Type</title>
4451
4452 <indexterm zone="datatype-xml">
4453 <primary>XML</primary>
4454 </indexterm>
4455
4456 <para>
4457 The <type>xml</type> data type can be used to store XML data. Its
4458 advantage over storing XML data in a <type>text</type> field is that it
4459 checks the input values for well-formedness, and there are support
4460 functions to perform type-safe operations on it; see <xref
4461 linkend="functions-xml"/>. Use of this data type requires the
4462 installation to have been built with <command>configure
4463 --with-libxml</command>.
4464 </para>
4465
4466 <para>
4467 The <type>xml</type> type can store well-formed
4468 <quote>documents</quote>, as defined by the XML standard, as well
4469 as <quote>content</quote> fragments, which are defined by reference
4470 to the more permissive
4471 <ulink url="https://www.w3.org/TR/2010/REC-xpath-datamodel-20101214/#DocumentNode"><quote>document node</quote></ulink>
4472 of the XQuery and XPath data model.
4473 Roughly, this means that content fragments can have
4474 more than one top-level element or character node. The expression
4475 <literal><replaceable>xmlvalue</replaceable> IS DOCUMENT</literal>
4476 can be used to evaluate whether a particular <type>xml</type>
4477 value is a full document or only a content fragment.
4478 </para>
4479
4480 <para>
4481 Limits and compatibility notes for the <type>xml</type> data type
4482 can be found in <xref linkend="xml-limits-conformance"/>.
4483 </para>
4484
4485 <sect2 id="datatype-xml-creating">
4486 <title>Creating XML Values</title>
4487 <para>
4488 To produce a value of type <type>xml</type> from character data,
4489 use the function
4490 <function>xmlparse</function>:<indexterm><primary>xmlparse</primary></indexterm>
4491 <synopsis>
4492 XMLPARSE ( { DOCUMENT | CONTENT } <replaceable>value</replaceable>)
4493 </synopsis>
4494 Examples:
4495 <programlisting><![CDATA[
4496 XMLPARSE (DOCUMENT '<?xml version="1.0"?><book><title>Manual</title><chapter>...</chapter></book>')
4497 XMLPARSE (CONTENT 'abc<foo>bar</foo><bar>foo</bar>')
4498 ]]></programlisting>
4499 While this is the only way to convert character strings into XML
4500 values according to the SQL standard, the PostgreSQL-specific
4501 syntaxes:
4502 <programlisting><![CDATA[
4503 xml '<foo>bar</foo>'
4504 '<foo>bar</foo>'::xml
4505 ]]></programlisting>
4506 can also be used.
4507 </para>
4508
4509 <para>
4510 The <type>xml</type> type does not validate input values
4511 against a document type declaration
4512 (DTD),<indexterm><primary>DTD</primary></indexterm>
4513 even when the input value specifies a DTD.
4514 There is also currently no built-in support for validating against
4515 other XML schema languages such as XML Schema.
4516 </para>
4517
4518 <para>
4519 The inverse operation, producing a character string value from
4520 <type>xml</type>, uses the function
4521 <function>xmlserialize</function>:<indexterm><primary>xmlserialize</primary></indexterm>
4522 <synopsis>
4523 XMLSERIALIZE ( { DOCUMENT | CONTENT } <replaceable>value</replaceable> AS <replaceable>type</replaceable> [ [ NO ] INDENT ] )
4524 </synopsis>
4525 <replaceable>type</replaceable> can be
4526 <type>character</type>, <type>character varying</type>, or
4527 <type>text</type> (or an alias for one of those). Again, according
4528 to the SQL standard, this is the only way to convert between type
4529 <type>xml</type> and character types, but PostgreSQL also allows
4530 you to simply cast the value.
4531 </para>
4532
4533 <para>
4534 The <literal>INDENT</literal> option causes the result to be
4535 pretty-printed, while <literal>NO INDENT</literal> (which is the
4536 default) just emits the original input string. Casting to a character
4537 type likewise produces the original string.
4538 </para>
4539
4540 <para>
4541 When a character string value is cast to or from type
4542 <type>xml</type> without going through <type>XMLPARSE</type> or
4543 <type>XMLSERIALIZE</type>, respectively, the choice of
4544 <literal>DOCUMENT</literal> versus <literal>CONTENT</literal> is
4545 determined by the <quote>XML option</quote>
4546 <indexterm><primary>XML option</primary></indexterm>
4547 session configuration parameter, which can be set using the
4548 standard command:
4549 <synopsis>
4550 SET XML OPTION { DOCUMENT | CONTENT };
4551 </synopsis>
4552 or the more PostgreSQL-like syntax
4553 <synopsis>
4554 SET xmloption TO { DOCUMENT | CONTENT };
4555 </synopsis>
4556 The default is <literal>CONTENT</literal>, so all forms of XML
4557 data are allowed.
4558 </para>
4559
4560 </sect2>
4561
4562 <sect2 id="datatype-xml-encoding-handling">
4563 <title>Encoding Handling</title>
4564 <para>
4565 Care must be taken when dealing with multiple character encodings
4566 on the client, server, and in the XML data passed through them.
4567 When using the text mode to pass queries to the server and query
4568 results to the client (which is the normal mode), PostgreSQL
4569 converts all character data passed between the client and the
4570 server and vice versa to the character encoding of the respective
4571 end; see <xref linkend="multibyte"/>. This includes string
4572 representations of XML values, such as in the above examples.
4573 This would ordinarily mean that encoding declarations contained in
4574 XML data can become invalid as the character data is converted
4575 to other encodings while traveling between client and server,
4576 because the embedded encoding declaration is not changed. To cope
4577 with this behavior, encoding declarations contained in
4578 character strings presented for input to the <type>xml</type> type
4579 are <emphasis>ignored</emphasis>, and content is assumed
4580 to be in the current server encoding. Consequently, for correct
4581 processing, character strings of XML data must be sent
4582 from the client in the current client encoding. It is the
4583 responsibility of the client to either convert documents to the
4584 current client encoding before sending them to the server, or to
4585 adjust the client encoding appropriately. On output, values of
4586 type <type>xml</type> will not have an encoding declaration, and
4587 clients should assume all data is in the current client
4588 encoding.
4589 </para>
4590
4591 <para>
4592 When using binary mode to pass query parameters to the server
4593 and query results back to the client, no encoding conversion
4594 is performed, so the situation is different. In this case, an
4595 encoding declaration in the XML data will be observed, and if it
4596 is absent, the data will be assumed to be in UTF-8 (as required by
4597 the XML standard; note that PostgreSQL does not support UTF-16).
4598 On output, data will have an encoding declaration
4599 specifying the client encoding, unless the client encoding is
4600 UTF-8, in which case it will be omitted.
4601 </para>
4602
4603 <para>
4604 Needless to say, processing XML data with PostgreSQL will be less
4605 error-prone and more efficient if the XML data encoding, client encoding,
4606 and server encoding are the same. Since XML data is internally
4607 processed in UTF-8, computations will be most efficient if the
4608 server encoding is also UTF-8.
4609 </para>
4610
4611 <caution>
4612 <para>
4613 Some XML-related functions may not work at all on non-ASCII data
4614 when the server encoding is not UTF-8. This is known to be an
4615 issue for <function>xmltable()</function> and <function>xpath()</function> in particular.
4616 </para>
4617 </caution>
4618 </sect2>
4619
4620 <sect2 id="datatype-xml-accessing-xml-values">
4621 <title>Accessing XML Values</title>
4622
4623 <para>
4624 The <type>xml</type> data type is unusual in that it does not
4625 provide any comparison operators. This is because there is no
4626 well-defined and universally useful comparison algorithm for XML
4627 data. One consequence of this is that you cannot retrieve rows by
4628 comparing an <type>xml</type> column against a search value. XML
4629 values should therefore typically be accompanied by a separate key
4630 field such as an ID. An alternative solution for comparing XML
4631 values is to convert them to character strings first, but note
4632 that character string comparison has little to do with a useful
4633 XML comparison method.
4634 </para>
4635
4636 <para>
4637 Since there are no comparison operators for the <type>xml</type>
4638 data type, it is not possible to create an index directly on a
4639 column of this type. If speedy searches in XML data are desired,
4640 possible workarounds include casting the expression to a
4641 character string type and indexing that, or indexing an XPath
4642 expression. Of course, the actual query would have to be adjusted
4643 to search by the indexed expression.
4644 </para>
4645
4646 <para>
4647 The text-search functionality in PostgreSQL can also be used to speed
4648 up full-document searches of XML data. The necessary
4649 preprocessing support is, however, not yet available in the PostgreSQL
4650 distribution.
4651 </para>
4652 </sect2>
4653 </sect1>
4654
4655 &json;
4656
4657 &array;
4658
4659 &rowtypes;
4660
4661 &rangetypes;
4662
4663 <sect1 id="domains">
4664 <title>Domain Types</title>
4665
4666 <indexterm zone="domains">
4667 <primary>domain</primary>
4668 </indexterm>
4669
4670 <indexterm zone="domains">
4671 <primary>data type</primary>
4672 <secondary>domain</secondary>
4673 </indexterm>
4674
4675 <para>
4676 A <firstterm>domain</firstterm> is a user-defined data type that is
4677 based on another <firstterm>underlying type</firstterm>. Optionally,
4678 it can have constraints that restrict its valid values to a subset of
4679 what the underlying type would allow. Otherwise it behaves like the
4680 underlying type &mdash; for example, any operator or function that
4681 can be applied to the underlying type will work on the domain type.
4682 The underlying type can be any built-in or user-defined base type,
4683 enum type, array type, composite type, range type, or another domain.
4684 </para>
4685
4686 <para>
4687 For example, we could create a domain over integers that accepts only
4688 positive integers:
4689 <programlisting>
4690 CREATE DOMAIN posint AS integer CHECK (VALUE &gt; 0);
4691 CREATE TABLE mytable (id posint);
4692 INSERT INTO mytable VALUES(1); -- works
4693 INSERT INTO mytable VALUES(-1); -- fails
4694 </programlisting>
4695 </para>
4696
4697 <para>
4698 When an operator or function of the underlying type is applied to a
4699 domain value, the domain is automatically down-cast to the underlying
4700 type. Thus, for example, the result of <literal>mytable.id - 1</literal>
4701 is considered to be of type <type>integer</type> not <type>posint</type>.
4702 We could write <literal>(mytable.id - 1)::posint</literal> to cast the
4703 result back to <type>posint</type>, causing the domain's constraints
4704 to be rechecked. In this case, that would result in an error if the
4705 expression had been applied to an <structfield>id</structfield> value of
4706 1. Assigning a value of the underlying type to a field or variable of
4707 the domain type is allowed without writing an explicit cast, but the
4708 domain's constraints will be checked.
4709 </para>
4710
4711 <para>
4712 For additional information see <xref linkend="sql-createdomain"/>.
4713 </para>
4714 </sect1>
4715
4716 <sect1 id="datatype-oid">
4717 <title>Object Identifier Types</title>
4718
4719 <indexterm zone="datatype-oid">
4720 <primary>object identifier</primary>
4721 <secondary>data type</secondary>
4722 </indexterm>
4723
4724 <indexterm zone="datatype-oid">
4725 <primary>oid</primary>
4726 </indexterm>
4727
4728 <indexterm zone="datatype-oid">
4729 <primary>regclass</primary>
4730 </indexterm>
4731
4732 <indexterm zone="datatype-oid">
4733 <primary>regcollation</primary>
4734 </indexterm>
4735
4736 <indexterm zone="datatype-oid">
4737 <primary>regconfig</primary>
4738 </indexterm>
4739
4740 <indexterm zone="datatype-oid">
4741 <primary>regdictionary</primary>
4742 </indexterm>
4743
4744 <indexterm zone="datatype-oid">
4745 <primary>regnamespace</primary>
4746 </indexterm>
4747
4748 <indexterm zone="datatype-oid">
4749 <primary>regoper</primary>
4750 </indexterm>
4751
4752 <indexterm zone="datatype-oid">
4753 <primary>regoperator</primary>
4754 </indexterm>
4755
4756 <indexterm zone="datatype-oid">
4757 <primary>regproc</primary>
4758 </indexterm>
4759
4760 <indexterm zone="datatype-oid">
4761 <primary>regprocedure</primary>
4762 </indexterm>
4763
4764 <indexterm zone="datatype-oid">
4765 <primary>regrole</primary>
4766 </indexterm>
4767
4768 <indexterm zone="datatype-oid">
4769 <primary>regtype</primary>
4770 </indexterm>
4771
4772 <indexterm zone="datatype-oid">
4773 <primary>xid8</primary>
4774 </indexterm>
4775
4776 <indexterm zone="datatype-oid">
4777 <primary>cid</primary>
4778 </indexterm>
4779
4780 <indexterm zone="datatype-oid">
4781 <primary>tid</primary>
4782 </indexterm>
4783
4784 <indexterm zone="datatype-oid">
4785 <primary>xid</primary>
4786 </indexterm>
4787
4788 <para>
4789 Object identifiers (OIDs) are used internally by
4790 <productname>PostgreSQL</productname> as primary keys for various
4791 system tables.
4792 Type <type>oid</type> represents an object identifier. There are also
4793 several alias types for <type>oid</type>, each
4794 named <type>reg<replaceable>something</replaceable></type>.
4795 <xref linkend="datatype-oid-table"/> shows an
4796 overview.
4797 </para>
4798
4799 <para>
4800 The <type>oid</type> type is currently implemented as an unsigned
4801 four-byte integer. Therefore, it is not large enough to provide
4802 database-wide uniqueness in large databases, or even in large
4803 individual tables.
4804 </para>
4805
4806 <para>
4807 The <type>oid</type> type itself has few operations beyond comparison.
4808 It can be cast to integer, however, and then manipulated using the
4809 standard integer operators. (Beware of possible
4810 signed-versus-unsigned confusion if you do this.)
4811 </para>
4812
4813 <para>
4814 The OID alias types have no operations of their own except
4815 for specialized input and output routines. These routines are able
4816 to accept and display symbolic names for system objects, rather than
4817 the raw numeric value that type <type>oid</type> would use. The alias
4818 types allow simplified lookup of OID values for objects. For example,
4819 to examine the <structname>pg_attribute</structname> rows related to a table
4820 <literal>mytable</literal>, one could write:
4821 <programlisting>
4822 SELECT * FROM pg_attribute WHERE attrelid = 'mytable'::regclass;
4823 </programlisting>
4824 rather than:
4825 <programlisting>
4826 SELECT * FROM pg_attribute
4827 WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'mytable');
4828 </programlisting>
4829 While that doesn't look all that bad by itself, it's still oversimplified.
4830 A far more complicated sub-select would be needed to
4831 select the right OID if there are multiple tables named
4832 <literal>mytable</literal> in different schemas.
4833 The <type>regclass</type> input converter handles the table lookup according
4834 to the schema path setting, and so it does the <quote>right thing</quote>
4835 automatically. Similarly, casting a table's OID to
4836 <type>regclass</type> is handy for symbolic display of a numeric OID.
4837 </para>
4838
4839 <table id="datatype-oid-table">
4840 <title>Object Identifier Types</title>
4841 <tgroup cols="4">
4842 <thead>
4843 <row>
4844 <entry>Name</entry>
4845 <entry>References</entry>
4846 <entry>Description</entry>
4847 <entry>Value Example</entry>
4848 </row>
4849 </thead>
4850
4851 <tbody>
4852
4853 <row>
4854 <entry><type>oid</type></entry>
4855 <entry>any</entry>
4856 <entry>numeric object identifier</entry>
4857 <entry><literal>564182</literal></entry>
4858 </row>
4859
4860 <row>
4861 <entry><type>regclass</type></entry>
4862 <entry><structname>pg_class</structname></entry>
4863 <entry>relation name</entry>
4864 <entry><literal>pg_type</literal></entry>
4865 </row>
4866
4867 <row>
4868 <entry><type>regcollation</type></entry>
4869 <entry><structname>pg_collation</structname></entry>
4870 <entry>collation name</entry>
4871 <entry><literal>"POSIX"</literal></entry>
4872 </row>
4873
4874 <row>
4875 <entry><type>regconfig</type></entry>
4876 <entry><structname>pg_ts_config</structname></entry>
4877 <entry>text search configuration</entry>
4878 <entry><literal>english</literal></entry>
4879 </row>
4880
4881 <row>
4882 <entry><type>regdictionary</type></entry>
4883 <entry><structname>pg_ts_dict</structname></entry>
4884 <entry>text search dictionary</entry>
4885 <entry><literal>simple</literal></entry>
4886 </row>
4887
4888 <row>
4889 <entry><type>regnamespace</type></entry>
4890 <entry><structname>pg_namespace</structname></entry>
4891 <entry>namespace name</entry>
4892 <entry><literal>pg_catalog</literal></entry>
4893 </row>
4894
4895 <row>
4896 <entry><type>regoper</type></entry>
4897 <entry><structname>pg_operator</structname></entry>
4898 <entry>operator name</entry>
4899 <entry><literal>+</literal></entry>
4900 </row>
4901
4902 <row>
4903 <entry><type>regoperator</type></entry>
4904 <entry><structname>pg_operator</structname></entry>
4905 <entry>operator with argument types</entry>
4906 <entry><literal>*(integer,&zwsp;integer)</literal>
4907 or <literal>-(NONE,&zwsp;integer)</literal></entry>
4908 </row>
4909
4910 <row>
4911 <entry><type>regproc</type></entry>
4912 <entry><structname>pg_proc</structname></entry>
4913 <entry>function name</entry>
4914 <entry><literal>sum</literal></entry>
4915 </row>
4916
4917 <row>
4918 <entry><type>regprocedure</type></entry>
4919 <entry><structname>pg_proc</structname></entry>
4920 <entry>function with argument types</entry>
4921 <entry><literal>sum(int4)</literal></entry>
4922 </row>
4923
4924 <row>
4925 <entry><type>regrole</type></entry>
4926 <entry><structname>pg_authid</structname></entry>
4927 <entry>role name</entry>
4928 <entry><literal>smithee</literal></entry>
4929 </row>
4930
4931 <row>
4932 <entry><type>regtype</type></entry>
4933 <entry><structname>pg_type</structname></entry>
4934 <entry>data type name</entry>
4935 <entry><literal>integer</literal></entry>
4936 </row>
4937 </tbody>
4938 </tgroup>
4939 </table>
4940
4941 <para>
4942 All of the OID alias types for objects that are grouped by namespace
4943 accept schema-qualified names, and will
4944 display schema-qualified names on output if the object would not
4945 be found in the current search path without being qualified.
4946 For example, <literal>myschema.mytable</literal> is acceptable input
4947 for <type>regclass</type> (if there is such a table). That value
4948 might be output as <literal>myschema.mytable</literal>, or
4949 just <literal>mytable</literal>, depending on the current search path.
4950 The <type>regproc</type> and <type>regoper</type> alias types will only
4951 accept input names that are unique (not overloaded), so they are
4952 of limited use; for most uses <type>regprocedure</type> or
4953 <type>regoperator</type> are more appropriate. For <type>regoperator</type>,
4954 unary operators are identified by writing <literal>NONE</literal> for the unused
4955 operand.
4956 </para>
4957
4958 <para>
4959 The input functions for these types allow whitespace between tokens,
4960 and will fold upper-case letters to lower case, except within double
4961 quotes; this is done to make the syntax rules similar to the way
4962 object names are written in SQL. Conversely, the output functions
4963 will use double quotes if needed to make the output be a valid SQL
4964 identifier. For example, the OID of a function
4965 named <literal>Foo</literal> (with upper case <literal>F</literal>)
4966 taking two integer arguments could be entered as
4967 <literal>' "Foo" ( int, integer ) '::regprocedure</literal>. The
4968 output would look like <literal>"Foo"(integer,integer)</literal>.
4969 Both the function name and the argument type names could be
4970 schema-qualified, too.
4971 </para>
4972
4973 <para>
4974 Many built-in <productname>PostgreSQL</productname> functions accept
4975 the OID of a table, or another kind of database object, and for
4976 convenience are declared as taking <type>regclass</type> (or the
4977 appropriate OID alias type). This means you do not have to look up
4978 the object's OID by hand, but can just enter its name as a string
4979 literal. For example, the <function>nextval(regclass)</function> function
4980 takes a sequence relation's OID, so you could call it like this:
4981 <programlisting>
4982 nextval('foo') <lineannotation>operates on sequence <literal>foo</literal></lineannotation>
4983 nextval('FOO') <lineannotation>same as above</lineannotation>
4984 nextval('"Foo"') <lineannotation>operates on sequence <literal>Foo</literal></lineannotation>
4985 nextval('myschema.foo') <lineannotation>operates on <literal>myschema.foo</literal></lineannotation>
4986 nextval('"myschema".foo') <lineannotation>same as above</lineannotation>
4987 nextval('foo') <lineannotation>searches search path for <literal>foo</literal></lineannotation>
4988 </programlisting>
4989 </para>
4990
4991 <note>
4992 <para>
4993 When you write the argument of such a function as an unadorned
4994 literal string, it becomes a constant of type <type>regclass</type>
4995 (or the appropriate type).
4996 Since this is really just an OID, it will track the originally
4997 identified object despite later renaming, schema reassignment,
4998 etc. This <quote>early binding</quote> behavior is usually desirable for
4999 object references in column defaults and views. But sometimes you might
5000 want <quote>late binding</quote> where the object reference is resolved
5001 at run time. To get late-binding behavior, force the constant to be
5002 stored as a <type>text</type> constant instead of <type>regclass</type>:
5003 <programlisting>
5004 nextval('foo'::text) <lineannotation><literal>foo</literal> is looked up at runtime</lineannotation>
5005 </programlisting>
5006 The <function>to_regclass()</function> function and its siblings
5007 can also be used to perform run-time lookups. See
5008 <xref linkend="functions-info-catalog-table"/>.
5009 </para>
5010 </note>
5011
5012 <para>
5013 Another practical example of use of <type>regclass</type>
5014 is to look up the OID of a table listed in
5015 the <literal>information_schema</literal> views, which don't supply
5016 such OIDs directly. One might for example wish to call
5017 the <function>pg_relation_size()</function> function, which requires
5018 the table OID. Taking the above rules into account, the correct way
5019 to do that is
5020 <programlisting>
5021 SELECT table_schema, table_name,
5022 pg_relation_size((quote_ident(table_schema) || '.' ||
5023 quote_ident(table_name))::regclass)
5024 FROM information_schema.tables
5025 WHERE ...
5026 </programlisting>
5027 The <function>quote_ident()</function> function will take care of
5028 double-quoting the identifiers where needed. The seemingly easier
5029 <programlisting>
5030 SELECT pg_relation_size(table_name)
5031 FROM information_schema.tables
5032 WHERE ...
5033 </programlisting>
5034 is <emphasis>not recommended</emphasis>, because it will fail for
5035 tables that are outside your search path or have names that require
5036 quoting.
5037 </para>
5038
5039 <para>
5040 An additional property of most of the OID alias types is the creation of
5041 dependencies. If a
5042 constant of one of these types appears in a stored expression
5043 (such as a column default expression or view), it creates a dependency
5044 on the referenced object. For example, if a column has a default
5045 expression <literal>nextval('my_seq'::regclass)</literal>,
5046 <productname>PostgreSQL</productname>
5047 understands that the default expression depends on the sequence
5048 <literal>my_seq</literal>, so the system will not let the sequence
5049 be dropped without first removing the default expression. The
5050 alternative of <literal>nextval('my_seq'::text)</literal> does not
5051 create a dependency.
5052 (<type>regrole</type> is an exception to this property. Constants of this
5053 type are not allowed in stored expressions.)
5054 </para>
5055
5056 <para>
5057 Another identifier type used by the system is <type>xid</type>, or transaction
5058 (abbreviated <abbrev>xact</abbrev>) identifier. This is the data type of the system columns
5059 <structfield>xmin</structfield> and <structfield>xmax</structfield>. Transaction identifiers are 32-bit quantities.
5060 In some contexts, a 64-bit variant <type>xid8</type> is used. Unlike
5061 <type>xid</type> values, <type>xid8</type> values increase strictly
5062 monotonically and cannot be reused in the lifetime of a database
5063 cluster. See <xref linkend="transaction-id"/> for more details.
5064 </para>
5065
5066 <para>
5067 A third identifier type used by the system is <type>cid</type>, or
5068 command identifier. This is the data type of the system columns
5069 <structfield>cmin</structfield> and <structfield>cmax</structfield>. Command identifiers are also 32-bit quantities.
5070 </para>
5071
5072 <para>
5073 A final identifier type used by the system is <type>tid</type>, or tuple
5074 identifier (row identifier). This is the data type of the system column
5075 <structfield>ctid</structfield>. A tuple ID is a pair
5076 (block number, tuple index within block) that identifies the
5077 physical location of the row within its table.
5078 </para>
5079
5080 <para>
5081 (The system columns are further explained in <xref
5082 linkend="ddl-system-columns"/>.)
5083 </para>
5084 </sect1>
5085
5086 <sect1 id="datatype-pg-lsn">
5087 <title><type>pg_lsn</type> Type</title>
5088
5089 <indexterm zone="datatype-pg-lsn">
5090 <primary>pg_lsn</primary>
5091 </indexterm>
5092
5093 <para>
5094 The <type>pg_lsn</type> data type can be used to store LSN (Log Sequence
5095 Number) data which is a pointer to a location in the WAL. This type is a
5096 representation of <type>XLogRecPtr</type> and an internal system type of
5097 <productname>PostgreSQL</productname>.
5098 </para>
5099
5100 <para>
5101 Internally, an LSN is a 64-bit integer, representing a byte position in
5102 the write-ahead log stream. It is printed as two hexadecimal numbers of
5103 up to 8 digits each, separated by a slash; for example,
5104 <literal>16/B374D848</literal>. The <type>pg_lsn</type> type supports the
5105 standard comparison operators, like <literal>=</literal> and
5106 <literal>&gt;</literal>. Two LSNs can be subtracted using the
5107 <literal>-</literal> operator; the result is the number of bytes separating
5108 those write-ahead log locations. Also the number of bytes can be
5109 added into and subtracted from LSN using the
5110 <literal>+(pg_lsn,numeric)</literal> and
5111 <literal>-(pg_lsn,numeric)</literal> operators, respectively. Note that
5112 the calculated LSN should be in the range of <type>pg_lsn</type> type,
5113 i.e., between <literal>0/0</literal> and
5114 <literal>FFFFFFFF/FFFFFFFF</literal>.
5115 </para>
5116 </sect1>
5117
5118 <sect1 id="datatype-pseudo">
5119 <title>Pseudo-Types</title>
5120
5121 <indexterm zone="datatype-pseudo">
5122 <primary>record</primary>
5123 </indexterm>
5124
5125 <indexterm zone="datatype-pseudo">
5126 <primary>any</primary>
5127 </indexterm>
5128
5129 <indexterm zone="datatype-pseudo">
5130 <primary>anyelement</primary>
5131 </indexterm>
5132
5133 <indexterm zone="datatype-pseudo">
5134 <primary>anyarray</primary>
5135 </indexterm>
5136
5137 <indexterm zone="datatype-pseudo">
5138 <primary>anynonarray</primary>
5139 </indexterm>
5140
5141 <indexterm zone="datatype-pseudo">
5142 <primary>anyenum</primary>
5143 </indexterm>
5144
5145 <indexterm zone="datatype-pseudo">
5146 <primary>anyrange</primary>
5147 </indexterm>
5148
5149 <indexterm zone="datatype-pseudo">
5150 <primary>anymultirange</primary>
5151 </indexterm>
5152
5153 <indexterm zone="datatype-pseudo">
5154 <primary>anycompatible</primary>
5155 </indexterm>
5156
5157 <indexterm zone="datatype-pseudo">
5158 <primary>anycompatiblearray</primary>
5159 </indexterm>
5160
5161 <indexterm zone="datatype-pseudo">
5162 <primary>anycompatiblenonarray</primary>
5163 </indexterm>
5164
5165 <indexterm zone="datatype-pseudo">
5166 <primary>anycompatiblerange</primary>
5167 </indexterm>
5168
5169 <indexterm zone="datatype-pseudo">
5170 <primary>anycompatiblemultirange</primary>
5171 </indexterm>
5172
5173 <indexterm zone="datatype-pseudo">
5174 <primary>void</primary>
5175 </indexterm>
5176
5177 <indexterm zone="datatype-pseudo">
5178 <primary>trigger</primary>
5179 </indexterm>
5180
5181 <indexterm zone="datatype-pseudo">
5182 <primary>event_trigger</primary>
5183 </indexterm>
5184
5185 <indexterm zone="datatype-pseudo">
5186 <primary>pg_ddl_command</primary>
5187 </indexterm>
5188
5189 <indexterm zone="datatype-pseudo">
5190 <primary>language_handler</primary>
5191 </indexterm>
5192
5193 <indexterm zone="datatype-pseudo">
5194 <primary>fdw_handler</primary>
5195 </indexterm>
5196
5197 <indexterm zone="datatype-pseudo">
5198 <primary>table_am_handler</primary>
5199 </indexterm>
5200
5201 <indexterm zone="datatype-pseudo">
5202 <primary>index_am_handler</primary>
5203 </indexterm>
5204
5205 <indexterm zone="datatype-pseudo">
5206 <primary>tsm_handler</primary>
5207 </indexterm>
5208
5209 <indexterm zone="datatype-pseudo">
5210 <primary>cstring</primary>
5211 </indexterm>
5212
5213 <indexterm zone="datatype-pseudo">
5214 <primary>internal</primary>
5215 </indexterm>
5216
5217 <indexterm zone="datatype-pseudo">
5218 <primary>unknown</primary>
5219 </indexterm>
5220
5221 <para>
5222 The <productname>PostgreSQL</productname> type system contains a
5223 number of special-purpose entries that are collectively called
5224 <firstterm>pseudo-types</firstterm>. A pseudo-type cannot be used as a
5225 column data type, but it can be used to declare a function's
5226 argument or result type. Each of the available pseudo-types is
5227 useful in situations where a function's behavior does not
5228 correspond to simply taking or returning a value of a specific
5229 <acronym>SQL</acronym> data type. <xref
5230 linkend="datatype-pseudotypes-table"/> lists the existing
5231 pseudo-types.
5232 </para>
5233
5234 <table id="datatype-pseudotypes-table">
5235 <title>Pseudo-Types</title>
5236 <tgroup cols="2">
5237 <colspec colname="col1" colwidth="2*"/>
5238 <colspec colname="col2" colwidth="3*"/>
5239 <thead>
5240 <row>
5241 <entry>Name</entry>
5242 <entry>Description</entry>
5243 </row>
5244 </thead>
5245
5246 <tbody>
5247 <row>
5248 <entry><type>any</type></entry>
5249 <entry>Indicates that a function accepts any input data type.</entry>
5250 </row>
5251
5252 <row>
5253 <entry><type>anyelement</type></entry>
5254 <entry>Indicates that a function accepts any data type
5255 (see <xref linkend="extend-types-polymorphic"/>).</entry>
5256 </row>
5257
5258 <row>
5259 <entry><type>anyarray</type></entry>
5260 <entry>Indicates that a function accepts any array data type
5261 (see <xref linkend="extend-types-polymorphic"/>).</entry>
5262 </row>
5263
5264 <row>
5265 <entry><type>anynonarray</type></entry>
5266 <entry>Indicates that a function accepts any non-array data type
5267 (see <xref linkend="extend-types-polymorphic"/>).</entry>
5268 </row>
5269
5270 <row>
5271 <entry><type>anyenum</type></entry>
5272 <entry>Indicates that a function accepts any enum data type
5273 (see <xref linkend="extend-types-polymorphic"/> and
5274 <xref linkend="datatype-enum"/>).</entry>
5275 </row>
5276
5277 <row>
5278 <entry><type>anyrange</type></entry>
5279 <entry>Indicates that a function accepts any range data type
5280 (see <xref linkend="extend-types-polymorphic"/> and
5281 <xref linkend="rangetypes"/>).</entry>
5282 </row>
5283
5284 <row>
5285 <entry><type>anymultirange</type></entry>
5286 <entry>Indicates that a function accepts any multirange data type
5287 (see <xref linkend="extend-types-polymorphic"/> and
5288 <xref linkend="rangetypes"/>).</entry>
5289 </row>
5290
5291 <row>
5292 <entry><type>anycompatible</type></entry>
5293 <entry>Indicates that a function accepts any data type,
5294 with automatic promotion of multiple arguments to a common data type
5295 (see <xref linkend="extend-types-polymorphic"/>).</entry>
5296 </row>
5297
5298 <row>
5299 <entry><type>anycompatiblearray</type></entry>
5300 <entry>Indicates that a function accepts any array data type,
5301 with automatic promotion of multiple arguments to a common data type
5302 (see <xref linkend="extend-types-polymorphic"/>).</entry>
5303 </row>
5304
5305 <row>
5306 <entry><type>anycompatiblenonarray</type></entry>
5307 <entry>Indicates that a function accepts any non-array data type,
5308 with automatic promotion of multiple arguments to a common data type
5309 (see <xref linkend="extend-types-polymorphic"/>).</entry>
5310 </row>
5311
5312 <row>
5313 <entry><type>anycompatiblerange</type></entry>
5314 <entry>Indicates that a function accepts any range data type,
5315 with automatic promotion of multiple arguments to a common data type
5316 (see <xref linkend="extend-types-polymorphic"/> and
5317 <xref linkend="rangetypes"/>).</entry>
5318 </row>
5319
5320 <row>
5321 <entry><type>anycompatiblemultirange</type></entry>
5322 <entry>Indicates that a function accepts any multirange data type,
5323 with automatic promotion of multiple arguments to a common data type
5324 (see <xref linkend="extend-types-polymorphic"/> and
5325 <xref linkend="rangetypes"/>).</entry>
5326 </row>
5327
5328 <row>
5329 <entry><type>cstring</type></entry>
5330 <entry>Indicates that a function accepts or returns a null-terminated C string.</entry>
5331 </row>
5332
5333 <row>
5334 <entry><type>internal</type></entry>
5335 <entry>Indicates that a function accepts or returns a server-internal
5336 data type.</entry>
5337 </row>
5338
5339 <row>
5340 <entry><type>language_handler</type></entry>
5341 <entry>A procedural language call handler is declared to return <type>language_handler</type>.</entry>
5342 </row>
5343
5344 <row>
5345 <entry><type>fdw_handler</type></entry>
5346 <entry>A foreign-data wrapper handler is declared to return <type>fdw_handler</type>.</entry>
5347 </row>
5348
5349 <row>
5350 <entry><type>table_am_handler</type></entry>
5351 <entry>A table access method handler is declared to return <type>table_am_handler</type>.</entry>
5352 </row>
5353
5354 <row>
5355 <entry><type>index_am_handler</type></entry>
5356 <entry>An index access method handler is declared to return <type>index_am_handler</type>.</entry>
5357 </row>
5358
5359 <row>
5360 <entry><type>tsm_handler</type></entry>
5361 <entry>A tablesample method handler is declared to return <type>tsm_handler</type>.</entry>
5362 </row>
5363
5364 <row>
5365 <entry><type>record</type></entry>
5366 <entry>Identifies a function taking or returning an unspecified row type.</entry>
5367 </row>
5368
5369 <row>
5370 <entry><type>trigger</type></entry>
5371 <entry>A trigger function is declared to return <type>trigger.</type></entry>
5372 </row>
5373
5374 <row>
5375 <entry><type>event_trigger</type></entry>
5376 <entry>An event trigger function is declared to return <type>event_trigger.</type></entry>
5377 </row>
5378
5379 <row>
5380 <entry><type>pg_ddl_command</type></entry>
5381 <entry>Identifies a representation of DDL commands that is available to event triggers.</entry>
5382 </row>
5383
5384 <row>
5385 <entry><type>void</type></entry>
5386 <entry>Indicates that a function returns no value.</entry>
5387 </row>
5388
5389 <row>
5390 <entry><type>unknown</type></entry>
5391 <entry>Identifies a not-yet-resolved type, e.g., of an undecorated
5392 string literal.</entry>
5393 </row>
5394 </tbody>
5395 </tgroup>
5396 </table>
5397
5398 <para>
5399 Functions coded in C (whether built-in or dynamically loaded) can be
5400 declared to accept or return any of these pseudo-types. It is up to
5401 the function author to ensure that the function will behave safely
5402 when a pseudo-type is used as an argument type.
5403 </para>
5404
5405 <para>
5406 Functions coded in procedural languages can use pseudo-types only as
5407 allowed by their implementation languages. At present most procedural
5408 languages forbid use of a pseudo-type as an argument type, and allow
5409 only <type>void</type> and <type>record</type> as a result type (plus
5410 <type>trigger</type> or <type>event_trigger</type> when the function is used
5411 as a trigger or event trigger). Some also support polymorphic functions
5412 using the polymorphic pseudo-types, which are shown above and discussed
5413 in detail in <xref linkend="extend-types-polymorphic"/>.
5414 </para>
5415
5416 <para>
5417 The <type>internal</type> pseudo-type is used to declare functions
5418 that are meant only to be called internally by the database
5419 system, and not by direct invocation in an <acronym>SQL</acronym>
5420 query. If a function has at least one <type>internal</type>-type
5421 argument then it cannot be called from <acronym>SQL</acronym>. To
5422 preserve the type safety of this restriction it is important to
5423 follow this coding rule: do not create any function that is
5424 declared to return <type>internal</type> unless it has at least one
5425 <type>internal</type> argument.
5426 </para>
5427
5428 </sect1>
5429
5430 </chapter>