PostgreSQL教程（十二）：SQL语言（五）之函数和操作符

一、逻辑操作符

常用的逻辑操作符有：

AND

OR

NOT

SQL使用三值的逻辑体系。真、假和null，这是null代表“未知”。观察下面真值表：

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都是可以交换的，也就是说，你可以交换左右操作数而不影响结果。

---

二、比较操作符

可用的比较操作符见下表。

 比较操作符

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

注意: !=操作符在分析器阶段被转换成<>。 !=和<>操作符是完全等价的。

比较操作符可以用于所有相关的数据类型。所有比较操作符都是双目操作符， 返回boolean类型数值；像1 < 2 < 3 这样的表达式是非法的(因为布尔值和3之间不能做比较)。

除了比较操作符以外，我们还可以使用BETWEEN构造。

a BETWEEN x AND y

等价于

a >= x AND a <= y

注意BETWEEN认为端点值是包含在范围内的。NOT BETWEEN 做相反的比较：

a NOT BETWEEN x AND y

等价于

a < x OR a > y

BETWEEN SYMMETRIC和BETWEEN一样，只是没有要求 AND左边的参数小于或等于右边的参数。如果左面的参数不小于或等于右面的参数， 那么两个参数是自动交换的，所以非空范围总是适用。

要检查一个值是否为 NULL ，使用下面的构造：

expression IS NULL
expression IS NOT NULL

或者等效，但并不标准的构造：

expression ISNULL
expression NOTNULL

不要写expression = NULL 因为NULL是不"等于"NULL的。 NULL 代表一个未知的数值，因此我们无法知道两个未知的数值是否相等。这个行为遵循 SQL 标准。

        TIPS:

                有些应用可能要求表达式expression = NULL, 在expression为 NULL 时候返回真。 我们           强烈建议这样的应用修改成遵循 SQL 标准。但是，如果这样修改是不可能的， 那么我们可           以打开transform_null_equals配置参数， 让PostgreSQL将x = NULL 自动转换成x IS NULL。

        NOTES:

                如果expression是行值， 那么当行表达式本身为 NULL 或该行的所有字段都为 NULL                时，IS NULL将为真； 当行表达式本身不为 NULL 并且该行的所有字段都不为 NULL 时，              IS NOT NULL 也将为真。因为这个行为，IS NULL和IS NOT NULL 并不总是为行值表达式            返回相反的值，也就是， 一个同时包含NULL和non-null值的行值表达式将在两种情况下都              返回false。 这个规定符合 SQL 标准，但是与PostgreSQL之前的版本不兼容。

如果有任何一个输入是 NULL ，那么普通的比较操作符生成 NULL(表示"未知")， 而不是true或false。例如，7 = NULL生成null，7 <> NULL 也生成null。当这种行为不适用时，使用IS [ NOT ] DISTINCT FROM构造：

expression IS DISTINCT FROM expression
expression IS NOT DISTINCT FROM expression

对于非 NULL 的输入IS DISTINCT FROM与<> 操作符相同。但是，如果两个输入都是 NULL ，那么它将返回假；如果只有一个输入是 NULL ， 那么它将返回真。类似的，对于非 NULL 的输入IS NOT DISTINCT FROM 与=操作符相同。但是，如果两个输入都是 NULL ，那么它将返回真； 如果只有一个输入是 NULL ，那么它将返回假。这样就很有效地把 NULL 当作一个普通数据值看待， 而不是"未知"。

布尔数值可以用下面的构造进行测试

expression IS TRUE
expression IS NOT TRUE
expression IS FALSE
expression IS NOT FALSE
expression IS UNKNOWN
expression IS NOT UNKNOWN

这些构造将总是返回真或假，从来不返回 NULL ，即使操作数是 NULL 也如此。 NULL 输入被当做逻辑数值"未知"。请注意实际上IS UNKNOWN 和IS NOT UNKNOWN分别与IS NULL和IS NOT NULL 相同，只是输入表达式必须是布尔类型。

---

三、数学函数和操作符

PostgreSQL为许多类型提供了数学操作符。 对于那些没有标准的数学传统的类型(比如日期/时间类型)， 我们在随后的章节里描述实际的行为。

下表显示了可用的数据操作符。

数学操作符

操作符	描述	例子	结果
+	加	2 + 3	5
-	减	2 - 3	-1
*	乘	2 * 3	6
/	除(整数除法将截断结果)	4 / 2	2
%	模(求余)	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。

下表显示了可用的数学函数。在该表中， dp表示double precision。 这些函数中有许多都有多种不同的形式，区别是参数不同。除非特别指明， 任何特定形式的函数都返回和它的参数相同的数据类型。处理double precision 数据的函数大多数是在宿主系统的C库的基础上实现的；因此， 精度和数值范围方面的行为是根据宿主系统而变化的。

数学函数

函数	返回类型	描述	例子	结果
abs(x)	(与输入相同)	绝对值	abs(-17.4)	17.4
cbrt(dp)	dp	立方根	cbrt(27.0)	3
ceil(dp 或 numeric)	(与输入相同)	不小于参数的最小的整数	ceil(-42.8)	-42
ceiling(dp 或 numeric)	(与输入相同)	不小于参数的最小整数(ceil的别名)	ceiling(-95.3)	-95
degrees(dp)	dp	把弧度转为角度	degrees(0.5)	28.6478897565412
div(y numeric, x numeric)	numeric	integer quotient of y/x	div(9,4)	2
exp(dp 或 numeric)	(与输入相同)	自然指数	exp(1.0)	2.71828182845905
floor(dp 或 numeric)	(与输入相同)	不大于参数的最大整数	floor(-42.8)	-43
ln(dp 或 numeric)	(与输入相同)	自然对数	ln(2.0)	0.693147180559945
log(dp 或 numeric)	(与输入相同)	以 10 为底的对数	log(100.0)	2
log(b numeric, x numeric)	numeric	以b为底数的对数	log(2.0, 64.0)	6.0000000000
mod(y, x)	(与参数类型相同)	y/x的余数(模)	mod(9,4)	1
pi()	dp	"π" 常量	pi()	3.14159265358979
power(a dp, b dp)	dp	a的b次幂	power(9.0, 3.0)	729
power(a numeric, b numeric)	numeric	a的b次幂	power(9.0, 3.0)	729
radians(dp)	dp	把角度转为弧度	radians(45.0)	0.785398163397448
random()	dp	0.0 到 1.0 之间的随机数	random()
round(dp 或 numeric)	(与输入相同)	圆整为最接近的整数	round(42.4)	42
round(v numeric, s int)	numeric	圆整为s 位小数	round(42.4382, 2)	42.44
setseed(dp)	void	为随后的random()调用设置种子( -1.0 到 1.0 之间，包含)	setseed(0.54823)
sign(dp 或 numeric)	(与输入相同)	参数的符号(-1, 0, +1)	sign(-8.4)	-1
sqrt(dp 或 numeric)	(与输入相同)	平方根	sqrt(2.0)	1.4142135623731
trunc(dp 或 numeric)	(与输入相同)	截断(向零靠近)	trunc(42.8)	42
trunc(v numeric, s int)	numeric	截断为s位小数	trunc(42.4382, 2)	42.43
width_bucket(op numeric, b1 numeric, b2 numeric, count int)	int	返回一个桶，这个桶是在一个有count个桶， 上界为b1下界为b2的等深柱图中 operand将被赋予的那个桶。	width_bucket(5.35, 0.024, 10.06, 5)	3
width_bucket(op dp, b1 dp, b2 dp, count int)	int	返回一个桶，这个桶是在一个有count个桶， 上界为b1下界为b2的等深柱图中 operand将被赋予的那个桶。	width_bucket(5.35, 0.024, 10.06, 5)	3

