Peter Eisentraut <peter_e@gmx.net> writes:
> On Mon, 2012-12-24 at 10:12 -0500, Tom Lane wrote:
>> It is stated at
>> http://www.postgresql.org/docs/devel/static/indexes-opclass.html
>> that you don't need the special pattern opclasses in C locale.
>> Feel free to rephrase or document elsewhere if you find that too
>> obscure.
> What it doesn't make very clear is that you can also override the locale
> in the index definition itself. So instead of the recommended
> CREATE INDEX test_index ON test_table (col varchar_pattern_ops);
> you can write nearly equivalently
> CREATE INDEX test_index ON test_table (col COLLATE "C");
Ah, now I see what you're getting at. That's not equivalent though:
IIRC, an index defined in that way will not match to a plain old
WHERE col = 'constant' query, unless the prevailing locale is C anyway.
The pattern_ops index will match, because varchar_pattern_ops and
regular varchar_ops share the same equality operator.
> I'm also wondering whether the latter wouldn't be a preferable
> recommendation going forward.
Because of the above, it is most definitely not a preferable
recommendation. I don't mind if it's documented more explicitly, but
the pattern_ops approach is the one to recommend in most cases,
I believe.
regards, tom lane
