English | 简体中文 | 繁體中文 | Русский язык | Français | Español | Português | Deutsch | 日本語 | 한국어 | Italiano | بالعربية
PostgreSQL built-in functions, also known as aggregate functions, are used to process string or numeric data.
The following 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 of a specific column.
SUM Function: Used to calculate the sum of all values in a numeric column.
ARRAY Functions: Used to add input values (including null) to an array.
Numeric Functions: List all the functions required for operands in SQL.
String Functions: List all the functions required for operations in SQL.
The following is a list of mathematical functions provided by PostgreSQL. It should be noted that many of these functions exist in multiple forms, the difference being the type of parameters. Unless otherwise specified, any specific form of a function returns the same data type as its parameters.
| Function | Return Type | Description | example | result |
|---|---|---|---|---|
| absolute value of x | absolute value | abs(-17.4) | 17.4 | |
| cbrt(double | cube root | cbrt(27.0) | 3 | |
| ceil(double/numeric) | the 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 exponentiation | exp(1.0) | 2.71828182845905 | |
| floor(double/numeric) | the largest integer not greater than the argument | floor(-42.8) | -43 | |
| ln(double/numeric) | natural logarithm | ln(2.0) | 0.693147180559945 | |
| log(double/numeric) | 10logarithm to the base | log(100.0) | 2 | |
| log(b numeric,x numeric) | numeric | logarithm with specified base | log(2.0, 64.0) | 6.0000000000 |
| mod(y, x) | get the remainder | mod(9,4) | 1 | |
| pi() | double | "π" constant | pi() | 3.14159265358979 |
| power(a double, b double) | double | calculate the power of a to the b | power(9.0, 3.0) | 729 |
| power(a numeric, b numeric) | numeric | calculate the power of a to the b | power(9.0, 3.0) | 729 |
| radians(double) | double | convert an angle to radians | radians(45.0) | 0.785398163397448 |
| random() | double | 0.0 to1to generate a random number between 0.0 and | 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 |
| Function | Description |
|---|---|
| acos(x) | arccosine |
| asin(x) | arcsine |
| atan(x) | arctangent |
| atan2(x, y) | tangent y/inverse of x |
| cos(x) | cosine |
| cot(x) | cotangent |
| sin(x) | sine |
| tan(x) | tangent |
Below is a list of string operators provided by PostgreSQL:
| Function | Return Type | Description | example | result |
|---|---|---|---|---|
| string || string | text | string concatenation | 'Post' || 'greSQL' | PostgreSQL |
| bit_length(string) | int | number of binary bits in a string | bit_length('jose') | 32 |
| char_length(string) | int | number of characters in a 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 a string to lowercase | lower('TOM') | tom |
| octet_length(string) | int | number of bytes in a string | octet_length('jose') | 4 |
| overlay(string placing string from int [for int]) | text | replacing a substring | overlay('Txxxxas' placing 'hom' from 2 for 4) | Thomas |
| position(substring in string) | int | the position of the specified substring | position('om' in 'Thomas') | 3 |
| substring(string [from int] [for int]) | text | extracting a substring | substring('Thomas' from 2 for 3) | hom |
| substring(string from pattern) | text | Extract the substring that matches the POSIX regular expression | substring('Thomas' from '…$') | mas |
| substring(string from pattern for escape) | text | Extract the substring that matches the SQL regular expression | substring('Thomas' from '%#"o_a#"_' for '#') | oma |
| trim([leading丨trailing 丨 both] [characters] from string) | text | From the beginning of the string string/End/Both sides/ Remove the longest substring that only contains characters (default is a blank) | trim(both 'x' from 'xTomxx') | Tom |
| upper(string) | text | Convert the 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 substring at the beginning and end of the string that only contains characters in characters (default is blank). | btrim('xyxtrimyyx','xy') | trim |
| chr(int) | text | Character represented by 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) | In ISO 8859-1Encoding representation of text_in_utf8 |
| initcap(text) | text | Convert the first letter of each word to uppercase, while keeping the others in lowercase. A word is a series of alphanumeric characters separated by non-alphanumeric characters. | initcap('hi thomas') | Hi Thomas |
| length(string text) | int | The number of characters in the string | length('jose') | 4 |
| lpad(string text, length int [, fill text]) | text | By filling with characters fill (default is blank), fill the string to the length length. If the string is already longer than length, it will be truncated (on the right). | lpad('hi', 5, 'xy') | xyxhi |
| ltrim(string text [, characters text]) | text | Remove the longest word containing only characters (default is a space) from the beginning of the string string. | ltrim('zzzytrim','xyz') | trim |
| md5(string text) | text | Calculate the MD of the given string5Hash, return 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 | Fill the string to the length length by padding characters fill (default is a space). If the string is already longer than length, it will be truncated. | rpad('hi', 5, 'xy') | hixyx |
| rtrim(string text [, character text]) | text | Remove the longest word containing only character(s) (default is a space) from the end of the string string. | rtrim('trimxxxx','x') | trim |
| split_part(string text, delimiter text, field int) | text | Return the generated substring of the fieldth by splitting string with delimiter1 Base). | split_part('abc~@~def~@~ghi', '~@~', 2) | def |
| strpos(string, substring) | text | The position of the declared 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 other 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 character in string that matches the character in from to the corresponding character in to. | translate('12345', '14', 'ax') | a23x5 |
| 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 time 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 number 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 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) |
Reference article: https://blog.csdn.net/sun5769675/article/details/50628979