最后，下表显示了可用的三角函数。 所有三角函数都使用类型为double precision 的参数和返回类型。 三角函数参数用弧度来表达。反函数的返回值也是用弧度来表达的。 参阅上面的单元转换函数radians() 和degrees()。

 三角函数

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

---

四、字符串函数和操作符

本节描述了用于检查和操作字符串数值的函数和操作符。 在这个环境中的字符串包括character,character varying, text类型的值。除非另外说明，所有下面列出的函数都可以处理这些类型， 不过要小心的是，在使用character类型的时候，需要注意自动填充的潜在影响。 有些函数还可以处理位串类型。

SQL定义了一些字符串函数，用特定的关键字而不是逗号来分隔参数。 详情请见表4-1。PostgreSQL 也提供了使用正常的函数调用语法实现的这些函数的版本(参阅 表4-2)。

        NOTES:

             在PostgreSQL 8.3之前， 这些函数将默默接受一些非字符串数据类型的值，由于存在从           这些数据类型到text 的隐式强制转换，转换后的它们经常发生意外的行为，因此删除了隐式强         制转换。 然而，字符串连接操作符(||)仍接受非字符串输入， 只要至少有一个输入是字符串类         型，如表4-2所示。 对于其它情况下，如果你需要重复以前的行为，插入一个明确的强制转           换到text。

表 4-1. SQL 字符串函数和操作符

函数	返回类型	描述	示例	结果
string || string	text	字符串连接	'Post' || 'greSQL'	PostgreSQL
string || non-string 或 non-string || string	text	带有一个非字符串输入的字符串连接	'Value: ' || 42	Value: 42
bit_length(string)	int	字符串的位	bit_length('jose')	32
char_length(string) 或 character_length(string)	int	字符串中的字符个数	char_length('jose')	4
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 '...$')	mas
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

还有额外的字符串操作函数可以用，它们在表4-2列出。 它们有些在内部用于实现表4-1列出的SQL标准字符串函数。

表 4-2. 其它字符串函数

函数	返回类型	描述	示例	结果
ascii(string)	int	参数中第一个字符的ASCII编码值。 对于UTF8返回字符的宽字节编码值。 对于其它的多字节编码，参数必须是一个ASCII字符。	ascii('x')	120
btrim(string text [, characters text])	text	从string开头和结尾删除只包含 characters中字符(缺省是空白)的最长字符串。	btrim('xyxtrimyyx', 'xy')	trim
chr(int)	text	给定编码的字符。对于UTF8这个参数作为宽字节代码处理。 对于其它的多字节编码，这个参数必须指定一个ASCII字符， 因为text数据类型无法存储NULL数据字节，不能将NULL(0)作为字符参数。	chr(65)	A
concat(str "any" [, str "any" [, ...] ])	text	连接所有参数的文本表示。NULL 参数被忽略。	concat('abcde', 2, NULL, 22)	abcde222
concat_ws(sep text, str "any" [, str "any" [, ...] ])	text	连接所有参数，但是第一个参数是分隔符，用于将所有参数分隔。NULL 参数被忽略。	concat_ws(',', 'abcde', 2, NULL, 22)	abcde,2,22
convert(string bytea, src_encoding name, dest_encoding name)	bytea	把原来编码为src_encoding的字符串转换为 dest_encoding编码。 在这种编码格式中string必须是有效的。 用CREATE CONVERSION定义转换。 这也有些预定义的转换。 参阅表4-3显示可用的转换。	convert('text_in_utf8', 'UTF8', 'LATIN1')	text_in_utf8 用 Latin-1 编码表示 (ISO 8859-1)
convert_from(string bytea, src_encoding name)	text	把原来编码为src_encoding的字符串转换为数据库编码格式。 这种编码格式中，string必须是有效的。	convert_from('text_in_utf8', 'UTF8')	text_in_utf8 用当前数据库编码表示
convert_to(string text, dest_encoding name)	bytea	将字符串转化为dest_encoding编码格式。	convert_to('some text', 'UTF8')	some text 用 UTF8 编码表示
decode(string text, format text)	bytea	把用string表示的文本里面的二进制数据解码。 format选项和encode相同。	decode('MTIzAAE=', 'base64')	\x3132330001
encode(data bytea, format text)	text	把二进制数据编码为文本表示。支持的格式有：base64, hex, escape。escape 转换零字节和高位设置字节为八进制序列(\nnn) 和双反斜杠。	encode(E'123\\000\\001', 'base64')	MTIzAAE=
format(formatstr text [, formatarg "any" [, ...] ])	text	根据格式字符串格式参数。这个函数类似C函数sprintf。 参阅下面 4.1 格式化。	format('Hello %s, %1$s', 'World')	Hello World, World
initcap(string)	text	把每个单词的第一个字母转为大写，其它的保留小写。 单词是一系列字母数字组成的字符，用非字母数字分隔。	initcap('hi THOMAS')	Hi Thomas
left(str text, n int)	text	返回字符串的前n个字符。当n是负数时， 返回除最后|n|个字符以外的所有字符。	left('abcde', 2)	ab
length(string)	int	string中字符的数目	length('jose')	4
length(string bytea, encoding name )	int	指定encoding编码格式的string的字符数。 在这个编码格式中，string必须是有效的。	length('jose', 'UTF8')	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
md5(string)	text	计算string的MD5散列，以十六进制返回结果。	md5('abc')	900150983cd24fb0 d6963f7d28e17f72
pg_client_encoding()	name	当前客户端编码名称	pg_client_encoding()	SQL_ASCII
quote_ident(string text)	text	返回适用于SQL语句的标识符形式(使用适当的引号进行界定)。 只有在必要的时候才会添加引号(字符串包含非标识符字符或者会转换大小写的字符)。 嵌入的引号被恰当地写了双份。	quote_ident('Foo bar')	"Foo bar"
quote_literal(string text)	text	返回适用于在SQL语句里当作文本使用的形式(使用适当的引号进行界定)。 嵌入的引号和反斜杠被恰当地写了双份。请注意，当输入是null时， quote_literal返回null；如果参数可能为null， 通常quote_nullable更适用。	quote_literal(E'O\'Reilly')	'O''Reilly'
quote_literal(value anyelement)	text	将给定的值强制转换为text，加上引号作为文本。嵌入的引号和反斜杠被恰当地写了双份。	quote_literal(42.5)	'42.5'
quote_nullable(string text)	text	返回适用于在SQL语句里当作字符串使用的形式(使用适当的引号进行界定)。 或者，如果参数为空，返回NULL。嵌入的引号和反斜杠被恰当地写了双份。	quote_nullable(NULL)	NULL
quote_nullable(value anyelement)	text	将给定的参数值转化为text，加上引号作为文本；或者，如果参数为空， 返回NULL。嵌入的引号和反斜杠被恰当地写了双份。	quote_nullable(42.5)	'42.5'
regexp_matches(string text, pattern text [, flags text])	setof text[]	返回string中所有匹配POSIX正则表达式的子字符串。	regexp_matches('foobarbequebaz', '(bar)(beque)')	{bar,beque}
regexp_replace(string text, pattern text, replacement text [, flags text])	text	替换匹配 POSIX 正则表达式的子字符串。	regexp_replace('Thomas', '.[mN]a.', 'M')	ThM
regexp_split_to_array(string text, pattern text [, flags text ])	text[]	用POSIX正则表达式作为分隔符，分隔string。	regexp_split_to_array('hello world', E'\\s+')	{hello,world}
regexp_split_to_table(string text, pattern text [, flags text])	setof text	用POSIX正则表达式作为分隔符，分隔string。	regexp_split_to_table('hello world', E'\\s+')	hello world (2 rows)
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
reverse(str)	text	返回颠倒的字符串	reverse('abcde')	edcba
right(str text, n int)	text	返回字符串中的后n个字符。当n是负值时， 返回除前|n|个字符以外的所有字符。	right('abcde', 2)	de
rpad(string text, length int [, fill text])	text	使用填充字符fill(缺省时为空白)， 把string填充到length长度。 如果string已经比length长则将其从尾部截断。	rpad('hi', 5, 'xy')	hixyx
rtrim(string text [, characters text])	text	从字符串string的结尾删除只包含 characters中字符(缺省是个空白)的最长的字符串。	rtrim('trimxxxx', 'x')	trim
split_part(string text, delimiter text, field int)	text	根据delimiter分隔string 返回生成的第 field 个子字符串(1为基)。	split_part('abc~@~def~@~ghi', '~@~', 2)	def
strpos(string, substring)	int	指定的子字符串的位置。和position(substring in string)一样，不过参数顺序相反。	strpos('high', 'ig')	2
substr(string, from [, count])	text	抽取子字符串。和substring(string from from for count))一样	substr('alphabet', 3, 2)	ph
to_ascii(string text [, encoding text])	text	把string从其它编码转换为ASCII (仅支持LATIN1, LATIN2, LATIN9, WIN1250编码)。	to_ascii('Karel')	Karel
to_hex(number int or bigint)	text	把number转换成十六进制表现形式	to_hex(2147483647)	7fffffff
translate(string text, from text, to text)	text	把在string中包含的任何匹配from 中字符的字符转化为对应的在to中的字符。 如果from比to长， 删掉在from中出现的额外的字符。	translate('12345', '143', 'ax')	a2x5

