How to allow only alpha-numeric field value in postgresql [duplicate]

Question

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)

score 9 · Accepted Answer · 2022-08-01 09:28:15Z

9

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

edited Aug 1, 2022 at 9:28

answered Sep 17, 2015 at 16:31

user330315

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

5 Comments

Gallaecio Over a year ago

Note: You are missing a closing parenthesis.

Dustin Kane Over a year ago

This no longer works in Postgres 10+ because "set-returning functions are not allowed in check constraints"

user330315 Over a year ago

@DustinKane: good point. regexp_matches wasn't a good idea to begin with.

hraban Over a year ago

Be careful with ranges in PG regex. They depend on collation, so e.g. they're normally case insensitive, and you might think '[A-Z]' only matches an uppercase character, but it matches lowercase all the same. From the docs: "Ranges are very collating-sequence-dependent, so portable programs should avoid relying on them." postgresql.org/docs/current/functions-matching.html

user2959071 Over a year ago

The $ and the ^ need to swap positions.

Collectives™ on Stack Overflow

How to allow only alpha-numeric field value in postgresql [duplicate]

1 Answer 1

5 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Linked

Related