From: Scott Marlowe <scott.marlowe@gmail.com>
To: John Lister <john.lister-ps@kickstone.co.uk>
Cc: pgsql-sql@postgresql.org
Subject: Re: Partitioning by letter question
Date: Sat, 30 Jan 2010 09:32:54 -0700
Message-ID: <dcc563d11001300832g4ff23e8bo50ee6d0497ae3bdb@mail.gmail.com> (raw)
In-Reply-To: <3AD82906BF1A43CB940573A88A42C2B3@squarepi.com>
References: <4B636022.90804@kickstone.com>
<dcc563d11001291550j70867455q2f16fdf3a4a9cc4@mail.gmail.com>
<3AD82906BF1A43CB940573A88A42C2B3@squarepi.com>
On Sat, Jan 30, 2010 at 7:11 AM, John Lister
<john.lister-ps@kickstone.co.uk> wrote:
>> <john.lister-ps@kickstone.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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: scott.marlowe@gmail.com, john.lister-ps@kickstone.co.uk
Subject: Re: Partitioning by letter question
In-Reply-To: <dcc563d11001300832g4ff23e8bo50ee6d0497ae3bdb@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox