Message-ID: <855BA0CC16444AAAA11F11E6082B510A@squarepi.com>
From: "John Lister" <john.lister-ps@kickstone.com>
To: <pgsql-sql@postgresql.org>
Subject: Re: Partitioning by letter question
Date: Sat, 30 Jan 2010 15:04:34 -0000
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1";
	reply-type=response
Content-Transfer-Encoding: 7bit

><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.

 I'm obviously missing some inherent sorting behaviour her, but not sure..

 Thanks

 John