Thread: Convert to upper

Convert to upper

From

Matthias Teege

Date:

02 March 2001, 13:45:44

Moin,

is there any way to limit values to upper case strings?
Somthing like:

name char(15) DEFAULT (upper(name))

or must I use triggers?

Many thanks
Matthias

--
Matthias Teege -- matthias@mteege.de -- http://emugs.de
make world not war
PGP-Key auf Anfrage

Re: Convert to upper

From

"Mitch Vincent"

Date:

02 March 2001, 13:55:30

> is there any way to limit values to upper case strings?
> Somthing like:
>
> name char(15) DEFAULT (upper(name))
>
> or must I use triggers?

I'd say a trigger is your best bet.

-Mitch

Re: Re: Convert to upper

From

"Rod Taylor"

Date:

02 March 2001, 14:21:19

check

create table uppercase (
  name char(15) check (name ~ '[A-Z]')
);

--
Rod Taylor

There are always four sides to every story: your side, their side, the
truth, and what really happened.
----- Original Message -----
From: "Mitch Vincent" <mitch@venux.net>
To: <pgsql-general@postgresql.org>
Sent: Friday, March 02, 2001 10:52 AM
Subject: [GENERAL] Re: Convert to upper


> > is there any way to limit values to upper case strings?
> > Somthing like:
> >
> > name char(15) DEFAULT (upper(name))
> >
> > or must I use triggers?
>
> I'd say a trigger is your best bet.
>
> -Mitch
>
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to
majordomo@postgresql.org)
>

Re: Convert to upper

From

Peter Schindler

Date:

02 March 2001, 15:22:43

Matthias,

the easiest way is to use CHECK constraints. see example below.

Servus,
Peter

Matthias Teege wrote:
> is there any way to limit values to upper case strings?
> Somthing like:
>
> name char(15) DEFAULT (upper(name))
>
> or must I use triggers?

test=# create table bla(bb char(10) CHECK (bb =UPPER(bb)));
CREATE
test=# \d bla
             Table "bla"
 Attribute |     Type      | Modifier
-----------+---------------+----------
 bb        | character(10) |
Constraint: ((bb)::text = upper((bb)::text))

test=# insert into bla values ('OTTO');
INSERT 381409 1
test=# insert into bla values ('otto');
ERROR:  ExecAppend: rejected due to CHECK constraint bla_bb
ERROR:  ExecAppend: rejected due to CHECK constraint bla_bb
test=# select * from bla;
     bb
------------
 OTTO
(1 row)

Re: Re: Convert to upper

From

"Mitch Vincent"

Date:

02 March 2001, 15:52:23

Oh, I misunderstood.. I read his post that he wanted anything inserted to be
converted to upper case.. If you're just looking to throw an error if it's
not upper case, the check constraint is the way to go..

My apologies, I'll try and read more carefully..

-Mitch

----- Original Message -----
From: "Rod Taylor" <rod.taylor@inquent.com>
To: <pgsql-general@postgresql.org>
Sent: Friday, March 02, 2001 11:05 AM
Subject: Re: Re: Convert to upper


