The ~ <~ operator in Postgres

Question

^{(The initial part of this question , but it was a little inappropriate, so I decided to do it myself.)}

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

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

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

And this gives the reverse order:

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

Also information about 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) 

The third and fourth lines are the operator that I am looking for, but the description is not enough for me.

Answer 1

~>=~ , ~<=~ , ~>~ and ~<~ are text patterns (or varchar , basically the same) operators, analogues from their respective siblings >= , <= , > and < . They sort character data strictly by their byte values , ignoring the rules of any sorting setting (unlike their siblings). This makes them faster, but also invalid for most languages ​​/ countries.

The locale "C" is actually no different from the locale, not the sorting rules. This explains why ORDER BY name USING ~<~ and ORDER BY name COLLATE "C" do the same.

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

• Pattern matching with LIKE, SIMILAR or regular expressions in PostgreSQL

---

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

• ~~ Postgres statement
• Symfony2 Doctrine - ILIKE clause for PostgreSQL?
• PostgreSQL - a text array contains a value similar to