> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Gauthier, Dave
> Sent: Friday, February 18, 2011 1:24 PM
> To: pgsql-general@postgresql.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.
I would normally do this in the perl script, using regexes, and not allowing the insert if the input data failed to pass the tests. If this is a CGI program, it is easy to use JavaScript to validate before submitting to the CGI script.
Of course, you have to ensure that every program that inserts or updates data checks the constraints. Or put them in the database, or both.
Susan
