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

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

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

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related