PostgreSQL函数和运算符(一)

 PostgreSQL为内置数据类型提供了大量的函数和运算符。用户也可以定义自己的函数（参考第11章）。在psql中执行命令/df和/do可以分别列出可用的函数和运算符的列表。

       本章中的大部分函数和运算符都是SQL标准中定义的，也有一部分是PostgreSQL自己扩展的。SQL标准定义了一些有特殊语法的字符串函数，这些函数使用特殊关键字而不是逗号来分隔参数，例如from和for。

7.1 逻辑运算符

   常用的逻辑运算符有：

AND

OR

NOT

   SQL 使用三值的布尔逻辑，空值代表"unknown"。 下面逻辑运算符的真值表：

a	b	a AND b		a OR b
TRUE	TRUE	TRUE		TRUE
TRUE	FALSE	FALSE		TRUE
TRUE	NULL	NULL		TRUE
FALSE	FALSE	FALSE		FALSE
FALSE	NULL	FALSE		NULL
NULL	NULL	NULL		NULL
a	NOT a
TRUE	FALSE
FALSE	TRUE
NULL	NULL

 

      运算符 AND 和 OR 满足交换律。

 

7.2 比较运算符

    表 7-1 列出了所有的比较运算符。

表7-1 比较运算符

运算符	描述
<	小于
>	大于
<=	小于或等于
>=	大于或等于
=	等于
<> 或 !=	不等于

    注意: != 运算符在进行此法分析时会被自动转换成 <>。所以不可能让!= 和 <> 实现不同的功能。

    比较运算符可以用于所有可以进行比较的操作的数据类型。所有比较运算符都是二元运算符，返回 boolean类型的结果，类似“1 < 2 < 3” 这样的表达式是非法的。

    除了比较运算符，还可以使用 BETWEEN谓词。 a BETWEEN x AND y 等价于

a >= x AND a <= y。类似地，a NOT BETWEEN x AND y 等价于 a < x OR a > y。

这两种形式之间没有什么区别。使用BETWEEN谓词时要注意，AND左边的操作数应该小于或等于它右边的操作数，否则可能会得到意想不到的结果，例如：

（1）select 3 between 4 and 2;

 ?column?

----------

 f                  --结果为假

(1 row)

（2）select 3 between 2 and 4;

 ?column?

----------

 t                 --结果为真

(1 row)

 

    BETWEEN SYMMETRIC和BETWEEN的功能类似，但是在使用BETWEEN SYMMETRIC时，AND运算符左边的操作数不一定要小于或者等于它右边的操作数。例如：

（1）select 3 between symmetric 2 and 4;

 ?column?

----------

 t                --结果为真

(1 row)

（2）select 3 between symmetric 4 and 2;

 ?column?

----------

 t               --结果为真

(1 row)

   要检查一个值是否是空值，使用下面的语法：

expression IS NULL

expression IS NOT NULL

或者使用下面的等价的但并不标准的语法

expression ISNULL

expression NOTNULL

不能写成expression = NULL，因为两个空值是不相等的。空值代表一个未知的数值，因此无法决定两个未知的数值是否相等，这个规则符合SQL 标准。

 

注意：如果表达式返回一个复合数据类型的值，只有在这个值是空值或者这个值的所有域都是空值的情况下，IS NULL才返回“真”。只有在这个值不是空值和这个值的所有域的值都不是空值的情况下，IS NOT NULL才返回“真”。这个规则是SQL标准定义的。

 

    如果有任何一个操作数是空值，普通的比较运算符的运算结果也是空值（表示"未知"）。

    还可以用IS [NOT] DISTINCT FROM 来比较两个表达式的值，语法如下：

expression IS DISTINCT FROM expression

expression IS NOT DISTINCT FROM expression

    如果两个表达式都不是空值，IS DISTINCT FROM的功能与运算符“<>”完全相同。 但是，假如两个表达式的值都是空值，IS DISTINCT FROM将返回“假”，而如果只有一个表达式的值是空值，那么它将返回“真”。

    如果两个表达式都不是空值，IS NOT DISTINCT FROM的功能与运算符“=”完全相同。但是，假如两个表达式的值都是空值，IS NOT DISTINCT FROM将返回“真“，而如果只有一个表达式的值是空值，那么它将返回“假”。

    可以使用下面的谓词来测试布尔类型的数值：

