CODEWITHSUNDEEP
postgresqlconstraintsspecial-characterscheck-constraints
Smiley
Smiley

Reputation: 3437

PostgreSQL CHECK Constraint to Limit Specific Characters

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]%')

Upvotes: 3

Views: 3290

Answers (1)

Victoria Stuart
Victoria Stuart

Reputation: 5072

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

Upvotes: 1

Related Questions