/sql-functions

The ANSI/ISO SQL standard defines an extensive suite of built-in functions to ensure database portability. While most databases add their own non-standard extensions (like Postgres's AGE() or SQL Server's GETDATE()), the core standard functions can be divided into a few key categories. [1] (/goto?url=CAEShwEB6zswFS5zEowKU7klCfrenJmccm3G6mtXYSvmx2U2wkbQp-m924dYY-9JqlyBUJRLdvIeGaraBNzNZvyyC52oqc78Q3L7OufSeWPXPd1P6-Q3pp3QJbYfxAPniP0U5DpJXJ2bPUzaGaJ7vsO1nkWehIYR1HPgqG6Sn2BveHDkq0KGuuuIsAs), [2] (/goto?url=CAESpgEB6zswFRPz_pnC6n_MrB_7l_h2CB9Jt9HWEI8uNdJtvTprevpiYyeApSVHXDJCtvLbYXABOsVMzJwDQ6Aalm-VT1vPwwEVumkUtfH_TrSmh4JUFrZ-TKZimrxoowC1g21SHhSVQ44RxI2_CdxbRvoleu5KgU-tbRxyedI2E09DSLZv4Tl8QVcp6OwWX4FtlG18DUs3UspFuGzhEOz2eaP14JsfuUVC), [3] (/goto?url=CAEScwHrOzAVc_ECpsMxXrCxtZ0S8DuqiwcHev89nswa0VaKBao6-cL8-WtusqXt_sI8JaZF4yEZ_6Eive-UdcfjtzmpdYNRbR2gaayn7GI16DG3dT3y_BgkQzm22MQC-2gD56k3zVmYD4YI79a2MQhYvKJxswI)

1. Niladic Functions (Parameterless Context)

Like CURRENT_DATE, these functions are called without parentheses and grab environment or session information: [1] (/goto?url=CAESbAHrOzAVs5vQAmm8synsGgzuTAxJsvjwU62n9_ArdbCEtwHKxDv8IFZkxwnaZe2td55aNP9fpbr2ReIIagEJ7uLk1C5jftrv0HvJWiFjCOzQPuZMUmFs9-kTsC7RS9F1N2tXR70-XVn0Xp4DSQ), [2] (/goto?url=CAESdQHrOzAVciBPYtsl5rw23rnSrF9kLUkPzmaEsEcUr1ywkrpvm-pfUDDTNkIIWCBdYSYDdUzbAx5BkjjSkePfk-0TXqFktRqvb5bdrOjHRw3u26vbAVLBr8_48sitpcEAhyoGeC2rdQoxPci6H4dRy1WOd_MEUQ)
• CURRENT_TIME / CURRENT_TIMESTAMP: Returns the session time and date-time stamp.
• LOCALTIME / LOCALTIMESTAMP: Similar to the above, but returns the value without the time zone offset context.
• USER / CURRENT_USER / SESSION_USER: Returns the authorization name of the active database user. [1] (/goto?url=CAESdQHrOzAVciBPYtsl5rw23rnSrF9kLUkPzmaEsEcUr1ywkrpvm-pfUDDTNkIIWCBdYSYDdUzbAx5BkjjSkePfk-0TXqFktRqvb5bdrOjHRw3u26vbAVLBr8_48sitpcEAhyoGeC2rdQoxPci6H4dRy1WOd_MEUQ), [2] (/goto?url=CAESbAHrOzAVs5vQAmm8synsGgzuTAxJsvjwU62n9_ArdbCEtwHKxDv8IFZkxwnaZe2td55aNP9fpbr2ReIIagEJ7uLk1C5jftrv0HvJWiFjCOzQPuZMUmFs9-kTsC7RS9F1N2tXR70-XVn0Xp4DSQ), [3] (/goto?url=CAESawHrOzAVE1WNuLLxK5Z7uiOfpf4Xll6cwYJ6jMDdsy1CfQwdh6tHKYJ0p-LRZAL0NJ7WfS80m7lZVPpQyybVc7Cmnnb6EBOR2eT8KCMpJS5k247y03MF3aosCDYiNhXpOtJ5TtIJhuOvBpb7)

