Prev: recovery_connections cannot start
Next: [HACKERS] INSERT and parentheses
From: Takahiro Itagaki on 26 Apr 2010 05:53

Rod Taylor <pg(a)rbt.ca> wrote:

> Is there any particular reason why the citext module doesn't have
> citext_pattern_ops operator family?
>
> Specifically, I wish to index for this type of query:
>
> ... WHERE citext_column LIKE 'Foo%';

I think it is a reasonable suggestion.

=# \d tbl
Table "public.tbl"
Column | Type | Modifiers
--------+--------+-----------
t | text |
c | citext |
Indexes:
"tbl_c_idx" btree (c)
"tbl_t_idx" btree (t)

=# SET enable_seqscan = off;
SET
=# EXPLAIN SELECT * FROM tbl WHERE t LIKE 'abc%';
QUERY PLAN
----------------------------------------------------------------------
Index Scan using tbl_t_idx on tbl (cost=0.00..8.27 rows=1 width=64)
Index Cond: ((t >= 'abc'::text) AND (t < 'abd'::text))
Filter: (t ~~ 'abc%'::text)
(3 rows)

=# EXPLAIN SELECT * FROM tbl WHERE c LIKE 'abc%';
QUERY PLAN
------------------------------------------------------------------------
Seq Scan on tbl (cost=10000000000.00..10000000001.01 rows=1 width=64)
Filter: (c ~~ 'abc%'::citext)
(2 rows)


Regards,
---
Takahiro Itagaki
NTT Open Source Software Center



--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Robert Haas on 26 Apr 2010 06:19
On Fri, Apr 23, 2010 at 11:27 PM, Rod Taylor <pg(a)rbt.ca> wrote:
> Is there any particular reason why the citext module doesn't have
> citext_pattern_ops operator family?

You forgot to send in the patch. :-)

....Robert

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Thom Brown on 26 Apr 2010 09:40
On 26 April 2010 11:19, Robert Haas <robertmhaas(a)gmail.com> wrote:

> On Fri, Apr 23, 2010 at 11:27 PM, Rod Taylor <pg(a)rbt.ca> wrote:
> > Is there any particular reason why the citext module doesn't have
> > citext_pattern_ops operator family?
>
> You forgot to send in the patch. :-)
>
> ...Robert
>
>
Yes, someone implementing this would be greatly appreciated, especially
since I've just started using this datatype. ;)

Thom
 | 
Pages: 1
Prev: recovery_connections cannot start
Next: [HACKERS] INSERT and parentheses