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?

user80168 · Accepted Answer · 2010-01-05 09:47:23Z

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.

answered Jan 5, 2010 at 9:47

user80168

Sign up to request clarification or add additional context in comments.

5 Comments

kender Over a year ago

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 } }

Evan Carroll Over a year ago

requires scripting but the key to your task is in the information_schema.

user80168 Over a year ago

Check my blogpost on "grantall" : depesz.com/index.php/2007/10/19/grantall - the technique I used there can be applied in this case as well.

Noumenon Over a year ago

I just used your_column !~ ' '.

d36williams Over a year ago

I believe that won't account for other blank space characters, like Tabs and Carriage Returns

Milen A. Radev · Accepted Answer · 2010-01-05 09:37:45Z

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.

edited Jan 5, 2010 at 9:37

Milen A. Radev

63.2k22 gold badges111 silver badges112 bronze badges

answered Jan 5, 2010 at 9:18

Nicolas Goy

2 Comments

kender Over a year ago

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.

filiprem Over a year ago

this does NOT require altering all your data, only schema. DOMAINs are designed precisely for this. ALTER TABLE foo ALTER bar TYPE my_domain;

Christian V · Accepted Answer · 2010-01-05 09:42:50Z

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.

answered Jan 5, 2010 at 9:42

Christian V

2,01817 silver badges26 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

5 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related