expression IS TRUE

expression IS NOT TRUE

expression IS FALSE

expression IS NOT FALSE

expression IS UNKNOWN

expression IS NOT UNKNOWN

 上面的谓词总是返回真或假，从来不返回空值。空值输入被当做逻辑数值"未知"（UNKNOWN）。注意IS UNKNOWN和 IS NOT UNKNOWN分别与IS NULL和IS NOT NULL相同，只是输入表达式必须是布尔类型的。

 

7.3 数学函数和运算符

   PostgreSQL为许多类型提供了数学运算符。表 7-2 列出了所有的数学运算符。

表 7-2. 数学运算符

运算符	描述	例子	结果
+	加	2 + 3	5
-	减	2 - 3	-1
*	乘	2 * 3	6
/	除 （两个整数相除的结果如果不是整数，会将结果的小数部分去掉，只保留整数部分）	（1）4 / 2 （2）3／2 （3）2／4	（1）2 （2）1 （3）0
%	模除 （求余）	5 % 4	1
^	幂（指数运算）	2.0 ^ 3.0	8
|/	平方根	|/ 25.0	5
||/	立方根	||/ 27.0	3
!	阶乘	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，如表7-3所示。

        表7-3列出了所有的数学函数。在该表中dp表示double precision，除非特别指明，函数的返回值的数据类型和它的参数的数据类型相同。表7-4列出了这些数学函数的实例。处理 double precision 数据的函数大多数是在操作系统的C函数库的基础上实现的。

表7-3. 数学函数

函数	返回值类型	描述
abs(x)	和x类型相同	绝对值
cbrt(dp)	Dp	立方根
ceil(dp 或者 numeric)	与输入相同	不小于参数的最小的整数
ceiling(dp or numeric)	与输入相同	不小于参数的最小整数（ceil 的别名）
degrees(dp)	dp	把弧度转为角度
exp(dp 或 numeric)	与输入相同	自然指数
floor(dp 或 numeric)	与输入相同	不大于参数的最大整数
ln(dp 或 numeric)	与输入相同	自然对数
log(dp 或 numeric)	与输入相同	10 为底的对数
log(b numeric, x numeric)	numeric	指定底数的对数
mod(y, x)	和参数类型相同	除法 y/x 的余数（模）
pi()	Dp	"π" 常量
power(a dp, b dp)	Dp	求a的 b 次幂
power(a numeric, b numeric)	numeric	求a的 b 次幂
radians(dp)	Dp	把角度转为弧度
random()	Dp	0.0 到 1.0 之间的随机数值
round(dp 或者 numeric)	与输入相同	约为最接近参数的整数
round(v numeric, s int)	Numeric	约为最接近参数的有s位小数的数字
setseed(dp)	Int	为以后被调用的 random()函数设置种子
sign(dp 或者 numeric)	和输入相同	参数的符号（-1, 0, +1）
sqrt(dp 或者 numeric)	和输入相同	平方根
trunc(dp 或者 numeric)	和输入相同	去掉参数的小数位
trunc(v numeric, s int)	Numeric	将参数截断为含有 s位小数的数字
width_bucket(op numeric, b1 numeric, b2 numeric, count in)	Int	将b1和b2平分成count个取值区间，取值区间的编号从1开始。   如果b1>b2，则编号为0的取值区间表示的范围是（b1,正无穷大),编号为count+1的取值区间表示的范围是(负无穷大，b2)。   如果b1<b2，则编号为0的取值区间表示的范围是(负无穷大,b1),编号为count+1的取值区间表示的范围是(b2,负无穷大)。   若op落在某个取值区间内，则返回该取值区间的编号。
width_bucket(op dp, b1 dp, b2 dp, count int)	Int	函数功能同上

表7-4. 数学函数实例

