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
[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] 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
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
