Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes15kdownloads
arrays.html647 linesDownload Raw Back to 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 16.3 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 be4  defined as variable-length multidimensional arrays. Arrays of any5  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">10CREATE 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 brackets17  (<code class="literal">[]</code>) to the data type name of the array elements.  The18  above command will create a table named19  <code class="structname">sal_emp</code> with a column of type20  <code class="type">text</code> (<code class="structfield">name</code>), a21  one-dimensional array of type <code class="type">integer</code>22  (<code class="structfield">pay_by_quarter</code>), which represents the23  employee's salary by quarter, and a two-dimensional array of24  <code class="type">text</code> (<code class="structfield">schedule</code>), which25  represents the employee's weekly schedule.26 </p><p>27  The syntax for <code class="command">CREATE TABLE</code> allows the exact size of28  arrays to be specified, for example:29 30</p><pre class="programlisting">31CREATE TABLE tictactoe (32    squares   integer[3][3]33);34</pre><p>35 36  However, the current implementation ignores any supplied array size37  limits, i.e., the behavior is the same as for arrays of unspecified38  length.39 </p><p>40  The current implementation does not enforce the declared41  number of dimensions either.  Arrays of a particular element type are42  all considered to be of the same type, regardless of size or number43  of dimensions.  So, declaring the array size or number of dimensions in44  <code class="command">CREATE TABLE</code> is simply documentation; it does not45  affect run-time behavior.46 </p><p>47  An alternative syntax, which conforms to the SQL standard by using48  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 defined50  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 the59  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 element62   values within curly braces and separate them by commas.  (If you63   know C, this is not unlike the C syntax for initializing64   structures.)  You can put double quotes around any element value,65   and must do so if it contains commas or curly braces.  (More66   details appear below.)  Thus, the general format of an array67   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 character72   for the type, as recorded in its <code class="literal">pg_type</code> entry.73   Among the standard data types provided in the74   <span class="productname">PostgreSQL</span> distribution, all use a comma75   (<code class="literal">,</code>), except for type <code class="type">box</code> which uses a semicolon76   (<code class="literal">;</code>). Each <em class="replaceable"><code>val</code></em> is77   either a constant of the array element type, or a subarray. An example78   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 of83   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 of87   <code class="literal">NULL</code> will do.)  If you want an actual string value88   <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 of91   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 initially92   treated as a string and passed to the array input conversion93   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">98INSERT INTO sal_emp99    VALUES ('Bill',100    '{10000, 10000, 10000, 10000}',101    '{{"meeting", "lunch"}, {"training", "presentation"}}');102 103INSERT INTO sal_emp104    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">112SELECT * FROM sal_emp;113 name  |      pay_by_quarter       |                 schedule114-------+---------------------------+-------------------------------------------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 each121  dimension. A mismatch causes an error, for example:122 123</p><pre class="programlisting">124INSERT INTO sal_emp125    VALUES ('Bill',126    '{10000, 10000, 10000, 10000}',127    '{{"meeting", "lunch"}, {"meeting"}}');128ERROR:  multidimensional arrays must have array expressions with matching dimensions129</pre><p>130 </p><p>131  The <code class="literal">ARRAY</code> constructor syntax can also be used:132</p><pre class="programlisting">133INSERT INTO sal_emp134    VALUES ('Bill',135    ARRAY[10000, 10000, 10000, 10000],136    ARRAY[['meeting', 'lunch'], ['training', 'presentation']]);137 138INSERT INTO sal_emp139    VALUES ('Carol',140    ARRAY[20000, 25000, 25000, 25000],141    ARRAY[['breakfast', 'consulting'], ['meeting', 'lunch']]);142</pre><p>143  Notice that the array elements are ordinary SQL constants or144  expressions; for instance, string literals are single quoted, instead of145  double quoted as they would be in an array literal.  The <code class="literal">ARRAY</code>146  constructor syntax is discussed in more detail in147  <a class="xref" href="sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS" title="4.2.12. Array Constructors">Section 4.2.12</a>.148 </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>149  Now, we can run some queries on the table.150  First, we show how to access a single element of an array.151  This query retrieves the names of the employees whose pay changed in152  the second quarter:153 154</p><pre class="programlisting">155SELECT name FROM sal_emp WHERE pay_by_quarter[1] &lt;&gt; pay_by_quarter[2];156 157 name158-------159 Carol160(1 row)161</pre><p>162 163  The array subscript numbers are written within square brackets.164  By default <span class="productname">PostgreSQL</span> uses a165  one-based numbering convention for arrays, that is,166  an array of <em class="replaceable"><code>n</code></em> elements starts with <code class="literal">array[1]</code> and167  ends with <code class="literal">array[<em class="replaceable"><code>n</code></em>]</code>.168 </p><p>169  This query retrieves the third quarter pay of all employees:170 171</p><pre class="programlisting">172SELECT pay_by_quarter[3] FROM sal_emp;173 174 pay_by_quarter175----------------176          10000177          25000178(2 rows)179</pre><p>180 </p><p>181  We can also access arbitrary rectangular slices of an array, or182  subarrays.  An array slice is denoted by writing183  <code class="literal"><em class="replaceable"><code>lower-bound</code></em>:<em class="replaceable"><code>upper-bound</code></em></code>184  for one or more array dimensions.  For example, this query retrieves the first185  item on Bill's schedule for the first two days of the week:186 187</p><pre class="programlisting">188SELECT schedule[1:2][1:1] FROM sal_emp WHERE name = 'Bill';189 190        schedule191------------------------192 {{meeting},{training}}193(1 row)194</pre><p>195 196  If any dimension is written as a slice, i.e., contains a colon, then all197  dimensions are treated as slices.  Any dimension that has only a single198  number (no colon) is treated as being from 1199  to the number specified.  For example, <code class="literal">[2]</code> is treated as200  <code class="literal">[1:2]</code>, as in this example:201 202</p><pre class="programlisting">203SELECT schedule[1:2][2] FROM sal_emp WHERE name = 'Bill';204 205                 schedule206-------------------------------------------207 {{meeting,lunch},{training,presentation}}208(1 row)209</pre><p>210 211  To avoid confusion with the non-slice case, it's best to use slice syntax212  for all dimensions, e.g., <code class="literal">[1:2][1:1]</code>, not <code class="literal">[2][1:1]</code>.213 </p><p>214  It is possible to omit the <em class="replaceable"><code>lower-bound</code></em> and/or215  <em class="replaceable"><code>upper-bound</code></em> of a slice specifier; the missing216  bound is replaced by the lower or upper limit of the array's subscripts.217  For example:218 219</p><pre class="programlisting">220SELECT schedule[:2][2:] FROM sal_emp WHERE name = 'Bill';221 222        schedule223------------------------224 {{lunch},{presentation}}225(1 row)226 227SELECT schedule[:][1:1] FROM sal_emp WHERE name = 'Bill';228 229        schedule230------------------------231 {{meeting},{training}}232(1 row)233</pre><p>234 </p><p>235  An array subscript expression will return null if either the array itself or236  any of the subscript expressions are null.  Also, null is returned if a237  subscript is outside the array bounds (this case does not raise an error).238  For example, if <code class="literal">schedule</code>239  currently has the dimensions <code class="literal">[1:3][1:2]</code> then referencing240  <code class="literal">schedule[3][3]</code> yields NULL.  Similarly, an array reference241  with the wrong number of subscripts yields a null rather than an error.242 </p><p>243  An array slice expression likewise yields null if the array itself or244  any of the subscript expressions are null.  However, in other245  cases such as selecting an array slice that246  is completely outside the current array bounds, a slice expression247  yields an empty (zero-dimensional) array instead of null.  (This248  does not match non-slice behavior and is done for historical reasons.)249  If the requested slice partially overlaps the array bounds, then it250  is silently reduced to just the overlapping region instead of251  returning null.252 </p><p>253  The current dimensions of any array value can be retrieved with the254  <code class="function">array_dims</code> function:255 256</p><pre class="programlisting">257SELECT array_dims(schedule) FROM sal_emp WHERE name = 'Carol';258 259 array_dims260------------261 [1:2][1:2]262(1 row)263</pre><p>264 265  <code class="function">array_dims</code> produces a <code class="type">text</code> result,266  which is convenient for people to read but perhaps inconvenient267  for programs.  Dimensions can also be retrieved with268  <code class="function">array_upper</code> and <code class="function">array_lower</code>,269  which return the upper and lower bound of a270  specified array dimension, respectively:271 272</p><pre class="programlisting">273SELECT array_upper(schedule, 1) FROM sal_emp WHERE name = 'Carol';274 275 array_upper276-------------277           2278(1 row)279</pre><p>280 281 <code class="function">array_length</code> will return the length of a specified282 array dimension:283 284</p><pre class="programlisting">285SELECT array_length(schedule, 1) FROM sal_emp WHERE name = 'Carol';286 287 array_length288--------------289            2290(1 row)291</pre><p>292 293 <code class="function">cardinality</code> returns the total number of elements in an294 array across all dimensions.  It is effectively the number of rows a call to295 <code class="function">unnest</code> would yield:296 297</p><pre class="programlisting">298SELECT cardinality(schedule) FROM sal_emp WHERE name = 'Carol';299 300 cardinality301-------------302           4303(1 row)304</pre><p>305 </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>306  An array value can be replaced completely:307 308</p><pre class="programlisting">309UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'310    WHERE name = 'Carol';311</pre><p>312 313  or using the <code class="literal">ARRAY</code> expression syntax:314 315</p><pre class="programlisting">316UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000]317    WHERE name = 'Carol';318</pre><p>319 320  An array can also be updated at a single element:321 322</p><pre class="programlisting">323UPDATE sal_emp SET pay_by_quarter[4] = 15000324    WHERE name = 'Bill';325</pre><p>326 327  or updated in a slice:328 329</p><pre class="programlisting">330UPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}'331    WHERE name = 'Carol';332</pre><p>333 334  The slice syntaxes with omitted <em class="replaceable"><code>lower-bound</code></em> and/or335  <em class="replaceable"><code>upper-bound</code></em> can be used too, but only when336  updating an array value that is not NULL or zero-dimensional (otherwise,337  there is no existing subscript limit to substitute).338 </p><p>339  A stored array value can be enlarged by assigning to elements not already340  present.  Any positions between those previously present and the newly341  assigned elements will be filled with nulls.  For example, if array342  <code class="literal">myarray</code> currently has 4 elements, it will have six343  elements after an update that assigns to <code class="literal">myarray[6]</code>;344  <code class="literal">myarray[5]</code> will contain null.345  Currently, enlargement in this fashion is only allowed for one-dimensional346  arrays, not multidimensional arrays.347 </p><p>348  Subscripted assignment allows creation of arrays that do not use one-based349  subscripts.  For example one might assign to <code class="literal">myarray[-2:7]</code> to350  create an array with subscript values from -2 to 7.351 </p><p>352  New array values can also be constructed using the concatenation operator,353  <code class="literal">||</code>:354</p><pre class="programlisting">355SELECT ARRAY[1,2] || ARRAY[3,4];356 ?column?357-----------358 {1,2,3,4}359(1 row)360 361SELECT ARRAY[5,6] || ARRAY[[1,2],[3,4]];362      ?column?363---------------------364 {{5,6},{1,2},{3,4}}365(1 row)366</pre><p>367 </p><p>368  The concatenation operator allows a single element to be pushed onto the369  beginning or end of a one-dimensional array. It also accepts two370  <em class="replaceable"><code>N</code></em>-dimensional arrays, or an <em class="replaceable"><code>N</code></em>-dimensional371  and an <em class="replaceable"><code>N+1</code></em>-dimensional array.372 </p><p>373  When a single element is pushed onto either the beginning or end of a374  one-dimensional array, the result is an array with the same lower bound375  subscript as the array operand. For example:376</p><pre class="programlisting">377SELECT array_dims(1 || '[0:1]={2,3}'::int[]);378 array_dims379------------380 [0:2]381(1 row)382 383SELECT array_dims(ARRAY[1,2] || 3);384 array_dims385------------386 [1:3]387(1 row)388</pre><p>389 </p><p>390  When two arrays with an equal number of dimensions are concatenated, the391  result retains the lower bound subscript of the left-hand operand's outer392  dimension. The result is an array comprising every element of the left-hand393  operand followed by every element of the right-hand operand. For example:394</p><pre class="programlisting">395SELECT array_dims(ARRAY[1,2] || ARRAY[3,4,5]);396 array_dims397------------398 [1:5]399(1 row)400 401SELECT array_dims(ARRAY[[1,2],[3,4]] || ARRAY[[5,6],[7,8],[9,0]]);402 array_dims403------------404 [1:5][1:2]405(1 row)406</pre><p>407 </p><p>408  When an <em class="replaceable"><code>N</code></em>-dimensional array is pushed onto the beginning409  or end of an <em class="replaceable"><code>N+1</code></em>-dimensional array, the result is410  analogous to the element-array case above. Each <em class="replaceable"><code>N</code></em>-dimensional411  sub-array is essentially an element of the <em class="replaceable"><code>N+1</code></em>-dimensional412  array's outer dimension. For example:413</p><pre class="programlisting">414SELECT array_dims(ARRAY[1,2] || ARRAY[[3,4],[5,6]]);415 array_dims416------------417 [1:3][1:2]418(1 row)419</pre><p>420 </p><p>421  An array can also be constructed by using the functions422  <code class="function">array_prepend</code>, <code class="function">array_append</code>,423  or <code class="function">array_cat</code>. The first two only support one-dimensional424  arrays, but <code class="function">array_cat</code> supports multidimensional arrays.425  Some examples:426 427</p><pre class="programlisting">428SELECT array_prepend(1, ARRAY[2,3]);429 array_prepend430---------------431 {1,2,3}432(1 row)433 434SELECT array_append(ARRAY[1,2], 3);435 array_append436--------------437 {1,2,3}438(1 row)439 440SELECT array_cat(ARRAY[1,2], ARRAY[3,4]);441 array_cat442-----------443 {1,2,3,4}444(1 row)445 446SELECT array_cat(ARRAY[[1,2],[3,4]], ARRAY[5,6]);447      array_cat448---------------------449 {{1,2},{3,4},{5,6}}450(1 row)451 452SELECT array_cat(ARRAY[5,6], ARRAY[[1,2],[3,4]]);453      array_cat454---------------------455 {{5,6},{1,2},{3,4}}456</pre><p>457 </p><p>458  In simple cases, the concatenation operator discussed above is preferred459  over direct use of these functions.  However, because the concatenation460  operator is overloaded to serve all three cases, there are situations where461  use of one of the functions is helpful to avoid ambiguity.  For example462  consider:463 464</p><pre class="programlisting">465SELECT ARRAY[1, 2] || '{3, 4}';  -- the untyped literal is taken as an array466 ?column?467-----------468 {1,2,3,4}469 470SELECT ARRAY[1, 2] || '7';                 -- so is this one471ERROR:  malformed array literal: "7"472 473SELECT ARRAY[1, 2] || NULL;                -- so is an undecorated NULL474 ?column?475----------476 {1,2}477(1 row)478 479SELECT array_append(ARRAY[1, 2], NULL);    -- this might have been meant480 array_append481--------------482 {1,2,NULL}483</pre><p>484 485  In the examples above, the parser sees an integer array on one side of the486  concatenation operator, and a constant of undetermined type on the other.487  The heuristic it uses to resolve the constant's type is to assume it's of488  the same type as the operator's other input — in this case,489  integer array.  So the concatenation operator is presumed to490  represent <code class="function">array_cat</code>, not <code class="function">array_append</code>.  When491  that's the wrong choice, it could be fixed by casting the constant to the492  array's element type; but explicit use of <code class="function">array_append</code> might493  be a preferable solution.494 </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>495  To search for a value in an array, each value must be checked.496  This can be done manually, if you know the size of the array.497  For example:498 499</p><pre class="programlisting">500SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR501                            pay_by_quarter[2] = 10000 OR502                            pay_by_quarter[3] = 10000 OR503                            pay_by_quarter[4] = 10000;504</pre><p>505 506  However, this quickly becomes tedious for large arrays, and is not507  helpful if the size of the array is unknown. An alternative method is508  described in <a class="xref" href="functions-comparisons.html" title="9.24. Row and Array Comparisons">Section 9.24</a>. The above509  query could be replaced by:510 511</p><pre class="programlisting">512SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter);513</pre><p>514 515  In addition, you can find rows where the array has all values516  equal to 10000 with:517 518</p><pre class="programlisting">519SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);520</pre><p>521 522 </p><p>523  Alternatively, the <code class="function">generate_subscripts</code> function can be used.524  For example:525 526</p><pre class="programlisting">527SELECT * FROM528   (SELECT pay_by_quarter,529           generate_subscripts(pay_by_quarter, 1) AS s530      FROM sal_emp) AS foo531 WHERE pay_by_quarter[s] = 10000;532</pre><p>533 534  This function is described in <a class="xref" href="functions-srf.html#FUNCTIONS-SRF-SUBSCRIPTS" title="Table 9.66. Subscript Generating Functions">Table 9.66</a>.535 </p><p>536  You can also search an array using the <code class="literal">&amp;&amp;</code> operator,537  which checks whether the left operand overlaps with the right operand.538  For instance:539 540</p><pre class="programlisting">541SELECT * FROM sal_emp WHERE pay_by_quarter &amp;&amp; ARRAY[10000];542</pre><p>543 544  This and other array operators are further described in545  <a class="xref" href="functions-array.html" title="9.19. Array Functions and Operators">Section 9.19</a>.  It can be accelerated by an appropriate546  index, as described in <a class="xref" href="indexes-types.html" title="11.2. Index Types">Section 11.2</a>.547 </p><p>548  You can also search for specific values in an array using the <code class="function">array_position</code>549  and <code class="function">array_positions</code> functions. The former returns the subscript of550  the first occurrence of a value in an array; the latter returns an array with the551  subscripts of all occurrences of the value in the array.  For example:552 553</p><pre class="programlisting">554SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat'], 'mon');555 array_position556----------------557              2558(1 row)559 560SELECT array_positions(ARRAY[1, 4, 3, 1, 3, 4, 2, 1], 1);561 array_positions562-----------------563 {1,4,8}564(1 row)565</pre><p>566 </p><div class="tip"><h3 class="title">Tip</h3><p>567   Arrays are not sets; searching for specific array elements568   can be a sign of database misdesign.  Consider569   using a separate table with a row for each item that would be an570   array element.  This will be easier to search, and is likely to571   scale better for a large number of elements.572  </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>573   The external text representation of an array value consists of items that574   are interpreted according to the I/O conversion rules for the array's575   element type, plus decoration that indicates the array structure.576   The decoration consists of curly braces (<code class="literal">{</code> and <code class="literal">}</code>)577   around the array value plus delimiter characters between adjacent items.578   The delimiter character is usually a comma (<code class="literal">,</code>) but can be579   something else: it is determined by the <code class="literal">typdelim</code> setting580   for the array's element type.  Among the standard data types provided581   in the <span class="productname">PostgreSQL</span> distribution, all use a comma,582   except for type <code class="type">box</code>, which uses a semicolon (<code class="literal">;</code>).583   In a multidimensional array, each dimension (row, plane,584   cube, etc.) gets its own level of curly braces, and delimiters585   must be written between adjacent curly-braced entities of the same level.586  </p><p>587   The array output routine will put double quotes around element values588   if they are empty strings, contain curly braces, delimiter characters,589   double quotes, backslashes, or white space, or match the word590   <code class="literal">NULL</code>.  Double quotes and backslashes591   embedded in element values will be backslash-escaped.  For numeric592   data types it is safe to assume that double quotes will never appear, but593   for textual data types one should be prepared to cope with either the presence594   or absence of quotes.595  </p><p>596   By default, the lower bound index value of an array's dimensions is597   set to one.  To represent arrays with other lower bounds, the array598   subscript ranges can be specified explicitly before writing the599   array contents.600   This decoration consists of square brackets (<code class="literal">[]</code>)601   around each array dimension's lower and upper bounds, with602   a colon (<code class="literal">:</code>) delimiter character in between. The603   array dimension decoration is followed by an equal sign (<code class="literal">=</code>).604   For example:605</p><pre class="programlisting">606SELECT f1[1][-2][3] AS e1, f1[1][-1][5] AS e2607 FROM (SELECT '[1:1][-2:-1][3:5]={{{1,2,3},{4,5,6}}}'::int[] AS f1) AS ss;608 609 e1 | e2610----+----611  1 |  6612(1 row)613</pre><p>614   The array output routine will include explicit dimensions in its result615   only when there are one or more lower bounds different from one.616  </p><p>617   If the value written for an element is <code class="literal">NULL</code> (in any case618   variant), the element is taken to be NULL.  The presence of any quotes619   or backslashes disables this and allows the literal string value620   <span class="quote">“<span class="quote">NULL</span>”</span> to be entered.  Also, for backward compatibility with621   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 turned622   <code class="literal">off</code> to suppress recognition of <code class="literal">NULL</code> as a NULL.623  </p><p>624   As shown previously, when writing an array value you can use double625   quotes around any individual array element. You <span class="emphasis"><em>must</em></span> do so626   if the element value would otherwise confuse the array-value parser.627   For example, elements containing curly braces, commas (or the data type's628   delimiter character), double quotes, backslashes, or leading or trailing629   whitespace must be double-quoted.  Empty strings and strings matching the630   word <code class="literal">NULL</code> must be quoted, too.  To put a double631   quote or backslash in a quoted array element value, precede it632   with a backslash. Alternatively, you can avoid quotes and use633   backslash-escaping to protect all data characters that would otherwise634   be taken as array syntax.635  </p><p>636   You can add whitespace before a left brace or after a right637   brace. You can also add whitespace before or after any individual item638   string. In all of these cases the whitespace will be ignored. However,639   whitespace within double-quoted elements, or surrounded on both sides by640   non-whitespace characters of an element, is not ignored.641  </p><div class="tip"><h3 class="title">Tip</h3><p>642   The <code class="literal">ARRAY</code> constructor syntax (see643   <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 work644   with than the array-literal syntax when writing array values in SQL645   commands. In <code class="literal">ARRAY</code>, individual element values are written the646   same way they would be written when not members of an array.647  </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 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 8.16. Composite Types</td></tr></table></div></body></html>
codekingpro/portable-devtools · Team Ai