PostgreSQL Tutorial (V): A detailed explanation of functions and operators (1) _postgresql

One, the logical operator:

The common logical operators are: and, or and not. The semantics are exactly the same as the logical operators in other programming languages.

Second, the comparison operator:

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

Comparison operators can be used for all data types that can be compared. All comparison operators are binocular operators, and Boolean types are returned. In addition to comparison operators, we can also use between statements, such as:
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 the mathematical operators provided in PostgreSQL:

Bitwise operators are available only for integer types, while other operators can be used for all numeric data types. Bitwise operators can also be used for bit and bit varying of a string type,

The following is a list of the mathematical functions provided in PostgreSQL, and it should be explained that many of these functions exist in multiple forms, except that the parameter types are different. Unless specifically specified, any particular form of function returns the same data type as its parameter.

Trigonometric Functions list:

Four, String functions and operators:

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

Function	return type	Describe	Example	Results
string | | String	Text	String connection	' Post ' | | ' Gresql '	PostgreSQL
Bit_length (String)	Int	Number of 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	Converts 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 a child string	Overlay (' Txxxxas ' placing ' hom ' from 2 to 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 child string	SUBSTRING (' Thomas ' from 2 for 3)	Hom
SUBSTRING (string from pattern)	Text	Extracts substrings that match POSIX regular expressions	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	The longest string that contains only characters (default is a blank) from the start/end/Side/delete of string string	Trim (both ' x ' from ' Xtomxx ')	Tom
Upper (String)	Text	Converts the string to uppercase.	Upper (' Tom ')	TOM
ASCII (text)	Int	ASCII code for the first character of a parameter	ASCII (' x ')	120
Btrim (string text [, characters text])	Text	Deletes the longest string of characters that are contained only in characters (default is blank) from the beginning and end of a string	Btrim (' Xyxtrimyyx ', ' xy ')	Trim
Chr (int)	Text	Given the ASCII code character	Chr (65)	A
Convert (string text, [src_encoding name,] dest_encoding name)	Text	Converting strings to Dest_encoding	Convert (' Text_in_utf8 ', ' UTF8 ', ' LATIN1 ')	Text_in_utf8 in ISO 8859-1 encoding
Initcap (text)	Text	The first child of each word is capitalized, others are lowercase. A word is a series of alphanumeric characters separated by a non-alphanumeric character.	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 length lengths by filling the character fill (blank by default). If string is already longer than length, it is truncated (on the right).	Lpad (' Hi ', 5, ' XY ')	Xyxhi
LTrim (string text [, characters text])	Text	Deletes the longest string that contains only characters (the default is a blank) from the beginning of string string.	LTrim (' Zzzytrim ', ' xyz ')	Trim
MD5 (string text)	Text	Computes a MD5 hash of string, returning the result in hexadecimal.	MD5 (' abc ')
Repeat (string text, number int)	Text	Repeat string number twice.	Repeat (' Pg ', 4)	Pgpgpgpg
Replace (string text, from text, to text)	Text	Replaces all substring occurrences of a string string from a substring to.	Replace (' Abcdefabcdef ', ' CD ', ' XX ')	Abxxefabxxef
Rpad (string text, length int [, fill text])	Text	Fill the string with length lengths by filling the character fill (blank by default). If string is already longer than length, it is truncated.	Rpad (' Hi ', 5, ' XY ')	Hixyx
RTrim (string text [, character text])	Text	Remove the longest word that contains only character (default is a blank) from the end of string string	RTrim (' trimxxxx ', ' x ')	Trim
Split_part (string text, delimiter text, field int)	Text	Returns the generated first field substring (1 base) based on the delimiter separator string.	Split_part (' Abc~@~def~@~ghi ', ' ~@~ ', 2)	Def
Strpos (string, substring)	Text	The position of the child string that is declared.	Strpos (' High ', ' IG ')	2
SUBSTR (String, from [, Count])	Text	Extracts the substring of a string.	substr (' Alphabet ', 3, 2)	Ph
To_ascii (text [, encoding])	Text	Converts the text from other encodings to ASCII.	To_ascii (' Karel ')	Karel
To_hex (number Int/bigint)	Text	Converts number to its corresponding hexadecimal representation.	To_hex (9223372036854775807)	7fffffffffffffff
Translate (string text, from text, to text)	Text	Converts the character in any matching from character in string to the corresponding character in to.	Translate (' 12345 ', ' ', ', ' ax ')	a23x5

Five, bit string functions and operators:

For type bit and bit varying, in addition to the common comparison operators, you can also use the bit string functions and operators provided by PostgreSQL in the following list, where the number of string operands for &, | and # must be equal. Retains the length of the original bit string as it shifts.

In addition to the operators mentioned in the list above, bit strings can also use String functions: Length, bit_length, octet_length, position, substring. In addition, we can switch back and forth between the integer and the bit, such as:

Copy Code code 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 ' 1110 ':: Bit (4):: integer;
Int4
------
14
(1 row)

Note: If you just convert to bit, it means to convert to bit (1), so you will only convert to the lowest bit of an integer.

All of the information provided in this blog originates from the PostgreSQL official document, and the main purpose of writing the blog is to facilitate future access, hereby declares.