Thread: Difference between text_pattern_ops and varchar_pattern_ops
Hello all,
The documentation for operator classes (https://www.postgresql.org/docs/current/indexes-opclass.html) seems to indicate that text_pattern_ops should only be used for text columns and varchar_pattern_ops should be used for varchar columns. However today I stumbled upon an index using text_pattern_ops on a varchar column. Everything seems to be working fine. Now I am wondering what the difference between text_pattern_ops and varchar_pattern_ops might be.
Can somebody explain why text_pattern_ops can be used on varchar columns, considering it can’t be used for bpchar columns? And are there any downsides in doing this (aside from my confusion of course)?
Minimal example:
CREATE TABLE film ( title varchar(40) );
CREATE INDEX film_title_ix ON film USING btree (title text_pattern_ops);
Any thoughts on this subject are greatly appreciated.
Kind regards
David Morgenstern
David Morgenstern <d.morgenstern@synedra.com> writes:
> Can somebody explain why text_pattern_ops can be used on varchar
> columns, considering it can’t be used for bpchar columns?
The underlying rule there is that an opclass can be applied to an index
column if the column's actual type is binary-coercible to what the opclass
expects. varchar is binary-coercible to text, since they're basically the
same type. bpchar is not, because we must insert a coercion function that
strips any trailing blanks. (Essentially, the restriction exists because
the index machinery won't cope with such intermediate functions.)
regards, tom lane