Vol. I · A Postgres Handbook

The Query & the Schema

A reading-room reference for writing PostgreSQL by hand — selects to window functions, with the schema in plain view.

EasyString Functions

Extract domain from email addresses

The query

SQL
SELECT
  email,
  SPLIT_PART(email, '@', 2) AS domain,
  SPLIT_PART(SPLIT_PART(email, '@', 2), '.', -1) AS tld
FROM customers
WHERE email IS NOT NULL;
Tested against PostgreSQL 16

Note

“SPLIT_PART with -1 as position returns last segment (PostgreSQL 14+). For older versions, use multiple SPLIT_PARTs.

Tables referenced