PostgreSQL built-in functions, also known as aggregate functions, are used to perform processing on string or numeric data.
The following is a list of all the common 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 particular column.
MIN function: used to query the minimum value in a particular column.
AVG function: used to calculate the average in a particular column.
SUM function: used to calculate the sum of all values in a numeric column.
ARRAY function: used to enter values (including null) to add to the array.
Numeric function: a function that fully lists the operands required in SQL.
String function: a function that fully lists the required operation characters in SQL.
5.44.1. Mathematical function ¶
The following is a list of mathematical functions provided in PostgreSQL, and it is important to note that many of these functions have multiple forms, except that the parameter types are different. Unless otherwise specified, any particular form of function returns the same data type as its parameters.
Function | Return type | Description | Examples | Result |
|---|---|---|---|---|
Abs (x) | absolute value | Abs (- 17.4) | 17.4 | |
Cbrt (double) | cubic root | Cbrt (27.0) | 3 | |
Ceil (double/numeric) | The smallest integer not less than the parameter | Ceil (- 42.8) | -42 | |
Degrees (double) | Convert radians to angles | Degrees (0.5) | 28.64788976 | |
Exp (double/numeric) | Natural index | Exp (1.0) | 2.718281828 | |
Floor (double/numeric) | The largest integer not greater than the parameter | Floor (- 42.8) | -43 | |
Ln (double/numeric) | Natural logarithm | Ln (2.0) | 0.693147181 | |
Log (double/numeric) | Logarithm with base 10 | Log (100.0) | 2 | |
Log (b numeric,x numeric) | Numeric | Specify the logarithm of the base | Log (2.0,64.0) | 6 |
Mod (y, x) | Take the remainder | Mod (9pr 4) | 1 | |
Pi () | Double | “π” constant | Pi () | 3.141592654 |
Power (a double, b double) | Double | Find the b power of a | Power (9.0,3.0) | 729 |
Power (a numeric, b numeric) | Numeric | Find the b power of a | Power (9.0,3.0) | 729 |
Radians (double) | Double | Turn the angle into radians | Radians (45.0) | 0.785398163 |
Random () | Double | 0.0到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) | 42.44 |
Sign (double/numeric) | The symbol of the parameter (- 1, 0, 1, 1). | Sign (- 8.4) | -1 | |
Sqrt (double/numeric) | Square root | Sqrt (2.0) | 1.414213562 | |
Trunc (double/numeric) | Truncate (close to zero) | Trunc (42.8) | 42 | |
Trunc (v numeric, s int) | Numeric | A number truncated to s decimal position | Trunc (42.438) | 42.43 |
5.44.2. Trigonometric function list ¶
Function | Description |
|---|---|
Acos (x) | Inverse cosine |
Asin (x) | Arcsine |
Atan (x) | Anyway tangent |
Atan2 (x, y) | The inverse function of tangent y _ (b) x |
Cos (x) | CoSine |
Cot (x) | Cotangent |
Sin (x) | Sinusoidal |
Tan (x) | Tangent |
5.44.3. String functions and operators ¶
The following is a list of string operators available in PostgreSQL:
Function | Return type | Description | Examples | Result |
|---|---|---|---|---|
String | | string | Text | String connection | ‘Post’ ‘greSQL’ | PostgreSQL |
Bit_length (string) | Int | The number of binary bits in a string | Bit_length (‘jose’) | 32 |
Char_length (string) | Int | The number of characters in a string | Char_length (‘jose’) | 4 |
Convert (string using conversion_name) | Text | Changes the 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 | Replace substring | Overlay (‘Txxxxas’ placing’ hom’ from 2 for 4) | Thomas |
Position (substring in string) | Int | The location 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 substrings that match POSIX regular expressions | Substring (‘Thomas’ from’… $’) | Mas |
Substring (string from pattern for escape) | Text | Extract substrings that match SQL regular expressions | Substring (‘Thomas’ from’% # “o_a#” _ ‘for’’) | Oma |
trim([leading丨trailing 丨 both] [characters] from string) | Text | Removes the longest string containing only characters (default is a blank) from the beginning / end / both sides of the string string | 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 | Removes the longest string of characters contained only in the characters (default is blank) from the beginning and end of the string | Btrim (‘xyxtrimyyx’,’xy’) | Trim |
Chr (int) | Text | Give the character of the ASCII code | Chr (65) | A |
Convert (string text [src_encoding name,] Dest_encoding name) | Text | 把字串转换为dest_encoding | Convert (‘text_in_utf8’,’ UTF8’, ‘LATIN1’) | Text_in_utf8 expressed in ISO 8859-1 coding |
Initcap (text) | Text | Change the first child of each word to uppercase and keep the rest in lowercase. A word is a series 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 | Fill the string with the length length by filling the character fill (the default is blank). Truncate string if it is already longer than length (on the right). | Lpad (‘hi’, 5,’ xy’) | Xyxhi |
Ltrim (string text [, characters text] ) | Text | Removes the longest string containing only characters (default is a blank) from the beginning of the string string. | Ltrim (‘zzzytrim’,’xyz’) | Trim |
Md5 (string text) | Text | The MD5 hash of string is calculated and the result is returned 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 substrings from that appear in the string string with substrings to. | Replace (‘abcdefabcdef’,’ cd’, ‘XX’) | AbXXefabXXef |
Rpad (string text, length int [, fill text] ) | Text | Fill the string with the length length by filling the character fill (the default is blank). Truncate string if it is already longer than length. | Rpad (‘hi’, 5,’ xy’) | Hixyx |
Rtrim (string text [, character text] ) | Text | Removes the longest word containing only character (default is a blank) from the end of the string string | Rtrim (‘trimxxxx’,’x’) | Trim |
Split_part (string text, delimiter text, field int) | Text | Separates the string based on delimiter and returns the generated field substring (1 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 substrings. | Substr (‘alphabet’, 3,2) | Ph |
To_ascii (text [, encoding] ) | Text | Convert text from other codes 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 | Converts any character contained in string that matches the character in from to the corresponding character in to. | Translate (‘12345,’ 14, ‘ax’) | A23x5 |
5.44.4. Type conversion correlation function ¶
Function | Return type | Description | Example |
|---|---|---|---|
To_char (timestamp, text) | Text | Convert a timestamp to a string | To_char (current_timestamp, ‘HH12:MI:SS’) |
To_char (interval, text) | Text | Convert the interval to a string | To_char (interval ‘15h 2m 12s,’ HH24:MI:SS’) |
To_char (int, text) | Text | Convert an integer to a string | To_char (125,999’) |
To_char (double precision, text) | Text | Double precision conversion to string | To_char (125.8::real, ‘999D9’) |
To_char (numeric, text) | Text | Convert numbers to strings | To_char (- 125.8, ‘999D99S’) |
To_date (text, text) | Date | Convert a string to a date | To_date (‘05 Dec 2000 years,’DD Mon YYYY’) |
To_number (text, text) | Numeric | Convert a string to a number | To_number (‘12454.8 Mutual,’ 99G999D9S’) |
To_timestamp (text, text) | Timestamp | Convert to the specified time format time zone convert string to time stamp | To_timestamp (‘05 Dec 2000 years,’DD Mon YYYY’) |
To_timestamp (double precision) | Timestamp | Convert the UNIX era into a timestamp | To_timestamp (1284352323) |
Reference article: https://blog.csdn.net/sun5769675/article/details/50628979