YouTipPostgresql Functions
PostgreSQL built-in functions, also known as aggregate functions, are used to perform processing on string or numeric data.
Below is a list of all general PostgreSQL built-in functions:
* COUNT function: Used to calculate 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 of 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 operands required in SQL.
* String functions: A complete list of functions for character operations required in SQL.
* * *
## Mathematical Functions
Below is a list of mathematical functions provided in PostgreSQL. It should be noted that many of these functions exist in multiple forms, differing only in parameter types. Unless otherwise specified, any specific form of a 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 argument | ceil(-42.8) | -42 |
| degrees(double) | | Convert 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) | | Logarithm base 10 | log(100.0) | 2 |
| log(b numeric,x numeric) | numeric | Logarithm to a 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 | 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 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 (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) | Inverse cosine |
| asin(x) | Inverse sine |
| atan(x) | Inverse tangent |
| atan2(x, y) | Inverse tangent 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 ) | text | Replace a 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 ) | text | Extract a substring | substring('Thomas' from 2 for 3) | hom |
| substring(string from pattern) | text | Extract a substring matching a POSIX regular expression | substring('Thomas' from 'β¦$') | mas |
| substring(string from pattern for escape) | text | Extract a substring matching an SQL regular expression | substring('Thomas' from '%#"o_a#"_' for '#') | oma |
| trim([leading|trailing | both] from string) | text | Remove the longest string containing only characters (default is a space) from the start/end/both of 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 containing only characters (default is a space) from the start 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 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 character 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 string | length('jose') | 4 |
| lpad(string text, length int [, fill text]) | text | Pad the string to length length by prepending fill characters (default is a space). 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 | Remove the longest string containing only characters (default is a space) from the start of string. | ltrim('zzzytrim','xyz') | trim |
| md5(string text) | text | Compute 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 with substring to. | replace('abcdefabcdef', 'cd', 'XX') | abXXefabXXef |
| rpad(string text, length int [, fill text]) | text | Pad the string to length length by appending fill characters (default is a space). If the string is already longer than length, it is truncated. | rpad('hi', 5, 'xy') | hixyx |
| rtrim(string text [, character text]) | text | Remove the longest string containing only characters (default is a space) from the end of string | rtrim('trimxxxx','x') | trim |
| split_part(string text, delimiter text, field int) | text | Split string by delimiter and return the field-th substring (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 a 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 hexadecimal representation. | to_hex(9223372036854775807) | 7fffffffffffffff |
| translate(string text, from text, to text) | text | Convert any character in string that matches a character in from to the corresponding character 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 numeric | to_number('12,454.8-', '99G999D9S') |
| to_timestamp(text, text) | timestamp | Convert string to timestamp with specified time zone | to_timestamp('05 Dec 2000', 'DD Mon YYYY') |
| to_timestamp(double precision) | timestamp | Convert UNIX epoch to timestamp | to_timestamp(1284352323) |
> Reference article: https://blog.csdn.net/sun5769675/article/details/50628979