2. String Manipulation Functions

The standard guarantees a uniform way to clean and transform text data: [1] (/goto?url=CAESbAHrOzAVs5vQAmm8synsGgzuTAxJsvjwU62n9_ArdbCEtwHKxDv8IFZkxwnaZe2td55aNP9fpbr2ReIIagEJ7uLk1C5jftrv0HvJWiFjCOzQPuZMUmFs9-kTsC7RS9F1N2tXR70-XVn0Xp4DSQ)
• UPPER(str) / LOWER(str): Forces character casing.
• TRIM([both|leading|trailing] char FROM str): Strips characters/spaces from text.
• SUBSTRING(str FROM start FOR length): Extracts a portion of a string (note the standard uses FROM/FOR rather than commas).
• CHARACTER_LENGTH(str) (or CHAR_LENGTH): Measures text length in characters.
• OCTET_LENGTH(str): Measures text size in bytes.
• POSITION(substr IN str): Finds the starting index of a substring. [1] (/goto?url=CAESugEB6zswFTUOXV7Wax79is01pXEB0LS3vVDOiPzwI9NO-I3tC-XDachJY---GYccuyeAgCMMtNVTgLJkEkZfiAcikrHCuQM-AGz7arPSKuKw0Izbzx32lPkJ1-NWHMVcS5rjTcWpzS0_vvOEMWofYzAoIT52XY2Oq04SgPzXYuMCHNQpWJwscInN5Mz_oJtV1kdqS0UTlI8xqjrHaev2YOr12PyOrC6SwTkZmZntUe62OQ4lnAdsmiHkTgc), [2] (/goto?url=CAESpgEB6zswFRPz_pnC6n_MrB_7l_h2CB9Jt9HWEI8uNdJtvTprevpiYyeApSVHXDJCtvLbYXABOsVMzJwDQ6Aalm-VT1vPwwEVumkUtfH_TrSmh4JUFrZ-TKZimrxoowC1g21SHhSVQ44RxI2_CdxbRvoleu5KgU-tbRxyedI2E09DSLZv4Tl8QVcp6OwWX4FtlG18DUs3UspFuGzhEOz2eaP14JsfuUVC), [3] (/goto?url=CAESdQHrOzAVciBPYtsl5rw23rnSrF9kLUkPzmaEsEcUr1ywkrpvm-pfUDDTNkIIWCBdYSYDdUzbAx5BkjjSkePfk-0TXqFktRqvb5bdrOjHRw3u26vbAVLBr8_48sitpcEAhyoGeC2rdQoxPci6H4dRy1WOd_MEUQ), [4] (/goto?url=CAESpgIB6zswFYLsdXk5SgzYYyC1BDJpruEKjEmeiT6wtgoVCXbZfBePV3rP-jT1Kl1ICLuU7RmFcgxk1xN9PRnZqiZrQ-ee5K9lAlPVS2N8SGyzqLM_bh24PsM4heBHPFhYeCKI2AQVYdOm3rifjHy-TLgqSBIhj2bRY1kHTLHX9lSQ9w5DlluutSTsMg22mo8G5Hofh5CnrERI7q7yicVSpmxcPF9cPSynZILzYffku06OoA020Tmx4T6TSmJ8LSpJFysCwsAto53lbGo2wCB5bwn7rNBJV-uj2AVK3Y1v1oC_I3UmVUSaqwPawIvyZPw4a2BKZHFzKmIn1GH1Zck1CQZm0STF9xhkjWqLm7pnz75pXAmqiT3aVsGUDkDNHKfrThulvCZaVYs), [5] (/goto?url=CAESbAHrOzAVs5vQAmm8synsGgzuTAxJsvjwU62n9_ArdbCEtwHKxDv8IFZkxwnaZe2td55aNP9fpbr2ReIIagEJ7uLk1C5jftrv0HvJWiFjCOzQPuZMUmFs9-kTsC7RS9F1N2tXR70-XVn0Xp4DSQ)