> check
>
> create table uppercase (
>   name char(15) check (name ~ '[A-Z]')
> );
>
> --
> Rod Taylor
>
> There are always four sides to every story: your side, their side, the
> truth, and what really happened.
> ----- Original Message -----
> From: "Mitch Vincent" <mitch@venux.net>
> To: <pgsql-general@postgresql.org>
> Sent: Friday, March 02, 2001 10:52 AM
> Subject: [GENERAL] Re: Convert to upper
>
>
> > > is there any way to limit values to upper case strings?
> > > Somthing like:
> > >
> > > name char(15) DEFAULT (upper(name))
> > >
> > > or must I use triggers?
> >
> > I'd say a trigger is your best bet.
> >
> > -Mitch
> >
> >
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> >     (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

RE: Convert to upper

From

"Trewern, Ben"

Date:

02 March 2001, 15:53:13

It may be better using a trigger. You don't then get the error message (from the constraint) the record is just updated with the uppercase version of what was inserted.

Regards

Ben

> -----Original Message-----
> From: Peter Schindler [mailto:pschindler@synchronicity.com]
> Sent: 02 March 2001 16:16
> To: Matthias Teege
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Convert to upper
>
>
> Matthias,
>
> the easiest way is to use CHECK constraints. see example below.
>
> Servus,
> Peter
>
> Matthias Teege wrote:
> > is there any way to limit values to upper case strings?
> > Somthing like:
> >
> > name char(15) DEFAULT (upper(name))
> >
> > or must I use triggers?
>
> test=# create table bla(bb char(10) CHECK (bb =UPPER(bb)));
> CREATE
> test=# \d bla
>              Table "bla"
> Attribute |     Type      | Modifier
> -----------+---------------+----------
> bb        | character(10) |
> Constraint: ((bb)::text = upper((bb)::text))
>
> test=# insert into bla values ('OTTO');
> INSERT 381409 1
> test=# insert into bla values ('otto');
> ERROR: ExecAppend: rejected due to CHECK constraint bla_bb
> ERROR: ExecAppend: rejected due to CHECK constraint bla_bb
> test=# select * from bla;
>      bb
> ------------
> OTTO
> (1 row)
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
>

Re: Convert to upper

From

"Rod Taylor"

Date:

02 March 2001, 16:49:15

Why not just do:

INSERT INTO TABLE (uppercase_value) VALUES (upper('value'));

Leave in the check, and all problems are solved without overhead of a trigger. Simple checks like the one shown don't have any noticeable speed loss. Trigger overhead does no matter how small the operation its doing.

--
Rod Taylor

There are always four sides to every story: your side, their side, the truth, and what really happened.

----- Original Message -----
From: Trewern, Ben
To: pgsql-general@postgresql.org
Sent: Friday, March 02, 2001 12:35 PM
Subject: RE: [GENERAL] Convert to upper

It may be better using a trigger. You don't then get the error message (from the constraint) the record is just updated with the uppercase version of what was inserted.
Regards
Ben
> -----Original Message-----
> From: Peter Schindler [mailto:pschindler@synchronicity.com]
> Sent: 02 March 2001 16:16
> To: Matthias Teege
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Convert to upper
>
>
> Matthias,
>
> the easiest way is to use CHECK constraints. see example below.
>
> Servus,
> Peter
>
> Matthias Teege wrote:
> > is there any way to limit values to upper case strings?
> > Somthing like:
> >
> > name char(15) DEFAULT (upper(name))
> >
> > or must I use triggers?
>
> test=# create table bla(bb char(10) CHECK (bb =UPPER(bb)));
> CREATE
> test=# \d bla
>              Table "bla"
> Attribute |     Type      | Modifier
> -----------+---------------+----------
> bb        | character(10) |
> Constraint: ((bb)::text = upper((bb)::text))
>
> test=# insert into bla values ('OTTO');
> INSERT 381409 1
> test=# insert into bla values ('otto');
> ERROR: ExecAppend: rejected due to CHECK constraint bla_bb
> ERROR: ExecAppend: rejected due to CHECK constraint bla_bb
> test=# select * from bla;
>      bb
> ------------
> OTTO
> (1 row)
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
>

Re: Convert to upper

From

Tomek Zielonka

Date:

03 March 2001, 10:35:50

On Fri, Mar 02, 2001 at 11:05:42AM -0500, Rod Taylor wrote:
> check
>
> create table uppercase (
>   name char(15) check (name ~ '[A-Z]')
> );

If don't want lowercase letters, but allow digits, !@#$%^&*( etc.:

create table uppercase (
  name char(15) check (name !~ '[[:lower:]]')
);

If you want only uppercase letters:

create table uppercase (
  name char(15) check (name ~ '^[[:upper:]]*$');
);

Tomek

--
.signature: Too many levels of symbolic links