~~ Operator In Postgres

From 9.7.1. LIKE of PostgreSQL documentation:

The operator ~~ is equivalent to LIKE, and ~~* corresponds to ILIKE. There are also !~~ and !~~* operators that represent NOT LIKE and NOT ILIKE, respectively. All of these operators are PostgreSQL-specific.

It isn't listed in the index of the documentation which is frustrating.

So I had a look with psql:

regress=> \do ~~                                     List of operators   Schema   | Name | Left arg type | Right arg type | Result type |       Description       ------------+------+---------------+----------------+-------------+------------------------- 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(4 rows)

It's an operator alias for LIKE, that's all.

~>=~, ~<=~, ~>~ 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.

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.

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

• Pattern matching with LIKE, SIMILAR TO or regular expressions in 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:

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