TIL — Postgres user-defined data types with DOMAINs

We can create a user-defined data type with constraints using DOMAIN. We will use citext here, so the email field is case-insensitive.

-- Install citext extension
CREATE EXTENSION IF NOT EXISTS "citext";

-- Create domain with constraints
CREATE DOMAIN "email" AS citext
  CHECK (VALUE ~ '(([^<>()\[\]\\.,;:\s@"]+(\.[^<>()\[\]\\.,;:\s@"]+)*)|(".+"))@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}])|(([a-zA-Z\-0-9]+\.)+[a-zA-Z]{2,}))');

-- Example table
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email NOT NULL email -- Use email domain datatype
);

Usage:

-- Selecting data will lowercase since "email" is of type citext
SELECT 'ABC@foobar.cOm'::email; 

-- Result
abc@foobar.com

-- Insert data
INSERT INTO users (email) VALUES ('foobar');

-- Result
Error: value for domain email violates check constraint