Database Reference
In-Depth Information
dt | mh | tm
-----------------------+----+----------
2012-03-11 00:30:00-05 | 0 | 12:30 AM
2012-03-11 00:45:00-05 | 0 | 12:45 AM
2012-03-11 01:00:00-05 | 1 | 01:00 AM
2012-03-11 01:15:00-05 | 1 | 01:15 AM
2012-03-11 01:30:00-05 | 1 | 01:30 AM
2012-03-11 01:45:00-05 | 1 | 01:45 AM
2012-03-11 03:00:00-04 | 3 | 03:00 AM
By default,
generate_series
assumes
timestamptz
if you don't explicitly cast values to
timestamp
.
Arrays
Arrays play an important role in PostgreSQL. They are particularly useful in building
aggregate functions, forming
IN
and
ANY
clauses, and holding intermediary values for
morphing to other data types. In PostgreSQL, every data type has a companion array
type. If you define your own data type, PostgreSQL creates a corresponding array type
in the background for you. For example,
integer
has an integer array type
integer[]
,
character
has a character array type
character[]
, and so forth. We'll show you some
useful functions to construct arrays short of typing them in manually. We will then point
out some handy functions for array manipulations. You can get the complete listing of
array functions and operators in the
Official Manual: Array Functions and Operators
.
Array Constructors
The most rudimentary way to create an array is to type the elements:
SELECT
ARRAY
[
2001
,
2002
,
2003
]
As
yrs
;
If the elements of your array can be extracted from a query, you can use the more
sophisticated constructor function:
array()
:
SELECT
array
(
SELECT
DISTINCT
date_part
(
'year'
,
log_ts
)
FROM
logs
ORDER
BY
date_part
(
'year'
,
log_ts
)
);
Although the
array
function has to be used with a query returning a single column,
you can specify a composite type as the output, thereby achieving multicolumn results.
We demonstrate this in
“Custom and Composite Data Types” on page 103
.
You can cast a string representation of an array to an array with syntax of the form:
SELECT
'{Alex,Sonia}'
::
text
[]
As
name
,
'{43,40}'
::
smallint
[]
As
age
;
name | age
-------------+--------
{Alex,Sonia} | {43,40}
