3

I need to limit five special characters in my column specifically: period, comma, single-quotes, double-quotes, and dollar sign.

I understand I can use the following to only allow alphanumeric characters but I need to be able to accept the other special characters as well.

-- Check Constraint to only Allow Numbers & Alphabets 
ALTER TABLE Table_Name  
ADD CONSTRAINT ck_No_Special_Characters 
CHECK (Column_Name NOT LIKE '%[^A-Z0-9]%')
Smiley
  • 3,207
  • 13
  • 49
  • 66

1 Answers1

1

Here is an example. I have a table with a column that contains Enzyme Commission numbers, typically in the form 1.1.1.1 -- i.e. four period-delimited integers where the integers can be 1-3 digits (e.g., 4.2.3.181 ...).

I can ensure that pattern through the following CHECK constraint:

...
ec_num TEXT NOT NULL CHECK (ec_num ~ '[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}')
...

where

  • the tilde (~) is a regular expression match ("similarity comparison") operator, described in ref. [1] and [2] (below)
  • [0-9] matches any single digit, modified by {n} which specifies the degree of repetition of the preceding expression -- see ref. [3]
  • the period is \-escaped, otherwise it would match any single character -- again, see ref [3]

If I try to insert a malformed number, e.g. 1.1.1,4 (note the comma preceding the 4) then PostgreSQL throws an error, similar to:

ERROR:  23514: new row for relation "kegg_enzymes" violates
        check constraint "kegg_enzymes_ec_num_check"
DETAIL:  Failing row contains
         (4, 1.1.1,4, (r,r)-butanediol dehydrogenase, (R,R)-butanediol dehydrogenase).

[1] Difference between LIKE and ~ in Postgres

[2] https://www.postgresql.org/docs/current/functions-matching.html

[3] https://www.gnu.org/software/sed/manual/html_node/Regular-Expressions.html

Victoria Stuart
  • 4,610
  • 2
  • 44
  • 37
  • ~ will match any part of the string, meaning "foo1.2.3.4bar" is also valid. You should use the ^ and $ characters to indicate that the match must be at the start and the end (the entire string), not just a part of it. Like so: '^[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}$' – NeverCast Sep 11 '20 at 03:50