例子	结果
abs(-17.4)	17.4
cbrt(27.0)	3
ceil(-42.8)	-42
ceiling(-95.3)	-95
degrees(0.5)	28.6478897565412
exp(1.0)	2.71828182845905
floor(-42.8)	-43
ln(2.0)	0.693147180559945
log(100.0)	2
log(2.0, 64.0)	6.0000000000
mod(9,4)	1
pi()	3.14159265358979
power(9.0, 3.0)	729
power(9.0, 3.0)	729
radians(45.0)	0.785398163397448
random()	每次调用的结果是随机的，例如0.453876388259232
(1)round(42.4) (2)round(42.8)	（1）42 （2）43
(1)round(42.4382, 2) (2)round(42.4322,2)	(1)42.44 (2)42.43
setseed(0.54823)	1177314959
sign(-8.4)	-1
sqrt(2.0)	1.4142135623731
trunc(42.8)	42
(1)trunc(42.4382, 2) (2)trunc(42,2)	(1)42.43 (2)42.00
(1)width_bucket(0.7,1,4,2);   (2)width_bucket(10,1,4,2);   (3)width_bucket(10,4,1,2);   (3)width_bucket(3,4,1,2);	(1)0   (2)3   (3)0   (4)1
width_bucket(0.35, 0.024, 10.06, 5)	1

 

   表7-5列出了所有的三角函数。所有三角函数的参数类型和返回值的类型都是double precision。

表7-5. 三角函数

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

 

 

7.4 字符串函数和运算符

       本节描述用处检查和处理字符串数值的函数和运算符。字符串类型包括类型 character、character varying和 text/除非另外说明，所有下面列出的函数都可以处理这些数据类型，在处理character 类型的时候，要注意它的自动空格填充机制对运算结果的影响。有些函数还可以处理位串类型的数据。表 7-6列出了SQL字符串函数和运算符，表 7-7列出了使用这些运算符的实例。

表 7-6. SQL字符串函数和运算符

函数或运算符	返回值类型	描述
String || string	text	连接两个字符串
String || non-string   或  non-string || string	text	连接一个字符串和另一个非字符串类型的值
bit_length(string)	int	字符串包含的二进制位的个数
char_length(string)或character_length(string)	int	字符串包含的字符的个数
lower(string)	text	将字符串转换成小写的格式
octet_length(string)	int	字符串包含的字节的个数
overlay(string placing string from int [for int])	text	替换字符串中的子串
position(substring in string)	int	查找子串在字符串中出现的位置
substring(string [from int] [for int])	text	从字符串中找出指定的子串。from int表示子串开始的位置，默认从1开始，例如from 2表示子串从string的第二个字符开始。for int表示子串的长度，默认取string从子串开始位置到string的末尾的所有子串，例如for 3表示子串的长度是3。
substring(string frompattern)	text	从字符串中找出匹配POSIX正则表达式的子串，参见第7.7.3节获取模式匹配的详细信息。
substring(string from patternfor escape)	text	从字符串中找出匹配正则表达式的子串，参见第7.7.3节获取模式匹配的详细信息。
trim([leading | trailing | both] [characters] from string)	text	从字符串string的开始、末尾或者开始和末尾删除只包含指定的字符串characters 中的字符的最长的字符串。 如果没有指定参数characters，则它的值默认是空格。 leading表示只删除字符串头部匹配的子串。 trailing 表示只删除字符串尾部匹配的子串。 both表示同时删除字符串头部和尾部匹配的子串。
upper(string)	text	将字符串转换成大写的格式

表 7-7. SQL字符串函数和运算符实例

例子	结果
'Post' || 'greSQL'	PostgreSQL
'Value: ' || 42	Value: 42
bit_length('jose')	32
char_length('jose')	4
lower('TOM')	tom
octet_length('jose')	4
Overlay('Txxxxas' placing 'hom' from 2 for 4)	Thomas
position('om' in 'Thomas')	3
(1)substring('Thomas' from 2 for 3) (2)substring('Thomas' from 1 for 1) (3)substring('Thomas' from 2)	(1)hom (2)T (3)homas
substring('Thomas' from '...$')	mas
substring('Thomas' from '%#"o_a#"_' for '#')	oma
(1)trim(both 'x' from 'xTomxx')   (2)trim(both 'xf' from 'xTomxxf');   (3)trim(both 'gxf' from 'xgTxTomxxf');   (4)trim(both  from '  xgTxTomxxf  ');   (5)trim(leading 'xf' from 'xTomxxf')	(1)Tom (2)Tom (3)TxTom (4)xgTxTomxxf (5)Tomxxf
upper('tom')	TOM

 

    还有其它的字符串运算函数可以用，表7-8列出了这些函数。它们有些在内部用于实现表7-6列出的SQL标准字符串函数。表7-9列出了表7-8中的函数实例。

