Aggregation function
- COUNT function: Used to COUNT 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.
- The SUM function: Calculates the SUM of all the values of a numeric column.
- ARRAY function: Used to add input values (including null) to an ARRAY.
- Numeric function: A function that lists the entire operands required in an SQL.
- String function: a function that lists all required operation characters in an SQL file.
String functions and operators
| function | The return type | describe | example | The results of |
|---|---|---|---|---|
| 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 | Number of characters in a string | char_length(‘jose’) | 4 |
| convert(string using conversion_name) | text | Changes the encoding using the specified transformation name. | convert(‘PostgreSQL’ using iso_8859_1_to_utf8) | ‘PostgreSQL’ |
| lower(string) | text | Convert the 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 the 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 | Extract a substring | substring(‘Thomas’ from 2 for 3) | hom |
| substring(string from pattern) | text | Extracts a substring that matches a POSIX regular expression | The substring (‘ Thomas’ from ‘… $’) | mas |
| substring(string from pattern for escape) | text | Extracts substrings that match SQL regular expressions | substring(‘Thomas’ from ‘%#”o_a#”_’ for ‘#’) | oma |
| Trim ([leading | trailing | both] [characters] from string) | text | Remove the longest string containing only characters(a blank by default) from the beginning/end/sides/of string | trim(both ‘x’ from ‘xTomxx’) | Tom |
| upper(string) | text | Converts the string to uppercase. | upper(‘tom’) | TOM |
| ascii(text) | int | The ASCII code of the first character of the argument | ascii(‘x’) | 120 |
| btrim(string text [, characters text]) | text | Removes the longest string of characters contained only in characters (default blank) from the beginning and end of string | btrim(‘xyxtrimyyx’,’xy’) | trim |
| chr(int) | text | Gives characters in 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 encoded in ISO 8859-1 |
| initcap(text) | text | Capitalize the first part of each word and keep the rest in lower case. 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 a string | length(‘jose’) | 4 |
| lpad(string text, length int [, fill text]) | text | Fill the string with length by filling the character fill(blank by default). 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(a blank by default) from the beginning of string. | ltrim(‘zzzytrim’,’xyz’) | trim |
| md5(string text) | text | Computes the MD5 hash of the string, returning the result in hexadecimal. | md5(‘abc’) | |
| repeat(string text, number int) | text | Repeat string number several times. | repeat(‘Pg’, 4) | PgPgPgPg |
| replace(string text, from text, to text) | text | Replace all substrings that occur in string with substrings to. | replace(‘abcdefabcdef’, ‘cd’, ‘XX’) | abXXefabXXef |
| rpad(string text, length int [, fill text]) | text | Fill the string with length by filling the character fill(blank by default). Truncate string if it is already longer than length. | rpad(‘hi’, 5, ‘xy’) | hixyx |
| rtrim(string text [, character text]) | text | Removes the longest word that contains only character(default blank) from the end of the string | rtrim(‘trimxxxx’,’x’) | trim |
| split_part(string text, delimiter text, field int) | text | Delimiter returns the generated field substring (1 Base) according to the delimiter string. | 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 | Converts the number to its corresponding hexadecimal representation. | to_hex(9223372036854775807) | 7fffffffffffffff |
| translate(string text, from text, to text) | text | Converts any characters contained in string that match characters in from to the corresponding characters in to. | translate(‘12345′, ’14’, ‘ax’) | a23x5 |
Type conversion related functions
| function | The return type | describe | The instance |
|---|---|---|---|
| to_char(timestamp, text) | text | Converts the timestamp to a string | to_char(current_timestamp, ‘HH12:MI:SS’) |
| to_char(interval, text) | text | Converts the interval to a string | to_char(interval ’15h 2m 12s’, ‘HH24:MI:SS’) |
| to_char(int, text) | text | An integer is converted to a string | to_char(125, ‘999’) |
| to_char(double precision, text) | text | The double is converted to a string | To_char (125.8: : real, ‘999 d9’) |
| to_char(numeric, text) | text | Numbers are converted to strings | To_char (125.8, ‘999 d99s’) |
| to_date(text, text) | date | String is converted to date | to_date(’05 Dec 2000′, ‘DD Mon YYYY’) |
| to_number(text, text) | numeric | Converts a string to a number | To_number (‘ 12454.8 – ‘, ’99 g999d9s’) |
| 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 the UNIX era to a timestamp | to_timestamp(1284352323) |
Mathematical function
The following 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 otherwise specified, any function of a particular form returns the same data type as its arguments.
| function | The return type | describe | example | The results of |
|---|---|---|---|---|
| abs(x) | The absolute value | Abs (17.4) | 17.4 | |
| cbrt(double) | Cube root | CBRT (27.0) | 3 | |
| ceil(double/numeric) | The smallest integer that is not less than the parameter | The ceil (42.8) | – 42 | |
| degrees(double) | Let’s convert radians to angles | Degrees (0.5) | 28.6478897565412 | |
| exp(double/numeric) | Natural index | Exp (1.0) | 2.71828182845905 | |
| floor(double/numeric) | The largest integer not greater than the parameter | Floor (42.8) | 43 – | |
| ln(double/numeric) | Natural logarithm | Ln (2.0) | 0.693147180559945 | |
| log(double/numeric) | Log base 10 | The log (100.0) | 2 | |
| log(b numeric,x numeric) | numeric | Specifies the logarithm of the base | The log (2.0, 64.0) | 6.0000000000 |
| mod(y, x) | modulo | The mod (9, 4) | 1 | |
| pi() | double | “PI” constants | pi() | 3.14159265358979 |
| power(a double, b double) | double | Take a to the b power | Power (9.0, 3.0) | 729 |
| power(a numeric, b numeric) | numeric | Take a to the b power | Power (9.0, 3.0) | 729 |
| radians(double) | double | Let’s convert the Angle to radians | Radians (45.0) | 0.785398163397448 |
| random() | double | Random values 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 | Rounded to s decimal digits | Round (42.438, 2) | 42.44 |
| sign(double/numeric) | The symbol of the argument (-1,0,+1) | Sign (8.4) | – 1 | |
| sqrt(double/numeric) | The square root | SQRT (2.0) | 1.4142135623731 | |
| trunc(double/numeric) | Truncate (approach to zero) | Trunc (42.8) | 42 | |
| trunc(v numeric, s int) | numeric | A number truncated to the decimal position of s | Trunc (42.438, 2) | 42.43 |
Trigonometric list
| function | describe |
|---|---|
| acos(x) | Arc cosine |
| asin(x) | arcsine |
| atan(x) | The arctangent |
| atan2(x, y) | The inverse of the tangent of y over x |
| cos(x) | cosine |
| cot(x) | cotangent |
| sin(x) | sine |
| tan(x) | tangent |