pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHowto have a unique restraint on UPPER (textfield)
8+ messages / 5 participants
[nested] [flat]
* Howto have a unique restraint on UPPER (textfield)
@ 2010-01-31 02:26 Andreas <maps.on@gmx.net>
0 siblings, 3 replies; 8+ messages in thread
From: Andreas @ 2010-01-31 02:26 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Howto have a unique restraint on UPPER (textfield)
@ 2010-01-31 02:56 Joshua Tolley <eggyknap@gmail.com>
parent: Andreas <maps.on@gmx.net>
2 siblings, 1 reply; 8+ messages in thread
From: Joshua Tolley @ 2010-01-31 02:56 UTC (permalink / raw)
To: Andreas <maps.on@gmx.net>; +Cc: pgsql-sql
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 josh@josh# create table c (d text);
CREATE TABLE
5432 josh@josh*# create unique index c_ix on c (upper(d));
CREATE INDEX
5432 josh@josh*# insert into c (d) values ('text');
INSERT 0 1
5432 josh@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
Attachments:
signature.asc (application/pgp-signature, 196B) download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Howto have a unique restraint on UPPER (textfield)
@ 2010-01-31 08:02 msi77 <msi77@yandex.ru>
parent: Andreas <maps.on@gmx.net>
2 siblings, 0 replies; 8+ messages in thread
From: msi77 @ 2010-01-31 08:02 UTC (permalink / raw)
To: Andreas <maps.on@gmx.net>; pgsql-sql
Hi,
I think you need CS collation and UNIQUE(name).
> 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
>
Здесь спама нет http://mail.yandex.ru/nospam/sign
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Howto have a unique restraint on UPPER (textfield)
@ 2010-01-31 20:48 Andreas <maps.on@gmx.net>
parent: Joshua Tolley <eggyknap@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Andreas @ 2010-01-31 20:48 UTC (permalink / raw)
To: Joshua Tolley <eggyknap@gmail.com>; +Cc: pgsql-sql
Joshua Tolley schrieb:
> 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 josh@josh# create table c (d text);
> CREATE TABLE
> 5432 josh@josh*# create unique index c_ix on c (upper(d));
> CREATE INDEX
> 5432 josh@josh*# insert into c (d) values ('text');
> INSERT 0 1
> 5432 josh@josh*# insert into c (d) values ('tExt');
> ERROR: duplicate key value violates unique constraint "c_ix"
>
Thanks for clearing this up. :)
It works with CREATE UNIQUE INDEX.
So I had the missconception that UNIQUE (...) within CREATE TABLE (...)
was actually just an shorter way to define a unique index which it is not.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Howto have a unique restraint on UPPER (textfield)
@ 2010-01-31 21:07 Tom Lane <tgl@sss.pgh.pa.us>
parent: Andreas <maps.on@gmx.net>
0 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2010-01-31 21:07 UTC (permalink / raw)
To: Andreas <maps.on@gmx.net>; +Cc: Joshua Tolley <eggyknap@gmail.com>; pgsql-sql
Andreas <maps.on@gmx.net> writes:
> So I had the missconception that UNIQUE (...) within CREATE TABLE (...)
> was actually just an shorter way to define a unique index which it is not.
Well, it is that --- it just doesn't provide access to all the features
that CREATE INDEX does.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Howto have a unique restraint on UPPER (textfield)
@ 2010-02-01 04:27 Andreas <maps.on@gmx.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Andreas @ 2010-02-01 04:27 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Joshua Tolley <eggyknap@gmail.com>; pgsql-sql
Tom Lane schrieb:
> Andreas <maps.on@gmx.net> writes:
>
>> So I had the missconception that UNIQUE (...) within CREATE TABLE (...)
>> was actually just an shorter way to define a unique index which it is not.
>>
>
> Well, it is that --- it just doesn't provide access to all the features
> that CREATE INDEX does.
>
So as it is a shortcut for "create index" then why would the function
call of upper not be accepted when the sql parser maps the
uniqe-constraint into the "create index" command? The parser could just
take everything in the ( ) and use it as is.
Somehow there must be a notice in the meta data to mark the difference.
pgAdmin shows a unique as constraint but no index when created within
"create table".
The unique-index only shows up when created seperately.
regards
Andreas
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Howto have a unique restraint on UPPER (textfield)
@ 2010-02-01 04:41 Tom Lane <tgl@sss.pgh.pa.us>
parent: Andreas <maps.on@gmx.net>
0 siblings, 0 replies; 8+ messages in thread
From: Tom Lane @ 2010-02-01 04:41 UTC (permalink / raw)
To: Andreas <maps.on@gmx.net>; +Cc: Joshua Tolley <eggyknap@gmail.com>; pgsql-sql
Andreas <maps.on@gmx.net> writes:
> Tom Lane schrieb:
>> Well, it is that --- it just doesn't provide access to all the features
>> that CREATE INDEX does.
>>
> So as it is a shortcut for "create index" then why would the function
> call of upper not be accepted when the sql parser maps the
> uniqe-constraint into the "create index" command?
Because the UNIQUE constraint syntax is defined by the SQL standard,
and among other things the standard requires all UNIQUE constraints
to be represented in the information_schema. But the information_schema
views don't have the flexibility to represent anything but simple column
values in a unique constraint. So we just expose that in CREATE INDEX,
which is outside the standard anyway.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Howto have a unique restraint on UPPER (textfield)
@ 2010-02-05 20:57 Yeb Havinga <yebhavinga@gmail.com>
parent: Andreas <maps.on@gmx.net>
2 siblings, 0 replies; 8+ messages in thread
From: Yeb Havinga @ 2010-02-05 20:57 UTC (permalink / raw)
To: Andreas <maps.on@gmx.net>; +Cc: pgsql-sql
Andreas wrote:
> is there a way to define a unique restraint on UPPER (textfield)?
> psql throws a syntax error because of the upper() function.
The third section of the create index command at
http://www.postgresql.org/docs/8.4/interactive/sql-createindex.html
describes a function based index with as example the function ....
upper! :-)
postgres=# create table aap (a text);
CREATE TABLE
postgres=# create unique index ai on aap (upper(a));
CREATE INDEX
postgres=# insert into aap values ('aap');
INSERT 0 1
postgres=# insert into aap values ('aaP');
ERROR: duplicate key value violates unique constraint "ai"
regards,
Yeb Havinga
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2010-02-05 20:57 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-01-31 02:26 Howto have a unique restraint on UPPER (textfield) Andreas <maps.on@gmx.net>
2010-01-31 02:56 ` Joshua Tolley <eggyknap@gmail.com>
2010-01-31 20:48 ` Andreas <maps.on@gmx.net>
2010-01-31 21:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-02-01 04:27 ` Andreas <maps.on@gmx.net>
2010-02-01 04:41 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-01-31 08:02 ` msi77 <msi77@yandex.ru>
2010-02-05 20:57 ` Yeb Havinga <yebhavinga@gmail.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