Re: [SQL] Partitioning by letter question
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 >'' 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
On Sat, Jan 30, 2010 at 7:11 AM, John Lister wrote: >> 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 >'' 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
[SQL] Howto have a unique restraint on UPPER (textfield)
Hi, is there a way to define a unique restraint on UPPER (textfield)? E.g. mytable ( name_id serial PRIMARY KEY, name varchar(255), UNIQUE ( upper (name) ) ) psql throws a syntax error because of the upper() function. I need to prohibit that 2 of strings like cow, Cow, CoW appears in the name-column. -- 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] Howto have a unique restraint on UPPER (textfield)
On Sun, Jan 31, 2010 at 03:26:14AM +0100, Andreas wrote: > Hi, > > is there a way to define a unique restraint on UPPER (textfield)? > > E.g. mytable ( >name_id serial PRIMARY KEY, >name varchar(255), >UNIQUE ( upper (name) ) > ) > > psql throws a syntax error because of the upper() function. > > I need to prohibit that 2 of strings like cow, Cow, CoW appears in > the name-column. Like this: 5432 j...@josh# create table c (d text); CREATE TABLE 5432 j...@josh*# create unique index c_ix on c (upper(d)); CREATE INDEX 5432 j...@josh*# insert into c (d) values ('text'); INSERT 0 1 5432 j...@josh*# insert into c (d) values ('tExt'); ERROR: duplicate key value violates unique constraint "c_ix" -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com signature.asc Description: Digital signature