4.2.10. Collation Expressions

4.2.10. Collation Expressions

4.2.10.排序规则表达式

The COLLATE clause overrides the collation of an expression. It is appended to the expression it  applies to:

COLLATE子句会重写表达式的排序规则。它放到表达式后面：

 

expr COLLATE collation

 

where collation is a possibly schema-qualified identifier. The COLLATE clause binds tighter than  operators; parentheses can be used when necessary.

collation可能是一个有模式限定的标识符。COLLATE子句比运算符优先级更高；如有必要，可使用括号。

 

If no collation is explicitly specified, the database system either derives a collation from the columns  involved in the expression, or it defaults to the default collation of the database if no column is involved  in the expression.

如果未明确指定排序规则，则数据库系统要么从表达式中涉及的列中得出排序规则，要么在表达式中不涉及任何列时将其设置为数据库的默认排序规则。

 

The two common uses of the COLLATE clause are overriding the sort order in an ORDER BY clause, for example:

COLLATE子句的两种常见用法是覆盖ORDER BY子句中的排序顺序，例如：

 

SELECT a, b, c FROM tbl WHERE ... ORDER BY a COLLATE "C";

 

and overriding the collation of a function or operator call that has locale-sensitive results, for example:

并覆盖具有对语言环境敏感的函数或运算符调用时的排序规则，例如：

 

SELECT * FROM tbl WHERE a > 'foo' COLLATE "C";

 

Note that in the latter case the COLLATE clause is attached to an input argument of the operator we  wish to affect. It doesn't matter which argument of the operator or function call the COLLATE clause is  attached to, because the collation that is applied by the operator or function is derived by considering  all arguments, and an explicit COLLATE clause will override the collations of all other arguments. (Attaching non-matching   COLLATE clauses to more than one argument, however, is an error. For  more details see Section 23.2.) Thus, this gives the same result as the previous example:

注意，在后一种情况下，COLLATE子句附加到我们希望影响的运算符的输入参数上。COLLATE子句附加到的运算符或函数的自变量无关紧要，因为运算符或函数调用时所应用的排序规则是针对所有变量的，而显式COLLATE子句将覆盖所有其他参数的排序规则。（但是，将不匹配的COLLATE子句附加到多个参数上是一个错误。有关更多详细信息，请参见 第23.2节 。）因此，以下得出的结果与前面的示例相同：

 

SELECT * FROM tbl WHERE a COLLATE "C" > 'foo';

 

But this is an error:

而下面，是一个错误的示例：

 

SELECT * FROM tbl WHERE (a > 'foo') COLLATE "C";

 

because it attempts to apply a collation to the result of the > operator, which is of the non-collatable  data type boolean .

因为上例试图将排序规则应用到操作符>上，而 类型boolean并不是一个 排序规则数据类型。

 

查看表的排序规则：

 

mydb=# \d dept

                       Table "public.dept"

Column |          Type          | Collation | Nullable | Default

--------+------------------------+-----------+----------+---------

id     | numeric                |           |          |

dept   | character varying(100) |           |          |

name   | character varying(100) |           |          |

 

 

 

 

mydb=# select table_schema,

mydb-#        table_name,

mydb-#        column_name,

mydb-#        collation_name

mydb-# from information_schema.columns

mydb-# where collation_name is not null

mydb-# order by table_schema,

mydb-#          table_name,

mydb-#          ordinal_position;

    table_schema    |              table_name               |          column_name           | collation_name

--------------------+---------------------------------------+--------------------------------+----------------

information_schema | _pg_foreign_data_wrappers             | fdwoptions                     | C

information_schema | _pg_foreign_data_wrappers             | foreign_data_wrapper_catalog   | C

information_schema | _pg_foreign_data_wrappers             | foreign_data_wrapper_name      | C

information_schema | _pg_foreign_data_wrappers             | authorization_identifier       | C

information_schema | _pg_foreign_data_wrappers             | foreign_data_wrapper_language  | C

查看数据库的排序规则：

 

mydb=# select datname,

mydb-#        datcollate

mydb-# from pg_database;

  datname  | datcollate  

-----------+-------------

postgres  | en_US.UTF-8

mydb      | en_US.UTF-8

template1 | en_US.UTF-8

template0 | en_US.UTF-8

(4 rows)