Re: Question about weird construct

From: Richard Huxton <dev(at)archonet(dot)com>
To: Diego Manilla Suárez <diego(dot)manilla(at)xeridia(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Question about weird construct
Date: 2008-11-11 11:13:56
Message-ID: 491968F4.3090109@archonet.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Richard Huxton 2008-11-11 11:16:22 Re: merge 2 dumps
Previous Message Richard Huxton 2008-11-11 11:03:20 Re: Very slow queries w/ NOT IN preparation (seems like a bug, test case)