concat, concat_ws和format 函数是可变的，所以用VARIADIC 关键字标记传递的数值以连接或者格式化为一个数组是可能的。数组的元素对函数来说是单独的普通参数。 如果可变数组的元素是NULL，那么concat和concat_ws 返回NULL，但是format把NULL作为零元素数组对待。

表 4-3. 内置的转换

转换名 [a]	源编码	目的编码
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	KOI8R
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	KOI8R	ISO_8859_5
koi8_r_to_mic	KOI8R	MULE_INTERNAL
koi8_r_to_utf8	KOI8R	UTF8
koi8_r_to_windows_1251	KOI8R	WIN1251
koi8_r_to_windows_866	KOI8R	WIN866
koi8_u_to_utf8	KOI8U	UTF8
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	KOI8R
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	KOI8R
utf8_to_koi8_u	UTF8	KOI8U
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	KOI8R
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	KOI8R
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
utf8_to_euc_jis_2004	UTF8	EUC_JIS_2004
shift_jis_2004_to_utf8	SHIFT_JIS_2004	UTF8
utf8_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
表注: a. 转换名遵循一个标准的命名模式：将源编码中的所有非字母数字字符用下划线替换， 后面跟着_to_，然后后面再跟着经过相似处理的目标编码的名字。 因此这些名字可能和客户的编码名字不同。

4.1 格式化

函数format生成根据格式字符串格式化了的输出，风格类似于C函数sprintf。

format(formatstr text [, formatarg "any" [, ...] ])

formatstr是指定结果如何格式化的格式字符串。格式字符串中的文本直接拷贝到结果中， 除非已经使用了格式说明符。格式说明符在字符串中作为占位符使用， 定义后续函数参数应该格式化并且插入到结果中。每个formatarg 参数根据这种数据类型的通常输出规则转化为文本，然后根据格式说明符格式化并且插入到结果中。

格式说明符由%字符引进，格式为

%[position][flags][width]type

组件的字段有：

①：position (optional)

        n$格式的字符串，这里的n 是要打印的参数的索引。索引为1表示在formatstr 之后的第一个参数。如果省略了formatstr， 默认使用序列中的下一个参数。

②：flags (optional)

        附加选项，控制如何格式化格式说明符的输出。当前只支持负号(-)， 负号导致格式说明符的输出是左对齐的。这是没有影响的，除非指定了 width字段。

③：width (optional)

        声明字符数的minimum值用来显示格式说明符的输出。需要补充宽度时， 空白添加到输出的左侧或右侧（取决于-标志）。一个比较小的宽度不会导致输出的截断， 只是简单的忽略了。宽度可以用下列方法指定：一个正整数；一个星号(*) 表示使用下一个函数参数作为宽度；或一个格式为*n$ 的字符串表示使用第n个函数参数作为宽度。

        如果宽度来自函数参数，那么这个参数在作为格式说明符的数值之前消耗掉。 如果宽度参数是负的，那么结果是左对齐的（就像声明了-标志一样）， 并且字段长度为abs(width)。

④：type (required)

        格式转换的类型用来产生格式说明符的输出。支持下列的类型：

                s格式参数值为简单的字符串。空值作为空字符串对待。

                I将参数值作为SQL标识符对待，如果需要，双写它。值为空是错误的。

                L引用参数值作为SQL文字。空值用字符串NULL显示，没有引用。

除了上述的格式说明符，特殊的序列%%可以用作输出%字符。

这里有一些基本的格式转换的例子：

SELECT format('Hello %s', 'World');
Result: Hello World

SELECT format('Testing %s, %s, %s, %%', 'one', 'two', 'three');
Result: Testing one, two, three, %

SELECT format('INSERT INTO %I VALUES(%L)', 'Foo bar', E'O\'Reilly');
Result: INSERT INTO "Foo bar" VALUES('O''Reilly')

SELECT format('INSERT INTO %I VALUES(%L)', 'locations', E'C:\\Program Files');
Result: INSERT INTO locations VALUES(E'C:\\Program Files')

这里是使用width字段和-标志的例子：

SELECT format('|%10s|', 'foo');
Result: |       foo|

SELECT format('|%-10s|', 'foo');
Result: |foo       |

SELECT format('|%*s|', 10, 'foo');
Result: |       foo|

SELECT format('|%*s|', -10, 'foo');
Result: |foo       |

SELECT format('|%-*s|', 10, 'foo');
Result: |foo       |

SELECT format('|%-*s|', -10, 'foo');
Result: |foo       |

下面是使用position字段的例子：

SELECT format('Testing %3$s, %2$s, %1$s', 'one', 'two', 'three');
Result: Testing three, two, one

SELECT format('|%*2$s|', 'foo', 10, 'bar');
Result: |       bar|

SELECT format('|%1$*2$s|', 'foo', 10, 'bar');
Result: |       foo|

和C函数sprintf不同，PostgreSQL的format 函数允许带有或不带有position字段的格式说明符在相同的格式字符串中混合使用。 没有position字段的格式说明符总是使用最后消耗参数的下一个参数。 另外，format函数不要求在格式字符串中使用所有函数参数。例如：

SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
Result: Testing three, two, three

%I和%L格式说明符对于安全构造动态SQL语句尤其有用。

---

五、二进制字符串函数和操作符

本节描述那些检查和操作类型为bytea数值的函数和操作符。

SQL定义了一些字符串函数， 在这些函数里使用关键字而不是逗号来分隔参数。 详情请见 表5-1。 PostgreSQL也提供了使用常用语法进行函数调用的函数的版本 (参阅 表5-2)。

        NOTES:

        本页面例子的结果在假设服务器的参数 bytea_output 设置为escape的基础上的（传统的PostgreSQL格式）。

表 5-1. SQL 二进制字符串函数和操作符

