Postgresql - how to disallow use of spaces in some string fields

Question

I want to disallow the use of spaces in some text/varchar fields.

Even more, it would be best to have only a set of characters that are allowed to use there, like:

[a-zA-Z0-9_\-]

And I want to make it as a rule to all VARCHAR fields that are members of primary key in their tables.

This should be done on the database level and could throw an exception when trying to insert a wrong record or update one with a change of a key field to invalid value.

Can this be done within the database level? Should I use Pl/Perl for that, or is there any simpler method?

Answer 1

You can define a domain, look at http://www.postgresql.org/docs/current/interactive/sql-createdomain.html at the bottom, there is an example about US postal code.

Answer 2

Seeing your latest comment you could perhaps use CHECK constraints and regex search? But you will have to modify the schema (tables) and insert it for each field.

Answer 3

You don't even need stored procedures:

alter table xxx add constraint check_valid_chars check ( your_column ~ '^[a-zA-Z0-9_\-]+$' );

should work.