表 7-8. 其它的字符串函数

 

函数	返回值类型	描述
ascii(string)	int	参数的第一个字符的ASCII编码。对于UTF8类型的字符串，返回它的第一个字符的UTF-8编码。对于其它的多字节编码类型的字符串，参数的第一个字符必须是ASCII类型的字符。
btrim(string text [,characters text])	text	从字符串string的开始和末尾删除只包含指定的字符串characters中的字符的最长的字符串。如果没有指定参数characters，则它的值默认是空格。
chr(int)	text	返回指定的编码值对应的字符。参数的值不能是0。参数必须是合法的ASCII编码值或UTF8编码值。
convert(string bytea, src_encoding name, dest_encoding name)	bytea	将用 src_encoding编码的字符串转换成用dest_encoding编码的字符串。数据库中必须存在src_encoding到dest_encoding的编码转换函数。表7-10列出了数据库中内置的合法的编码转换组合。 如果数据库中不存在src_encoding到est_encoding的编码转换函数，可以使用命令CREATE CONVERSION创建一个。
convert_from(string bytea, src_encoding name)	text	将用 src_encoding编码的字符串转换成用数据库当前的编码类型编码的字符串。
convert_to(string text, dest_encoding name)	bytea	将字符串转换成以dest_encoding编码的格式。
decode(string text, type text)	bytea	从指定的格式的字符串中解码出二进制字符串。格式包括base64、hex和escape，详细信息参考下面的encode函数。
encode(data bytea, type text)	text	将二进制字符串转换成指定的格式字符串。一共有三种格式：base64、hex和escape。关于base64请参考RFC2045，hex是十六进制格式。escape只是用/000来表示字节0，用两个反斜杠来表示一个反斜杠。
initcap(string)	text	首先将字符串用非字母和数字字符分割成多个子串，然后将每个子串的第一个字符大写，剩下的字符都小写。
length(string)	int	返回字符串中字符的个数
length(string bytea, encoding name )	int	返回字符串 bytea中的字符的个数，bytea的编码类型必须是encoding指定的。
lpad(string text, length int [, fill text])	text	用指定的字符串fill将字符串string填充成长度为 length的字符串。如果string的长度已经超过length，则将string截断成长度为length的字符串。 如果没有指定fill的值，则fill的值默认是空格。填充的字符串放在string的头部。
ltrim(string text [,characters text])	text	从字符串string的头部删除只包含指定的字符串characters 中的字符的最长的字符串。 如果没有指定参数characters，则它的值默认是空格。
md5(string)	text	计算字符串string的MD5哈希值。结果用十六进制的形式表示。
pg_client_encoding()	name	返回客户端的当前字符编码类型名称。
quote_ident(string text)	text	返回字符串string作为合法的SQL标识符的表示形式。
quote_literal(string text)	text	将字符串string转换成一个合法的SQL语句字符串常量的形式。
quote_literal(value anyelement)	text	将 value转换成字符串常量,value必须是一个数值。
regexp_matches(string text, pattern text [, flags text])	setof text[]	返回所有匹配指定的POSIX正则表达式的子串。参见第7.7.3节。
regexp_replace(string text, pattern text, replacement text [, flags text])	text	用字符串 replacement替换所有匹配指定的POSIX正则表达式的子串。参见第7.7.3节。
regexp_split_to_array(string text, pattern text [, flags text ])	text[]	使用POSIX正则表达式作为分割符来分割字符串。参见第7.7.3节。
regexp_split_to_table(string text, pattern text [, flags text])	setof text	使用POSIX正则表达式作为分割符来分割字符串。参见第7.7.3节。
repeat(string text, number int)	text	将字符串string重复指定的次数。
replace(string text, from text, to text)	text	将字符串string中的所有子串from用子串to代替。
rpad(string text, length int [, fill text])	text	用指定的字符串fill将字符串string填充成长度为 length的字符串。如果string的长度已经超过length，则将string截断成长度为length的字符串。 如果没有指定fill的值，则fill的值默认是空格。填充的字符串放在string的尾部。
rtrim(string text [,characters text])	text	从字符串string的尾部删除只包含指定的字符串characters 中的字符的最长的字符串。 如果没有指定参characters，则它的值默认是空格。
split_part(string text, delimiter text, field int)	text	将字符串string用分割符delimiter分成多个域后，返回field指定的域（域的编号从1开始）。delimiter可以是一个字符串。
strpos(string, substring)	int	返回substring在string中的位置。
substr(string, from [,count] int)	text	从字符串string中取出指定的子串，如果没有指定参数count，则取出从from到字符串结尾这部分子串。与函数substring(string from from for count) 的功能相同。
to_ascii(string text [,encoding text])	text	将用 ASCII表示的字符串转换成其它编码类型的字符串(只支持LATIN1, LATIN2,LATIN9和WIN1250 )。
to_hex(number int or bigint)	text	将数字转换成十六进制的表示形式。
translate(string text, from text, to text)	text	如果字符串string中的某个字符匹配字符串from的某个字符，则string中的这个字符将用字符串to中和from中的匹配字符对应的字符代替。字符串to和from的长度应该相等，如果不相等，参见例子中的处理方式。

