databasepostgresqlplperl

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

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?

Solution

  • You don't even need stored procedures:

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

    should work.