Re: [HACKERS] CIText and pattern_ops

2010-05-26 Thread David E. Wheeler 
Just picking up a dropped thread, does anyone have a patch for this?

  http://archives.postgresql.org/pgsql-hackers/2010-04/msg01191.php

Best,

David

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

Re: [HACKERS] CIText and pattern_ops

2010-04-26 Thread Takahiro Itagaki 

Rod Taylor p...@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=100.00..101.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] CIText and pattern_ops

2010-04-26 Thread Robert Haas 
On Fri, Apr 23, 2010 at 11:27 PM, Rod Taylor p...@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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] CIText and pattern_ops

2010-04-26 Thread Thom Brown 
On 26 April 2010 11:19, Robert Haas robertmh...@gmail.com wrote:

 On Fri, Apr 23, 2010 at 11:27 PM, Rod Taylor p...@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

[HACKERS] CIText and pattern_ops

2010-04-23 Thread Rod Taylor 
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%';

This, of course, is equivalent to ILIKE 'Foo%' which does not appear
to be indexable without using a functional index (
lower(citext_column) ).

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