3. Mathematical & Numeric Functions

Standardized scalar arithmetic operations: [1] (/goto?url=CAESbAHrOzAVs5vQAmm8synsGgzuTAxJsvjwU62n9_ArdbCEtwHKxDv8IFZkxwnaZe2td55aNP9fpbr2ReIIagEJ7uLk1C5jftrv0HvJWiFjCOzQPuZMUmFs9-kTsC7RS9F1N2tXR70-XVn0Xp4DSQ)
• ABS(num): Absolute value.
• MOD(dividend, divisor): Remainder of a division.
• CEIL(num) / FLOOR(num): Rounds up or down to the nearest whole integer.
• SQRT(num) / POWER(base, exp): Core exponential operations.
• LN(num) / EXP(num): Natural logarithm and exponential base-e. [1] (/goto?url=CAESpgIB6zswFYLsdXk5SgzYYyC1BDJpruEKjEmeiT6wtgoVCXbZfBePV3rP-jT1Kl1ICLuU7RmFcgxk1xN9PRnZqiZrQ-ee5K9lAlPVS2N8SGyzqLM_bh24PsM4heBHPFhYeCKI2AQVYdOm3rifjHy-TLgqSBIhj2bRY1kHTLHX9lSQ9w5DlluutSTsMg22mo8G5Hofh5CnrERI7q7yicVSpmxcPF9cPSynZILzYffku06OoA020Tmx4T6TSmJ8LSpJFysCwsAto53lbGo2wCB5bwn7rNBJV-uj2AVK3Y1v1oC_I3UmVUSaqwPawIvyZPw4a2BKZHFzKmIn1GH1Zck1CQZm0STF9xhkjWqLm7pnz75pXAmqiT3aVsGUDkDNHKfrThulvCZaVYs), [2] (/goto?url=CAESbAHrOzAVs5vQAmm8synsGgzuTAxJsvjwU62n9_ArdbCEtwHKxDv8IFZkxwnaZe2td55aNP9fpbr2ReIIagEJ7uLk1C5jftrv0HvJWiFjCOzQPuZMUmFs9-kTsC7RS9F1N2tXR70-XVn0Xp4DSQ), [3] (/goto?url=CAESdQHrOzAVciBPYtsl5rw23rnSrF9kLUkPzmaEsEcUr1ywkrpvm-pfUDDTNkIIWCBdYSYDdUzbAx5BkjjSkePfk-0TXqFktRqvb5bdrOjHRw3u26vbAVLBr8_48sitpcEAhyoGeC2rdQoxPci6H4dRy1WOd_MEUQ)

4. Date & Time Specific Functions