函数	返回类型	描述	例子	结果
string || string	bytea	字符串连接	E'\\\\Post'::bytea || E'\\047gres\\000'::bytea	\\Post'gres\000
octet_length(string)	int	二进制字符串中的字节数	octet_length(E'jo\\000se'::bytea)	5
overlay(string placing string from int [for int])	bytea	替换子串	overlay(E'Th\\000omas'::bytea placing E'\\002\\003'::bytea from 2 for 3)	T\\002\\003mas
position(substring in string)	int	特定子字符串的位置	position(E'\\000om'::bytea in E'Th\\000omas'::bytea)	3
substring(string [from int] [for int])	bytea	截取子串	substring(E'Th\\000omas'::bytea from 2 for 3)	h\000o
trim([both] bytes from string)	bytea	从string的开头和结尾删除只包含 bytes中字节的最长字符串	trim(E'\\000'::bytea from E'\\000Tom\\000'::bytea)	Tom

还有一些二进制字符串处理函数可以使用，在 表5-2 列出。其中有一些是在内部使用，用于实现表5-1 列出的SQL标准的字符串函数。

表 5-2. 其它二进制字符串函数

函数	返回类型	描述	例子	结果
btrim(string bytea, bytes bytea)	bytea	从string的开头和结尾删除只包含bytes 中字节的最长的字符串	btrim(E'\\000trim\\000'::bytea, E'\\000'::bytea)	trim
decode(string text, format text)	bytea	把string中的文本表示解码为二进制数据。format 的选项和encode相同。	decode(E'123\\000456', 'escape')	123\000456
encode(data bytea, format text)	text	把二进制数据编码为文本表现形式。支持的格式：base64, hex, escape。escape 转换零字节和高位设置字节为八进制序列(\nnn) 和双写反斜杠。	encode(E'123\\000456'::bytea, 'escape')	123\000456
get_bit(string, offset)	int	从字符串中抽取位	get_bit(E'Th\\000omas'::bytea, 45)	1
get_byte(string, offset)	int	从字符串中抽取字节	get_byte(E'Th\\000omas'::bytea, 4)	109
length(string)	int	二进制字符串的长度	length(E'jo\\000se'::bytea)	5
md5(string)	text	计算string的MD5散列值，以十六进制方式返回结果。	md5(E'Th\\000omas'::bytea)	8ab2d3c9689aaf18 b4958c334c82d8b1
set_bit(string, offset, newvalue)	bytea	设置字符串中的位	set_bit(E'Th\\000omas'::bytea, 45, 0)	Th\000omAs
set_byte(string, offset, newvalue)	bytea	设置字符串中的字节	set_byte(E'Th\\000omas'::bytea, 4, 64)	Th\000o@as

get_byte和set_byte数以二进制字符串的第一个字节为0字节。 get_bit和set_bit从每个字节的右边取位； 例如位0是第一个字节的最低位，位15是第二个字节的最高位。

六、位串函数和操作符

本节描述用于检查和操作位串的函数和操作符，也就是操作类型为bit 和bit varying值的函数和操作符。除了常用的比较操作符之外， 还可以使用 表6-1 里显示的操作符。 &, |,# 的位串操作数必须等长。在移位的时候，保留原始的位串长度(并以 0 填充)，如例子所示。

表6-1. 位串操作符

操作符	描述	例子	结果
||	连接	B'10001' || B'011'	10001011
&	位与	B'10001' & B'01101'	00001
|	位或	B'10001' | B'01101'	11101
#	位异或	B'10001' # B'01101'	11100
~	位非	~ B'10001'	01110
<<	位左移	B'10001' << 3	01000
>>	位右移	B'10001' >> 2	00100

下面的SQL标准函数除了可以用于字符串之外，也可以用于位串： length, bit_length, octet_length, position, substring, overlay。

下面的函数用于位串和二进制字符串：get_bit， set_bit。当用于位串时， 这些函数位数从字符串的第一位（最左边）作为0位 。

另外，我们可以在整数和bit之间来回转换。例子：

44::bit(10)                    0000101100
44::bit(3)                     100
cast(-44 as bit(12))           111111010100
'1110'::bit(4)::integer        14

请注意，只是转换为"bit"的意思是转换成bit(1)，因此只会转换成整数的最低位。

注意: 在PostgreSQL 8.0以前，把一个整数转换成bit(n) 将拷贝整数的最左边的n位，而现在是拷贝最右边的n位。 还有，把一个整数转换成比整数本身长的位串，就会扩展最左边的位(非负数为 0 ，负数为 1)。

七、模式匹配

PostgreSQL提供了三种实现模式匹配的方法： 传统SQL的LIKE操作符、SQL99 新增的 SIMILAR TO操作符、POSIX风格的正则表达式。 除了基本的"这个字符串匹配这个模式"操作符之外， 也可以使用函数抽取或替换匹配的子字符串并且在匹配的位置分隔字符串。

提示: 如果你的模式匹配要求比这些还多，请考虑用 Perl 或 Tcl 写一个用户定义函数。

7.1 LIKE

string LIKE pattern [ESCAPE escape-character]
string NOT LIKE pattern [ESCAPE escape-character]

如果该string匹配提供的pattern， 那么LIKE表达式返回真。和我们想像的一样，如果LIKE 返回真，那么NOT LIKE表达式将返回假，反之亦然。 一个等效的表达式是NOT (string LIKE pattern)。

如果pattern不包含百分号或者下划线，那么该模式只代表它本身； 这时候LIKE的行为就像等号操作符。在pattern 里的下划线(_)匹配任何单个字符；而一个百分号(%) 匹配零或多个任何序列。

一些例子：

'abc' LIKE 'abc'    true
'abc' LIKE 'a%'     true
'abc' LIKE '_b_'    true
'abc' LIKE 'c'      false

LIKE模式匹配总是覆盖整个字符串。因此， 如果想要匹配在字符串内部任何位置的序列，该模式必须以百分号开头和结尾。

要匹配下划线或者百分号本身，在pattern 里相应的字符必须前导转义字符。缺省的转义字符是反斜杠， 但是你可以用ESCAPE子句指定一个。要匹配转义字符本身， 写两个转义字符。

注意: 如果你关闭了standard_conforming_strings选项， 那么在文本字符串常量里的任意反斜杠都需要双写。 

我们也可以通过写成ESCAPE ''的方式关闭转义机制， 这时，我们就不能关闭下划线和百分号的特殊含义。

关键字ILIKE可以用于替换LIKE， 令该匹配就当前的区域设置是大小写无关的。这个特性不是SQL标准， 是PostgreSQL扩展。

操作符~~等效于LIKE，而~~* 等效于ILIKE。还有!~~和!~~* 操作符分别代表NOT LIKE和NOT ILIKE。 所有这些操作符都是PostgreSQL特有的。

7.2 SIMILAR TO 正则表达式

string SIMILAR TO pattern [ESCAPE escape-character]
string NOT SIMILAR TO pattern [ESCAPE escape-character]

SIMILAR TO根据自己的模式是否匹配给定字符串而返回真或者假。 它和LIKE非常类似，只不过它使用 SQL 标准定义的正则表达式理解模式。 SQL 标准的正则表达式是在LIKE 表示法和普通的正则表达式表示法之间古怪的交叉。

类似LIKE，SIMILAR TO 操作符只有在它的模式匹配整个字符串的时候才能成功； 这一点和普通的正则表达式的行为不同，在普通的正则表达式里， 模式匹配字符串的任意部分。和LIKE类似的地方还有 SIMILAR TO使用_和% 分别匹配单个字符和任意字符串(这些和 POSIX 正则表达式里的. 和.*兼容)。

除了这些从LIKE借用的功能之外，SIMILAR TO 支持下面这些从 POSIX 正则表达式借用的模式匹配元字符：

