How to use regex with PostgreSQL to Constrain a Columns input as alpha-numeric [duplicate]

Question

I am attempting to create a constraint on a table that will ensure that a columns input is alpha-numerical ( eventually wishing to add a constraint that will prevent certain inputs based on the context type)

The following code does not seem to work because the regexp_matches() function needs to return a boolean. How would I go about using Regex in a column constraint so it returns a boolean value for CHECK.

CREATE TABLE contexts (
  id INT PRIMARY KEY,
  name VARCHAR NOT NULL,
  short_name VARCHAR NOT NULL,
  context_type VARCHAR REFERENCES context_types ON DELETE RESTRICT,
  context_status VARCHAR REFERENCES context_statuses ON DELETE RESTRICT,
  parent_id INT REFERENCES contexts ON DELETE CASCADE,

  CONSTRAINT CK_name CHECK (regexp_matches(name, '[a-zA-Z0-9]*')),
  CONSTRAINT CK_short_name CHECK (regexp_matches(short_name, '[a-zA-Z0-9]*'))
);

.

[SOLVED] As the answer I have shows that the best way to use regex with CHECK is to use the Regular Expression Match Operators ( mentioned in this documentation -- see section 9.7.3)

max · Accepted Answer · 2016-03-06 02:43:43Z

12

I suppose you want just use regex match operator ~ without returning captured group. So your query would be like this:

CREATE TABLE contexts (
  id INT PRIMARY KEY,
  name VARCHAR NOT NULL,
  short_name VARCHAR NOT NULL,
  context_type VARCHAR REFERENCES context_types ON DELETE RESTRICT,
  context_status VARCHAR REFERENCES context_statuses ON DELETE RESTRICT,
  parent_id INT REFERENCES contexts ON DELETE CASCADE,

  CONSTRAINT CK_name CHECK (name ~ '^[a-zA-Z0-9]*$'),
  CONSTRAINT CK_short_name CHECK (short_name ~ '^[a-zA-Z0-9]*$')
);

I have added ^$ to your regular expression as a whole line value should meet the regular expressions.

answered Mar 6, 2016 at 2:43

max

2,81725 silver badges21 bronze badges

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

Collectives™ on Stack Overflow

How to use regex with PostgreSQL to Constrain a Columns input as alpha-numeric [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related