pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPartitioning by letter question
4+ messages / 2 participants
[nested] [flat]
* Partitioning by letter question
@ 2010-01-29 22:24 John Lister <john.lister-ps@kickstone.com>
2010-01-29 23:50 ` Re: Partitioning by letter question Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: John Lister @ 2010-01-29 22:24 UTC (permalink / raw)
To: pgsql-sql
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
Thanks
John
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Partitioning by letter question
2010-01-29 22:24 Partitioning by letter question John Lister <john.lister-ps@kickstone.com>
@ 2010-01-29 23:50 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-01-30 16:32 ` Re: Partitioning by letter question Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Scott Marlowe @ 2010-01-29 23:50 UTC (permalink / raw)
To: John Lister <john.lister-ps@kickstone.com>; +Cc: pgsql-sql
On Fri, Jan 29, 2010 at 3:24 PM, John Lister
<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')
?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Partitioning by letter question
2010-01-29 22:24 Partitioning by letter question John Lister <john.lister-ps@kickstone.com>
2010-01-29 23:50 ` Re: Partitioning by letter question Scott Marlowe <scott.marlowe@gmail.com>
@ 2010-01-30 16:32 ` Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Scott Marlowe @ 2010-01-30 16:32 UTC (permalink / raw)
To: John Lister <john.lister-ps@kickstone.co.uk>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Partitioning by letter question
@ 2010-01-30 15:04 John Lister <john.lister-ps@kickstone.com>
0 siblings, 0 replies; 4+ messages in thread
From: John Lister @ 2010-01-30 15:04 UTC (permalink / raw)
To: pgsql-sql
><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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2010-01-30 16:32 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-01-29 22:24 Partitioning by letter question John Lister <john.lister-ps@kickstone.com>
2010-01-29 23:50 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-01-30 16:32 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-01-30 15:04 Re: Partitioning by letter question John Lister <john.lister-ps@kickstone.com>
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