doc/src/sgml/array.sgml

   1 <!-- $PostgreSQL$ -->
   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, or composite type
  14   can be created.
  15   Arrays of domains are not yet supported.
  16  </para>
  17
  18  <sect2 id="arrays-declaration">
  19   <title>Declaration of Array Types</title>
  20
  21   <indexterm>
  22    <primary>array</primary>
  23    <secondary>declaration</secondary>
  24   </indexterm>
  25
  26  <para>
  27   To illustrate the use of array types, we create this table:
  28 <programlisting>
  29 CREATE TABLE sal_emp (
  30     name            text,
  31     pay_by_quarter  integer[],
  32     schedule        text[][]
  33 );
  34 </programlisting>
  35   As shown, an array data type is named by appending square brackets
  36   (<literal>[]</>) to the data type name of the array elements.  The
  37   above command will create a table named
  38   <structname>sal_emp</structname> with a column of type
  39   <type>text</type> (<structfield>name</structfield>), a
  40   one-dimensional array of type <type>integer</type>
  41   (<structfield>pay_by_quarter</structfield>), which represents the
  42   employee's salary by quarter, and a two-dimensional array of
  43   <type>text</type> (<structfield>schedule</structfield>), which
  44   represents the employee's weekly schedule.
  45  </para>
  46
  47  <para>
  48   The syntax for <command>CREATE TABLE</command> allows the exact size of
  49   arrays to be specified, for example:
  50
  51 <programlisting>
  52 CREATE TABLE tictactoe (
  53     squares   integer[3][3]
  54 );
  55 </programlisting>
  56
  57   However, the current implementation does not enforce the array size
  58   limits &mdash; the behavior is the same as for arrays of unspecified
  59   length.
  60  </para>
  61
  62  <para>
  63   Actually, the current implementation does not enforce the declared
  64   number of dimensions either.  Arrays of a particular element type are
  65   all considered to be of the same type, regardless of size or number
  66   of dimensions.  So, declaring number of dimensions or sizes in
  67   <command>CREATE TABLE</command> is simply documentation, it does not
  68   affect run-time behavior.
  69  </para>
  70
  71  <para>
  72   An alternative syntax, which conforms to the SQL standard, can
  73   be used for one-dimensional arrays.
  74   <structfield>pay_by_quarter</structfield> could have been defined
  75   as:
  76 <programlisting>
  77     pay_by_quarter  integer ARRAY[4],
  78 </programlisting>
  79   Or, if no array size is to be specified:
  80 <programlisting>
  81     pay_by_quarter  integer ARRAY,
  82 </programlisting>
  83   As before, however, <productname>PostgreSQL</> does not enforce the
  84   size restriction in any case.
  85  </para>
  86  </sect2>
  87
  88  <sect2 id="arrays-input">
  89   <title>Array Value Input</title>
  90
  91   <indexterm>
  92    <primary>array</primary>
  93    <secondary>constant</secondary>
  94   </indexterm>
  95
  96   <para>
  97    To write an array value as a literal constant, enclose the element
  98    values within curly braces and separate them by commas.  (If you
  99    know C, this is not unlike the C syntax for initializing
 100    structures.)  You can put double quotes around any element value,
 101    and must do so if it contains commas or curly braces.  (More
 102    details appear below.)  Thus, the general format of an array
 103    constant is the following:
 104 <synopsis>
 105 '{ <replaceable>val1</replaceable> <replaceable>delim</replaceable> <replaceable>val2</replaceable> <replaceable>delim</replaceable> ... }'
 106 </synopsis>
 107    where <replaceable>delim</replaceable> is the delimiter character
 108    for the type, as recorded in its <literal>pg_type</literal> entry.
 109    Among the standard data types provided in the
 110    <productname>PostgreSQL</productname> distribution, type
 111    <literal>box</> uses a semicolon (<literal>;</>) but all the others
 112    use comma (<literal>,</>). Each <replaceable>val</replaceable> is
 113    either a constant of the array element type, or a subarray. An example
 114    of an array constant is:
 115 <programlisting>
 116 '{{1,2,3},{4,5,6},{7,8,9}}'
 117 </programlisting>
 118    This constant is a two-dimensional, 3-by-3 array consisting of
 119    three subarrays of integers.
 120   </para>
 121
 122   <para>
 123    To set an element of an array constant to NULL, write <literal>NULL</>
 124    for the element value.  (Any upper- or lower-case variant of
 125    <literal>NULL</> will do.)  If you want an actual string value
 126    <quote>NULL</>, you must put double quotes around it.
 127   </para>
 128
 129   <para>
 130    (These kinds of array constants are actually only a special case of
 131    the generic type constants discussed in <xref
 132    linkend="sql-syntax-constants-generic">.  The constant is initially
 133    treated as a string and passed to the array input conversion
 134    routine.  An explicit type specification might be necessary.)
 135   </para>
 136
 137   <para>
 138    Now we can show some <command>INSERT</command> statements:
 139
 140 <programlisting>
 141 INSERT INTO sal_emp
 142     VALUES ('Bill',
 143     '{10000, 10000, 10000, 10000}',
 144     '{{"meeting", "lunch"}, {"training", "presentation"}}');
 145
 146 INSERT INTO sal_emp
 147     VALUES ('Carol',
 148     '{20000, 25000, 25000, 25000}',
 149     '{{"breakfast", "consulting"}, {"meeting", "lunch"}}');
 150 </programlisting>
 151   </para>
 152
 153  <para>
 154   The result of the previous two inserts looks like this:
 155
 156 <programlisting>
 157 SELECT * FROM sal_emp;
 158  name  |      pay_by_quarter       |                 schedule
 159 -------+---------------------------+-------------------------------------------
 160  Bill  | {10000,10000,10000,10000} | {{meeting,lunch},{training,presentation}}
 161  Carol | {20000,25000,25000,25000} | {{breakfast,consulting},{meeting,lunch}}
 162 (2 rows)
 163 </programlisting>
 164  </para>
 165
 166  <para>
 167   The <literal>ARRAY</> constructor syntax can also be used:
 168 <programlisting>
 169 INSERT INTO sal_emp
 170     VALUES ('Bill',
 171     ARRAY[10000, 10000, 10000, 10000],
 172     ARRAY[['meeting', 'lunch'], ['training', 'presentation']]);
 173
 174 INSERT INTO sal_emp
 175     VALUES ('Carol',
 176     ARRAY[20000, 25000, 25000, 25000],
 177     ARRAY[['breakfast', 'consulting'], ['meeting', 'lunch']]);
 178 </programlisting>
 179   Notice that the array elements are ordinary SQL constants or
 180   expressions; for instance, string literals are single quoted, instead of
 181   double quoted as they would be in an array literal.  The <literal>ARRAY</>
 182   constructor syntax is discussed in more detail in
 183   <xref linkend="sql-syntax-array-constructors">.
 184  </para>
 185
 186  <para>
 187   Multidimensional arrays must have matching extents for each
 188   dimension. A mismatch causes an error report, for example:
 189
 190 <programlisting>
 191 INSERT INTO sal_emp
 192     VALUES ('Bill',
 193     '{10000, 10000, 10000, 10000}',
 194     '{{"meeting", "lunch"}, {"meeting"}}');
 195 ERROR:  multidimensional arrays must have array expressions with matching dimensions
 196 </programlisting>
 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 at a time.
 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 the
 225   one-based numbering convention for arrays, that is,
 226   an array of <replaceable>n</> elements starts with <literal>array[1]</literal> and
 227   ends with <literal>array[<replaceable>n</>]</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 <literal>1</>
 263   to the number specified.  For example, <literal>[2]</> is treated as
 264   <literal>[1:2]</>, 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]</>, not <literal>[2][1:1]</>.
 277  </para>
 278
 279  <para>
 280   An array subscript expression will return null if either the array itself or
 281   any of the subscript expressions are null.  Also, null is returned if a
 282   subscript is outside the array bounds (this case does not raise an error).
 283   For example, if <literal>schedule</>
 284   currently has the dimensions <literal>[1:3][1:2]</> then referencing
 285   <literal>schedule[3][3]</> yields NULL.  Similarly, an array reference
 286   with the wrong number of subscripts yields a null rather than an error.
 287  </para>
 288
 289  <para>
 290   An array slice expression likewise yields null if the array itself or
 291   any of the subscript expressions are null.  However, in other corner
 292   cases such as selecting an array slice that
 293   is completely outside the current array bounds, a slice expression
 294   yields an empty (zero-dimensional) array instead of null.  (This
 295   does not match non-slice behavior and is done for historical reasons.)
 296   If the requested slice partially overlaps the array bounds, then it
 297   is silently reduced to just the overlapping region.
 298  </para>
 299
 300  <para>
 301   The current dimensions of any array value can be retrieved with the
 302   <function>array_dims</function> function:
 303
 304 <programlisting>
 305 SELECT array_dims(schedule) FROM sal_emp WHERE name = 'Carol';
 306
 307  array_dims
 308 ------------
 309  [1:2][1:2]
 310 (1 row)
 311 </programlisting>
 312
 313   <function>array_dims</function> produces a <type>text</type> result,
 314   which is convenient for people to read but perhaps not so convenient
 315   for programs.  Dimensions can also be retrieved with
 316   <function>array_upper</function> and <function>array_lower</function>,
 317   which return the upper and lower bound of a
 318   specified array dimension, respectively:
 319
 320 <programlisting>
 321 SELECT array_upper(schedule, 1) FROM sal_emp WHERE name = 'Carol';
 322
 323  array_upper
 324 -------------
 325            2
 326 (1 row)
 327 </programlisting>
 328
 329  <function>array_length</function> will return the length of a specified
 330  array dimension:
 331
 332 <programlisting>
 333 SELECT array_length(schedule, 1) FROM sal_emp WHERE name = 'Carol';
 334
 335  array_length
 336 --------------
 337             2
 338 (1 row)
 339 </programlisting>
 340  </para>
 341  </sect2>
 342
 343  <sect2 id="arrays-modifying">
 344   <title>Modifying Arrays</title>
 345
 346   <indexterm>
 347    <primary>array</primary>
 348    <secondary>modifying</secondary>
 349   </indexterm>
 350
 351  <para>
 352   An array value can be replaced completely:
 353
 354 <programlisting>
 355 UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
 356     WHERE name = 'Carol';
 357 </programlisting>
 358
 359   or using the <literal>ARRAY</literal> expression syntax:
 360
 361 <programlisting>
 362 UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000]
 363     WHERE name = 'Carol';
 364 </programlisting>
 365
 366   An array can also be updated at a single element:
 367
 368 <programlisting>
 369 UPDATE sal_emp SET pay_by_quarter[4] = 15000
 370     WHERE name = 'Bill';
 371 </programListing>
 372
 373   or updated in a slice:
 374
 375 <programlisting>
 376 UPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}'
 377     WHERE name = 'Carol';
 378 </programlisting>
 379
 380  </para>
 381
 382  <para>
 383   A stored array value can be enlarged by assigning to element(s) not already
 384   present.  Any positions between those previously present and the newly
 385   assigned element(s) will be filled with nulls.  For example, if array
 386   <literal>myarray</> currently has 4 elements, it will have six
 387   elements after an update that assigns to <literal>myarray[6]</>,
 388   and <literal>myarray[5]</> will contain a null.
 389   Currently, enlargement in this fashion is only allowed for one-dimensional
 390   arrays, not multidimensional arrays.
 391  </para>
 392
 393  <para>
 394   Subscripted assignment allows creation of arrays that do not use one-based
 395   subscripts.  For example one might assign to <literal>myarray[-2:7]</> to
 396   create an array with subscript values running from -2 to 7.
 397  </para>
 398
 399  <para>
 400   New array values can also be constructed by using the concatenation operator,
 401   <literal>||</literal>:
 402 <programlisting>
 403 SELECT ARRAY[1,2] || ARRAY[3,4];
 404  ?column?
 405 -----------
 406  {1,2,3,4}
 407 (1 row)
 408
 409 SELECT ARRAY[5,6] || ARRAY[[1,2],[3,4]];
 410       ?column?
 411 ---------------------
 412  {{5,6},{1,2},{3,4}}
 413 (1 row)
 414 </programlisting>
 415  </para>
 416
 417  <para>
 418   The concatenation operator allows a single element to be pushed on to the
 419   beginning or end of a one-dimensional array. It also accepts two
 420   <replaceable>N</>-dimensional arrays, or an <replaceable>N</>-dimensional
 421   and an <replaceable>N+1</>-dimensional array.
 422  </para>
 423
 424  <para>
 425   When a single element is pushed on to either the beginning or end of a
 426   one-dimensional array, the result is an array with the same lower bound
 427   subscript as the array operand. For example:
 428 <programlisting>
 429 SELECT array_dims(1 || '[0:1]={2,3}'::int[]);
 430  array_dims
 431 ------------
 432  [0:2]
 433 (1 row)
 434
 435 SELECT array_dims(ARRAY[1,2] || 3);
 436  array_dims
 437 ------------
 438  [1:3]
 439 (1 row)
 440 </programlisting>
 441  </para>
 442
 443  <para>
 444   When two arrays with an equal number of dimensions are concatenated, the
 445   result retains the lower bound subscript of the left-hand operand's outer
 446   dimension. The result is an array comprising every element of the left-hand
 447   operand followed by every element of the right-hand operand. For example:
 448 <programlisting>
 449 SELECT array_dims(ARRAY[1,2] || ARRAY[3,4,5]);
 450  array_dims
 451 ------------
 452  [1:5]
 453 (1 row)
 454
 455 SELECT array_dims(ARRAY[[1,2],[3,4]] || ARRAY[[5,6],[7,8],[9,0]]);
 456  array_dims
 457 ------------
 458  [1:5][1:2]
 459 (1 row)
 460 </programlisting>
 461  </para>
 462
 463  <para>
 464   When an <replaceable>N</>-dimensional array is pushed on to the beginning
 465   or end of an <replaceable>N+1</>-dimensional array, the result is
 466   analogous to the element-array case above. Each <replaceable>N</>-dimensional
 467   sub-array is essentially an element of the <replaceable>N+1</>-dimensional
 468   array's outer dimension. For example:
 469 <programlisting>
 470 SELECT array_dims(ARRAY[1,2] || ARRAY[[3,4],[5,6]]);
 471  array_dims
 472 ------------
 473  [1:3][1:2]
 474 (1 row)
 475 </programlisting>
 476  </para>
 477
 478  <para>
 479   An array can also be constructed by using the functions
 480   <function>array_prepend</function>, <function>array_append</function>,
 481   or <function>array_cat</function>. The first two only support one-dimensional
 482   arrays, but <function>array_cat</function> supports multidimensional arrays.
 483
 484   Note that the concatenation operator discussed above is preferred over
 485   direct use of these functions. In fact, the functions exist primarily for use
 486   in implementing the concatenation operator. However, they might be directly
 487   useful in the creation of user-defined aggregates. Some examples:
 488
 489 <programlisting>
 490 SELECT array_prepend(1, ARRAY[2,3]);
 491  array_prepend
 492 ---------------
 493  {1,2,3}
 494 (1 row)
 495
 496 SELECT array_append(ARRAY[1,2], 3);
 497  array_append
 498 --------------
 499  {1,2,3}
 500 (1 row)
 501
 502 SELECT array_cat(ARRAY[1,2], ARRAY[3,4]);
 503  array_cat
 504 -----------
 505  {1,2,3,4}
 506 (1 row)
 507
 508 SELECT array_cat(ARRAY[[1,2],[3,4]], ARRAY[5,6]);
 509       array_cat
 510 ---------------------
 511  {{1,2},{3,4},{5,6}}
 512 (1 row)
 513
 514 SELECT array_cat(ARRAY[5,6], ARRAY[[1,2],[3,4]]);
 515       array_cat
 516 ---------------------
 517  {{5,6},{1,2},{3,4}}
 518 </programlisting>
 519  </para>
 520  </sect2>
 521
 522  <sect2 id="arrays-searching">
 523   <title>Searching in Arrays</title>
 524
 525   <indexterm>
 526    <primary>array</primary>
 527    <secondary>searching</secondary>
 528   </indexterm>
 529
 530  <para>
 531   To search for a value in an array, you must check each value of the
 532   array. This can be done by hand, if you know the size of the array.
 533   For example:
 534
 535 <programlisting>
 536 SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR
 537                             pay_by_quarter[2] = 10000 OR
 538                             pay_by_quarter[3] = 10000 OR
 539                             pay_by_quarter[4] = 10000;
 540 </programlisting>
 541
 542   However, this quickly becomes tedious for large arrays, and is not
 543   helpful if the size of the array is uncertain. An alternative method is
 544   described in <xref linkend="functions-comparisons">. The above
 545   query could be replaced by:
 546
 547 <programlisting>
 548 SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter);
 549 </programlisting>
 550
 551   In addition, you could find rows where the array had all values
 552   equal to 10000 with:
 553
 554 <programlisting>
 555 SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);
 556 </programlisting>
 557
 558  </para>
 559
 560  <para>
 561   Alternatively, the <function>generate_subscripts</> function can be used.
 562   For example:
 563
 564 <programlisting>
 565 SELECT * FROM
 566    (SELECT pay_by_quarter,
 567            generate_subscripts(pay_by_quarter, 1) AS s
 568       FROM sal_emp) AS foo
 569  WHERE pay_by_quarter[s] = 10000;
 570 </programlisting>
 571
 572   This function is described in <xref linkend="functions-srf-subscripts">.
 573  </para>
 574
 575  <tip>
 576   <para>
 577    Arrays are not sets; searching for specific array elements
 578    can be a sign of database misdesign.  Consider
 579    using a separate table with a row for each item that would be an
 580    array element.  This will be easier to search, and is likely to
 581    scale up better to large numbers of elements.
 582   </para>
 583  </tip>
 584  </sect2>
 585
 586  <sect2 id="arrays-io">
 587   <title>Array Input and Output Syntax</title>
 588
 589   <indexterm>
 590    <primary>array</primary>
 591    <secondary>I/O</secondary>
 592   </indexterm>
 593
 594   <para>
 595    The external text representation of an array value consists of items that
 596    are interpreted according to the I/O conversion rules for the array's
 597    element type, plus decoration that indicates the array structure.
 598    The decoration consists of curly braces (<literal>{</> and <literal>}</>)
 599    around the array value plus delimiter characters between adjacent items.
 600    The delimiter character is usually a comma (<literal>,</>) but can be
 601    something else: it is determined by the <literal>typdelim</> setting
 602    for the array's element type.  (Among the standard data types provided
 603    in the <productname>PostgreSQL</productname> distribution, type
 604    <literal>box</> uses a semicolon (<literal>;</>) but all the others
 605    use comma.)  In a multidimensional array, each dimension (row, plane,
 606    cube, etc.) gets its own level of curly braces, and delimiters
 607    must be written between adjacent curly-braced entities of the same level.
 608   </para>
 609
 610   <para>
 611    The array output routine will put double quotes around element values
 612    if they are empty strings, contain curly braces, delimiter characters,
 613    double quotes, backslashes, or white space, or match the word
 614    <literal>NULL</>.  Double quotes and backslashes
 615    embedded in element values will be backslash-escaped.  For numeric
 616    data types it is safe to assume that double quotes will never appear, but
 617    for textual data types one should be prepared to cope with either presence
 618    or absence of quotes.
 619   </para>
 620
 621   <para>
 622    By default, the lower bound index value of an array's dimensions is
 623    set to one.  To represent arrays with other lower bounds, the array
 624    subscript ranges can be specified explicitly before writing the
 625    array contents.
 626    This decoration consists of square brackets (<literal>[]</>)
 627    around each array dimension's lower and upper bounds, with
 628    a colon (<literal>:</>) delimiter character in between. The
 629    array dimension decoration is followed by an equal sign (<literal>=</>).
 630    For example:
 631 <programlisting>
 632 SELECT f1[1][-2][3] AS e1, f1[1][-1][5] AS e2
 633  FROM (SELECT '[1:1][-2:-1][3:5]={{{1,2,3},{4,5,6}}}'::int[] AS f1) AS ss;
 634
 635  e1 | e2
 636 ----+----
 637   1 |  6
 638 (1 row)
 639 </programlisting>
 640    The array output routine will include explicit dimensions in its result
 641    only when there are one or more lower bounds different from one.
 642   </para>
 643
 644   <para>
 645    If the value written for an element is <literal>NULL</> (in any case
 646    variant), the element is taken to be NULL.  The presence of any quotes
 647    or backslashes disables this and allows the literal string value
 648    <quote>NULL</> to be entered.  Also, for backwards compatibility with
 649    pre-8.2 versions of <productname>PostgreSQL</>, the <xref
 650    linkend="guc-array-nulls"> configuration parameter might be turned
 651    <literal>off</> to suppress recognition of <literal>NULL</> as a NULL.
 652   </para>
 653
 654   <para>
 655    As shown previously, when writing an array value you can write double
 656    quotes around any individual array element. You <emphasis>must</> do so
 657    if the element value would otherwise confuse the array-value parser.
 658    For example, elements containing curly braces, commas (or whatever the
 659    delimiter character is), double quotes, backslashes, or leading or trailing
 660    whitespace must be double-quoted.  Empty strings and strings matching the
 661    word <literal>NULL</> must be quoted, too.  To put a double quote or
 662    backslash in a quoted array element value, use escape string syntax
 663    and precede it with a backslash. Alternatively, you can use
 664    backslash-escaping to protect all data characters that would otherwise
 665    be taken as array syntax.
 666   </para>
 667
 668   <para>
 669    You can write whitespace before a left brace or after a right
 670    brace. You can also write whitespace before or after any individual item
 671    string. In all of these cases the whitespace will be ignored. However,
 672    whitespace within double-quoted elements, or surrounded on both sides by
 673    non-whitespace characters of an element, is not ignored.
 674   </para>
 675
 676  <note>
 677   <para>
 678    Remember that what you write in an SQL command will first be interpreted
 679    as a string literal, and then as an array.  This doubles the number of
 680    backslashes you need.  For example, to insert a <type>text</> array
 681    value containing a backslash and a double quote, you'd need to write:
 682 <programlisting>
 683 INSERT ... VALUES (E'{"\\\\","\\""}');
 684 </programlisting>
 685    The escape string processor removes one level of backslashes, so that
 686    what arrives at the array-value parser looks like <literal>{"\\","\""}</>.
 687    In turn, the strings fed to the <type>text</> data type's input routine
 688    become <literal>\</> and <literal>"</> respectively.  (If we were working
 689    with a data type whose input routine also treated backslashes specially,
 690    <type>bytea</> for example, we might need as many as eight backslashes
 691    in the command to get one backslash into the stored array element.)
 692    Dollar quoting (see <xref linkend="sql-syntax-dollar-quoting">) can be
 693    used to avoid the need to double backslashes.
 694   </para>
 695  </note>
 696
 697  <tip>
 698   <para>
 699    The <literal>ARRAY</> constructor syntax (see
 700    <xref linkend="sql-syntax-array-constructors">) is often easier to work
 701    with than the array-literal syntax when writing array values in SQL
 702    commands. In <literal>ARRAY</>, individual element values are written the
 703    same way they would be written when not members of an array.
 704   </para>
 705  </tip>
 706  </sect2>
 707
 708 </sect1>