Re: [SQL] Partitioning by letter question

2010-01-30 Thread John Lister 

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 id2000) 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 '' 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 '', this doesn't seem 
consistent.


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

Thanks

John


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Partitioning by letter question

2010-01-30 Thread Scott Marlowe 
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 id2000) 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 '' 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 '', 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

Re: [SQL] Partitioning by letter question

2010-01-29 Thread Scott Marlowe 
On Fri, Jan 29, 2010 at 3:24 PM, John Lister
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 id2000) 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')

?

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql