begriffs open source - ai-pg/blob - full-docs/html/arrays.html

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