CODEWITHSUNDEEP
postgresql
sjain
sjain

Reputation: 23344

How to allow only alpha-numeric field value in postgresql

In SQL Server (T-SQL) we can do this like posted here:

http://www.sqlrelease.com/allow-only-alphanumeric-characters-in-a-column

How to do the same in PostgreSQL?

The following didn't worked -

CREATE OR REPLACE FUNCTION dummy.checkalphanumeric(username character varying(32))
  RETURNS integer AS
$BODY$
BEGIN
RETURN (SELECT CASE WHEN regexp_matches(username, '%[^a-zA-Z0-9]%') > 
false THEN false ELSE true END);
END; $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION dummy.checkalphanumeric(username character varying(32))
  OWNER TO postgres;

And the ALTER query -

ALTER TABLE dummy.provisioning_user ADD CONSTRAINT 
CK_provisioning_user CHECK (dummycheckalphanumeric(username) = 1)

Upvotes: 2

Views: 5617

Answers (1)

user330315
user330315

Reputation:

No need for your own function.

Also: % is not a wildcard for a regular expression. The reason why that is used in the example is that the example is for SQL Server which doesn't support regular expressions (but I fail to see why a separate function would be necessary in SQL Server)

ALTER TABLE dummy.provisioning_user 
   ADD CONSTRAINT CK_provisioning_user 
   CHECK (username ~ '^[a-zA-Z0-9]*$');

the constraint will check for values that only contain the characters (from a-z) and digits from 0-9. It will allow "nothing" though. If you don't want that you need to use '[a-zA-Z0-9]+' as the regex

Upvotes: 9

Related Questions