表 7-9. 其它的字符串函数实例

例子	结果
(1)ascii('x') (2)ascii('yz') (3)ascii('我是')	(1)120 (2)121 (3)25105
(1)btrim('xyxtrimyyx', 'xy') (2)btrim('xgTomxxf','gxf');	(1)trim (2)Tom
(1)chr(65) (2)chr(25105) (3)chr(25104)	(1)A (2)我 (3)成
convert('text_in_utf8', 'UTF8', 'LATIN1')	用ISO 8859-1格式表示的字符串text_in_utf8
convert_from('text_in_utf8', 'UTF8')	用数据库当前的编码类型表示的字符串text_in_utf8
convert_to('some text', 'UTF8')	用UTF8编码的字符串
(1)decode(‘MTIzAAE=', 'base64') (2)decode('3132330001','hex')	(1)123/000/001 (2)123/000/001
(1)encode(E'123//000//001', 'base64') (2)encode(E'123//000//001', 'hex')	(1)MTIzAAE= (2)3132330001
(1)initcap('hi THOMAS') (2)initcap('hh+jj');	(1)Hi Thomas (2)Hh+Jj
(1)length('jose') (2)length('我是谁')	(1)4 (2)3
length('jose', 'UTF8')	4
(1)lpad('hi', 5, 'xy') (2)lpad('gggggg',4,'h'); (3)lpad('gg',4,'h'); (4)lpad('gg',4);	(1)xyxhi (2)gggg (3)hhgg (4)  gg，注意gg前面是两个空格。
ltrim('zzzytrim', 'xyz')	trim
md5('abc')	900150983cd24fb0 d6963f7d28e17f72
pg_client_encoding()	GB18030
(1)quote_ident('Foo bar') (2)quote_ident('abc') (3)quote_ident('abc''');	(1)"Foo bar" (2)abc (3) "abc'"
(1)quote_literal('O/'Reilly') (2)quote_literal('abc//');	(1)'O''Reilly' (2) E'abc//'
quote_literal(42.5)	'42.5'
regexp_matches('foobarbequebaz', '(bar)(beque)')	{bar,beque}
regexp_replace('Thomas', '.[mN]a.', 'M')	ThM
regexp_split_to_array('hello world', E'//s+')	{hello,world}
regexp_split_to_table('hello world', E'//s+')	hello world (2 rows)
repeat('Pg', 4)	PgPgPgPg
replace('abcdefabcdef', 'cd', 'XX')	abXXefabXXef
rpad('hi', 5, 'xy')	hixyx
rtrim('trimxxxx', 'x')	trim
(1)split_part('abc~@~def~@~ghi', '~@~', 2) (2)split_part('abc$de$f','$',3);	(1)def (2)f
(1)strpos('high', 'ig') (2)strpos('我是','是')	(1)2 (2)2
substr('alphabet', 3, 2)	ph
to_ascii('Karel')	Karel
to_hex(2147483647)	7fffffff
(1)translate('12345', '14', 'ax') (2)translate('abcdef','abc','f') (3)translate('abcdef','abc','fg')	(1)a23x5 (2)fdef (3)fgdef

 表 7-10. 系统内置的编码类型转换

编码类型转换名称	源编码类型	目标编码类型
ascii_to_mic	SQL_ASCII	MULE_INTERNAL
ascii_to_utf8	SQL_ASCII	UTF8
big5_to_euc_tw	BIG5	EUC_TW
big5_to_mic	BIG5	MULE_INTERNAL
big5_to_utf8	BIG5	UTF8
euc_cn_to_mic	EUC_CN	MULE_INTERNAL
euc_cn_to_utf8	EUC_CN	UTF8
euc_jp_to_mic	EUC_JP	MULE_INTERNAL
euc_jp_to_sjis	EUC_JP	SJIS
euc_jp_to_utf8	EUC_JP	UTF8
euc_kr_to_mic	EUC_KR	MULE_INTERNAL
euc_kr_to_utf8	EUC_KR	UTF8
euc_tw_to_big5	EUC_TW	BIG5
euc_tw_to_mic	EUC_TW	MULE_INTERNAL
euc_tw_to_utf8	EUC_TW	UTF8
gb18030_to_utf8	GB18030	UTF8
gbk_to_utf8	GBK	UTF8
iso_8859_10_to_utf8	LATIN6	UTF8
iso_8859_13_to_utf8	LATIN7	UTF8
iso_8859_14_to_utf8	LATIN8	UTF8
iso_8859_15_to_utf8	LATIN9	UTF8
iso_8859_16_to_utf8	LATIN10	UTF8
iso_8859_1_to_mic	LATIN1	MULE_INTERNAL
iso_8859_1_to_utf8	LATIN1	UTF8
iso_8859_2_to_mic	LATIN2	MULE_INTERNAL
iso_8859_2_to_utf8	LATIN2	UTF8
iso_8859_2_to_windows_1250	LATIN2	WIN1250
iso_8859_3_to_mic	LATIN3	MULE_INTERNAL
iso_8859_3_to_utf8	LATIN3	UTF8
iso_8859_4_to_mic	LATIN4	MULE_INTERNAL
iso_8859_4_to_utf8	LATIN4	UTF8
iso_8859_5_to_koi8_r	ISO_8859_5	KOI8
iso_8859_5_to_mic	ISO_8859_5	MULE_INTERNAL
iso_8859_5_to_utf8	ISO_8859_5	UTF8
iso_8859_5_to_windows_1251	ISO_8859_5	WIN1251
iso_8859_5_to_windows_866	ISO_8859_5	WIN866
iso_8859_6_to_utf8	ISO_8859_6	UTF8
iso_8859_7_to_utf8	ISO_8859_7	UTF8
iso_8859_8_to_utf8	ISO_8859_8	UTF8
iso_8859_9_to_utf8	LATIN5	UTF8
johab_to_utf8	JOHAB	UTF8
koi8_r_to_iso_8859_5	KOI8	ISO_8859_5
koi8_r_to_mic	KOI8	MULE_INTERNAL
koi8_r_to_utf8	KOI8	UTF8
koi8_r_to_windows_1251	KOI8	WIN1251
koi8_r_to_windows_866	KOI8	WIN866
mic_to_ascii	MULE_INTERNAL	SQL_ASCII
mic_to_big5	MULE_INTERNAL	BIG5
mic_to_euc_cn	MULE_INTERNAL	EUC_CN
mic_to_euc_jp	MULE_INTERNAL	EUC_JP
mic_to_euc_kr	MULE_INTERNAL	EUC_KR
mic_to_euc_tw	MULE_INTERNAL	EUC_TW
mic_to_iso_8859_1	MULE_INTERNAL	LATIN1
mic_to_iso_8859_2	MULE_INTERNAL	LATIN2
mic_to_iso_8859_3	MULE_INTERNAL	LATIN3
mic_to_iso_8859_4	MULE_INTERNAL	LATIN4
mic_to_iso_8859_5	MULE_INTERNAL	ISO_8859_5
mic_to_koi8_r	MULE_INTERNAL	KOI8
mic_to_sjis	MULE_INTERNAL	SJIS
mic_to_windows_1250	MULE_INTERNAL	WIN1250
mic_to_windows_1251	MULE_INTERNAL	WIN1251
mic_to_windows_866	MULE_INTERNAL	WIN866
sjis_to_euc_jp	SJIS	EUC_JP
sjis_to_mic	SJIS	MULE_INTERNAL
sjis_to_utf8	SJIS	UTF8
tcvn_to_utf8	WIN1258	UTF8
uhc_to_utf8	UHC	UTF8
utf8_to_ascii	UTF8	SQL_ASCII
utf8_to_big5	UTF8	BIG5
utf8_to_euc_cn	UTF8	EUC_CN
utf8_to_euc_jp	UTF8	EUC_JP
utf8_to_euc_kr	UTF8	EUC_KR
utf8_to_euc_tw	UTF8	EUC_TW
utf8_to_gb18030	UTF8	GB18030
utf8_to_gbk	UTF8	GBK
utf8_to_iso_8859_1	UTF8	LATIN1
utf8_to_iso_8859_10	UTF8	LATIN6
utf8_to_iso_8859_13	UTF8	LATIN7
utf8_to_iso_8859_14	UTF8	LATIN8
utf8_to_iso_8859_15	UTF8	LATIN9
utf8_to_iso_8859_16	UTF8	LATIN10
utf8_to_iso_8859_2	UTF8	LATIN2
utf8_to_iso_8859_3	UTF8	LATIN3
utf8_to_iso_8859_4	UTF8	LATIN4
utf8_to_iso_8859_5	UTF8	ISO_8859_5
utf8_to_iso_8859_6	UTF8	ISO_8859_6
utf8_to_iso_8859_7	UTF8	ISO_8859_7
utf8_to_iso_8859_8	UTF8	ISO_8859_8
utf8_to_iso_8859_9	UTF8	LATIN5
utf8_to_johab	UTF8	JOHAB
utf8_to_koi8_r	UTF8	KOI8
utf8_to_sjis	UTF8	SJIS
utf8_to_tcvn	UTF8	WIN1258
utf8_to_uhc	UTF8	UHC
utf8_to_windows_1250	UTF8	WIN1250
utf8_to_windows_1251	UTF8	WIN1251
utf8_to_windows_1252	UTF8	WIN1252
utf8_to_windows_1253	UTF8	WIN1253
utf8_to_windows_1254	UTF8	WIN1254
utf8_to_windows_1255	UTF8	WIN1255
utf8_to_windows_1256	UTF8	WIN1256
utf8_to_windows_1257	UTF8	WIN1257
utf8_to_windows_866	UTF8	WIN866
utf8_to_windows_874	UTF8	WIN874
windows_1250_to_iso_8859_2	WIN1250	LATIN2
windows_1250_to_mic	WIN1250	MULE_INTERNAL
windows_1250_to_utf8	WIN1250	UTF8
windows_1251_to_iso_8859_5	WIN1251	ISO_8859_5
windows_1251_to_koi8_r	WIN1251	KOI8
windows_1251_to_mic	WIN1251	MULE_INTERNAL
windows_1251_to_utf8	WIN1251	UTF8
windows_1251_to_windows_866	WIN1251	WIN866
windows_1252_to_utf8	WIN1252	UTF8
windows_1256_to_utf8	WIN1256	UTF8
windows_866_to_iso_8859_5	WIN866	ISO_8859_5
windows_866_to_koi8_r	WIN866	KOI8
windows_866_to_mic	WIN866	MULE_INTERNAL
windows_866_to_utf8	WIN866	UTF8
windows_866_to_windows_1251	WIN866	WIN
windows_874_to_utf8	WIN874	UTF8
euc_jis_2004_to_utf8	EUC_JIS_2004	UTF8
ut8_to_euc_jis_2004	UTF8	EUC_JIS_2004
shift_jis_2004_to_utf8	SHIFT_JIS_2004	UTF8
ut8_to_shift_jis_2004	UTF8	SHIFT_JIS_2004
euc_jis_2004_to_shift_jis_2004	EUC_JIS_2004	SHIFT_JIS_2004
shift_jis_2004_to_euc_jis_2004	SHIFT_JIS_2004	EUC_JIS_2004

 

7.5 二进制字符串函数和运算符

       本节描述处理bytea类型的数值的函数和运算符。表7-11列出了这些函数和运算符，表7-12列出了这些函数和运算符的实例。表7-13中列出了其它的二进制字符串处理函数，这些函数有一些在内部使用，用来实现表7-11中列出的SQL标准的二进制字符串函数的，表7-14列出了表7-13中函数的实例。

表7-11. 二进制字符串函数和运算符

函数	返回值类型	描述
string || string	bytea	连接两个二进制字符串
get_bit(string, offset)	int	从二进制字符串中找出一个指定的二进制位
get_byte(string, offset)	int	从二进制字符串中找出一个指定的字节
octet_length(string)	int	二进制字符串含有的字节的个数
position(substring in string)	int	从二进制字符串中找出指定的子串出现的位置
set_bit(string, offset,newvalue)	bytea	将二进制字符串中的某个二进制位设为指定的值
set_byte(string, offset,newvalue)	bytea	将二进制字符串中的某个字节设为指定的值
substring(string [from int] [for int])	bytea	从二进制字符串中中找出指定的子串。from int表示子串开始的位置，默认从1开始，例如from 2表示子串从string的第二个字符开始。for int表示子串的长度，默认取string从子串开始位置到string的末尾的所有子串，例如for 3表示子串的长度是3。
trim([both] bytes from string)	bytea	从二进制字符串string的开始和末尾删除只包含指定的二进制字符串bytes中的字符的最长的二进制字符串。

表7-12. 二进制字符串函数和运算符实例

例子	结果
E'////Post'::bytea || E'//047gres//000'::bytea	//Post'gres/000
get_bit(E'Th//000omas'::bytea, 45)	1
get_byte(E'Th//000omas'::bytea, 4)	109
octet_length(E'jo//000se'::bytea)	5
position(E'//000om'::bytea in E'Th//000omas'::bytea)	3
set_bit(E'Th//000omas'::bytea, 45, 0)	Th/000omAs
set_byte(E'Th//000omas'::bytea, 4, 64)	Th/000o@as
substring(E'Th//000omas'::bytea from 2 for 3)	h/000o
trim(E'//000'::bytea from E'//000Tom//000'::bytea)	Tom

 

表7-13. 其它二进制字符串函数

函数	返回值类型	描述
btrim(string bytea, bytes bytea)	bytea	从二进制字符串string的开始和末尾删除只包含指定的二进制字符串bytes中的字符的最长的二进制字符串。
decode(string text, type text)	bytea	从指定的格式的字符串中解码出二进制字符串。格式包括base64、hex和escape，详细信息参考下面的encode函数。
encode(string bytea, type text)	text	将二进制字符串转换成指定的格式字符串。一共有三种格式：base64、hex和escape。关于base64请参考RFC2045，hex是十六进制格式。escape只是用/000来表示字节0，用两个反斜杠来表示一个反斜杠。
length(string)	int	返回二进制字符串的长度。
md5(string)	text	计算二进制字符串的MD5哈希值， 结果用十六进制数表示 。

表7-14. 其它二进制字符串函数实例

例子	结果
btrim(E'//000trim//000'::bytea, E'//000'::bytea)	trim
decode(E'123//000456', 'escape')	123/000456
encode(E'123//000456'::bytea, 'escape')	123/000456
length(E'jo//000se'::bytea)	5
md5(E'Th//000omas'::bytea)	8ab2d3c9689aaf18 b4958c334c82d8b1

 

 

7.6 位串函数和运算符

    本节描述用于处理位串类型数据的函数和运算符。位串类型包括bit 和bit varying。对于位串类型的数据可以使用常用的比较运算符，表7-15列出了其它的运算符。运算符&、|和 # 要求两个操作数的长度相等。在对位串类型的数据进行移位操作的时候，位串的长度不会发生变化。

表7-15.位串运算符

运算符	描述	例子	结果
||	连接	B'10001' || B'011'	10001011
&	按位AND（与）	B'10001' & B'01101'	00001
|	按位OR（或）	B'10001' | B'01101'	11101
#	按位XOR（异或）	B'10001' # B'01101'	11100
~	按位NOT（非）	~ B'10001'	01110
<<	按位左移	B'10001' << 3	01000
>>	按位右移	B'10001' >> 2	00100

    函数length、bit_length、octet_length、position和substring除了用于字符串之外,也可以用于位串。

    此外，可以在整数和位串类型之间进行转换。例如：

44::bit(10)                    0000101100

44::bit(3)                     100

cast(-44 as bit(12))           111111010100

'1110'::bit(4)::integer        14

 

    将整数转换成bit(n)类型时，只会取整数的最右边的n个二进制位。注意，如果只是转换为 "bit"，意思就是转换成 bit(1)，结果只会取整数的最右边的那个二进制位。