codekingpro/portable-devtools
115k
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] <> 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">&&</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 && 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>