Database Reference
In-Depth Information
Range Operators
Two range operators tend to be used more often than all others: overlap (
&&
) and con‐
Overlap operator
As the name suggests, the overlap operator
&&
returns
true
if two ranges have any values
in common.
Example 5-21
demonstrates this operator as well as putting to use the
string_agg
function for aggregating the list of employees into a single text field.
Example 5-21. Who worked with whom?
SELECT
e1
.
employee
,
string_agg
(
DISTINCT
e2
.
employee
,
', '
ORDER
BY
e2
.
employee
)
As
colleagues
FROM
employment
As
e1
INNER
JOIN
employment
As
e2
ON
e1
.
period
&&
e2
.
period
WHERE
e1
.
employee
<>
e2
.
employee
GROUP
BY
e1
.
employee
;
employee | colleagues
----------+------------------
Alex | Leo, Regina, Sonia
Leo | Alex, Regina
Regina | Alex, Leo
Sonia | Alex
Contains and contained in operators
In the contains operator (
@>
), the first argument is a range and the second is a value. If
the second is within the first, the contains operator returns true.
Example 5-22
dem‐
onstrates its use.
Example 5-22. Who is currently working?
SELECT
employee
FROM
employment
WHERE
period
@>
CURRENT_DATE
GROUP
BY
employee
;
employee
----
Alex
The reverse of the contains operator is the contained operator (
<@
), whose first argument
is the value and the second the range.
JSON
JSON
data type and support functions came on the scene with version 9.2. JSON is a
popular data type for web applications, as it serves as the lingua franca for data in
JavaScript. Version 9.3 significantly beefed up JSON support with new functions for
extracting, editing, and casting to other data types. Version 9.4 introduced the
jsonb
