postgresql常用函数及操作符及类型转换等

一、逻辑操作符：

常用的逻辑操作符有：AND、OR和NOT。其语义与其它编程语言中的逻辑操作符完全相同。

二、比较操作符：

下面是postgresql中提供的比较操作符列表：

操作符	描述
‘<’	小于
‘>’	大于
‘<=’	小于或等于
‘>=’	大于或等于
‘=’	等于
‘!=’	不等于

比较操作符可以用于所有可以比较的数据类型。所有比较操作符都是双目操作符，且返回Boolean类型。除了比较操作符以外，我们还可以使用betweeN语句，如：
a betweeN x AND y 等效于 a >= x AND a <= y
a NOT betweeN x AND y 等效于 a < x OR a > y

三、 数学函数和操作符：

下面是postgresql中提供的数学操作符列表：

操作符	描述	例子	结果
+	加	2 + 3	5
-	减	2 - 3	-1
*	乘	2 * 3	6
/	除	4 / 2	2
%	模	5 % 4	1
^	幂	2.0 ^ 3.0	8
!	阶乘	5 !	120
!!	阶乘	!! 5	120
@	绝对值	@ -5.0	5
&	按位AND	91 & 15	11
丨	按位OR	32 丨 3	35
‘#’	按位XOR	17 # 5	20
~	按位NOT	~1	-2
‘<<’	按位左移	1 << 4	16
‘>>’	按位右移	8 >> 2	2

按位操作符只能用于整数类型，而其它的操作符可以用于全部数值数据类型。按位操作符还可以用于位串类型bit和bit varying。

下面是postgresql中提供的数学函数列表，需要说明的是，这些函数中有许多都存在多种形式，区别只是参数类型不同。除非特别指明，任何特定形式的函数都返回和它的参数相同的数据类型。

@H_771_20@mod(y,X) @H_771_20@mod(9,4)

函数	返回类型	描述	例子	结果
abs(X)		绝对值	abs(-17.4)	17.4
cbrt(doublE)		立方根	cbrt(27.0)	3
ceil(double/numeriC)		不小于参数的最小的整数	ceil(-42.8)	-42
degrees(doublE)		把弧度转为角度	degrees(0.5)	28.6478897565412
exp(double/numeriC)		自然指数	exp(1.0)	2.71828182845905
floor(double/numeriC)		不大于参数的最大整数	floor(-42.8)	-43
ln(double/numeriC)		自然对数	ln(2.0)	0.693147180559945
log(double/numeriC)		10为底的对数	log(100.0)	2
log(b numeric,x numeriC)	numeric	指定底数的对数	log(2.0,64.0)	6.0000000000
	取余数	1
pi()	double	“π”常量	pi()	3.14159265358979
power(a double,b doublE)	double	求a的b次幂	power(9.0,3.0)	729
power(a numeric,b numeriC)	numeric	求a的b次幂	power(9.0,3.0)	729
radians(doublE)	double	把角度转为弧度	radians(45.0)	0.785398163397448
random()	double	0.0到1.0之间的随机数值	random()
round(double/numeriC)		圆整为最接近的整数	round(42.4)	42
round(v numeric,s int)	numeric	圆整为s位小数数字	round(42.438,2)	42.44
sign(double/numeriC)		参数的符号(-1,+1)	sign(-8.4)	-1
sqrt(double/numeriC)		平方根	sqrt(2.0)	1.4142135623731
trunc(double/numeriC)		截断(向零靠近)	trunc(42.8)	42
trunc(v numeric,s int)	numeric	截断为s小数位置的数字	trunc(42.438,2)	42.43

三角函数列表：

函数	描述
acos(X)	反余弦
asin(X)	反正弦
atan(X)	反正切
atan2(x,y)	正切 y/x 的反函数
cos(X)	余弦
cot(X)	余切
sin(X)	正弦
tan(X)	正切

四、字符串函数和操作符：

下面是postgresql中提供的字符串操作符列表：

@H_771_20@mas @H_771_20@md5(String text) @H_771_20@md5(‘abc’)

