Re: Question about weird construct

Hi. I found this somewhere:

select a from b order by a using ~<~

I've been searching the docs but I found nothing about this weird ~<~
comparator. Not in comparison operators, String functions and operators,
nor the order by clause. Maybe it has something to do with regular
expressions, but so far I haven't found anything.

Thanks in advance.

Diego Manilla Suárez wrote:
> Hi. I found this somewhere:
>
> select a from b order by a using ~<~
>
> I've been searching the docs but I found nothing about this weird ~<~
> comparator. Not in comparison operators, String functions and operators,
> nor the order by clause. Maybe it has something to do with regular
> expressions, but so far I haven't found anything.

It's part of the text pattern operator family (also varchar, bpchar and
name patterns). When you define an index you can specify
text_pattern_ops to be used rather than the default comparison. This
lets you have LIKE 'abc%' use the index when you have a non-C locale
(where sorting isn't strictly alphabetic).

Of course, such an index is only usable by pattern-matching operations
not locale-aware < or >, so you might want two indexes on the same
column(s).

It's covered in the manuals under Indexes / Operator Classes (although
it doesn't seem to feature in the A-Z index).

Useful ways to explore operators:
\do ~>~
SELECT * FROM pg_operator WHERE oprname = '~>~';
SELECT * FROM pg_operator WHERE oprcode::text LIKE 'text_pattern%';

HTH
--
Richard Huxton
Archonet Ltd