On Sat, Jan 30, 2010 at 7:11 AM, John Lister <john.lister...@kickstone.co.uk> wrote: >> <john.lister...@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. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql