This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing an article on how to validate the Email Address Column in PostgreSQL.
In 2016, I already shared a below article on this. This article is similar to the below old article, but here I added “citext” extension which for case-insensitive check.
If you don’t know about the DOMAIN DATA TYPE, visit below old article.
PostgreSQL: CREATE DOMAIN to Abstract Data Type and Enforce Business Rules
Check the below demonstration, and validate the email address column:
Create a new citext extension:
The citext module provides a case-insensitive character string type, citext. Essentially, it internally calls lower when comparing values. Otherwise, it behaves almost exactly like text.
|
1 |
CREATE EXTENSION citext; |
Create a domain data type for validating an email address:
|
1 2 3 4 |
CREATE DOMAIN domain_email AS citext CHECK( VALUE ~ '^\w+@[a-zA-Z_]+?\.[a-zA-Z]{2,3}$' ); |
Use domain_email and Cast/Compare valid email address:
|
1 2 |
SELECT 'anvesh@gmail.com'::domain_email; SELECT 'anvesh_08@dbrnd.org'::domain_email; |
Few invalid email addresses:
|
1 2 |
SELECT 'dba@aol.info'::domain_email; SELECT 'anvesh_08@08dbrnd.org'::domain_email; |