函数	返回类型	描述	例子	结果
String 丨丨 String	text	字串连接	‘Post’ 丨丨 ‘gresql’	POSTGResql
bit_length(String)	int	字串里二进制位的个数	bit_length(‘jose’)	32
char_length(String)	int	字串中的字符个数	char_length(‘jose’)	4
convert(String using conversion_Name)	text	使用指定的转换名字改变编码。	convert(‘POSTGResql’ using iso_8859_1_to_utf8)	‘POSTGResql’
lower(String)	text	把字串转化为小写	lower(‘TOM’)	tom
octet_length(String)	int	字串中的字节数	octet_length(‘jose’)	4
overlay(String placing String from int [for int])	text	替换子字串	overlay(‘Txxxxas’ placing ‘hom’ from 2 for 4)	Thomas
position(subString in String)	int	指定的子字串的位置	position(‘om’ in ‘Thomas’)	3
subString(String [from int] [for int])	text	抽取子字串	subString(‘Thomas’ from 2 for 3)	hom
subString(String from pattern)	text	抽取匹配 POSIX 正则表达式的子字串	subString(‘Thomas’ from ‘…$’)
subString(String from pattern for escapE)	text	抽取匹配sql正则表达式的子字串	subString(‘Thomas’ from ‘%#”o_a#”_’ for ‘#’)	oma
trim([leading丨Trailing 丨 both] [characters] from String)	text	从字串String的开头/结尾/两边/ 删除只包含characters(缺省是一个空白)的最长的字串	trim(both ‘x’ from ‘xTomxx’)	Tom
upper(String)	text	把字串转化为大写。	upper(‘tom’)	TOM
ascii(text)	int	参数第一个字符的ASCII码	ascii(‘x’)	120
btrim(String text [,characters text])	text	从String开头和结尾删除只包含在characters里(缺省是空白)的字符的最长字串	btrim(‘xyxtrimyyx’,’xy’)	trim
chr(int)	text	给出ASCII码的字符	chr(65)	A
convert(String text,[src_enCoding name,] dest_enCoding Name)	text	把字串转换为dest_enCoding	convert( ‘text_in_utf8’,‘UTF8’,‘laTin1’)	以ISO 8859-1编码表示的text_in_utf8
initcap(text)	text	把每个单词的第一个子母转为大写，其它的保留小写。单词是一系列字母数字组成的字符，用非字母数字分隔。	initcap(‘hi thomas’)	Hi Thomas
length(String text)	int	String中字符的数目	length(‘jose’)	4
LPAD(String text,length int [,fill text])	text	通过填充字符fill(缺省时为空白)，把String填充为长度length。 如果String已经比length长则将其截断(在右边)。	LPAD(‘hi’,5,‘xy’)	xyxhi
ltrim(String text [,characters text])	text	从字串String的开头删除只包含characters(缺省是一个空白)的最长的字串。	ltrim(‘zzzytrim’,’xyz’)	trim
text	计算给出String的MD5散列，以十六进制返回结果。
repeat(String text,number int)	text	重复String number次。	repeat(‘Pg’,4)	PgPgPgPg
replace(string text,from text,to text)	text	把字串String里出现地所有子字串from替换成子字串to。	replace(‘abcdefabcdef’,‘cd’,‘XX’)	abXXefabXXef
rpad(String text,fill text])	text	通过填充字符fill(缺省时为空白)，把String填充为长度length。如果String已经比length长则将其截断。	rpad(‘hi’,‘xy’)	hixyx
rtrim(string text [,character text])	text	从字串String的结尾删除只包含character(缺省是个空白)的最长的字	rtrim(‘trimxxxx’,’x’)	trim
split_part(String text,delimiter text,fIEld int)	text	根据delimiter分隔String返回生成的第fIEld个子字串(1 BasE)。	split_part(‘abc~@~def~@~ghi’,‘~@~’,2)	def
strpos(String,subString)	text	声明的子字串的位置。	strpos(‘high’,’ig’)	2
substr(String,from [,count])	text	抽取子字串。	substr(‘Alphabet’,3,2)	ph
to_ascii(text [,enCoding])	text	把text从其它编码转换为ASCII。	to_ascii(‘Karel’)	Karel
to_hex(number int/bigint)	text	把number转换成其对应地十六进制表现形式。	to_hex(9223372036854775807)	7fffffffffffffff
translate(string text,to text)	text	把在String中包含的任何匹配from中的字符的字符转化为对应的在to中的字符。	translate(‘12345’,‘14’,‘ax’)	a23x5

五、类型转换相关函数：

Function	Return Type	Description	Example
TO_CHAR(timestamp,text)	text	convert time stamp to String	TO_CHAR(CURRENT_TIMESTAMP,‘HH12:MI:SS’)
TO_CHAR(interval,text)	text	convert interval to String	TO_CHAR(interval ‘15h 2m 12s’,‘HH24:MI:SS’)
TO_CHAR(int,text)	text	convert Integer to String	TO_CHAR(125,‘999’)
TO_CHAR(double precision,text)	text	convert real/double precision to String	TO_CHAR(125.8::real,‘999D9’)
TO_CHAR(numeric,text)	text	convert numeric to String	TO_CHAR(-125.8,‘999D99S’)
to_date(text,text)	date	convert String to date	to_date(‘05 Dec 2000’,‘DD Mon YYYY’)
to_number(text,text)	numeric	convert String to numeric	to_number(‘12,454.8-‘,‘99G999D9S’)
to_timestamp(text,text)	timestamp	with time zone convert String to time stamp	to_timestamp(‘05 Dec 2000’,‘DD Mon YYYY’)
to_timestamp(double precision)	timestamp	with time zone convert Unix epoch to time stamp	to_timestamp(1284352323)