Available Functions and Operators

This document provides an overview of PostgreSQL functions and operators available in this library. For detailed documentation of specific function categories, see the specialized documentation files linked below.

Operator Conflicts and Usage Notes

⚠️ Important: 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)	Works automatically with geometry/geography	N/A
`<@`	`IS_CONTAINED_BY` (element in array)	Works automatically with geometry/geography	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
Text: Use REGEXP, IREGEXP for pattern matching → Text and Pattern Functions
Boolean operators: All spatial operators return boolean values and should be used with = TRUE or = FALSE in DQL

📚 Function and Operator Categories

This library provides comprehensive PostgreSQL function and operator support organized into the following categories:

🔗 Array and JSON Functions

Complete documentation for array manipulation and JSON/JSONB operations.

Array and JSON Functions and Operators
Includes: Array operators (@>, <@, &&), JSON operators (->, ->>, #>, #>>), array functions, JSON functions, JSONB functions

🗺️ PostGIS Spatial Functions

Complete documentation for PostGIS spatial operations and geometry processing.

PostGIS Spatial Functions and Operators
Includes: Bounding box operators, distance operators, spatial relationship functions, measurement functions, overlay functions, geometry processing functions

📝 Text and Pattern Functions

Complete documentation for text processing, pattern matching, regular expressions, and hashing.

Text and Pattern Functions and Operators
Includes: Text operators (~, ilike, @@), regular expression functions, text processing functions, full-text search functions, fuzzy string matching functions, hashing and checksum functions

📅 Date and Range Functions

Complete documentation for date/time operations and range type functions.

Date, Time, and Range Functions
Includes: Date/time functions, range creation functions, range operators, temporal operations

🔢 Mathematical Functions

Complete documentation for mathematical operations.

Mathematical Functions
Includes: Mathematical functions, trigonometric functions, statistical functions

🔧 Utility Functions

Complete documentation for type conversion, formatting, and UUID functions.

Utility Functions
Includes: Type casting (cast), data formatting (to_char, to_number), UUID generation and inspection (uuidv4, uuidv7, uuid_extract_timestamp, uuid_extract_version)

📄 XML Functions

Complete documentation for PostgreSQL XML processing, validation, and XPath querying.

XML Functions
Includes: XML aggregation (xmlagg), XML validation (xml_is_well_formed, xml_is_well_formed_document, xml_is_well_formed_content), XML construction (xmltext, xmlcomment, xmlconcat), XPath querying (xpath, xpath_exists)

🪟 Window Functions

Complete documentation for PostgreSQL window functions, with OVER wrapping the call and its window specification, including frame clauses.

Window Functions
Includes: OVER for running any aggregate as a window function, the ranking functions ROW_NUMBER, RANK, DENSE_RANK, PERCENT_RANK, CUME_DIST, NTILE, and the value functions LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE

🌐 Network Address Functions

Complete documentation for PostgreSQL network address operations on inet and cidr types.

Network Address Functions
Includes: HOST, BROADCAST, NETWORK, NETMASK, HOSTMASK, FAMILY, MASKLEN, ABBREV, INET_MERGE, INET_SAME_FAMILY, SET_MASKLEN

🗃️ Hstore Functions (requires hstore extension)

Key-value store operations using the PostgreSQL hstore type.

Hstore Functions
Includes: HSTORE_AKEYS, HSTORE_AVALS, HSTORE_SKEYS, HSTORE_SVALS, HSTORE_TO_JSON, HSTORE_TO_JSON_LOOSE, HSTORE_DEFINED, HSTORE_DELETE

🌳 Ltree Functions

Complete documentation for PostgreSQL ltree (label tree) operations and hierarchical data processing.

Ltree Functions
Includes: Path manipulation functions, ancestor/descendant operations, type conversion functions

🔢 Vector Distance Functions (requires pgvector extension)

Distance functions for fixed-dimension float vectors stored with the vector type.

L2_DISTANCE — Euclidean (L2) distance between two vectors
COSINE_DISTANCE — Cosine distance between two vectors
INNER_PRODUCT — Inner (dot) product of two vectors

🚀 Quick Reference

Most Commonly Used Functions

Array Operations: (Complete documentation)

CONTAINS (@>) - Test if array/range contains elements
OVERLAPS (&&) - Test if arrays/ranges overlap
ARRAY_AGG - Aggregate values into arrays
FILTER - Restrict the rows an aggregate reads (FILTER (WHERE ...))

JSON Operations: (Complete documentation)

JSON_GET_FIELD_AS_TEXT (->>) - Extract JSON field as text
JSON_BUILD_OBJECT - Build JSON objects
JSONB_PATH_EXISTS - Test JSON path existence

Spatial Operations: (Complete documentation)

ST_INTERSECTS - Test if geometries intersect
ST_DISTANCE - Calculate distance between geometries
ST_CONTAINS - Test spatial containment

Text Operations: (Complete documentation)

ASCII - Get numeric code of first character
BTRIM/LTRIM/RTRIM - Trim characters from string ends
CASEFOLD - Advanced case-insensitive text comparison with Unicode support
CHR - Get character from code point
ILIKE - Case-insensitive pattern matching
INITCAP - Capitalize first letter of each word
LPAD/RPAD - Left/right pad a string to a given length
QUOTE_IDENT/QUOTE_LITERAL/QUOTE_NULLABLE - Quote SQL identifiers and literals
REGEXP (~) - Regular expression matching
STARTS_WITH - Test if text starts with a substring
STRPOS - Find position of substring
TRANSLATE - Replace characters in a string
LEVENSHTEIN - Calculate edit distance between strings (fuzzy matching)
SOUNDEX - Phonetic encoding for similarity matching
MD5/SHA256/SHA512 (and other SHA variants) - Cryptographic hashing
CRC32/CRC32C - CRC checksum computation
REVERSE_BYTES - Reverse byte order for bytea values

Date/Range Operations: (Complete documentation)

CLOCK_TIMESTAMP - Current timestamp at call time
DATE_ADD - Add interval to date
DATE_EXTRACT - Extract date components
DATERANGE - Create date ranges
ISFINITE - Test if date/timestamp/interval is finite
JUSTIFY_DAYS/JUSTIFY_HOURS/JUSTIFY_INTERVAL - Adjust interval representations
STATEMENT_TIMESTAMP - Timestamp of current SQL statement
TRANSACTION_TIMESTAMP - Timestamp of current transaction

Mathematical Operations: (Complete documentation)

DIV/GCD/LCM/FACTORIAL - Integer arithmetic functions
ERF/ERFC - Error and complementary error functions
GAMMA/LGAMMA - Gamma function for statistical calculations
GREATEST/LEAST - Find maximum/minimum values
ROUND/SCALE/MIN_SCALE/TRIM_SCALE - Numeric precision functions
RANDOM/RANDOM_NORMAL - Generate random numbers
Bitwise/Boolean Aggregates: BIT_AND, BIT_OR, BIT_XOR, BOOL_AND, BOOL_OR, EVERY
Statistical Aggregates: CORR, COVAR_POP, COVAR_SAMP, MODE, PERCENTILE_CONT, PERCENTILE_DISC, STDDEV, STDDEV_POP, VAR_POP, VARIANCE

Utility Functions: (Complete documentation)

CAST - General type conversion
TO_CHAR - Convert numbers and dates to formatted strings
TO_NUMBER - Parse formatted text as numbers
GEN_RANDOM_UUID - Generate a random UUID (version 4)
UUIDV4 - Explicit UUID version 4 generation
UUIDV7 - Generate timestamp-ordered UUIDs (version 7) for better database performance
UUID_EXTRACT_TIMESTAMP - Extract timestamp from UUID v1 or v7
UUID_EXTRACT_VERSION - Extract version number from UUID

Network Address Operations: (Complete documentation)

HOST/NETWORK/BROADCAST - Extract address parts
MASKLEN/NETMASK/HOSTMASK - Mask information
INET_MERGE - Smallest network containing two addresses

Hstore Operations: (Complete documentation)

HSTORE_AKEYS - Return hstore keys as an array
HSTORE_AVALS - Return hstore values as an array
HSTORE_DEFINED - Check if key exists and is not NULL
HSTORE_DELETE - Delete key from hstore

Ltree Operations: (Complete documentation)

SUBLTREE - Extract subpath from ltree
SUBPATH - Extract subpath with offset and length
NLEVEL - Get number of labels in path
INDEX - Find position of ltree in another ltree
LCA - Find longest common ancestor
TEXT2LTREE - Cast text to ltree
LTREE2TEXT - Cast ltree to text
MATCHES_LQUERY - Check whether a path matches an lquery pattern (~)
MATCHES_ANY_LQUERY - Check whether a path matches any lquery pattern in an array (?)
MATCHES_LTXTQUERY - Check whether a path matches an ltxtquery label query (@)

Vector Distance Operations:

L2_DISTANCE - Euclidean distance between vectors
COSINE_DISTANCE - Cosine distance between vectors
INNER_PRODUCT - Inner product of two vectors

Composite Types:

COMPOSITE_FIELD - Access a field from a PostgreSQL composite type column → Use Cases and Examples

XML Functions: (Complete documentation)

XML_IS_WELL_FORMED - Check whether a text string is well-formed XML
XML_IS_WELL_FORMED_CONTENT - Check whether a text string is well-formed XML content
XML_IS_WELL_FORMED_DOCUMENT - Check whether a text string is a well-formed XML document
XMLAGG - Aggregate XML values (supports ORDER BY)
XMLCOMMENT - Create an XML comment
XMLCONCAT - Concatenate multiple XML values into a single XML value
XMLEXISTS - Test if an XPath expression matches any nodes in an XML value
XMLPI - Create an XML processing instruction
XMLTEXT - Create an XML text node (with entity escaping)
XPATH - Evaluate an XPath expression against an XML value, returning matched nodes
XPATH_EXISTS - Test if an XPath expression matches any node in an XML value

Window Functions: (Complete documentation)

OVER - Run an aggregate or a window function over a window, e.g. running totals and moving averages
ROW_NUMBER/RANK/DENSE_RANK - Number or rank rows within their partition
PERCENT_RANK/CUME_DIST - Relative rank and cumulative distribution within the partition
NTILE - Split the partition into a number of buckets
LAG/LEAD - Value from a row before or after the current one
FIRST_VALUE/LAST_VALUE/NTH_VALUE - Value from the first, last or n-th row of the window frame

📋 Summary of Available Function Categories

Array & JSON Functions

Array Operations: Manipulate PostgreSQL arrays (append, remove, replace, shuffle)
JSON Functions: Work with JSON/JSONB data types
JSONB Path Functions: Advanced JSONB querying with path expressions

Spatial Functions (PostGIS)

Accessor Functions: Retrieve geometry properties
Constructor Functions: Create geometries from coordinates and parameters
Relationship Functions: Test spatial relationships between geometries
Measurement Functions: Calculate distances, areas, lengths, and angles
Overlay Functions: Perform geometric operations (intersection, union, difference)
Processing Functions: Transform, simplify, and modify geometries
Editor Functions: Modify geometry properties (SRID, coordinates, snapping, segmentizing)
Linear Referencing Functions: Locate points and substrings along linear geometries

Text & Pattern Functions

Regexp Functions: Pattern matching and replacement
Text Functions: String manipulation and searching (ascii, btrim, chr, initcap, lpad, ltrim, octet_length, quote_ident, quote_literal, quote_nullable, rpad, rtrim, strpos, translate, and more)
Full-Text Search: PostgreSQL’s text search capabilities
Fuzzy String Matching: Phonetic and edit distance algorithms (fuzzystrmatch extension)
Hashing & Checksum: MD5, SHA-2 family, CRC32, CRC32C, REVERSE_BYTES

Date & Range Functions

Date Operations: Add/subtract dates, extract components
Range Types: Create and work with various range types
Overlap Testing: Check if date ranges overlap

Hstore Functions (requires hstore extension)

Key Operations: Get keys as array or set (akeys, skeys)
Value Operations: Get values as array or set (avals, svals)
Inspection: Check if key is defined and non-NULL (defined)
Manipulation: Delete key from hstore (delete)
Conversion: Convert hstore to JSON, with strict or loose typing (hstore_to_json, hstore_to_json_loose)

Ltree Functions

Path Operations: Extract subpaths, manipulate hierarchical paths
Ancestor Operations: Find common ancestors, calculate path levels
Type Conversion: Convert between ltree and text types

Mathematical Functions

Basic Math: Power, square root, trigonometric functions
Statistical: Random numbers, rounding, precision functions
Bitwise/Boolean Aggregates: bit_and, bit_or, bit_xor, bool_and, bool_or, every
Statistical Aggregates: corr, covar_pop, covar_samp, mode, percentile_cont, percentile_disc, stddev, stddev_pop, var_pop, variance

Utility Functions

Type Conversion: CAST for general type conversion
Formatting: TO_CHAR for numbers and dates, TO_NUMBER for parsing
UUID: Generation (GEN_RANDOM_UUID, UUIDV4, UUIDV7) and inspection (UUID_EXTRACT_TIMESTAMP, UUID_EXTRACT_VERSION)

XML Functions

Aggregation: XMLAGG — aggregate XML values with optional ordering
Validation: XML_IS_WELL_FORMED, XML_IS_WELL_FORMED_DOCUMENT, XML_IS_WELL_FORMED_CONTENT — check XML well-formedness
Construction: XMLTEXT, XMLCOMMENT, XMLCONCAT, XMLPI — create and combine XML values
XPath Querying: XPATH, XPATH_EXISTS, XMLEXISTS — extract nodes and test existence

Network Address Functions

Extraction: HOST, BROADCAST, NETWORK, NETMASK, HOSTMASK — decompose inet/cidr types
Inspection: FAMILY, MASKLEN, ABBREV — address metadata
Multi-address: INET_MERGE, INET_SAME_FAMILY — compare or combine addresses
Modification: SET_MASKLEN — adjust subnet mask length

Operators

Array Operators: Contains, overlaps, element testing
Spatial Operators: Bounding box and distance operations
Text Operators: Pattern matching and concatenation

💡 Tips for Usage:

Boolean functions should be used with = TRUE or = FALSE in DQL → Common Use Cases and Examples
Spatial functions work best with proper geometry types and indexes → Spatial Types
Array functions provide efficient PostgreSQL array operations → Array and JSON Functions
JSON functions support both JSON and JSONB data types → Array and JSON Functions
Range functions provide efficient storage and querying for value ranges → Range Types
Mathematical functions work with numeric types and return appropriate precision → Mathematical Functions
Utility functions provide type casting, formatting, and UUID operations → Utility Functions
Ltree functions provide efficient hierarchical data operations and path manipulation → Ltree Functions

📖 For More Information:

Available Types - PostgreSQL data types supported by this library
Value Objects for Range Types - Working with range value objects
Common Use Cases and Examples - Practical examples and patterns
Spatial Types - PostGIS geometry and geography types
Geometry Arrays - Working with arrays of geometric types