Search Postgresql Archives

Re: Question about weird construct

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux