The emailaddr extension in PostgreSQL provides a specialized data type for storing and validating email addresses. This extension ensures proper email formatting and allows efficient querying. Your Nile database arrives with emailaddr extension already installed and enabled.

​
Creating and Populating contacts Table

Before using emailaddr, let’s create a sample table to store email addresses of key contacts: 
CREATE TABLE contacts (
    tenant_id UUID,
    name TEXT,
    email EMAILADDR,
    PRIMARY KEY (tenant_id, email)
);

-- Create a tenant first
INSERT INTO tenants (id, name) VALUES 
    ('11111111-1111-1111-1111-111111111111', 'Example Corp');

INSERT INTO contacts (tenant_id, name, email) VALUES
    ('11111111-1111-1111-1111-111111111111', 'Alice', 'alice@example.com'),
    ('11111111-1111-1111-1111-111111111111', 'Bob', 'bob@example.net'),
    ('11111111-1111-1111-1111-111111111111', 'Charlie', 'charlie@example.org');

​
Querying Email Addresses

Since EMAILADDR is a specialized type, queries can be performed as with standard text columns: 
SELECT * FROM contacts WHERE email = 'alice@example.com';

​
Validating Email Format

The emailaddr type ensures that only valid email addresses are stored. For example, the following insertion will fail: 
INSERT INTO contacts (tenant_id, name, email) VALUES ('11111111-1111-1111-1111-111111111111','Invalid User', 'not_an_email');
This will result in an error because 'not_an_email' is not a properly formatted email address.

​
Limitations

  • EMAILADDR ensures correct email formatting but does not verify if the email exists.
  • Cannot store internationalized email addresses using Unicode.

​
Removing an Index

If you need to remove the index: 
DROP INDEX email_idx;

​
Conclusion

The emailaddr extension in PostgreSQL simplifies email validation and storage while ensuring efficient indexing for lookup queries. For more details, refer to the official PostgreSQL documentation or community resources.