Postgresql restriction for validating non-ascii characters

Question

I have a Postgresql 9.3 database that is encoded with 'UTF8'. However, the database has a column that should never contain anything other than ASCII. And if non-ascii gets there, it causes a problem on another system in which I cannot control. So I want to add a constraint to the column. Note. I already have a BEFORE INSERT trigger - this might be a good place to check.

What is the best way to accomplish this in PostgreSQL?

Answer 1

You can define ASCII as ordinal 1 to 127 for this purpose, so the following query will identify a string with the values ​​"non-ascii":

 SELECT exists(SELECT 1 from regexp_split_to_table('abcdéfg','') x where ascii(x) not between 1 and 127); 

but this is unlikely to be super-efficient, and using subqueries will force you to do this in a trigger, not in a CHECK constraint.

Instead, I would use a regex. If you want all printable characters, you can use a range in a control limit, for example:

 CHECK (my_column ~ '^[ -~]*$') 

this will fit everything from place to tilde , which is an ASCII printable range.

If you want all ASCII printable and non-printable, you can use byte screens :

 CHECK (my_column ~ '^[\x00-\x7F]*$') 

---

The most strictly correct approach is convert_to(my_string, 'ascii') , and let an exception occur if it fails ... but PostgreSQL does not offer ASCII encoding (i.e. 7 bits), so the approach is not possible.

Answer 2

Use a regex-based CHECK constraint .

Assuming you have a specific column in mind, you should never contain anything but lowercase letters from a to z, uppercase letters from A to Z and numbers from 0 to 9, something like this should work.

 alter table your_table add constraint allow_ascii_only check (your_column ~ '^[a-zA-Z0-9]+$'); 

This is what people usually mean when they say “only ASCII” regarding database columns, but ASCII also includes glyphs for punctuation, arithmetic operators, etc. The characters you want to allow go between the square brackets.