Beyond pulling the current timestamp, the standard defines precise syntax for broken-down date math: [1] (/goto?url=CAESdQHrOzAVciBPYtsl5rw23rnSrF9kLUkPzmaEsEcUr1ywkrpvm-pfUDDTNkIIWCBdYSYDdUzbAx5BkjjSkePfk-0TXqFktRqvb5bdrOjHRw3u26vbAVLBr8_48sitpcEAhyoGeC2rdQoxPci6H4dRy1WOd_MEUQ)
• EXTRACT(field FROM source): Pulls specific pieces (like YEAR, MONTH, or DAY) out of a timestamp.
• OVERLAPS: A unique temporal operator/function used to evaluate if two time intervals intersect (e.g., (start1, end1) OVERLAPS (start2, end2)). [1] (/goto?url=CAESpgIB6zswFYLsdXk5SgzYYyC1BDJpruEKjEmeiT6wtgoVCXbZfBePV3rP-jT1Kl1ICLuU7RmFcgxk1xN9PRnZqiZrQ-ee5K9lAlPVS2N8SGyzqLM_bh24PsM4heBHPFhYeCKI2AQVYdOm3rifjHy-TLgqSBIhj2bRY1kHTLHX9lSQ9w5DlluutSTsMg22mo8G5Hofh5CnrERI7q7yicVSpmxcPF9cPSynZILzYffku06OoA020Tmx4T6TSmJ8LSpJFysCwsAto53lbGo2wCB5bwn7rNBJV-uj2AVK3Y1v1oC_I3UmVUSaqwPawIvyZPw4a2BKZHFzKmIn1GH1Zck1CQZm0STF9xhkjWqLm7pnz75pXAmqiT3aVsGUDkDNHKfrThulvCZaVYs), [2] (/goto?url=CAESdQHrOzAVciBPYtsl5rw23rnSrF9kLUkPzmaEsEcUr1ywkrpvm-pfUDDTNkIIWCBdYSYDdUzbAx5BkjjSkePfk-0TXqFktRqvb5bdrOjHRw3u26vbAVLBr8_48sitpcEAhyoGeC2rdQoxPci6H4dRy1WOd_MEUQ)

5. Conditional & Logical Functions

These are universal functions designed to handle logic and missing data smoothly: [1] (/goto?url=CAESdQHrOzAVciBPYtsl5rw23rnSrF9kLUkPzmaEsEcUr1ywkrpvm-pfUDDTNkIIWCBdYSYDdUzbAx5BkjjSkePfk-0TXqFktRqvb5bdrOjHRw3u26vbAVLBr8_48sitpcEAhyoGeC2rdQoxPci6H4dRy1WOd_MEUQ)
• CASE WHEN ... THEN ... ELSE END: Inline conditional logic evaluation.
• COALESCE(val1, val2, ...): Evaluates arguments in order and returns the first non-null value.
• NULLIF(val1, val2): Returns NULL if val1 equals val2; otherwise returns val1.
• CAST(value AS datatype): Explicitly converts data from one type to another (e.g., CAST('2026-01-01' AS DATE)). [1] (/goto?url=CAESbwHrOzAV6upI1-XpICtSRu-8Ug7dwBR-III4NRVjFHW0uB5OGNi8Hd_zljSfr5VnFgFMs61UOSS2Cf8Y-GwVM3TVlGuaFgGLPk3sXBPpeC-O86IQzoI83hkREETpE2ZGntZ-YdJNuMmcRKkvKFFs1Q), [2] (/goto?url=CAESpgEB6zswFRPz_pnC6n_MrB_7l_h2CB9Jt9HWEI8uNdJtvTprevpiYyeApSVHXDJCtvLbYXABOsVMzJwDQ6Aalm-VT1vPwwEVumkUtfH_TrSmh4JUFrZ-TKZimrxoowC1g21SHhSVQ44RxI2_CdxbRvoleu5KgU-tbRxyedI2E09DSLZv4Tl8QVcp6OwWX4FtlG18DUs3UspFuGzhEOz2eaP14JsfuUVC), [3] (/goto?url=CAESdQHrOzAVciBPYtsl5rw23rnSrF9kLUkPzmaEsEcUr1ywkrpvm-pfUDDTNkIIWCBdYSYDdUzbAx5BkjjSkePfk-0TXqFktRqvb5bdrOjHRw3u26vbAVLBr8_48sitpcEAhyoGeC2rdQoxPci6H4dRy1WOd_MEUQ)

6. Aggregate & Window Functions

