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
Message-ID: <855BA0CC16444AAAA11F11E6082B510A@squarepi.com> (raw)
><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
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: john.lister-ps@kickstone.com
Subject: Re: Partitioning by letter question
In-Reply-To: <855BA0CC16444AAAA11F11E6082B510A@squarepi.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