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 -----
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)
>
