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

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)

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)

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

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

RE: [GENERAL] Convert to upperWhy 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

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