Operator ~<~ in Postgres

Question

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

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

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)

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

This operator is being used by Postgres for lookup if you have an index with opclass. postgresql.org/docs/9.5/static/indexes-opclass.html — Kamil Gosciminski
– Kamil Gosciminski, Commented Mar 4, 2016 at 23:30
Create an index on test (name text_pattern_ops) and look at the EXPLAIN output for name LIKE 'abc%' — Nick Barnes
– Nick Barnes, Commented Mar 5, 2016 at 0:35

Erwin Brandstetter · Accepted Answer · 2023-08-01 19:54:52Z

10

~>=~, ~<=~, ~>~ 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. The latter syntax variant should be preferred: more standard, less error-prone.

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

Pattern matching with LIKE, SIMILAR TO or regular expressions

Note that ~~ / ~~* are Postgres operators for LIKE / ILIKE and barely related to the above. Similarly, !~~ / !~~* for NOT LIKE / NOT IILKE. (Use standard LIKE notation instead of these "internal" operators.)

answered Mar 5, 2016 at 0:36

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vojtech Sokol Over a year ago

Thanks for the answer, especially for pointing out the relation to the COLLATE "C". I even read your answer on dba.SE prior to asking my question, but somehow I missed the part with ~<~ operator.

Collectives™ on Stack Overflow

Operator ~<~ in Postgres

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related