PostgreSQL Functions
PostgreSQL has many built-in functions. For easy reference, a list of all PostgreSQL functions are provided. These functions can be used in queries in PostgreSQL and can also be used within the programming environment provided by the PostgreSQL database, such as stored procedures, functions, triggers, etc.
Below is the list of PostgreSQL functions, sorted by category:
PostgreSQL String Functions
| Function | Description |
|---|---|
| ASCII() | Returns the ASCII value for the specific character |
| BIT_LENGTH() | Return length of argument in bits |
| BTRIM() | Removes leading and trailing spaces (or specified characters) from a string |
| CHR() | Return the character for the given code |
| CHAR_LENGTH() | Returns the length of a string (in characters) |
| CHARACTER_LENGTH() | Returns the length of a string (in characters) |
| CONCAT() | Adds two or more expressions together |
| Concatenate with || | Returns the concatenated string |
| CONCAT_WS() | Adds two or more expressions together with a separator |
| INITCAP() | Convert the first letter of each word to upper case and the rest to lower case. |
| LEFT() | Extracts a number of characters from a string (starting from left) |
| LENGTH() | Returns the length of a string (in bytes) |
| LOWER() | Converts a string to lower-case |
| LPAD() | Left-pads a string with another string, to a certain length |
| LTRIM() | Removes leading spaces (or specified characters) from a string |
| OCTET_LENGTH() | Returns the length of a string (in bytes) |
| POSITION() | Returns the position of the first occurrence of a substring in a string |
| REPEAT() | Repeats a string as many times as specified |
| REPLACE() | Replaces all occurrences of a substring within a string, with a new substring |
| REVERSE() | Reverses a string and returns the result |
| RIGHT() | Extracts a number of characters from a string (starting from right) |
| RPAD() | Right-pads a string with another string, to a certain length |
| RTRIM() | Removes trailing spaces (or specified characters) from a string |
| STRPOS() | Returns the position of the first occurrence of a substring in a string |
| SUBSTR() | Extracts a substring from a string (starting at any position) |
| SUBSTRING() | Extracts a substring from a string (starting at any position) |
| TRANSLATE() | Replaces a sequence of characters in a string with another set of characters |
| TRIM() | Removes leading and trailing spaces (or specified characters) from a string |
| UPPER() | Converts a string to upper-case |
PostgreSQL Math/Numeric Functions
| Function | Description |
|---|---|
| ABS() | Returns the absolute value of a number |
| ACOS() | Returns the arc cosine of a number |
| ACOSH() | Returns the inverse hyperbolic cosine of a number |
| ASIN() | Returns the arc sine of a number |
| ASINH() | Returns the inverse hyperbolic sine of a number |
| ATAN() | Returns the arc tangent of a number |
| ATANH() | Returns the inverse hyperbolic tangent of a number |
| ATAN2() | Returns the arc tangent of two numbers |
| AVG() | Returns the average value of an expression |
| CBRT() | Returns the cube root of a number |
| CEIL() | Returns the smallest integer value that is >= to a number |
| CEILING() | Returns the smallest integer value that is >= to a number |
| COS() | Returns the trigonometric cosine of an angle |
| COSH() | Returns the hyperbolic cosine of a value |
| COT() | Returns the trigonometric cotangent of an angle |
| COUNT() | Returns the number of records returned by a select query |
| DEGREES() | Converts a value in radians to degrees |
| DIV() | Used for integer division |
| EXP() | Returns e raised to the power of a specified number |
| FACTORIAL() | Returns factorial of a given integer |
| FLOOR() | Returns the largest integer value that is <= to a number |
| GREATEST() | Returns the greatest value of the list of arguments |
| LEAST() | Returns the smallest value of the list of arguments |
| LN() | Returns the natural logarithm of a number |
| LOG() | Returns the base-10 logarithm of a number, or the logarithm of a number to a specified base |
| LOG10() | Returns the base-10 logarithm of a number |
| MAX() | Returns the maximum value in a set of values |
| MIN() | Returns the minimum value in a set of values |
| MOD() | Returns the remainder of a number divided by another number |
| PI() | Returns the value of PI |
| POW() | Returns the value of a number raised to the power of another number |
| POWER() | Returns the value of a number raised to the power of another number |
| RADIANS() | Converts a degree value into radians |
| RANDOM() | Returns a random number |
| ROUND() | Rounds a number to a specified number of decimal places |
| SCALE() | Returns the number of decimal digits in the fractional part |
| SETSEED() | Sets seed for subsequent RANDOM() calls |
| SIGN() | Returns the sign of a number |
| SIN() | Returns the trigonometric sine of an angle |
| SINH() | Returns the hyperbolic sine of a value |
| SQRT() | Returns the square root of a number |
| SUM() | Calculates the sum of a set of values |
| TAN() | Returns the trigonometric tangent of an angle |
| TANH() | Returns the hyperbolic tangent of a value |
| TRUNC() | Truncates a number to the specified number of decimal places |
PostgreSQL Date/Time Functions
| Function | Description |
|---|---|
| CLOCK_TIMESTAMP() | Returns the actual current date and time with the time zone |
| CURRENT_DATE | Returns the current date |
| CURRENT_TIME() | Returns the current time with the time zone |
| CURRENT_TIMESTAMP() | Returns the current date and time with the time zone |
| DATE() | Extracts the date part from a date/datetime expression |
| EXTRACT() | Extracts a part from a given date |
| LOCALTIME() | Returns the current time |
| LOCALTIMESTAMP() | Returns the current date and time |
| NOW() | Returns the current date and time |
| STATEMENT_TIMESTAMP() | Returns a string containing the current date and time with the time zone |
| TIME() | Extracts the time part from a given time/datetime value |
| TIMEOFDAY() | Returns a string containing the actual current date and time with the time zone |
| TRANSACTION_TIMESTAMP() | Returns the current date and time with the time zone |
| TIMESTAMP() | Returns a datetime value based on a date or datetime value |
PostgreSQL Advanced Functions
| Function | Description |
|---|---|
| CASE() | Goes through conditions and return a value when the first condition is met |
| CAST() | Converts a value (of any type) into a specified datatype |
| COALESCE() | Returns the first non-null value in a list |
| CURRENT_DATABASE() | Returns the name of current database |
| CURRENT_USER | Returns the current user name |
| TO_HEX() | Returns a string containing hexadecimal representation of decimal or string value |
| MD5() | Calculates the MD5 hash of string and returns the result in hexadecimal |
| NULLIF() | Compares two expressions and returns NULL if they are equal. Otherwise, the first expression is returned |
| SESSION_USER | Returns the session user name |
| USER | Returns the current user name |
| VERSION() | Returns the PostgreSQL version information |
