DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=jeP6Iz6s32aQ696YFIBhUu+QQjOBdLD/FKFm/JJ+nztNYIrPFz6BGz6qEAkIKzYwpy
	dQGV50/3Xjw5ecxDct49vGYVZPvgT707uiqDyi+RsqTq8OfXKuznN/9iYLrr/p1Ei1ZM
	KOEd9IHVfponC2Y/jy2bctFa1TcUv9+cYvECk=
MIME-Version: 1.0
In-Reply-To: <3AD82906BF1A43CB940573A88A42C2B3@squarepi.com>
References: <4B636022.90804@kickstone.com>
	<dcc563d11001291550j70867455q2f16fdf3a4a9cc4@mail.gmail.com>
	<3AD82906BF1A43CB940573A88A42C2B3@squarepi.com>
Date: Sat, 30 Jan 2010 09:32:54 -0700
Message-ID: <dcc563d11001300832g4ff23e8bo50ee6d0497ae3bdb@mail.gmail.com>
Subject: Re: Partitioning by letter question
From: Scott Marlowe <scott.marlowe@gmail.com>
To: John Lister <john.lister-ps@kickstone.co.uk>
Cc: pgsql-sql@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

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>=3D1000 and id<2000) then everything is fine an=
d
>>> 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)=3D'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 >=3D '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 =A0(word>=3D'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 see=
m
> 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.