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