This page lists the PostgreSQL functions and operators available in this library. Each category below links to its own page with the details.
See also:Query results for the PHP value a function result arrives as
Operator conflicts and usage notes
Some PostgreSQL operators have multiple meanings depending on the data types involved. This library provides specific DQL function names to avoid conflicts:
Operator
Array/JSON usage
Spatial usage
Text/pattern usage
@>
CONTAINS (arrays contain elements)
N/A (PostGIS defines no @>; see ~)
N/A
<@
IS_CONTAINED_BY (element in array)
N/A (PostGIS defines no <@; see @)
N/A
@
N/A
SPATIAL_CONTAINED_BY (bounding box contained)
N/A
~
N/A
SPATIAL_CONTAINS (bounding box contains)
REGEXP (text pattern matching)
&&
OVERLAPS (arrays/ranges overlap)
Works automatically with geometry/geography
N/A
Usage Guidelines:
Arrays/JSON: Use CONTAINS, IS_CONTAINED_BY, OVERLAPS for array and JSON operations → Array and JSON functions
Spatial: Use SPATIAL_CONTAINS, SPATIAL_CONTAINED_BY for explicit spatial bounding box operations → PostGIS spatial functions
Boolean operators: the containment, overlap and bounding box operators return booleans and must be compared with = TRUE or = FALSE in DQL
The rules behind these names, and the ones every function follows (= TRUE, FILTER and OVER, literals and parameters): Writing DQL
Function and operator categories
Each category has its own page:
Array and JSON functions
Array and JSON functions and operators: the array operators (@>, <@, &&), the JSON operators (->, ->>, #>, #>>), and the array, JSON, JSONB and JSON path functions.
PostGIS spatial functions
PostGIS spatial functions and operators: the bounding box and distance operators, and the accessor, constructor, relationship, measurement, overlay, processing, editor and linear referencing functions.
Text and pattern functions
Text and pattern functions and operators: the text operators (~, ilike, @@), regular expressions, text processing, full-text search, fuzzy string matching, and hashing and checksums.
Mathematical and statistical functions: arithmetic, rounding, trigonometry, random numbers, the bitwise and boolean aggregates (BIT_AND, BOOL_OR, EVERY, …) and the statistical aggregates (CORR, STDDEV, PERCENTILE_CONT, MODE, …).
Utility functions
Utility functions: type casting (CAST), formatting (TO_CHAR, TO_NUMBER), NULL checks (ERROR_ON_NULL), and UUID generation and inspection (GEN_RANDOM_UUID, UUIDV4, UUIDV7, UUID_EXTRACT_TIMESTAMP, UUID_EXTRACT_VERSION).
XML functions
XML functions: aggregation (XMLAGG), well-formedness checks (XML_IS_WELL_FORMED, XML_IS_WELL_FORMED_DOCUMENT, XML_IS_WELL_FORMED_CONTENT), construction (XMLTEXT, XMLCOMMENT, XMLCONCAT, XMLPI), serialization (XMLSERIALIZE) and XPath (XPATH, XPATH_EXISTS, XMLEXISTS).
Window functions
Window functions: OVER for running any aggregate over a window, with frame clauses, the ranking functions ROW_NUMBER, RANK, DENSE_RANK, PERCENT_RANK, CUME_DIST and NTILE, and the value functions LAG, LEAD, FIRST_VALUE, LAST_VALUE and NTH_VALUE.
Network address functions
Network address functions: taking an inet or cidr value apart (HOST, BROADCAST, NETWORK, NETMASK, HOSTMASK), reading it (FAMILY, MASKLEN, ABBREV), combining two (INET_MERGE, INET_SAME_FAMILY) and changing the mask (SET_MASKLEN).
Hstore functions: keys and values as arrays or sets (HSTORE_AKEYS, HSTORE_SKEYS, HSTORE_AVALS, HSTORE_SVALS), HSTORE_DEFINED, HSTORE_DELETE, and conversion to JSON (HSTORE_TO_JSON, HSTORE_TO_JSON_LOOSE).
Ltree functions
PostgreSQL ltree types: extracting subpaths, path length and position, the longest common ancestor, conversion to and from text, and the lquery and ltxtquery match operators.
