doc/src/sgml/array.sgml

   1 <!-- doc/src/sgml/array.sgml -->
   2
   3 <sect1 id="arrays">
   4  <title>Arrays</title>
   5
   6  <indexterm>
   7   <primary>array</primary>
   8  </indexterm>
   9
  10  <para>
  11   <productname>PostgreSQL</productname> allows columns of a table to be
  12   defined as variable-length multidimensional arrays. Arrays of any
  13   built-in or user-defined base type, enum type, composite type, range type,
  14   or domain can be created.
  15  </para>
  16
  17  <sect2 id="arrays-declaration">
  18   <title>Declaration of Array Types</title>
  19
  20   <indexterm>
  21    <primary>array</primary>
  22    <secondary>declaration</secondary>
  23   </indexterm>
  24
  25  <para>
  26   To illustrate the use of array types, we create this table:
  27 <programlisting>
  28 CREATE TABLE sal_emp (
  29     name            text,
  30     pay_by_quarter  integer[],
  31     schedule        text[][]
  32 );
  33 </programlisting>
  34   As shown, an array data type is named by appending square brackets
  35   (<literal>[]</literal>) to the data type name of the array elements.  The
  36   above command will create a table named
  37   <structname>sal_emp</structname> with a column of type
  38   <type>text</type> (<structfield>name</structfield>), a
  39   one-dimensional array of type <type>integer</type>
  40   (<structfield>pay_by_quarter</structfield>), which represents the
  41   employee's salary by quarter, and a two-dimensional array of
  42   <type>text</type> (<structfield>schedule</structfield>), which
  43   represents the employee's weekly schedule.
  44  </para>
  45
  46  <para>
  47   The syntax for <command>CREATE TABLE</command> allows the exact size of
  48   arrays to be specified, for example:
  49
  50 <programlisting>
  51 CREATE TABLE tictactoe (
  52     squares   integer[3][3]
  53 );
  54 </programlisting>
  55
  56   However, the current implementation ignores any supplied array size
  57   limits, i.e., the behavior is the same as for arrays of unspecified
  58   length.
  59  </para>
  60
  61  <para>
  62   The current implementation does not enforce the declared
  63   number of dimensions either.  Arrays of a particular element type are
  64   all considered to be of the same type, regardless of size or number
  65   of dimensions.  So, declaring the array size or number of dimensions in
  66   <command>CREATE TABLE</command> is simply documentation; it does not
  67   affect run-time behavior.
  68  </para>
  69
  70  <para>
  71   An alternative syntax, which conforms to the SQL standard by using
  72   the keyword <literal>ARRAY</literal>, can be used for one-dimensional arrays.
  73   <structfield>pay_by_quarter</structfield> could have been defined
  74   as:
  75 <programlisting>
  76     pay_by_quarter  integer ARRAY[4],
  77 </programlisting>
  78   Or, if no array size is to be specified:
  79 <programlisting>
  80     pay_by_quarter  integer ARRAY,
  81 </programlisting>
  82   As before, however, <productname>PostgreSQL</productname> does not enforce the
  83   size restriction in any case.
  84  </para>
  85  </sect2>
  86
  87  <sect2 id="arrays-input">
  88   <title>Array Value Input</title>
  89
  90   <indexterm>
  91    <primary>array</primary>
  92    <secondary>constant</secondary>
  93   </indexterm>
  94
  95   <para>
  96    To write an array value as a literal constant, enclose the element
  97    values within curly braces and separate them by commas.  (If you
  98    know C, this is not unlike the C syntax for initializing
  99    structures.)  You can put double quotes around any element value,
 100    and must do so if it contains commas or curly braces.  (More
 101    details appear below.)  Thus, the general format of an array
 102    constant is the following:
 103 <synopsis>
 104 '{ <replaceable>val1</replaceable> <replaceable>delim</replaceable> <replaceable>val2</replaceable> <replaceable>delim</replaceable> ... }'
 105 </synopsis>
 106    where <replaceable>delim</replaceable> is the delimiter character
 107    for the type, as recorded in its <literal>pg_type</literal> entry.
 108    Among the standard data types provided in the
 109    <productname>PostgreSQL</productname> distribution, all use a comma
 110    (<literal>,</literal>), except for type <type>box</type> which uses a semicolon
 111    (<literal>;</literal>). Each <replaceable>val</replaceable> is
 112    either a constant of the array element type, or a subarray. An example
 113    of an array constant is:
 114 <programlisting>
 115 '{{1,2,3},{4,5,6},{7,8,9}}'
 116 </programlisting>
 117    This constant is a two-dimensional, 3-by-3 array consisting of
 118    three subarrays of integers.
 119   </para>
 120
 121   <para>
 122    To set an element of an array constant to NULL, write <literal>NULL</literal>
 123    for the element value.  (Any upper- or lower-case variant of
 124    <literal>NULL</literal> will do.)  If you want an actual string value
 125    <quote>NULL</quote>, you must put double quotes around it.
 126   </para>
 127
 128   <para>
 129    (These kinds of array constants are actually only a special case of
 130    the generic type constants discussed in <xref
 131    linkend="sql-syntax-constants-generic"/>.  The constant is initially
 132    treated as a string and passed to the array input conversion
 133    routine.  An explicit type specification might be necessary.)
 134   </para>
 135
 136   <para>
 137    Now we can show some <command>INSERT</command> statements:
 138
 139 <programlisting>
 140 INSERT INTO sal_emp
 141     VALUES ('Bill',
 142     '{10000, 10000, 10000, 10000}',
 143     '{{"meeting", "lunch"}, {"training", "presentation"}}');
 144
 145 INSERT INTO sal_emp
 146     VALUES ('Carol',
 147     '{20000, 25000, 25000, 25000}',
 148     '{{"breakfast", "consulting"}, {"meeting", "lunch"}}');
 149 </programlisting>
 150   </para>
 151
 152  <para>
 153   The result of the previous two inserts looks like this:
 154
 155 <programlisting>
 156 SELECT * FROM sal_emp;
 157  name  |      pay_by_quarter       |                 schedule
 158 -------+---------------------------+-------------------------------------------
 159  Bill  | {10000,10000,10000,10000} | {{meeting,lunch},{training,presentation}}
 160  Carol | {20000,25000,25000,25000} | {{breakfast,consulting},{meeting,lunch}}
 161 (2 rows)
 162 </programlisting>
 163  </para>
 164
 165  <para>
 166   Multidimensional arrays must have matching extents for each
 167   dimension. A mismatch causes an error, for example:
 168
 169 <programlisting>
 170 INSERT INTO sal_emp
 171     VALUES ('Bill',
 172     '{10000, 10000, 10000, 10000}',
 173     '{{"meeting", "lunch"}, {"meeting"}}');
 174 ERROR:  malformed array literal: "{{"meeting", "lunch"}, {"meeting"}}"
 175 DETAIL:  Multidimensional arrays must have sub-arrays with matching dimensions.
 176 </programlisting>
 177  </para>
 178
 179  <para>
 180   The <literal>ARRAY</literal> constructor syntax can also be used:
 181 <programlisting>
 182 INSERT INTO sal_emp
 183     VALUES ('Bill',
 184     ARRAY[10000, 10000, 10000, 10000],
 185     ARRAY[['meeting', 'lunch'], ['training', 'presentation']]);
 186
 187 INSERT INTO sal_emp
 188     VALUES ('Carol',
 189     ARRAY[20000, 25000, 25000, 25000],
 190     ARRAY[['breakfast', 'consulting'], ['meeting', 'lunch']]);
 191 </programlisting>
 192   Notice that the array elements are ordinary SQL constants or
 193   expressions; for instance, string literals are single quoted, instead of
 194   double quoted as they would be in an array literal.  The <literal>ARRAY</literal>
 195   constructor syntax is discussed in more detail in
 196   <xref linkend="sql-syntax-array-constructors"/>.
 197  </para>
 198  </sect2>
 199
 200  <sect2 id="arrays-accessing">
 201   <title>Accessing Arrays</title>
 202
 203   <indexterm>
 204    <primary>array</primary>
 205    <secondary>accessing</secondary>
 206   </indexterm>
 207
 208  <para>
 209   Now, we can run some queries on the table.
 210   First, we show how to access a single element of an array.
 211   This query retrieves the names of the employees whose pay changed in
 212   the second quarter:
 213
 214 <programlisting>
 215 SELECT name FROM sal_emp WHERE pay_by_quarter[1] &lt;&gt; pay_by_quarter[2];
 216
 217  name
 218 -------
 219  Carol
 220 (1 row)
 221 </programlisting>
 222
 223   The array subscript numbers are written within square brackets.
 224   By default <productname>PostgreSQL</productname> uses a
 225   one-based numbering convention for arrays, that is,
 226   an array of <replaceable>n</replaceable> elements starts with <literal>array[1]</literal> and
 227   ends with <literal>array[<replaceable>n</replaceable>]</literal>.
 228  </para>
 229
 230  <para>
 231   This query retrieves the third quarter pay of all employees:
 232
 233 <programlisting>
 234 SELECT pay_by_quarter[3] FROM sal_emp;
 235
 236  pay_by_quarter
 237 ----------------
 238           10000
 239           25000
 240 (2 rows)
 241 </programlisting>
 242  </para>
 243
 244  <para>
 245   We can also access arbitrary rectangular slices of an array, or
 246   subarrays.  An array slice is denoted by writing
 247   <literal><replaceable>lower-bound</replaceable>:<replaceable>upper-bound</replaceable></literal>
 248   for one or more array dimensions.  For example, this query retrieves the first
 249   item on Bill's schedule for the first two days of the week:
 250
 251 <programlisting>
 252 SELECT schedule[1:2][1:1] FROM sal_emp WHERE name = 'Bill';
 253
 254         schedule
 255 ------------------------
 256  {{meeting},{training}}
 257 (1 row)
 258 </programlisting>
 259
 260   If any dimension is written as a slice, i.e., contains a colon, then all
 261   dimensions are treated as slices.  Any dimension that has only a single
 262   number (no colon) is treated as being from 1
 263   to the number specified.  For example, <literal>[2]</literal> is treated as
 264   <literal>[1:2]</literal>, as in this example:
 265
 266 <programlisting>
 267 SELECT schedule[1:2][2] FROM sal_emp WHERE name = 'Bill';
 268
 269                  schedule
 270 -------------------------------------------
 271  {{meeting,lunch},{training,presentation}}
 272 (1 row)
 273 </programlisting>
 274
 275   To avoid confusion with the non-slice case, it's best to use slice syntax
 276   for all dimensions, e.g., <literal>[1:2][1:1]</literal>, not <literal>[2][1:1]</literal>.
 277  </para>
 278
 279  <para>
 280   It is possible to omit the <replaceable>lower-bound</replaceable> and/or
 281   <replaceable>upper-bound</replaceable> of a slice specifier; the missing
 282   bound is replaced by the lower or upper limit of the array's subscripts.
 283   For example:
 284
 285 <programlisting>
 286 SELECT schedule[:2][2:] FROM sal_emp WHERE name = 'Bill';
 287
 288         schedule
 289 ------------------------
 290  {{lunch},{presentation}}
 291 (1 row)
 292
 293 SELECT schedule[:][1:1] FROM sal_emp WHERE name = 'Bill';
 294
 295         schedule
 296 ------------------------
 297  {{meeting},{training}}
 298 (1 row)
 299 </programlisting>
 300  </para>
 301
 302  <para>
 303   An array subscript expression will return null if either the array itself or
 304   any of the subscript expressions are null.  Also, null is returned if a
 305   subscript is outside the array bounds (this case does not raise an error).
 306   For example, if <literal>schedule</literal>
 307   currently has the dimensions <literal>[1:3][1:2]</literal> then referencing
 308   <literal>schedule[3][3]</literal> yields NULL.  Similarly, an array reference
 309   with the wrong number of subscripts yields a null rather than an error.
 310  </para>
 311
 312  <para>
 313   An array slice expression likewise yields null if the array itself or
 314   any of the subscript expressions are null.  However, in other
 315   cases such as selecting an array slice that
 316   is completely outside the current array bounds, a slice expression
 317   yields an empty (zero-dimensional) array instead of null.  (This
 318   does not match non-slice behavior and is done for historical reasons.)
 319   If the requested slice partially overlaps the array bounds, then it
 320   is silently reduced to just the overlapping region instead of
 321   returning null.
 322  </para>
 323
 324  <para>
 325   The current dimensions of any array value can be retrieved with the
 326   <function>array_dims</function> function:
 327
 328 <programlisting>
 329 SELECT array_dims(schedule) FROM sal_emp WHERE name = 'Carol';
 330
 331  array_dims
 332 ------------
 333  [1:2][1:2]
 334 (1 row)
 335 </programlisting>
 336
 337   <function>array_dims</function> produces a <type>text</type> result,
 338   which is convenient for people to read but perhaps inconvenient
 339   for programs.  Dimensions can also be retrieved with
 340   <function>array_upper</function> and <function>array_lower</function>,
 341   which return the upper and lower bound of a
 342   specified array dimension, respectively:
 343
 344 <programlisting>
 345 SELECT array_upper(schedule, 1) FROM sal_emp WHERE name = 'Carol';
 346
 347  array_upper
 348 -------------
 349            2
 350 (1 row)
 351 </programlisting>
 352
 353  <function>array_length</function> will return the length of a specified
 354  array dimension:
 355
 356 <programlisting>
 357 SELECT array_length(schedule, 1) FROM sal_emp WHERE name = 'Carol';
 358
 359  array_length
 360 --------------
 361             2
 362 (1 row)
 363 </programlisting>
 364
 365  <function>cardinality</function> returns the total number of elements in an
 366  array across all dimensions.  It is effectively the number of rows a call to
 367  <function>unnest</function> would yield:
 368
 369 <programlisting>
 370 SELECT cardinality(schedule) FROM sal_emp WHERE name = 'Carol';
 371
 372  cardinality
 373 -------------
 374            4
 375 (1 row)
 376 </programlisting>
 377  </para>
 378  </sect2>
 379
 380  <sect2 id="arrays-modifying">
 381   <title>Modifying Arrays</title>
 382
 383   <indexterm>
 384    <primary>array</primary>
 385    <secondary>modifying</secondary>
 386   </indexterm>
 387
 388  <para>
 389   An array value can be replaced completely:
 390
 391 <programlisting>
 392 UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
 393     WHERE name = 'Carol';
 394 </programlisting>
 395
 396   or using the <literal>ARRAY</literal> expression syntax:
 397
 398 <programlisting>
 399 UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000]
 400     WHERE name = 'Carol';
 401 </programlisting>
 402
 403   An array can also be updated at a single element:
 404
 405 <programlisting>
 406 UPDATE sal_emp SET pay_by_quarter[4] = 15000
 407     WHERE name = 'Bill';
 408 </programlisting>
 409
 410   or updated in a slice:
 411
 412 <programlisting>
 413 UPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}'
 414     WHERE name = 'Carol';
 415 </programlisting>
 416
 417   The slice syntaxes with omitted <replaceable>lower-bound</replaceable> and/or
 418   <replaceable>upper-bound</replaceable> can be used too, but only when
 419   updating an array value that is not NULL or zero-dimensional (otherwise,
 420   there is no existing subscript limit to substitute).
 421  </para>
 422
 423  <para>
 424   A stored array value can be enlarged by assigning to elements not already
 425   present.  Any positions between those previously present and the newly
 426   assigned elements will be filled with nulls.  For example, if array
 427   <literal>myarray</literal> currently has 4 elements, it will have six
 428   elements after an update that assigns to <literal>myarray[6]</literal>;
 429   <literal>myarray[5]</literal> will contain null.
 430   Currently, enlargement in this fashion is only allowed for one-dimensional
 431   arrays, not multidimensional arrays.
 432  </para>
 433
 434  <para>
 435   Subscripted assignment allows creation of arrays that do not use one-based
 436   subscripts.  For example one might assign to <literal>myarray[-2:7]</literal> to
 437   create an array with subscript values from -2 to 7.
 438  </para>
 439
 440  <para>
 441   New array values can also be constructed using the concatenation operator,
 442   <literal>||</literal>:
 443 <programlisting>
 444 SELECT ARRAY[1,2] || ARRAY[3,4];
 445  ?column?
 446 -----------
 447  {1,2,3,4}
 448 (1 row)
 449
 450 SELECT ARRAY[5,6] || ARRAY[[1,2],[3,4]];
 451       ?column?
 452 ---------------------
 453  {{5,6},{1,2},{3,4}}
 454 (1 row)
 455 </programlisting>
 456  </para>
 457
 458  <para>
 459   The concatenation operator allows a single element to be pushed onto the
 460   beginning or end of a one-dimensional array. It also accepts two
 461   <replaceable>N</replaceable>-dimensional arrays, or an <replaceable>N</replaceable>-dimensional
 462   and an <replaceable>N+1</replaceable>-dimensional array.
 463  </para>
 464
 465  <para>
 466   When a single element is pushed onto either the beginning or end of a
 467   one-dimensional array, the result is an array with the same lower bound
 468   subscript as the array operand. For example:
 469 <programlisting>
 470 SELECT array_dims(1 || '[0:1]={2,3}'::int[]);
 471  array_dims
 472 ------------
 473  [0:2]
 474 (1 row)
 475
 476 SELECT array_dims(ARRAY[1,2] || 3);
 477  array_dims
 478 ------------
 479  [1:3]
 480 (1 row)
 481 </programlisting>
 482  </para>
 483
 484  <para>
 485   When two arrays with an equal number of dimensions are concatenated, the
 486   result retains the lower bound subscript of the left-hand operand's outer
 487   dimension. The result is an array comprising every element of the left-hand
 488   operand followed by every element of the right-hand operand. For example:
 489 <programlisting>
 490 SELECT array_dims(ARRAY[1,2] || ARRAY[3,4,5]);
 491  array_dims
 492 ------------
 493  [1:5]
 494 (1 row)
 495
 496 SELECT array_dims(ARRAY[[1,2],[3,4]] || ARRAY[[5,6],[7,8],[9,0]]);
 497  array_dims
 498 ------------
 499  [1:5][1:2]
 500 (1 row)
 501 </programlisting>
 502  </para>
 503
 504  <para>
 505   When an <replaceable>N</replaceable>-dimensional array is pushed onto the beginning
 506   or end of an <replaceable>N+1</replaceable>-dimensional array, the result is
 507   analogous to the element-array case above. Each <replaceable>N</replaceable>-dimensional
 508   sub-array is essentially an element of the <replaceable>N+1</replaceable>-dimensional
 509   array's outer dimension. For example:
 510 <programlisting>
 511 SELECT array_dims(ARRAY[1,2] || ARRAY[[3,4],[5,6]]);
 512  array_dims
 513 ------------
 514  [1:3][1:2]
 515 (1 row)
 516 </programlisting>
 517  </para>
 518
 519  <para>
 520   An array can also be constructed by using the functions
 521   <function>array_prepend</function>, <function>array_append</function>,
 522   or <function>array_cat</function>. The first two only support one-dimensional
 523   arrays, but <function>array_cat</function> supports multidimensional arrays.
 524   Some examples:
 525
 526 <programlisting>
 527 SELECT array_prepend(1, ARRAY[2,3]);
 528  array_prepend
 529 ---------------
 530  {1,2,3}
 531 (1 row)
 532
 533 SELECT array_append(ARRAY[1,2], 3);
 534  array_append
 535 --------------
 536  {1,2,3}
 537 (1 row)
 538
 539 SELECT array_cat(ARRAY[1,2], ARRAY[3,4]);
 540  array_cat
 541 -----------
 542  {1,2,3,4}
 543 (1 row)
 544
 545 SELECT array_cat(ARRAY[[1,2],[3,4]], ARRAY[5,6]);
 546       array_cat
 547 ---------------------
 548  {{1,2},{3,4},{5,6}}
 549 (1 row)
 550
 551 SELECT array_cat(ARRAY[5,6], ARRAY[[1,2],[3,4]]);
 552       array_cat
 553 ---------------------
 554  {{5,6},{1,2},{3,4}}
 555 </programlisting>
 556  </para>
 557
 558  <para>
 559   In simple cases, the concatenation operator discussed above is preferred
 560   over direct use of these functions.  However, because the concatenation
 561   operator is overloaded to serve all three cases, there are situations where
 562   use of one of the functions is helpful to avoid ambiguity.  For example
 563   consider:
 564
 565 <programlisting>
 566 SELECT ARRAY[1, 2] || '{3, 4}';  -- the untyped literal is taken as an array
 567  ?column?
 568 -----------
 569  {1,2,3,4}
 570
 571 SELECT ARRAY[1, 2] || '7';                 -- so is this one
 572 ERROR:  malformed array literal: "7"
 573
 574 SELECT ARRAY[1, 2] || NULL;                -- so is an undecorated NULL
 575  ?column?
 576 ----------
 577  {1,2}
 578 (1 row)
 579
 580 SELECT array_append(ARRAY[1, 2], NULL);    -- this might have been meant
 581  array_append
 582 --------------
 583  {1,2,NULL}
 584 </programlisting>
 585
 586   In the examples above, the parser sees an integer array on one side of the
 587   concatenation operator, and a constant of undetermined type on the other.
 588   The heuristic it uses to resolve the constant's type is to assume it's of
 589   the same type as the operator's other input &mdash; in this case,
 590   integer array.  So the concatenation operator is presumed to
 591   represent <function>array_cat</function>, not <function>array_append</function>.  When
 592   that's the wrong choice, it could be fixed by casting the constant to the
 593   array's element type; but explicit use of <function>array_append</function> might
 594   be a preferable solution.
 595  </para>
 596  </sect2>
 597
 598  <sect2 id="arrays-searching">
 599   <title>Searching in Arrays</title>
 600
 601   <indexterm>
 602    <primary>array</primary>
 603    <secondary>searching</secondary>
 604   </indexterm>
 605
 606  <para>
 607   To search for a value in an array, each value must be checked.
 608   This can be done manually, if you know the size of the array.
 609   For example:
 610
 611 <programlisting>
 612 SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR
 613                             pay_by_quarter[2] = 10000 OR
 614                             pay_by_quarter[3] = 10000 OR
 615                             pay_by_quarter[4] = 10000;
 616 </programlisting>
 617
 618   However, this quickly becomes tedious for large arrays, and is not
 619   helpful if the size of the array is unknown. An alternative method is
 620   described in <xref linkend="functions-comparisons"/>. The above
 621   query could be replaced by:
 622
 623 <programlisting>
 624 SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter);
 625 </programlisting>
 626
 627   In addition, you can find rows where the array has all values
 628   equal to 10000 with:
 629
 630 <programlisting>
 631 SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);
 632 </programlisting>
 633
 634  </para>
 635
 636  <para>
 637   Alternatively, the <function>generate_subscripts</function> function can be used.
 638   For example:
 639
 640 <programlisting>
 641 SELECT * FROM
 642    (SELECT pay_by_quarter,
 643            generate_subscripts(pay_by_quarter, 1) AS s
 644       FROM sal_emp) AS foo
 645  WHERE pay_by_quarter[s] = 10000;
 646 </programlisting>
 647
 648   This function is described in <xref linkend="functions-srf-subscripts"/>.
 649  </para>
 650
 651  <para>
 652   You can also search an array using the <literal>&amp;&amp;</literal> operator,
 653   which checks whether the left operand overlaps with the right operand.
 654   For instance:
 655
 656 <programlisting>
 657 SELECT * FROM sal_emp WHERE pay_by_quarter &amp;&amp; ARRAY[10000];
 658 </programlisting>
 659
 660   This and other array operators are further described in
 661   <xref linkend="functions-array"/>.  It can be accelerated by an appropriate
 662   index, as described in <xref linkend="indexes-types"/>.
 663  </para>
 664
 665  <para>
 666   You can also search for specific values in an array using the <function>array_position</function>
 667   and <function>array_positions</function> functions. The former returns the subscript of
 668   the first occurrence of a value in an array; the latter returns an array with the
 669   subscripts of all occurrences of the value in the array.  For example:
 670
 671 <programlisting>
 672 SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat'], 'mon');
 673  array_position
 674 ----------------
 675               2
 676 (1 row)
 677
 678 SELECT array_positions(ARRAY[1, 4, 3, 1, 3, 4, 2, 1], 1);
 679  array_positions
 680 -----------------
 681  {1,4,8}
 682 (1 row)
 683 </programlisting>
 684  </para>
 685
 686  <tip>
 687   <para>
 688    Arrays are not sets; searching for specific array elements
 689    can be a sign of database misdesign.  Consider
 690    using a separate table with a row for each item that would be an
 691    array element.  This will be easier to search, and is likely to
 692    scale better for a large number of elements.
 693   </para>
 694  </tip>
 695  </sect2>
 696
 697  <sect2 id="arrays-io">
 698   <title>Array Input and Output Syntax</title>
 699
 700   <indexterm>
 701    <primary>array</primary>
 702    <secondary>I/O</secondary>
 703   </indexterm>
 704
 705   <para>
 706    The external text representation of an array value consists of items that
 707    are interpreted according to the I/O conversion rules for the array's
 708    element type, plus decoration that indicates the array structure.
 709    The decoration consists of curly braces (<literal>{</literal> and <literal>}</literal>)
 710    around the array value plus delimiter characters between adjacent items.
 711    The delimiter character is usually a comma (<literal>,</literal>) but can be
 712    something else: it is determined by the <literal>typdelim</literal> setting
 713    for the array's element type.  Among the standard data types provided
 714    in the <productname>PostgreSQL</productname> distribution, all use a comma,
 715    except for type <type>box</type>, which uses a semicolon (<literal>;</literal>).
 716    In a multidimensional array, each dimension (row, plane,
 717    cube, etc.) gets its own level of curly braces, and delimiters
 718    must be written between adjacent curly-braced entities of the same level.
 719   </para>
 720
 721   <para>
 722    The array output routine will put double quotes around element values
 723    if they are empty strings, contain curly braces, delimiter characters,
 724    double quotes, backslashes, or white space, or match the word
 725    <literal>NULL</literal>.  Double quotes and backslashes
 726    embedded in element values will be backslash-escaped.  For numeric
 727    data types it is safe to assume that double quotes will never appear, but
 728    for textual data types one should be prepared to cope with either the presence
 729    or absence of quotes.
 730   </para>
 731
 732   <para>
 733    By default, the lower bound index value of an array's dimensions is
 734    set to one.  To represent arrays with other lower bounds, the array
 735    subscript ranges can be specified explicitly before writing the
 736    array contents.
 737    This decoration consists of square brackets (<literal>[]</literal>)
 738    around each array dimension's lower and upper bounds, with
 739    a colon (<literal>:</literal>) delimiter character in between. The
 740    array dimension decoration is followed by an equal sign (<literal>=</literal>).
 741    For example:
 742 <programlisting>
 743 SELECT f1[1][-2][3] AS e1, f1[1][-1][5] AS e2
 744  FROM (SELECT '[1:1][-2:-1][3:5]={{{1,2,3},{4,5,6}}}'::int[] AS f1) AS ss;
 745
 746  e1 | e2
 747 ----+----
 748   1 |  6
 749 (1 row)
 750 </programlisting>
 751    The array output routine will include explicit dimensions in its result
 752    only when there are one or more lower bounds different from one.
 753   </para>
 754
 755   <para>
 756    If the value written for an element is <literal>NULL</literal> (in any case
 757    variant), the element is taken to be NULL.  The presence of any quotes
 758    or backslashes disables this and allows the literal string value
 759    <quote>NULL</quote> to be entered.  Also, for backward compatibility with
 760    pre-8.2 versions of <productname>PostgreSQL</productname>, the <xref
 761    linkend="guc-array-nulls"/> configuration parameter can be turned
 762    <literal>off</literal> to suppress recognition of <literal>NULL</literal> as a NULL.
 763   </para>
 764
 765   <para>
 766    As shown previously, when writing an array value you can use double
 767    quotes around any individual array element. You <emphasis>must</emphasis> do so
 768    if the element value would otherwise confuse the array-value parser.
 769    For example, elements containing curly braces, commas (or the data type's
 770    delimiter character), double quotes, backslashes, or leading or trailing
 771    whitespace must be double-quoted.  Empty strings and strings matching the
 772    word <literal>NULL</literal> must be quoted, too.  To put a double
 773    quote or backslash in a quoted array element value, precede it
 774    with a backslash. Alternatively, you can avoid quotes and use
 775    backslash-escaping to protect all data characters that would otherwise
 776    be taken as array syntax.
 777   </para>
 778
 779   <para>
 780    You can add whitespace before a left brace or after a right
 781    brace. You can also add whitespace before or after any individual item
 782    string. In all of these cases the whitespace will be ignored. However,
 783    whitespace within double-quoted elements, or surrounded on both sides by
 784    non-whitespace characters of an element, is not ignored.
 785   </para>
 786
 787  <tip>
 788   <para>
 789    The <literal>ARRAY</literal> constructor syntax (see
 790    <xref linkend="sql-syntax-array-constructors"/>) is often easier to work
 791    with than the array-literal syntax when writing array values in SQL
 792    commands. In <literal>ARRAY</literal>, individual element values are written the
 793    same way they would be written when not members of an array.
 794   </para>
 795  </tip>
 796  </sect2>
 797
 798 </sect1>