Skip to content

Instantly share code, notes, and snippets.

@Basje

Basje/postgres_emailaddress_domain.sql

Last active July 5, 2017 12:49
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save Basje/656f3a8eccedec20fbf56c0bb42f40db to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save Basje/656f3a8eccedec20fbf56c0bb42f40db to your computer and use it in GitHub Desktop.
Download ZIP
Simple e-mailaddress domain for Postgres
Raw
postgres_emailaddress_domain.sql
CREATE DOMAIN public."emailAddress" AS public.citext;
ALTER DOMAIN public."emailAddress" OWNER TO some_owner;
ALTER DOMAIN public."emailAddress" ADD CONSTRAINT emailaddress_must_not_exceed_maximum_length CHECK( char_length( VALUE ) < 255 );
ALTER DOMAIN public."emailAddress" ADD CONSTRAINT emailaddress_must_contain_exactly_one_at CHECK( VALUE ~ '^[^@]*@[^@]*$' );
ALTER DOMAIN public."emailAddress" ADD CONSTRAINT emailaddress_domain_must_contain_at_least_one_dot CHECK( VALUE ~ '@.*\.' );
ALTER DOMAIN public."emailAddress" ADD CONSTRAINT emailaddress_domain_must_not_end_with_dot CHECK( VALUE ~ '@.*[^\.]$' );
ALTER DOMAIN public."emailAddress" ADD CONSTRAINT emailaddress_domain_must_not_start_with_dot CHECK( VALUE ~ '@[^\.].*$' );
ALTER DOMAIN public."emailAddress" ADD CONSTRAINT emailaddress_local_part_must_not_be_empty CHECK( VALUE ~ '.+@' );
ALTER DOMAIN public."emailAddress" ADD CONSTRAINT emailaddress_local_part_must_not_end_with_dot CHECK( POSITION('.@' in VALUE) = 0 );
ALTER DOMAIN public."emailAddress" ADD CONSTRAINT emailaddress_local_part_must_not_start_with_dot CHECK( VALUE ~ '^[^\.].*@' );
ALTER DOMAIN public."emailAddress" ADD CONSTRAINT emailaddress_must_not_contain_dot_sequences CHECK( POSITION('..' in VALUE) = 0 );
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment