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