Emailaddr

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.