Database Reference
In-Depth Information
You can convert delimited strings to an array with the
string_to_array
function, as
demonstrated in
Example 5-13
.
Example 5-13. Converting a delimited string to an array
SELECT
string_to_array
(
'ca.ma.tx'
,
'.'
)
As
estados
;
estados
----------
{ca,ma,tx}
array_agg
is a variant aggregate function that can take a set of any data type and convert
it to an array, as demonstrated in
Example 5-14
.
Example 5-14. Using array_agg
SELECT
array_agg
(
log_ts
ORDER
BY
log_ts
)
As
x
FROM
logs
WHERE
log_ts
BETWEEN
'2011-01-01'
::
timestamptz
AND
'2011-01-15'
::
timestamptz
;
x
------------------------------------------
{'2011-01-01', '2011-01-13', '2011-01-14'}
Referencing Elements in an Array
Elements in arrays are most commonly referenced using the index of the element. Post‐
greSQL array indexes start at 1. If you try to access an element above the upper bound,
you won't get an error—only
NULL
will be returned. The next example grabs the first
and last element of our array column:
SELECT
fact_subcats
[
1
]
AS
primero
,
fact_subcats
[
array_upper
(
fact_subcats
,
1
)]
As
segundo
FROM
census
.
lu_fact_types
;
We used the
array_upper
function to get the upper bound of the array. The second,
required parameter of the function indicates the dimension. In our case, our array is
one-dimensional, but PostgreSQL does support multidimensional arrays.
Array Slicing and Splicing
PostgreSQL also supports array slicing using the
start:end
syntax. It returns another
array that is a subarray of the original. For example, to return new arrays that just contain
elements 2 through 4 of each original array, type:
SELECT
fact_subcats
[
2
:
4
]
FROM
census
.
lu_fact_types
;
To glue two arrays together end to end, use the concatenation operator
||
:
SELECT
fact_subcats
[
1
:
2
]
||
fact_subcats
[
3
:
4
]
FROM
census
.
lu_fact_types
;
