最近几年来，地理信息系统无论是在理论上还是应用上都处在一个飞速发展的阶段。 GIS被应用于多个领域的建模和决策支持，如城市管理、区划、环境整治等等，地理信息成为信息时代重要的组成部分之一； “数字地球”概念的提出，更进一步推动了作为其技术支撑的GIS的发展。 与此同时，一些学者致力于相关的理论研究，如空间感知、空间数据误差、空间关系的形式化等等。 这恰好说明了地理信息系统作为应用技术和学科的两个方面，并且这两个方面构成了相互促进的发展过程。
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