PostgreSQL tutorial (5): Functions and operators (1), postgresql Operators

PostgreSQL tutorial (5): Functions and operators (1), postgresql Operators

I. logical operators:

Common logical operators include AND, OR, and not. Its semantics is exactly the same as that of logical operators in other programming languages.

Ii. Comparison operators:

The following is a list of comparison operators provided by PostgreSQL:

The comparison operator can be used for all data types that can be compared. All comparison operators are binary operators and return the boolean type. Besides comparison operators, we can also use BETWEEN statements, such:
A BETWEEN x AND y is equivalent to a> = x AND a <= y
A not between x AND y is equivalent to a <x OR a> y

Iii. mathematical functions and operators:

The following is a list of mathematical operators provided by PostgreSQL:

The bitwise operator can only be used for integer types, while other operators can be used for all numeric data types. Bitwise operators can also be used for bit-string bit and bit varying,

The following is a list of mathematical functions provided by PostgreSQL. It should be noted that many of these functions have multiple forms, except that the parameter types are different. Unless otherwise specified, any function in a specific form returns the same data type as its parameter.

Trigonometric function list:

Iv. String functions and operators:

The following is a list of string operators provided by PostgreSQL:

 

Function	Return type	Description	Example	Result
String | string	Text	String connection	'Post' | 'gresql'	PostgreSQL
Bit_length (string)	Int	Number of binary digits in a string	Bit_length ('job ')	32
Char_length (string)	Int	Number of characters in a string	Char_length ('job ')	4
Convert (string using conversion_name)	Text	Use the specified conversion name to change the encoding.	Convert ('postgresql 'using iso_8859_1_to_utf8)	'Postgresql'
Lower (string)	Text	Converts a string to lowercase.	Lower ('Tom ')	Tom
Octet_length (string)	Int	Number of bytes in a string	Octet_length ('job ')	4
Overlay (string placing string from int [for int])	Text	Replace substring	Overlay ('txxxxas 'placing' from 2 for 4)	Thomas
Position (substring in string)	Int	Position 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 matching POSIX Regular Expressions	Substring ('Thomas 'from'... $ ')	Mas
Substring (string from pattern for escape)	Text	Extract substrings matching SQL Regular Expressions	Substring ('Thomas 'from' % # "o_a #" _ ''#')	Oma
Trim ([leading | trailing | both] [characters] from string)	Text	Removes the longest string that contains only characters (a blank space by default) from the beginning/end/both sides of the string.	Trim (both 'X' from 'xtomxx ')	Tom
Upper (string)	Text	Converts a 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	Deletes the longest string that contains only characters in characters (blank by default) from the beginning and end of a string.	Btrim ('xyxtrimyyx ', 'xy ')	Trim
Chr (int)	Text	ASCII characters	Chr (65)	A
Convert (string text, [src_encoding name,] dest_encoding name)	Text	Converts a string to dest_encoding.	Convert ('text _ in_utf8', 'utf8', 'latin1 ')	Text_in_utf8 encoded in ISO 8859-1
Initcap (text)	Text	Convert the first denominator of each word into uppercase, and keep the other lowercase letters. A word is a string of letters and numbers separated by non-alphanumeric characters.	Initcap ('Hi thomas ')	Hi Thomas
Length (string text)	Int	Number of characters in string	Length ('job ')	4
Lpad (string text, length int [, fill text])	Text	Fill the string with the length by filling the character fill (blank by default. If the string length is longer than the length, it is truncated (on the right ).	Lpad ('hi', 5, 'xy ')	Xyxhi
Ltrim (string text [, characters text])	Text	Delete the longest string that contains only characters (blank by default) from the beginning of the string.	Ltrim ('zzzytrim', 'xyz ')	Trim
Md5 (string text)	Text	Calculates the MD5 hash of the string and returns the result in hexadecimal format.	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 the sub-strings from in the string with the sub-string.	Replace ('abcdefabcdef ', 'cd', 'xx ')	AbXXefabXXef
Rpad (string text, length int [, fill text])	Text	Fill the string with the length by filling the character fill (blank by default. If the string is longer than the length, it is truncated.	Rpad ('hi', 5, 'xy ')	Hixyx
Rtrim (string text [, character text])	Text	Removes the longest character that contains only character (blank by default) from the end of the string.	Rtrim ('trimxxxx', 'x ')	Trim
Split_part (string text, delimiter text, field int)	Text	Returns the field substring (1 Base) generated based on 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 substrings.	Substr ('alphabet ', 3, 2)	Ph
To_ascii (text [, encoding])	Text	Converts text from other encodings to ASCII.	To_ascii ('karel ')	Karel
To_hex (number int/bigint)	Text	Converts a number to its corresponding hexadecimal representation.	To_hex (9223372036854775807)	7 fffffffffffff
Translate (string text, from text, to text)	Text	Converts any character contained in a string that matches the character in the from statement to the character in the to statement.	Translate ('2013', '14', 'ax ')	A23x5

5. Bit String functions and operators:

For bit and bit varying types, in addition to common comparison operators, you can also use the bit string functions and operators provided by PostgreSQL in the following list, the bit string operands of &, | and # must be of the same length. The length of the original bit string is retained during the shift.

In addition to the operators mentioned in the preceding list, you can also use string functions such as length, bit_length, octet_length, position, and substring. In addition, we can switch back and forth between integers and bits, for example:
Copy codeThe Code is as follows:
MyTest = # SELECT 44: bit (10 );
Bit
------------
0000101100
(1 row)
MyTest = # SELECT 44: bit (3 );
Bit
-----
100
(1 row)
MyTest = # SELECT cast (-44 as bit (12 ));
Bit
--------------
111111010100
(1 row)
MyTest = # SELECT '200': bit (4): integer;
Int4
------
14
(1 row)

NOTE: If it is only converted to "bit", it means to be converted to bit (1), so it will only be converted to the integer's second bit.

All information provided in this blog is sourced from the official PostgreSQL documentation. The main purpose of this blog is to facilitate future access.