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) | -42 |
| ceiling(dp or numeric) | (same as input) | smallest integer not less than argument (alias for ceil) | ceiling(-95.3) | -95 |
| 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) | -1 |
| 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.
