Обсуждение: Enforcing Case
I have a small single table database that multiple people enter data into, and I do some reporting from. I would like to be able to force everything to upper case because currently it seems that ABCD or abcd or AbCd are all treated as unique, and it cause me problems in reporting, not to mention it makes me nuts seeing the data in the tables all inconsistent like that. How would I force everything to upper case? I'm using PostgreSQL 7.2.1 on Debian Sid. Thanks in advance, jdk
On Thu, Aug 22, 2002 at 07:21:20 -0700, JDK <adahma@starport.org> wrote: > I have a small single table database that multiple people enter data > into, and I do some reporting from. I would like to be able to > force everything to upper case because currently it seems that ABCD > or abcd or AbCd are all treated as unique, and it cause me problems > in reporting, not to mention it makes me nuts seeing the data in the > tables all inconsistent like that. > > How would I force everything to upper case? You can use a check constraint. Something like col1 text constraint upper_only check (col1 ~ '^[A-Z]$') Note this ONLY allows uppercase. If you just want to ban lowercase then you need to change the constraint (but you probably want more than this).
> have a small single table database that multiple people enter data > into, and I do some reporting from. I would like to be able to force > everything to upper case because currently it seems that ABCD or abcd > or AbCd are all treated as unique, and it cause me problems in > reporting, not to mention it makes me nuts seeing the data in the > tables all inconsistent like that. > > How would I force everything to upper case? Would using the upper function work? for your data entry query insert into table (field) values (upper(value)); It'll be ok for new entries, but you'll have to do some wrangling on the old ones if they are to remain unique. Or on the output side select upper(field) from table Hope this helps Paul Butler
Or, if you want to make sure it is enforced by postgres, write a trigger that'll use the upper() function to force the case after an insert. > -----Original Message----- > From: pgsql-novice-owner@postgresql.org > [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of paul butler > Sent: Thursday, August 22, 2002 11:48 AM > To: JDK > Cc: pgsql-novice@postgresql.org > Subject: Re: [NOVICE] Enforcing Case > > > > > have a small single table database that multiple people enter data > > into, and I do some reporting from. I would like to be able to force > > everything to upper case because currently it seems that ABCD or abcd > > or AbCd are all treated as unique, and it cause me problems in > > reporting, not to mention it makes me nuts seeing the data in the > > tables all inconsistent like that. > > > > How would I force everything to upper case? > > Would using the upper function work? > > for your data entry query > > insert into table (field) values (upper(value)); > > It'll be ok for new entries, but you'll have to do some wrangling on > the old ones if they are to remain unique. > > Or on the output side > > select upper(field) from table > > Hope this helps > > Paul Butler > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > >
On Thu, Aug 22, 2002 at 04:47:53PM +0100, paul butler wrote: > > > have a small single table database that multiple people enter data > > into, and I do some reporting from. I would like to be able to force > > everything to upper case because currently it seems that ABCD or abcd > > or AbCd are all treated as unique, and it cause me problems in > > reporting, not to mention it makes me nuts seeing the data in the > > tables all inconsistent like that. > > > > How would I force everything to upper case? > > Would using the upper function work? > > for your data entry query > > insert into table (field) values (upper(value)); > OK, this worked nicely for my needs. Is there a command that I could run that would convert my existing data to all uppercase? Thanks, jdk
On Thu, 2002-08-22 at 13:08, JDK wrote: > On Thu, Aug 22, 2002 at 04:47:53PM +0100, paul butler wrote: > > > > > have a small single table database that multiple people enter data > > > into, and I do some reporting from. I would like to be able to force > > > everything to upper case because currently it seems that ABCD or abcd > > > or AbCd are all treated as unique, and it cause me problems in > > > reporting, not to mention it makes me nuts seeing the data in the > > > tables all inconsistent like that. > > > > > > How would I force everything to upper case? > > > > Would using the upper function work? > > > > for your data entry query > > > > insert into table (field) values (upper(value)); > > > > OK, this worked nicely for my needs. Is there a command that I > could run that would convert my existing data to all uppercase? update table set field=upper(field); > > Thanks, > jdk > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: ler@lerctr.org US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749