Now, we can run some queries on the table. First, we show how to access a single element of an array. This query retrieves the names of employees whose salary changed in the second quarter:
SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2]; name – Carol (1 row)
The subscript numbers in the array are enclosed in square brackets. By default, PostgreSQL uses a one-based numbering convention for arrays, that is, an array of n elements begins with array[1] and ends with array[n].
This consultation recovers the salary of the third quarter of all employees:
SELECT pay_by_quarter[3] FROM sal_emp; pay_by_quarter – 10000 25000 (2 rows)
We can also access arbitrary rectangular segments of a matrix or submatrices. An array segment is denoted by typing lower-bound:upper-bound for one or more array dimensions. For example, this query retrieves the first item of Bill’s schedule for the first two days of the week
: SELECT schedule[1:2][1:1] FROM sal_emp WHERE name = ‘Bill’; schedule – {{meeting},{training}} (1 row)
If some dimension is written as a slice, i.e. it contains colons, then all dimensions are treated as slices. Any dimension that has a single number (without a colon) is treated as if it were from 1 to the specified number. For example,
[2] is treated as [1:2], as in this example: SELECT schedule[1:2][2] FROM sal_emp WHERE name = ‘Bill’; Schedule – {{meeting,lunch},{training, presentation}} (1 row)
To avoid confusion with the non-cut case, it is best to use the cut-off syntax for all dimensions, for example,
[1:2][1:1], not [2][1:1].
You can omit the lower and/or upper limit of a sector specifier; The missing boundary is replaced by the lower or upper bound of the matrix subscripts. For example
: SELECT schedule[:2][2:] FROM sal_emp WHERE name = ‘Bill’; schedule – {{lunch},{presentation}} (1 row) SELECT schedule[:][1:1] FROM sal_emp WHERE name = ‘Bill’; schedule – {{meeting},{training}} (1 row) An array subscript expression
will return null if the array itself or any of the subscript expressions are null. Also, null is returned if a subscript is outside the boundaries of the array (this case does not generate an error). For example, if the programming currently has the dimensions [1:3][1:2], the reference to the programming[3][3] produces NULL. Similarly, an array reference with the wrong number of subscripts produces a null value instead of an error.
An array segment expression is also null if the array itself or any of the subscript expressions are null. However, in other cases, such as selecting an array segment that is completely outside the current array boundaries, a segment expression produces an empty (zero-dimensional) array instead of null. (This does not match the behavior without division and is done for historical reasons.) If the requested sector partially overlaps the array boundaries, it silently shrinks to just the overlapping region instead of returning null.
The current dimensions of any array value can be retrieved with the array_dims function:
SELECT array_dims(schedule) FROM sal_emp WHERE name = ‘Carol’; array_dims – [1:2][1:2] (1 row)
array_dims produces a text result, which is convenient for people to read, but perhaps inconvenient for programs. Dimensions can also be retrieved with array_upper and array_lower, which return the upper and lower bound of a specified array dimension, respectively: SELECT array_upper
(schedule, 1) FROM sal_emp WHERE name = ‘Carol’; array_upper – 2 (1 row) array_length
will return the length of a specified array dimension: SELECT array_length
(schedule, 1) FROM sal_emp WHERE name = ‘Carol’; array_length – 2 (1 row)
cardinality returns the total number of elements in an array in all dimensions. This is indeed the number of rows that a call to unnest would produce
: SELECT cardinality(schedule) FROM sal_emp WHERE name = ‘Carol’; cardinality – 4 (1 row)
