PostgreSQL Common Functions
PostgreSQL built-in functions, also called aggregate functions, are used to perform processing on string or numeric data.
The following 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 query the maximum value in a specific column.
- MIN function: used to query 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 sum of all values in a numeric column.
- ARRAY function: used to add input values (including null) to an array.
- Numeric functions: a complete list of functions for the operations required in SQL.
- String functions: a complete list of functions for character operations required in SQL.
Mathematical Functions
The following is a list of mathematical functions provided in PostgreSQL. Note that many of these functions have multiple forms, differing only in the types of their arguments. Unless otherwise specified, any specific form of a function returns the same data type as its argument.
| 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 argument | ceil(-42.8) | -42 | |
| degrees(double) | Converts radians to degrees | degrees(0.5) | 28.6478897565412 | |
| exp(double/numeric) | Natural exponential | exp(1.0) | 2.71828182845905 | |
| floor(double/numeric) | Largest integer not greater than the argument | 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 of specified base | log(2.0, 64.0) | 6.0000000000 |
| mod(y, x) | Remainder | mod(9,4) | 1 | |
| pi() | double | The "π" constant | pi() | 3.14159265358979 |
| power(a double, b double) | double | a raised to the power of b | power(9.0, 3.0) | 729 |
| power(a numeric, b numeric) | numeric | a raised to the power of b | power(9.0, 3.0) | 729 |
| radians(double) | double | Converts 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 argument (-1, 0, +1) | sign(-8.4) | -1 | |
| sqrt(double/numeric) | Square root | sqrt(2.0) | 1.4142135623731 | |
| trunc(double/numeric) | Truncate (toward 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) | Inverse tangent of y/x |
| cos(x) | Cosine |
| cot(x) | Cotangent |
| sin(x) | Sine |
| tan(x) | Tangent |
String Functions and Operators
The following is a list of string operators provided in PostgreSQL:
| Function | Return Type | Description | Example | Result |
|---|---|---|---|---|
| string || string | text | String concatenation | 'Post' || 'PostgreSQL' | PostgreSQL |
| bit_length(string) | int | Number of bits in string | bit_length('jose') | 32 |
| char_length(string) | int | Number of characters in 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 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 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 characters (default is a space) from the beginning/end/both sides of the string `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 argument | ascii('x') | 120 |
| btrim(string text [, characters text]) | text | Remove the longest string consisting of characters in `characters` (default is space) from the beginning and end of `string`. | 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 the 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, leaving the rest in 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 `string` | length('jose') | 4 |
| lpad(string text, length int [, fill text]) | text | Pad `string` to length `length` with the fill character `fill` (default is space). If `string` is already longer than `length`, truncate it (on the right). | lpad('hi', 5, 'xy') | xyxhi |
| ltrim(string text [, characters text]) | text | Remove the longest string consisting only of characters (default is space) from the beginning of `string`. | ltrim('zzzytrim','xyz') | trim |
| md5(string text) | text | Calculate the MD5 hash of the given `string`, returning the result in hexadecimal. | md5('abc') | |
| repeat(string text, number int) | text | Repeat `string` `number` times. | repeat('Pg', 4) | PgPgPgPg |
| replace(string text, from text, to text) | text | Replace all occurrences of substring `from` in string `string` with substring `to`. | replace('abcdefabcdef', 'cd', 'XX') | abXXefabXXef |
| rpad(string text, length int [, fill text]) | text | Pad `string` to length `length` with the fill character `fill` (default is space). If `string` is already longer than `length`, truncate it. | rpad('hi', 5, 'xy') | hixyx |
| rtrim(string text [, character text]) | text | Remove the longest string consisting only of characters (default is space) from the end of string `string`. | rtrim('trimxxxx','x') | trim |
| split_part(string text, delimiter text, field int) | text | Return the `field`-th substring generated by splitting `string` by `delimiter` (1-based). | split_part('abc~@~def~@~ghi', '~@~', 2) | def |
| strpos(string, substring) | text | Position of the specified substring. | strpos('high','ig') | 2 |
| substr(string, from [, count]) | text | Extract substring. | substr('alphabet', 3, 2) | ph |
| to_ascii(text [, encoding]) | text | Convert `text` from another encoding to ASCII. | to_ascii('Karel') | Karel |
| to_hex(number int/bigint) | text | Convert `number` to its corresponding hexadecimal representation. | to_hex(9223372036854775807) | 7fffffffffffffff |
| translate(string text, from text, to text) | text | Convert any characters in `string` that match characters in `from` to the corresponding characters in `to`. | translate('12345', '14', 'ax') | a23x5 |
Type Conversion Related Functions
| Function | Return Type | Description | Example |
|---|---|---|---|
| to_char(timestamp, text) | text | Convert timestamp to string | to_char(current_timestamp, 'HH12:MI:SS') |
| to_char(interval, text) | text | Convert interval to string | to_char(interval '15h 2m 12s', 'HH24:MI:SS') |
| to_char(int, text) | text | Convert integer to string | to_char(125, '999') |
| to_char(double precision, text) | text | Convert double precision to string | to_char(125.8::real, '999D9') |
| to_char(numeric, text) | text | Convert numeric to string | to_char(-125.8, '999D99S') |
| to_date(text, text) | date | Convert string to date | to_date('05 Dec 2000', 'DD Mon YYYY') |
| to_number(text, text) | numeric | Convert string to number | to_number('12,454.8-', '99G999D9S') |
| to_timestamp(text, text) | timestamp | Convert to the specified time format; time zone: convert string to time stamp | to_timestamp('05 Dec 2000', 'DD Mon YYYY') |
| to_timestamp(double precision) | timestamp | Convert Unix epoch to timestamp | to_timestamp(1284352323) |
Other ExtensionsReference article: https://blog.csdn.net/sun5769675/article/details/50628979