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:

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)

🌐 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

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, 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
• 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

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

📋 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)

• 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

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, 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 (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:

1. Boolean functions should be used with = TRUE or = FALSE in DQL → Common Use Cases and Examples
2. Spatial functions work best with proper geometry types and indexes → Spatial Types
3. Array functions provide efficient PostgreSQL array operations → Array and JSON Functions
4. JSON functions support both JSON and JSONB data types → Array and JSON Functions
5. Range functions provide efficient storage and querying for value ranges → Range Types
6. Mathematical functions work with numeric types and return appropriate precision → Mathematical Functions
7. Utility functions provide type casting, formatting, and UUID operations → Utility Functions
8. 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