Thread: Why is varchar_pattern_ops needed?

I understand the difference between "*_ops" and "*_pattern_ops".

But look at the following:

CREATE TABLE test (v varchar(30));
CREATE INDEX test_v_ind ON test (v varchar_pattern_ops);
CREATE INDEX test_t_ind ON test (v text_pattern_ops);

SET enable_seqscan = off;

EXPLAIN VERBOSE SELECT * FROM test WHERE v ~<~ 'mama';

                                QUERY PLAN
---------------------------------------------------------------------------
 Bitmap Heap Scan on laurenz.test  (cost=6.09..19.21 rows=250 width=78)
   Output: v
   Recheck Cond: ((test.v)::text ~<~ 'mama'::text)
   ->  Bitmap Index Scan on test_t_ind  (cost=0.00..6.03 rows=250 width=0)
         Index Cond: ((test.v)::text ~<~ 'mama'::text)
(5 rows)

DROP INDEX test_t_ind;

EXPLAIN VERBOSE SELECT * FROM test WHERE v ~<~ 'mama';

                                QUERY PLAN
---------------------------------------------------------------------------
 Bitmap Heap Scan on laurenz.test  (cost=6.09..19.21 rows=250 width=78)
   Output: v
   Recheck Cond: ((test.v)::text ~<~ 'mama'::text)
   ->  Bitmap Index Scan on test_v_ind  (cost=0.00..6.03 rows=250 width=0)
         Index Cond: ((test.v)::text ~<~ 'mama'::text)
(5 rows)

Obviously both indexes can be used.

It seems like internally, only text is used anyway; there is not a single
system operator that takes "varchar" as argument:

SELECT count(*) FROM pg_operator WHERE oprleft = 'varchar'::regtype;

 count
-------
     0
(1 row)

Now my question is:
Is there anything that "varchar_pattern_ops" is needed for that "text_pattern_ops" cannot provide?

In other words:
What were the consequences if I did
DELETE FROM pg_opclass WHERE opcname = 'varchar_pattern_ops';

Yours,
Laurenz Albe

Albe Laurenz <laurenz.albe@wien.gv.at> writes:
> Is there anything that "varchar_pattern_ops" is needed for that "text_pattern_ops" cannot provide?

Lack of surprise?  If you're creating a pattern index on a varchar column,
you'd likely expect to need to mention varchar_pattern_ops.

The idea that varchar is an alias for text might be second nature to old
Postgres hands, but it's not to most of the world.

            regards, tom lane

Tom Lane wrote:
> Albe Laurenz <laurenz.albe@wien.gv.at> writes:
>> Is there anything that "varchar_pattern_ops" is needed for that "text_pattern_ops" cannot provide?
>
> Lack of surprise?  If you're creating a pattern index on a varchar column,
> you'd likely expect to need to mention varchar_pattern_ops.
>
> The idea that varchar is an alias for text might be second nature to old
> Postgres hands, but it's not to most of the world.

Thanks for the explanation!

Yours,
Laurenz Albe