From: | David Morgenstern <d(dot)morgenstern(at)synedra(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Difference between text_pattern_ops and varchar_pattern_ops |
Date: | 2021-06-25 10:29:41 |
Message-ID: | 2EA363D8-E6F2-4EBF-9A74-BECBBF81B76E@synedra.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello all,
The documentation for operator classes (https://www.postgresql.org/docs/current/indexes-opclass.html <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
From | Date | Subject | |
---|---|---|---|
Next Message | Laurenz Albe | 2021-06-25 11:43:05 | Re: Difference between text_pattern_ops and varchar_pattern_ops |
Previous Message | Karsten Hilbert | 2021-06-25 09:50:09 | Re: pg_dump/pg_restore vs default_transaction_read_only under PG 13.2 |