PostgreSQL Common Functions
PostgreSQL built-in functions, also known as aggregate functions, are used to perform operations on string or numeric data.
Here is a list of all common PostgreSQL built-in functions:
- COUNT function: Used to count the number of rows in a database table.
- MAX function: Used to find the maximum value in a specific column.
- MIN function: Used to find the minimum value in a specific column.
- AVG function: Used to calculate the average value in a specific column.
- SUM function: Used to calculate the total sum of values in a numeric column.
- ARRAY function: Used to add values (including null) to an array.
- Numeric functions: Functions that list all required operands in SQL.
- String functions: Functions that list all required character operations in SQL.
Mathematical Functions
Below is a list of mathematical functions provided in PostgreSQL. It should be noted that many of these functions have multiple forms, differing only in their parameter types. Unless specified otherwise, any specific form of the function returns the same data type as its parameters.
| Function | Return Type | Description | Example | Result |
|---|---|---|---|---|
| abs(x) | Absolute value | abs(-17.4) | 17.4 | |
| cbrt(double) | Cube root | cbrt(27.0) | 3 | |
| ceil(double/numeric) | Smallest integer not less than the parameter | ceil(-42.8) | -42 | |
| degrees(double) | Convert radians to degrees | degrees(0.5) | 28.6478897565412 | |
| exp(double/numeric) | Natural exponent | exp(1.0) | 2.71828182845905 | |
| floor(double/numeric) | Largest integer not greater than the parameter | floor(-42.8) | -43 | |
| ln(double/numeric) | Natural logarithm | ln(2.0) | 0.693147180559945 | |
| log(double/numeric) | Base 10 logarithm | log(100.0) | 2 | |
| log(b numeric, x numeric) | numeric | Logarithm with specified base | log(2.0, 64.0) | 6.0000000000 |
| mod(y, x) | Remainder | mod(9,4) | 1 | |
| pi() | double | "π" constant | pi() | 3.14159265358979 |
| power(a double, b double) | double | Power of a to b | power(9.0, 3.0) | 729 |
| power(a numeric, b numeric) | numeric | Power of a to b | power(9.0, 3.0) | 729 |
| radians(double) | double | Convert degrees to radians | radians(45.0) | 0.785398163397448 |
| random() | double | Random value between 0.0 and 1.0 | random() | |
| round(double/numeric) | Round to the nearest integer | round(42.4) | 42 | |
| round(v numeric, s int) | numeric | Round to s decimal places | round(42.438,2) | 42.44 |
| sign(double/numeric) | Sign of the parameter (-1, 0, +1) | sign(-8.4) | -1 | |
| sqrt(double/numeric) | Square root | sqrt(2.0) | 1.4142135623731 | |
| trunc(double/numeric) | Truncate towards zero | trunc(42.8) | 42 | |
| trunc(v numeric, s int) | numeric | Truncate to s decimal places | trunc(42.438,2) | 42.43 |
Trigonometric Functions List
| Function | Description |
|---|---|
| acos(x) | Arccosine |
| asin(x) | Arcsine |
| atan(x) | Arctangent |
| atan2(x, y) | Arctangent of y/x |
| cos(x) | Cosine |
| cot(x) | Cotangent |
| sin(x) | Sine |
| tan(x) | Tangent |
String Functions and Operators
Below is a list of string operators provided in PostgreSQL:
| Function | Return Type | Description | Example | Result | ||||
|---|---|---|---|---|---|---|---|---|
| string | string | text | string concatenation | 'Post' | 'greSQL' | PostgreSQL | ||
| bit_length(string) | int | number of bits in the string | bit_length('jose') | 32 | ||||
| char_length(string) | int | number of characters in the string | char_length('jose') | 4 | ||||
| convert(string using conversion_name) | text | change encoding using the specified conversion name | convert('PostgreSQL' using iso_8859_1_to_utf8) | 'PostgreSQL' | ||||
| lower(string) | text | convert string to lowercase | lower('TOM') | tom | ||||
| octet_length(string) | int | number of bytes in the string | octet_length('jose') | 4 | ||||
| overlay(string placing string from int [for int]) | text | replace substring | overlay('Txxxxas' placing 'hom' from 2 for 4) | Thomas | ||||
| position(substring in string) | int | position of the specified substring | position('om' in 'Thomas') | 3 | ||||
| substring(string [from int] [for int]) | text | extract substring | substring('Thomas' from 2 for 3) | hom | ||||
| substring(string from pattern) | text | extract substring matching POSIX regular expression | substring('Thomas' from '…$') | mas | ||||
| substring(string from pattern for escape) | text | extract substring matching SQL regular expression | substring('Thomas' from '%#"o_a#"_' for '#') | oma | ||||
| trim([leading | trailing | both] [characters] from string) | text | remove the longest string containing only the characters from the start/end/both sides of the string | trim(both 'x' from 'xTomxx') | Tom | ||
| upper(string) | text | convert string to uppercase | upper('tom') | TOM | ||||
| ascii(text) | int | ASCII code of the first character of the parameter | ascii('x') | 120 | ||||
| btrim(string text [, characters text]) | text | remove the longest string from the start and end of string that contains only characters in characters (default is whitespace) | btrim('xyxtrimyyx','xy') | trim | ||||
| chr(int) | text | character with the given ASCII code | chr(65) | A | ||||
| convert(string text, [src_encoding name,] dest_encoding name) | text | convert string to dest_encoding | convert('text_in_utf8', 'UTF8', 'LATIN1') | text_in_utf8 represented in ISO 8859-1 encoding | ||||
| initcap(text) | text | convert the first letter of each word to uppercase and the rest to lowercase. Words are sequences of alphanumeric characters separated by non-alphanumeric characters. | initcap('hi thomas') | Hi Thomas | ||||
| length(string text) | int | number of characters in the string | length('jose') | 4 | ||||
| lpad(string text, length int [, fill text]) | text | pad string to length by prepending the characters fill (default is whitespace). If the string is already longer than length, it is truncated on the right. | lpad('hi', 5, 'xy') | xyxhi | ||||
| ltrim(string text [, characters text]) | text | Removes the longest string containing only the characters (default is a space) from the start of string. | ltrim('zzzytrim','xyz') | trim | ||||
| md5(string text) | text | Computes the MD5 hash of the given string and returns the result in hexadecimal. | md5('abc') | |||||
| repeat(string text, number int) | text | Repeats string number times. | repeat('Pg', 4) | PgPgPgPg | ||||
| replace(string text, from text, to text) | text | Replaces all occurrences of substring from with substring to in string. | replace('abcdefabcdef', 'cd', 'XX') | abXXefabXXef | ||||
| rpad(string text, length int [, fill text]) | text | Pads string to length by appending the characters fill (default is a space). If string is already longer than length, it is truncated. | rpad('hi', 5, 'xy') | hixyx | ||||
| rtrim(string text [, character text]) | text | Removes the longest string containing only the character (default is a space) from the end of string. | rtrim('trimxxxx','x') | trim | ||||
| split_part(string text, delimiter text, field int) | text | Splits string at delimiter and returns the field-th substring (1-based). | split_part('abc~@~def~@~ghi', '~@~', 2) | def | ||||
| strpos(string, substring) | text | Returns the position of the specified substring. | strpos('high','ig') | 2 | ||||
| substr(string, from [, count]) | text | Extracts a substring. | substr('alphabet', 3, 2) | ph | ||||
| to_ascii(text [, encoding]) | text | Converts text to ASCII from another encoding. | to_ascii('Karel') | Karel | ||||
| to_hex(number int/bigint) | text | Converts number to its hexadecimal representation. | to_hex(9223372036854775807) | 7fffffffffffffff | ||||
| translate(string text, from text, to text) | text | Replaces each character in string that matches a character in from with the corresponding character in to. | translate('12345', '14', 'ax') | a23x5 |
Type Conversion Functions
| Function | Return Type | Description | Example |
|---|---|---|---|
| to_char(timestamp, text) | text | Converts timestamp to string | to_char(current_timestamp, 'HH12:MI:SS') |
| to_char(interval, text) | text | Converts interval to string | to_char(interval '15h 2m 12s', 'HH24:MI:SS') |
| to_char(int, text) | text | Converts integer to string | to_char(125, '999') |
| to_char(double precision, text) | text | Converts double precision to string | to_char(125.8::real, '999D9') |
| to_char(numeric, text) | text | Converts numeric to string | to_char(-125.8, '999D99S') |
| to_date(text, text) | date | Converts string to date | to_date('05 Dec 2000', 'DD Mon YYYY') |
| to_number(text, text) | numeric | Converts string to numeric | to_number('12,454.8-', '99G999D9S') |
| to_timestamp(text, text) | timestamp | Convert to specified time format and time zone | to_timestamp('05 Dec 2000', 'DD Mon YYYY') |
| to_timestamp(double precision) | timestamp | Convert UNIX epoch to timestamp | to_timestamp(1284352323) |