Postgres中的运算符〜&~~ [英] Operator ~<~ in Postgres

问题描述

^{(最初是此问题的一部分，但这是无关紧要，所以我决定将其作为一个问题.)}

^{(Originally part of this question, but it was bit irrelevant, so I decided to make it a question of its own.)}

我找不到运算符~<~是什么. Postgres手册仅提及~和类似的运算符这里，但没有~<~的迹象.

I cannot find what the operator ~<~ is. The Postgres manual only mentions ~ and similar operators here, but no sign of ~<~.

在psql控制台中摆弄时，我发现这些命令给出了相同的结果:

When fiddling in the psql console, I found out that these commands give the same results:

SELECT * FROM test ORDER BY name USING ~<~;
SELECT * FROM test ORDER BY name COLLATE "C";

这些给出了相反的顺序:

And these gives the reverse ordering:

SELECT * FROM test ORDER BY name USING ~>~;
SELECT * FROM test ORDER BY name COLLATE "C" DESC;

有关代字号运算符的一些信息:

Also some info on the tilde operators:

\do ~*~
                                    List of operators
  Schema   | Name | Left arg type | Right arg type | Result type |       Description       
------------+------+---------------+----------------+-------------+-------------------------
pg_catalog | ~<=~ | character     | character      | boolean     | less than or equal
pg_catalog | ~<=~ | text          | text           | boolean     | less than or equal
pg_catalog | ~<~  | character     | character      | boolean     | less than
pg_catalog | ~<~  | text          | text           | boolean     | less than
pg_catalog | ~>=~ | character     | character      | boolean     | greater than or equal
pg_catalog | ~>=~ | text          | text           | boolean     | greater than or equal
pg_catalog | ~>~  | character     | character      | boolean     | greater than
pg_catalog | ~>~  | text          | text           | boolean     | greater than
pg_catalog | ~~   | bytea         | bytea          | boolean     | matches LIKE expression
pg_catalog | ~~   | character     | text           | boolean     | matches LIKE expression
pg_catalog | ~~   | name          | text           | boolean     | matches LIKE expression
pg_catalog | ~~   | text          | text           | boolean     | matches LIKE expression
(12 rows)

第3行和第4行是我要寻找的运算符，但说明对我来说有点不足.

The 3rd and 4th rows is the operator I'm looking for, but the description is a bit insufficient for me.

推荐答案

~>=~，~<=~，~>~和~<~是text模式(或varchar，基本上是相同的)运算符，各自同级>=，<=，>和<的对应对象.他们严格按字节值对字符数据进行排序，而忽略任何排序规则设置(与其同级相对)的规则.这样可以使它们更快，但对于大多数语言/国家/地区也无效.

~>=~, ~<=~, ~>~ and ~<~ are text pattern (or varchar, basically the same) operators, the counterparts of their respective siblings >=, <=, >and <. They sort character data strictly by their byte values, ignoring rules of any collation setting (as opposed to their siblings). This makes them faster, but also invalid for most languages / countries.

"C"语言环境实际上与无语言环境相同，这意味着没有排序规则.这就解释了为什么ORDER BY name USING ~<~和ORDER BY name COLLATE "C"最终会做同样的事情.

The "C" locale is effectively the same as no locale, meaning no collation rules. That explains why ORDER BY name USING ~<~ and ORDER BY name COLLATE "C" end up doing the same.

此相关答案的最后一章关于dba.SE的详细说明:

Detailed explanation in the last chapter of this related answer on dba.SE:

• 与LIKE，SIMILAR TO或PostgreSQL中的正则表达式匹配的模式

请注意， ~~ 是用于实现

Note that ~~ is the Postgres operator used to implement the SQL LIKE expression and is barely related to the above. Similarly, ~~* implements ILIKE. More:

• ~~ Postgres中的运算符
• Symfony2学说-PostgreSQL的ILIKE子句吗?
• PostgreSQL-文本数组包含类似于

• ~~ Operator In Postgres
• Symfony2 Doctrine - ILIKE clause for PostgreSQL?
• PostgreSQL - text Array contains value similar to

这篇关于Postgres中的运算符〜&~~的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！