From: | "David Johnston" <polobo(at)yahoo(dot)com> |
---|---|
To: | "'Gauthier, Dave'" <dave(dot)gauthier(at)intel(dot)com>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: constraining chars for all cols of a table |
Date: | 2011-02-18 21:41:56 |
Message-ID: | 057e01cbcfb4$aabe4410$003acc30$@yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Restrict access to the table (for inserts) to a function that does the
verification and then executes the insert in addition to any kind of logging
and "RAISE"ing you need.
If you need to validate existing data I'd probably just do some one-time
verifications and updates where required.
A column "CHECK" constraint, however, seems like it should work just find if
you use a regular expression - and I cannot imagine it would be that
performance limiting.
Without a more specific model in mind choosing between different approaches
is difficult.
David J.
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Gauthier, Dave
Sent: Friday, February 18, 2011 4:24 PM
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] constraining chars for all cols of a table
Hi:
I have to constrain the chars used for table columns. For example...
create table foo (col1 text, col2 text, col3 text);
... where
col1 has to be all uppercase, nothing but [A-Z]
col2 has to be all lowercase [a-z] plus [0-9] is also allowed
col3 can be mixed case plus [0-9] and sqr brackets (but nothing else).
I could put a check constraint on each/every table column, but that seems
complicated and potentially slow.
I could do this check using the existing insert and update before triggers,
but then I'd have to loop through all the columns and decide one by one how
to
check them. Again, slow and complicated.
Is there a better way? Whatever I do, I'd have to be able to capture
violations to provide informative feedback to the users through the perl
script that'll actually be doing the insert/update.
Thanks in Advance !
From | Date | Subject | |
---|---|---|---|
Next Message | Gauthier, Dave | 2011-02-18 21:50:45 | Re: constraining chars for all cols of a table |
Previous Message | Vick Khera | 2011-02-18 21:31:45 | Re: Canceling a DELETE query |