• |表示选择(两个候选之一)

• *表示重复前面的项零次或更多次

• +表示重复前面的项一次或更多次

• ?表示重复前面的项零次或一次

• { m}表示重复前面的项正好m次

• { m,}表示重复前面的项m或更多次

• { m,n} 表示重复前面的项至少m次，最多不超过n次

• Parentheses ()把项组合成一个逻辑项

• [...] 声明一个字符类，只在POSIX正则表达式中

请注意点(.)对于SIMILAR TO来说不是元字符。

和LIKE一样，反斜杠关闭所有这些元字符的特殊含义； 当然我们也可以用ESCAPE声明另外一个转义字符。

一些例子：

'abc' SIMILAR TO 'abc'      true
'abc' SIMILAR TO 'a'        false
'abc' SIMILAR TO '%(b|d)%'  true
'abc' SIMILAR TO '(b|c)%'   false

带三个参数的substring(string from pattern for escape-character) 函数提供了一个从字符串中抽取一个匹配 SQL 正则表达式模式的子字符串功能。 和SIMILAR TO一样，声明的模式必须匹配整个字符串， 否则函数失效并返回 NULL 。为了标识在成功的时候应该返回的模式部分， 模式必须出现后跟双引号(")的两个转义字符。 匹配这两个标记之间的模式的字符串将被返回。

一些例子，以#"分隔返回的字符串：

substring('foobar' from '%#"o_b#"%' for '#')   oob
substring('foobar' from '#"o_b#"%' for '#')    NULL

7.3 POSIX正则表达式

表7-3-1列出了所有用于 POSIX 正则表达式的操作符。

 表7-3-1 正则表达式匹配操作符

操作符	描述	例子
~	匹配正则表达式，大小写相关	'thomas' ~ '.*thomas.*'
~*	匹配正则表达式，大小写无关	'thomas' ~* '.*Thomas.*'
!~	不匹配正则表达式，大小写相关	'thomas' !~ '.*Thomas.*'
!~*	不匹配正则表达式，大小写无关	'thomas' !~* '.*vadim.*'

POSIX正则表达式提供了比LIKE和SIMILAR TO 操作符更强大的模式匹配的方法。许多 Unix 工具，比如egrep, sed,awk使用类似的模式匹配语言。

正则表达式是一个字符序列，它是定义一个字符串集合(一个正则集合 )的缩写。如果一个字符串是正则表达式描述的正则集合中的一员时， 我们就说这个字符串匹配该正则表达式。和LIKE一样， 模式字符准确地匹配字符串字符，除非在正则表达式语言里有特殊字符 (不过正则表达式用的特殊字符和LIKE用的不同)。 和LIKE不一样的是，正则表达式可以匹配字符串里的任何位置， 除非该正则表达式明确地锚定在字符串的开头或者结尾。

一些例子：

'abc' ~ 'abc'    true
'abc' ~ '^a'     true
'abc' ~ '(b|d)'  true
'abc' ~ '^(b|c)' false

POSIX模式语言将在下面详细描述。

带两个参数的substring(string from pattern) 函数提供了从字符串中抽取一个匹配 POSIX 正则表达式模式的子字符串的方法。 如果没有匹配它返回 NULL ，否则就是文本中匹配模式的那部分。 但是如果该模式包含任何圆括弧，那么将返回匹配第一对子表达式(对应第一个左圆括弧的)的文本。 如果你想在表达式里使用圆括弧而又不想导致这个例外， 那么你可以在整个表达式外边放上一对圆括弧。如果你需要在想抽取的子表达式前有圆括弧， 参阅描述的非捕获性圆括弧。

一些例子：

substring('foobar' from 'o.b')     oob
substring('foobar' from 'o(.)b')   o

regexp_replace(source, pattern, replacement [, flags ])函数提供了将匹配 POSIX 正则表达式模式的子字符串替换为新文本的功能。如果没有匹配 pattern 的子字符串， 那么返回不加修改的source字符串。如果有匹配， 则返回的 source字符串里面的对应子字符串将被replacement 字符串替换掉。replacement字符串可以包含\n， 这里的n是 1 到 9 ，表明源字符串中匹配第n 个圆括弧子表达式的部分将插入在该位置，并且它可以包含\& 表示应该插入匹配整个模式的字符串。如果你需要放一个文本反斜杠在替换文本里， 那么写\\。可选的flags 参数包含零个或多个改变函数行为的单字母标记。i表示进行大小写无关的匹配， g表示替换每一个匹配的子字符串而不仅仅是第一个。其他支持的标记在  表7-3-9 中描述。

一些例子：

regexp_replace('foobarbaz', 'b..', 'X')
                                   fooXbaz
regexp_replace('foobarbaz', 'b..', 'X', 'g')
                                   fooXX
regexp_replace('foobarbaz', 'b(..)', E'X\\1Y', 'g')
                                   fooXarYXazY

regexp_matches(string, pattern[, flags ])函数返回一个从匹配POSIX正则表达式模式中获取的所有子串结果的text数组。 这个函数可以返回零行，一行，或者多行（参阅下面的g标记）。如果pattern 没有匹配，则函数返回零行。如果模式包含没有括号的子表达式，则每行返回的是单元素的文本数组， 其中包含的子串相匹配整个模式。如果模式包含有括号的子表达式，函数返回一个文本数组，它的第 n个元素是子串匹配模式括号子表达式内的第n个元素。 （不计"非捕获"的括号；详细信息参阅下面）。参数flags是一个可选的text字符串， 含有0或者更多单字母标记来改变函数行为。标记g导致查找字符串中的每个匹配，而不仅是第一个， 每个匹配返回一行，其它支持的标记在  表7-3-9 里描述。

一些例子：

SELECT regexp_matches('foobarbequebaz', '(bar)(beque)');
 regexp_matches 
----------------
 {bar,beque}
(1 row)

SELECT regexp_matches('foobarbequebazilbarfbonk', '(b[^b]+)(b[^b]+)', 'g');
 regexp_matches 
----------------
 {bar,beque}
 {bazil,barf}
(2 rows)

SELECT regexp_matches('foobarbequebaz', 'barbeque');
 regexp_matches 
----------------
 {barbeque}
(1 row)

使用select子句，可能强制regexp_matches()总是返回一行； 当你想要返回SELECT目标列表中的所有行，甚至没有匹配的情况下，是有特别有用的。

SELECT col1, (SELECT regexp_matches(col2, '(bar)(beque)')) FROM tab;

regexp_split_to_table(string, pattern[, flags ])函数使用POSIX正则表达式模式作为分隔符，分隔字符串。 如果没有匹配pattern，函数将返回string。如果有至少一个匹配， 每个匹配返回从最后一个匹配结束（或者字符串的开头）到匹配开始的文本。当没有更多的匹配， 返回最后一个匹配的结束到字符串的结束的文本。flags参数是一个可选text字符串， 含有0或者更多单字母标记来改变函数行为。regexp_split_to_table 支持的标记在  表7-3-9 里描述。

除了regexp_split_to_array返回结果为text数组，regexp_split_to_array 函数行为与regexp_split_to_table相同，使用语法regexp_split_to_array (string, pattern[, flags ])。 参数与regexp_split_to_table相同。

一些例子：

SELECT foo FROM regexp_split_to_table('the quick brown fox jumps over the lazy dog', E'\\s+') AS foo;
  foo   
-------
 the    
 quick  
 brown  
 fox    
 jumps 
 over   
 the    
 lazy   
 dog    
(9 rows)

SELECT regexp_split_to_array('the quick brown fox jumps over the lazy dog', E'\\s+');
              regexp_split_to_array             
-----------------------------------------------
 {the,quick,brown,fox,jumps,over,the,lazy,dog}
(1 row)

SELECT foo FROM regexp_split_to_table('the quick brown fox', E'\\s*') AS foo;
 foo 
-----
 t         
 h         
 e         
 q         
 u         
 i         
 c         
 k         
 b         
 r         
 o         
 w         
 n         
 f         
 o         
 x         
(16 rows)

作为最后一个例子表明，发生在字符串的开始或结束或紧接前一个的匹配，regexp分隔函数忽略零长度匹配， 这样实现regexp_matches严格上来说是违背了的正则表达式匹配的定义，但在实际使用中， 通常是最便利的的行为。如Perl等软件系统，使用了类似的定义。

7.3.1 正则表达式细节

PostgreSQL的正则表达式使用 Henry Spencer 写的一个包来实现。 下面的大部分描述都是从他的手册页里逐字拷贝过来的。

正则表达式(REs)，在POSIX 1003.2中定义， 它有两种形式：扩展 RE或ERE (基本上就是在egrep里的那些)，基本RE 或BRE(基本上就是在ed里的那些)。PostgreSQL 两种形式都实现了，并且还做了一些 POSIX 里面没有的， 但是因为在类似 Perl 或者 Tcl 这样的语言中得到广泛应用的一些扩展。 使用了那些非 POSIX 扩展的正则表达式叫高级RE 或ARE。ARE 几乎完全是 ERE 的超集，但是 BRE 有几个符号上的不兼容(以及更多的限制)。 我们首先描述 ARE 和 ERE 形式，描述那些只适用于 ARE 的特性，然后描述与 BRE 的区别是什么。

注意: PostgreSQL总是初始化一个遵循ARE规则的正则表达式。然而， 更多限制的ERE或BRE规则可以通过在RE模式前放置一个embedded option来选择， 这对于期望完全兼容POSIX 1003.2规则的应用程序是有用的。

表 7-3-2. 正则表达式原子

原子	描述
(re)	(re 是任意正则表达式)匹配一个对re 的匹配，有可报告的匹配信息。
(?:re)	同上，但是匹配不会被报告(一个"非捕获"圆括弧)，只在 ARE 中有。
.	匹配任意单个字符
[chars]	一个方括弧表达式，匹配任意的字符
\k	(k是非字母数字字符)匹配一个当作普通字符看待的特定字符， 比如\\匹配一个反斜杠。
\c	c是一个字母数字(可能跟着其它字符)，它是一个转义。仅存在于 ARE 中；在 ERE 和 BRE 中， 它匹配c。
{	如果后面跟着一个非数字字符，那么就匹配左花括弧{； 如果跟着一个数字，那么它是范围的开始(见下面)
x	这里的x是一个没有其它特征的单个字符，则匹配该字符

RE不能以(\)结尾。

注意: 如果关闭了standard_conforming_stringsstandard_conforming_stringsstandard_conforming_strings， 任何文本字符串常量中的反斜杠都需要双写。

表 7-3-3. 正则表达式量词

量词	匹配
*	一个匹配 0 或者更多个原子的序列
+	一个匹配 1 或者更多个原子的序列
?	一个匹配 0 或者 1个原子的序列
{ m}	一个正好匹配m个原子的序列
{ m,}	一个匹配m个或者更多原子的序列
{ m,n}	一个匹配m到n个(包含两端)原子的序列； m不能比n大
*?	*的非贪婪模式
+?	+的非贪婪模式
??	?的非贪婪模式
{ m}?	{ m}的非贪婪模式
{ m,}?	{ m,}的非贪婪模式
{ m,n}?	{ m,n}的非贪婪模式

{ ...}的形式被称作范围。 一个范围内的数字m和n都是无符号十进制整数， 允许的数值从 0 到 255 (闭区间)。

非贪婪的量词(只在 ARE 中可用)匹配对应的正常(贪婪)模式， 区别是它寻找最少的匹配，而不是最多的匹配。

注意: 一个量词不能紧跟在另外一个量词后面，例如，**是非法的。 量词不能是表达式或者子表达式的开头，也不能跟在^ 或|后面。

表 7-3-4. 正则表达式约束

约束	描述
^	匹配字符串的开头
$	匹配字符串的结尾
(?=re)	正前瞻匹配任何匹配re 的子字符串起始点(只在 ARE 中有)
(?!re)	负前瞻匹配任何不匹配re 的子字符串起始点(只在 ARE 中有)

前瞻约束不能包含后引用， 并且在其中的所有圆括弧都被认为是不捕获的。

7.3.2 方括弧表达式

方括弧表达式是一个包围在[]里的字符列表。 它通常匹配任意单个列表中的字符(又见下文)。如果列表以^开头， 它匹配任意单个(又见下文)不在该列表中的字符。如果该列表中两个字符用 -隔开，那它就是那两个字符(包括在内)之间的所有字符范围的缩写， 比如，在ASCII里[0-9]包含任何十进制数字。 两个范围共享一个终点是非法的，比如a-c-e。这个范围与字符集关系密切， 可移植的程序不应该依靠它们。

想在列表中包含文本]，可以让它做列表的首字符(如果用到了， 跟在^ 后面)。想在列表中包含文本-， 可以让它做列表的首字符或者末字符，或者一个范围的第二个终点。 想在列表中把文本-当做范围的起点，把它用[. 和.]包围起来，这样它就成为一个集合元素(见下文)。 除了这些字符本身，和一些用[的组合(见下段)， 以及转义(只在 ARE 中有效)以外，所有其它特殊字符在方括弧表达式里都失去它们的特殊含义。 特别是，在 ERE 和 BRE 规则下\不是特殊的，但在 ARE 里， 它是特殊的(还是引入一个转义)。

在一个方括弧表达式里，一个集合元素(一个字符、一个当做一个字符的多字符序列、 或者一个表示上面两种情况的集合序列)包含在[.和.] 里面的时候表示该集合元素的字符序列。该序列是该方括弧列表的一个元素。 这允许一个包含多字符集合元素的方括弧表达式就可以匹配多于一个字符，比如， 如果集合序列包含一个ch集合元素，那么[[.ch.]]*c 匹配chchcc的头五个字符。译注：其实把 [. 和 .] 括起来的整体当一个字符看就行了。

注意: PostgreSQL目前不支持多字符集合元素。这些信息描述了将来可能有的行为。

在方括弧表达式里，在[=和=] 里包围的集合元素是一个等效表，代表等于这里所有集合元素的字符序列， 包括它本身(如果没有其它等效集合元素，那么就好像封装元素是[. 和.])。比如，如果o和^ 是一个等效表的成员，那么[[=o=]],[[=^=]], [o^]都是同义的。一个等效表不能是一个范围的端点。

在方括弧表达式里，在[:和:] 里面封装的字符表名字代表属于该表的所有字符的列表。标准的字符表名字是： alnum, alpha, blank, cntrl, digit, graph, lower, print, punct, space, upper, xdigit。 它们代表在ctype 里定义的字符表。本地化设置可能会提供其它的表。字符表不能用做一个范围的端点。

在方括弧表达式里有两个特例：方括弧表达式[[:<:]]和 [[:>:]]是约束，分别匹配一个单词开头和结束的空串。 单词定义为一个单词字符序列，前面和后面都没有其它单词字符。 单词字符是一个alnum字符(和ctype 里定义的一样)或者一个下划线。这是一个扩展，兼容POSIX 1003.2 ， 但那里面并没有说明，而且在准备移植到其它系统里去的软件里一定要小心使用。 通常下面描述的约束转义更好些；他们并非更标准，但是更容易输入。

7.3.3 正则表达式转义

转义是以\开头，后面跟着一个字母数字字符的特殊序列。 转义有好几种变体：字符项、表缩写、约束转义、后引用。在 ARE 里，如果一个\ 后面跟着一个字母数字，但是并未组成一个合法的转义，那么它是非法的。在 ERE 里则没有转义： 在方括弧表达式之外，一个跟着字母数字字符的\只是表示该字符是一个普通的字符， 而在一个方括弧表达式里，\是一个普通的字符(后者实际上是 ERE 和 ARE 之间的不兼容)。

字符项转义用于方便我们声明正则表达式里那些不可打印的字符。 它们在 表7-3-5 里列出。

类缩写转义用来提供一些常用的字符类缩写。 他们在表 9-16表7-3-6表 9-16里列出。

约束转义是一个约束，如果满足特定的条件，它匹配该空字符串，以转义形式写出。 它们在表 9-17表7-3-7表 9-17里列出。

后引用(\n)匹配数字n 指定的前面的圆括弧子表达式匹配的同一个字符串(参阅表 9-18表7-3-8表 9-18)。 比如，([bc])\1匹配bb或cc但是不匹配bc或cb。 正则表达式里的子表达式必须完全在后引用前面。子表达式以它的括号的顺序排序。 非捕获圆括弧并不定义子表达式。

注意: 请注意，如果把模式当作一个 SQL 字符串常量输入，那么转义前导的\需要双倍地写：

'123' ~ E'^\\d{3}' true

表 7-3-5. 正则表达式字符项转义

转义	描述
\a	警笛(铃声)字符，和 C 里一样
\b	退格，和 C 里一样
\B	\的同义词，用于减少反斜杠加倍的需要
\cX	(这里X是任意字符)字符的低 5 位和X 里的相同，其它位都是 0
\e	集合序列名字是ESC的字符，如果不是， 则是八进制值为 033 的字符
\f	进纸，和 C 里一样
\n	新行，和 C 里一样
\r	回车，和 C 里一样
\t	水平制表符，和 C 里一样
\uwxyz	(这里的wxyz是恰好四位十六进制位)本机字节序的 UTF-16 字符 U+wxyz
\Ustuvwxyz	(这里的stuvwxyz是恰好八位十六进制位) 为假想中的 Unicode 32 位扩展保留的
\v	垂直制表符，和 C 里一样
\xhhh	(这里的hhh是一个十六进制序列)十六进制值为 0xhhh的字符(不管用了几个十六进制位， 都是一个字符)
\0	值为0的字符 (null 字节)
\xy	(这里的xy是恰好两个八进制位， 并且不是一个后引用)八进制值为0xy 的字符
\xyz	(这里的xyz是恰好三位八进制位， 并且不是一个后引用)八进制值为0xyz 的字符

十六进制位是0-9, a-f, A-F。八进制位是0-7。

字符项转义总是被当作普通字符。比如，\135是 ASCII 中的]， 但\135并不终止一个方括弧表达式。

表 7-3-6. 正则表达式类缩写转义

转义	描述
\d	[[:digit:]]
\s	[[:space:]]
\w	[[:alnum:]_] (注意，这里是包含下划线的)
\D	[^[:digit:]]
\S	[^[:space:]]
\W	[^[:alnum:]_] (注意，这里是包含下划线的)

在方括弧表达式里，\d, \s, \w 会失去他们的外层方括弧，而 \D, \S, \W 是非法的。比如[a-c\d]等效于[a-c[:digit:]]。 同样[a-c\D]原来等效于[a-c^[:digit:]]的，也是非法的。

表 7-3-7. 正则表达式约束转义

转义	描述
\A	只匹配字符串开头
\m	只匹配一个词的开头
\M	只匹配一个词的结尾
\y	只匹配一个词的开头或者结尾
\Y	只匹配那些既不是词的开头也不是词的结尾的点
\Z	只匹配一个字符串的结尾

一个词的定义是上面[[:<:]]和[[:>:]]的声明。 在方括弧表达式里，约束转义是非法的。

表 7-3-8. 正则表达式后引用

转义	描述
\m	(这里的m是一个非零十进制位) 一个指向第m个子表达式的后引用
\mnn	(这里的m是一个非零十进制位，nn 是更多的十进制位，并且十进制数值mnn 不能大于到这个位置为止的闭合捕获圆括弧的个数)一个指向第mnn 个子表达式的后引用

注意: 在八进制字符项转义和后引用之间有一个继承的歧义存在，这个歧义是通过跟着的启发分析解决的， 像上面描述的那样。前导零总是表示这是一个八进制转义。而单个非零数字， 如果没有跟着任何其它数字，那么总是认为是后引用。 一个多数据位的非零开头的序列也认为是后引用(只要它在合适的子表达式后面， 也就是说，数值在后引用的合法范围内)，否则就认为是一个八进制。

7.3.4 正则表达式元语法

除了上面描述的主要语法之外，还有几种特殊形式和杂项语法。

正则表达式可以以两个特殊的指示器前缀之一开始： 如果一个正则表达式以***:开头，那么剩下的正则表达式都被当作 ARE 。 （这在PostgreSQL中通常没有影响，因为正则表达式被假设为ARE； 但是如果ERE或BRE模式被flags参数指定为正则表达式函数时是有影响的。） 如果一个的正则表达式以***=开头，那么剩下的正则表达式被当作一个文本串， 所有的字符都被认为是一个普通字符。

一个 ARE 可以以嵌入选项开头：一个(?xyz) 序列(这里的xyz是一个或多个字母字符)声明影响剩余正则表达式的选项。 这些选项覆盖任何前面判断的选项—它们可以重写正则表达式操作符隐含的大小写敏感性， 或者正则表达式函数的flags参数。可用的选项字母在 表7-3-9 显示。请注意， 正则表达式函数的flags参数使用相同的选项字母。

表7-3-9. ARE 嵌入选项字母

选项	描述
b	剩余的正则表达式是 BRE
c	大小写敏感匹配(覆盖操作符类型)
e	剩余的正则表达式是 ERE
i	大小写不敏感匹配(覆盖操作符类型)
m	n的历史同义词
n	新行敏感匹配
p	部分新行敏感匹配
q	重置正则表达式为一个文本("引起")字符串，所有都是普通字符。
s	非新行敏感匹配(缺省)
t	紧语法(缺省，见下文)
w	反转部分新行敏感("怪异")匹配
x	扩展的语法(见下文)

嵌入的选项在终止其序列的)发生作用。他们只在 ARE 的开始处起作用(如果有， 则在任何***:指示器后面)。

除了通常的(紧)正则表达式语法(这种情况下所有字符都重要)， 还有一种扩展语法，可以通过声明嵌入的x选项获得。 在扩展语法里，正则表达式中的空白字符被忽略，就像那些在# 和新行之间的字符一样（或正则表达式的结尾）。 这样就允许我们给一个复杂的正则表达式分段和注释。不过这个基本规则上有三种例外：

• 前置了\的空白字符或者 #保留

• 方括弧里的空白或者#保留

• 在多字符符号里面不能出现空白和注释，比如(?:

在这里，空白是空格、水平制表符、新行、和任何属于space(空白)字符表的字符。

最后，在 ARE 里，方括弧表达式外面，序列(?#ttt) (这里的ttt是任意不包含)的文本)是一个注释，完全被忽略。 同样，这样的东西是不允许出现在多字符符号的字符中间的，比如(?:。 这样的注释是比有用的机制的更久远的历史造成的，他们的用法已经废弃了； 我们应该使用扩展语法代替他。

如果声明了一个初始化的***=指示器，那么所有这些元语法扩展都 不能使用，因为这样表示把用户输入当作一个文本字符串而不是正则表达式对待。

7.3.5 正则表达式匹配规则

在正则表达式可以匹配给出的字符串中多于一个子字符串的情况下， 正则表达式匹配字符串中最靠前的那个子字符串。 如果正则表达式可以匹配在那个位置开始的多个子字符串，要么是取最长的子字符串， 要么是最短的，具体哪种，取决于正则表达式是贪婪的还是 非贪婪的。

一个正则表达式是否贪婪取决于下面规则：

• 大多数原子，以及所有约束，都没有贪婪属性(因为它们毕竟无法匹配个数变化的文本)。

• 在一个正则表达式周围加上圆括弧并不会改变其贪婪性。

• 一个带一个固定重复次数的量词({ m} 或{ m}?) 量化的原子和原子自身有着同样的贪婪性(可能是没有)。

• 一个带其它普通的量词(包括{ m,n} 中m等于n的情况)量化的原子是贪婪的(首选最长匹配)。

• 一个带非贪婪量词(包括{ m,n}? 中m等于n的情况)量化原子是非贪婪的(首选最短匹配)。

• 一个分支(也就是一个没有顶级|操作的正则表达式) 和它里面的第一个有贪婪属性的量化原子有着同样的贪婪性。

• 一个由|操作符连接起来的两个或者更多分支组成的正则表达式总是贪婪的。

上面的规则所描述的贪婪属性不仅仅适用于独立的量化原子， 而且也适用于包含量化原子的分支和整个正则表达式。这里的意思是， 匹配是按照分支或者整个正则表达式作为一个整体 匹配最长或者最短的子字符串的可能。一旦整个匹配的长度确定， 那么匹配任意子表达式的部分就基于该子表达式的贪婪属性进行判断， 在正则表达式里面靠前的子表达式的优先级高于靠后的子表达式。

一个表达这些的例子：

SELECT SUBSTRING('XY1234Z', 'Y*([0-9]{1,3})');
Result: 123
SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');
Result: 1

在第一个例子里，正则表达式作为整体是贪婪的，因为Y*是贪婪的。 它可以匹配从Y开始的东西，并且它匹配从这个位置开始的最长的字符串， 也就是Y123。输出是这里的圆括弧包围的部分，或者说是123。 在第二个例子里，正则表达式总体上是一个非贪婪的正则表达式 ，因为Y*? 是非贪婪的。它可以匹配从Y开始的最短的子字符串，也就是说Y1。 子表达式[0-9]{1,3}是贪婪的，但是它不能修改总体匹配长度的决定； 因此它被迫只匹配1。

简单说，如果一个正则表达式同时包含贪婪和非贪婪的子表达式， 那么总匹配长度要么是最长可能，要么是最短可能，取决于给整个正则表达式赋予的贪婪属性。 给子表达式赋予的贪婪属性只影响在这个匹配里，各个子表达式之间相互允许 "吃进"的多少。

量词{1,1}和{1,1}? 可以分别用于在一个子表达式或者整个正则表达式上强制贪婪或者非贪婪。

匹配长度是以字符衡量的，而不是集合的元素。一个空字符串会被认为比什么都不匹配长。 比如：bb*匹配abbbc的中间三个字符； (week|wee)(night|knights)匹配weeknights的所有十个字符； 而(.*).*匹配abc的时候，圆括弧包围的子表达式匹配所有三个字符； 而如果用(a*)*匹配bc，那么正则表达式和圆括弧子表达式都匹配空字符串。

如果声明了大小写无关的匹配，那么效果就好像把所有字母上的大小写区别取消了一样。 如果一个存在大小写差别的字母以一个普通字符的形式出现在方括弧表达式外面， 那么它实际上被转换成一个包含大小写的方括弧表达式，也就是说，x 变成[xX]。如果它出现在一个方括弧表达式里面， 那么它的所有大小写的同族都被加入方括弧表达式中，也就是说，[x] 变成[xX]而[^x]变成 [^xX]。

如果声明了新行敏感匹配，.和使用^ 的方括弧表达式将永远不会匹配新行字符(这样，匹配就绝对不会跨新行， 除非正则表达式明确地安排了这样的情况)并且^和$ 除了分别匹配字符串开头和结尾之外，还将分别匹配新行后面和前面的空字符串。 但是 ARE 转义\A和\Z 仍然只匹配字符串的开头和结尾。

如果声明了部分新行敏感匹配，那么它影响.和方括弧表达式， 这个时候和新行敏感匹配一样，但是不影响^和$。

如果声明了反转部分新行敏感匹配，那么它影响^和$， 作用和新行敏感匹配里一样，但是不影响.和方括弧表达式。 这个没什么太多用途，只是为了对称提供的。

7.3.6 限制和兼容性

在这个实现里，对正则表达式的长度没有特别的限制，但是， 那些希望能够有很好移植行的程序应该避免写超过 256 字节的正则表达式 ， 因为 POSIX 兼容的实现可以拒绝接受这样的正则表达式。

ARE 实际上和 POSIX ERE 不兼容的唯一的特性是在方括弧表达式里\ 并不失去它特殊的含义。所有其它 ARE 特性都使用在 POSIX ERE 里面是非法或者是未定义、 未声明效果的语法；指示器的***就是在 POSIX 的 BRE 和 ERE 之外的语法。

许多 ARE 扩展都是从 Perl 那里借来的，但是有些我做了修改，清理了一下， 以及一些 Perl 里没有出现的扩展。要注意的不兼容包括\b, \B， 对结尾的新行缺乏特别的处理，对那些新行敏感匹配的附加的补齐方括弧表达式， 在前瞻约束里对圆括弧和方括弧引用的限制，以及最长/最短匹配(而不是第一匹配)语义。

PostgreSQL 7.4 之前的版本里的 ARE 和 ERE 存在两个非常显著的不兼容：

• 在 ARE 里，后面跟着一个字母数字的\要么是一个转义，要么是错误， 但是在以前的版本里，它只是写那个字母数字的另外一种方法。这个应该不是什么问题， 因为在以前的版本里没有什么原因让我们写这样的序列。

• 在 ARE 里，\在[]里还是一个特殊字符， 因此在方括弧表达式里的一个文本\必须写成\\。

7.3.7 基本正则表达式

BRE 在几个方面和 ERE 不太一样。在BRE里，|, +,? 都是普通字符，它们没有等效的功能替换。范围的分隔符是\{和\}， 因为{和}本身是普通字符。嵌套的子表达式的圆括弧是\( 和\)，因为(和)自身是普通字符。 除非在正则表达式开头或者是圆括弧封装的子表达式开头，^都是普通字符， 除非在正则表达式结尾或者是圆括弧封装的子表达式的结尾，$是一个普通字符， 而如果*出现在正则表达式开头或者是圆括弧封装的子表达式开头 (前面可能有^)，那么它是个普通字符。最后，可以用单数字的后引用， 以及\<和\>分别是[[:<:]]和[[:>:]] 的同义词；在BRE里没有其它的转义。

八、数据类型和格式化函数

PostgreSQL格式化函数提供一套强大的工具用于把各种数据类型 （日期/时间、整数、浮点、数字） 转换成格式化的字符串以及反过来从格式化的字符串转换成 指定的数据类型。表 8-1 列出了这些函数。这些函数都遵循一个公共的调用规范： 第一个参数是待格式化的值，而第二个是一个定义输出或输入格式的模板。

表 8-1 格式化函数

函数	返回类型	描述	例子
to_char(timestamp, text)	text	把时间戳转成字符串	to_char(current_timestamp, 'HH12:MI:SS')
to_