Postgres Pro
Downloads
Home   >   mailing lists

Thread: primary key on lower(varchar)

primary key on lower(varchar)

From
Date:
hi list
 
why can't i define a primary key on a varchar field as being lower(field)?
 
i'm getting the following error:
 
ERROR:  syntax error at or near "(" at character 94:   PRIMARY KEY ((lower(e_name) ));
 
 
here's the sql script:
 
ALTER TABLE "oldtables"."lexikon_entries"
  DROP CONSTRAINT "lexikon_entries_pkey" RESTRICT;
 
ALTER TABLE "oldtables"."lexikon_entries"
  ADD CONSTRAINT "lexikon_entries_pkey"
  PRIMARY KEY ((lower(e_name) ));
 
ALTER INDEX "oldtables"."lexikon_entries_pkey"
  OWNER TO "db_outnow";
 
 
this seems to work for normal indices... do i really have to create a 2nd index on the same field?
 
- thomas
 

Re: primary key on lower(varchar)

From
Tom Lane
Date:
<me@alternize.com> writes:
> why can't i define a primary key on a varchar field as being
> lower(field)?

Primary keys are only on simple field values; that's what the SQL
standard requires, and that's what we do.

I don't really see the use-case for what you want anyway.  Why don't
you just require the field to be all lower case, eg with a CHECK
constraint?

            regards, tom lane

Re: primary key on lower(varchar)

From
Date:
 > I don't really see the use-case for what you want anyway.  Why don't
> you just require the field to be all lower case, eg with a CHECK
> constraint?

simple case: lets say the table "translated_names" contains an foreign key,
the translated word and the language the word is in. obviously,
"Brotaufstrich" and "brotaufstrich" must relate to the same record. if i'm
just saving the records in lowercase (or uppercase) i'm loosing the proper
letter case...

the workaround of adding 2 word fields (word_lower, word_normal)  and
setting word_lower to primary key unfortunately wastes a lot of diskspace
espially when the table grows large...

- thomas

Re: primary key on lower(varchar)

From
Date:
--- me@alternize.com wrote:

>  > I don't really see the use-case for what you want
> anyway.  Why don't
> > you just require the field to be all lower case,
> eg with a CHECK
> > constraint?
>
> simple case: lets say the table "translated_names"
> contains an foreign key,
> the translated word and the language the word is in.
> obviously,
> "Brotaufstrich" and "brotaufstrich" must relate to
> the same record. if i'm
> just saving the records in lowercase (or uppercase)
> i'm loosing the proper
> letter case...
>
> the workaround of adding 2 word fields (word_lower,
> word_normal)  and
> setting word_lower to primary key unfortunately
> wastes a lot of diskspace
> espially when the table grows large...
>
> - thomas

isn't it easier to set up a serial for your primary key?



__________________________________________
Yahoo! DSL – Something to write home about.
Just $16.99/mo. or less.
dsl.yahoo.com

Re: primary key on lower(varchar)

From
Charley Tiggs
Date:
On Jan 6, 2006, at 11:29 PM, <me@alternize.com> <me@alternize.com>
wrote:

> > I don't really see the use-case for what you want anyway.  Why don't
>> you just require the field to be all lower case, eg with a CHECK
>> constraint?
>
> simple case: lets say the table "translated_names" contains an
> foreign key, the translated word and the language the word is in.
> obviously, "Brotaufstrich" and "brotaufstrich" must relate to the
> same record. if i'm just saving the records in lowercase (or
> uppercase) i'm loosing the proper letter case...
>
> the workaround of adding 2 word fields (word_lower, word_normal)
> and setting word_lower to primary key unfortunately wastes a lot of
> diskspace espially when the table grows large...

If I'm understanding you correctly, you have two tables that look
like this:

names
-----------
primary key
name

translated_names
------------------------
foreign key
translated word
language

if that is the case, why not simply relate both versions of word
(lower and proper case) to the same primary key?

Charley