Re: Partitioning by letter question

On Sat, Jan 30, 2010 at 7:11 AM, John Lister
<john(dot)lister-ps(at)kickstone(dot)co(dot)uk> wrote:
>> <john(dot)lister-ps(at)kickstone(dot)com> wrote:
>
> .> Hi, I was wondering if this was possible. I'm trying to partition a
> table,
> .> which is straightforward enough thanks to the great documentation, but i
>>>
>>> have a question:
>>>
>>> If I partition using something like a product_id for example and have
>>> check
>>> constraints such as (id>=1000 and id<2000) then everything is fine and
>>> the
>>> planner correctly uses the right subset of the tables. However I would
>>> like
>>> to partition by the first letter and using something like this
>>> substr(word,1,1)='a' is ignored by the planner. From reading the docs I
>>> understand that complicated check constraints are ignored, but this
>>> doesn't
>>> seem overly complicated.
>>>
>>> Am i doing something wrong or is there another better way to do this
>
>> Have you tried:
>
>> (word >= 'a' and word <'b')
>
> Cheers, had my programming head on. One question:
>
> any ideas about what to put for the last in the list
>
> i thought something like  (word>='z' and word<'{') which is based on the
> ascii ordering. - my db is using utf8
>
> I tried to check this by doing
>
> select * from words where word >'zzzz' order by word limit 10;
>
> which returns '.' as the first result (ok not a word, but that is a
> different issue) but if i do
>
> select * from words where word <'.' order by word desc limit 10
>
> I get '/...' as the first result, I would expect 'zzzz', this doesn't seem
> consistent.

Yeah, in non C locales, things like . and " " don't count for ordering.

As for the constraints, why not something like:

where word < 'a' or word > 'z'

Or something like that. Not that I'm not taking upper and lower case
into consideration here. you might need something like lower(word) <
'a' etc.