5

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?

kender
  • 85,663
  • 26
  • 103
  • 145

3 Answers3

8

You don't even need stored procedures:

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

should work.

  • Ok. Any way to automate this for N tables, each with 1+ fields to be altered? like: for each table { for each field in table that is a key { alter } } – kender Jan 05 '10 at 09:51
  • 1
    requires scripting but the key to your task is in the `information_schema.` – Evan Carroll Jan 05 '10 at 15:51
  • 1
    Check my blogpost on "grantall" : http://www.depesz.com/index.php/2007/10/19/grantall/ - the technique I used there can be applied in this case as well. –  Jan 05 '10 at 20:11
  • 3
    I just used `your_column !~ ' '`. – Noumenon Mar 29 '18 at 04:25
  • I believe that won't account for other blank space characters, like Tabs and Carriage Returns – d36williams May 18 '22 at 15:16
4

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.

Milen A. Radev
  • 60,241
  • 22
  • 105
  • 110
  • This looks promising, but it requires altering all my existing data, which is quite a lot of tables. I was thinking more of a way to inject into my existing database, that's why I thought of a trigger and function first. – kender Jan 05 '10 at 09:26
  • this does NOT require altering all your data, only schema. DOMAINs are designed precisely for this. `ALTER TABLE foo ALTER bar TYPE my_domain;` – filiprem Jan 06 '10 at 08:52
0

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.

Christian V
  • 2,010
  • 17
  • 26