Functions that run calculations on sets of rows: [1] (/goto?url=CAESjQEB6zswFRW2VUS_kT0RIp2xaLfq5zpt9ZVKK38bOUFqV4KuEWjNwI3j8CdnHyGNKYw-utVYNlkfJUuBIl93ntbA1YYyLQEiMcOlWTpGNoKSuxK_Nu_Eys7jsADc2OGhjGzfqEBjU73t7e6RyOpABdwVt_UVgbCGceZSvhruGi8qwlUcRe25odQWff_Ac9A), [2] (/goto?url=CAESjgEB6zswFUixsdVOya9qNuy394auaqoSkbudHUdOSyl8M5qflenQMyFlIGtzyeJcIheDB_V-uEcZfZMAWnjtDPl737si8MpOiMnXszgGcdoJ_ZU6RNLvUhdUFfmoddGubDNF-w9ckWJQbtkN5g2YneE_GbJRqssRIvRvdQxmtEw-GvzBcedhUTlVSkin391x)
• COUNT(), SUM(), AVG(), MIN(), MAX(): The traditional core aggregates defined in the early SQL-92 layer.
• ROW_NUMBER(), RANK(), DENSE_RANK(): Standard analytical ranking calculations.
• LEAD(), LAG(): Standard window functions used to peer into adjacent rows without using heavy joins. [1] (/goto?url=CAESjgEB6zswFUixsdVOya9qNuy394auaqoSkbudHUdOSyl8M5qflenQMyFlIGtzyeJcIheDB_V-uEcZfZMAWnjtDPl737si8MpOiMnXszgGcdoJ_ZU6RNLvUhdUFfmoddGubDNF-w9ckWJQbtkN5g2YneE_GbJRqssRIvRvdQxmtEw-GvzBcedhUTlVSkin391x), [2] (/goto?url=CAESbwHrOzAV6upI1-XpICtSRu-8Ug7dwBR-III4NRVjFHW0uB5OGNi8Hd_zljSfr5VnFgFMs61UOSS2Cf8Y-GwVM3TVlGuaFgGLPk3sXBPpeC-O86IQzoI83hkREETpE2ZGntZ-YdJNuMmcRKkvKFFs1Q), [3] (/goto?url=CAESpgEB6zswFRPz_pnC6n_MrB_7l_h2CB9Jt9HWEI8uNdJtvTprevpiYyeApSVHXDJCtvLbYXABOsVMzJwDQ6Aalm-VT1vPwwEVumkUtfH_TrSmh4JUFrZ-TKZimrxoowC1g21SHhSVQ44RxI2_CdxbRvoleu5KgU-tbRxyedI2E09DSLZv4Tl8QVcp6OwWX4FtlG18DUs3UspFuGzhEOz2eaP14JsfuUVC), [4] (/goto?url=CAESpgIB6zswFYLsdXk5SgzYYyC1BDJpruEKjEmeiT6wtgoVCXbZfBePV3rP-jT1Kl1ICLuU7RmFcgxk1xN9PRnZqiZrQ-ee5K9lAlPVS2N8SGyzqLM_bh24PsM4heBHPFhYeCKI2AQVYdOm3rifjHy-TLgqSBIhj2bRY1kHTLHX9lSQ9w5DlluutSTsMg22mo8G5Hofh5CnrERI7q7yicVSpmxcPF9cPSynZILzYffku06OoA020Tmx4T6TSmJ8LSpJFysCwsAto53lbGo2wCB5bwn7rNBJV-uj2AVK3Y1v1oC_I3UmVUSaqwPawIvyZPw4a2BKZHFzKmIn1GH1Zck1CQZm0STF9xhkjWqLm7pnz75pXAmqiT3aVsGUDkDNHKfrThulvCZaVYs), [5] (/goto?url=CAESjQEB6zswFRW2VUS_kT0RIp2xaLfq5zpt9ZVKK38bOUFqV4KuEWjNwI3j8CdnHyGNKYw-utVYNlkfJUuBIl93ntbA1YYyLQEiMcOlWTpGNoKSuxK_Nu_Eys7jsADc2OGhjGzfqEBjU73t7e6RyOpABdwVt_UVgbCGceZSvhruGi8qwlUcRe25odQWff_Ac9A)
Are you checking for standard compliance because you are trying to write database-independent SQL applications, or are you running into a scenario where a specific Postgres function isn't working the way you expected?