9.1第九章

== 函数和操作(Functions and Operators)==
=== 逻辑操作符(Logical Operators)===
The usual logical operators are available:
AND
OR
NOT
SQL uses a three-valued Boolean logic where the null value represents "unknown". Observe the following truth tables:
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
The operators AND and OR are commutative, that is, you can switch the left and right operand without affecting the 
result. But see Section 4.2.14 for more information about the order of evaluation of subexpressions.
The usual comparison operators are available, shown in Table 9-1.
===比较操作符号（Comparison Operators）===
The usual comparison operators are available, shown in Table 9-1.
Table 9-1. Comparison Operators'
Operator	Description
< 	less than
> 	greater than
<= 	less than or equal to
>= 	greater than or equal to
= 	equal
<> or !=	not equal
Note: The != operator is converted to <> in the parser stage. It is not possible to implement != and <> 
operators that do different things.
Comparison operators are available for all relevant data types. All comparison operators are binary operators that 
return values of type boolean; expressions like 1 < 2 < 3 are not valid (because there is no < operator to compare 
a Boolean value with 3).
In addition to the comparison operators, the special BETWEEN construct is available:
a BETWEEN x AND y
is equivalent to
a >= x AND a <= y
Notice that BETWEEN treats the endpoint values as included in the range. NOT BETWEEN does the opposite comparison:
a NOT BETWEEN x AND y
is equivalent to
a < x OR a > y
BETWEEN SYMMETRIC is the same as BETWEEN except there is no requirement that the argument to the left of AND be 
less than or equal to the argument on the right. If it is not, those two arguments are automatically swapped, so 
that a nonempty range is always implied.
To check whether a value is or is not null, use the constructs:
expression IS NULL
expression IS NOT NULL
or the equivalent, but nonstandard, constructs:
expression ISNULL
expression NOTNULL
Do not write expression = NULL because NULL is not "equal to" NULL. (The null value represents an unknown value, 
and it is not known whether two unknown values are equal.) This behavior conforms to the SQL standard.
Tip: Some applications might expect that expression = NULL returns true if expression evaluates to the null 
value. It is highly recommended that these applications be modified to comply with the SQL standard. However, if 
that cannot be done the transform_null_equals configuration variable is available. If it is enabled, PostgreSQL 
will convert x = NULL clauses to x IS NULL.
Note: If the expression is row-valued, then IS NULL is true when the row expression itself is null or when 
all the row's fields are null, while IS NOT NULL is true when the row expression itself is non-null and all the 
row's fields are non-null. Because of this behavior, IS NULL and IS NOT NULL do not always return inverse results 
for row-valued expressions, i.e., a row-valued expression that contains both NULL and non-null values will return 
false for both tests. This definition conforms to the SQL standard, and is a change from the inconsistent behavior 
exhibited by PostgreSQL versions prior to 8.2.
Ordinary comparison operators yield null (signifying "unknown"), not true or false, when either input is null. For 
example, 7 = NULL yields null. When this behavior is not suitable, use the IS [ NOT ] DISTINCT FROM constructs:
expression IS DISTINCT FROM expression
expression IS NOT DISTINCT FROM expression
For non-null inputs, IS DISTINCT FROM is the same as the <> operator. However, if both inputs are null it returns 
false, and if only one input is null it returns true. Similarly, IS NOT DISTINCT FROM is identical to = for non-
null inputs, but it returns true when both inputs are null, and false when only one input is null. Thus, these 
constructs effectively act as though null were a normal data value, rather than "unknown".
Boolean values can also be tested using the constructs
expression IS TRUE
expression IS NOT TRUE
expression IS FALSE
expression IS NOT FALSE
expression IS UNKNOWN
expression IS NOT UNKNOWN
These will always return true or false, never a null value, even when the operand is null. A null input is treated 
as the logical value "unknown". Notice that IS UNKNOWN and IS NOT UNKNOWN are effectively the same as IS NULL and 
IS NOT NULL, respectively, except that the input expression must be of Boolean type.
===数学函数和操作符(Mathematical Functions and Operators)===
Mathematical operators are provided for many PostgreSQL types. For types without standard mathematical conventions 
(e.g., date/time types) we describe the actual behavior in subsequent sections.
Table 9-2 shows the available mathematical operators.
Table 9-2. Mathematical Operators
The bitwise operators work only on integral data types, whereas the others are available for all numeric data 
types. The bitwise operators are also available for the bit string types bit and bit varying, as shown in Table 9-
10.
Table 9-3 shows the available mathematical functions. In the table, dp indicates double precision. Many of these 
functions are provided in multiple forms with different argument types. Except where noted, any given form of a 
function returns the same data type as its argument. The functions working with double precision data are mostly 
implemented on top of the host system's C library; accuracy and behavior in boundary cases can therefore vary 
depending on the host system.
Table 9-3. Mathematical Functions
Function 	Return Type 	Description 	Example 	Result
abs(x) 	(same as input) 	absolute value 	abs(-17.4) 	17.4
cbrt(dp) 	dp 	cube root 	cbrt(27.0) 	3
ceil(dp or numeric) 	(same as input) 	smallest integer not less than argument 	ceil(-42.8) 	
ceiling(dp or numeric) 	(same as input) 	smallest integer not less than argument (alias for ceil) 	ceiling(-95.3) 	
degrees(dp) 	dp 	radians to degrees 	degrees(0.5) 	28.6478897565412
div(y numeric, x numeric) 	numeric 	integer quotient of y/x 	div(9,4) 	2
exp(dp or numeric) 	(same as input) 	exponential 	exp(1.0) 	2.71828182845905
floor(dp or numeric) 	(same as input) 	largest integer not greater than argument 	floor(-42.8) 	
ln(dp or numeric) 	(same as input) 	natural logarithm 	ln(2.0) 	0.693147180559945
log(dp or numeric) 	(same as input) 	base 10 logarithm 	log(100.0) 	2
log(b numeric, x numeric) 	numeric 	logarithm to base b 	log(2.0, 64.0) 	6.0000000000
mod(y, x) 	(same as argument types)	remainder of y/x 	mod(9,4) 	1
pi() 	dp 	"π" constant 	pi() 	3.14159265358979
power(a dp, b dp) 	dp 	a raised to the power of b 	power(9.0, 3.0) 	729
power(a numeric, b numeric) 	numeric 	a raised to the power of b 	power(9.0, 3.0) 	729
radians(dp) 	dp 	degrees to radians 	radians(45.0) 	0.785398163397448
random() 	dp 	random value in the range 0.0 <= x < 1.0 	random() 	
round(dp or numeric) 	(same as input) 	round to nearest integer 	round(42.4) 	42
round(v numeric, s int) 	numeric 	round to s decimal places 	round(42.4382, 2) 	42.44
setseed(dp) 	void 	set seed for subsequent random() calls (value between -1.0 and 1.0, inclusive)	setseed(0.54823) 	
sign(dp or numeric) 	(same as input) 	sign of the argument (-1, 0, +1) 	sign(-8.4) 	
sqrt(dp or numeric) 	(same as input) 	square root 	sqrt(2.0) 	1.4142135623731
trunc(dp or numeric) 	(same as input) 	truncate toward zero 	trunc(42.8) 	42
trunc(v numeric, s int) 	numeric 	truncate to s decimal places 	trunc(42.4382, 2) 	42.43
width_bucket(op numeric, b1 numeric, b2 numeric, count int)	int 	return the bucket to which operand would be assigned in an equidepth histogram with count buckets, in the range b1 
to b2	width_bucket(5.35, 0.024, 10.06, 5)	3
width_bucket(op dp, b1 dp, b2 dp, count int) 	int 	return the bucket to which operand would be assigned in an equidepth histogram with count buckets, in the range b1 
to b2	width_bucket(5.35, 0.024, 10.06, 5)	3
Finally, Table 9-4 shows the available trigonometric functions. All trigonometric functions take arguments and 
return values of type double precision. Trigonometric functions arguments are expressed in radians. Inverse 
functions return values are expressed in radians. See unit transformation functions radians() and degrees() above.
Table 9-4. Trigonometric Functions
Function 	Description
acos(x) 	inverse cosine
asin(x) 	inverse sine
atan(x) 	inverse tangent
atan2(y, x)	inverse tangent of y/x
cos(x) 	cosine
cot(x) 	cotangent
sin(x) 	sine
tan(x) 	tangent
===String Functions and Operators===
This section describes functions and operators for examining and manipulating string values. Strings in this context include values of the types character, character varying, and text. Unless otherwise noted, all of the functions listed below work on all of these types, but be wary of potential effects of automatic space-padding when using the character type. Some functions also exist natively for the bit-string types.
SQL defines some string functions that use key words, rather than commas, to separate arguments. Details are in Table 9-5. PostgreSQL also provides versions of these functions that use the regular function invocation syntax (see Table 9-6).
Note: Before PostgreSQL 8.3, these functions would silently accept values of several non-string data types as well, due to the presence of implicit coercions from those data types to text. Those coercions have been removed because they frequently caused surprising behaviors. However, the string concatenation operator (||) still accepts non-string input, so long as at least one input is of a string type, as shown in Table 9-5. For other cases, insert an explicit coercion to text if you need to duplicate the previous behavior.
Table 9-5. SQL String Functions and Operators
Additional string manipulation functions are available and are listed in Table 9-6. Some of them are used internally to implement the SQL-standard string functions listed in Table 9-5.
Table 9-6. Other String Functions
See also the aggregate function string_agg in Section 9.18.
Table 9-7. Built-in Conversions
Conversion Name [a] 		Source Encoding	Destination Encoding
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
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
Notes:
a. The conversion names follow a standard naming scheme: The official name of the source encoding with all non-alphanumeric characters replaced by underscores, followed by _to_, followed by the similarly processed destination encoding name. Therefore, the names might deviate from the customary encoding names.
===Binary String Functions and Operators===
This section describes functions and operators for examining and manipulating values of type bytea.
SQL defines some string functions that use key words, rather than commas, to separate arguments. Details are in Table 9-8. PostgreSQL also provides versions of these functions that use the regular function invocation syntax (see Table 9-9).
Table 9-8. SQL Binary String Functions and Operators 
Additional binary string manipulation functions are available and are listed in Table 9-9. Some of them are used internally to implement the SQL-standard string functions listed in Table 9-8.
Table 9-9. Other Binary String Functions
get_byte and set_byte number the first byte of a binary string as byte 0. get_bit and set_bit number bits from the right within each byte; for example bit 0 is the least significant bit of the first byte, and bit 15 is the most significant bit of the second byte.
Category:Chinese