From: | Mark Steben <mark(dot)steben(at)drivedominion(dot)com> |
---|---|
To: | pgsql-admin <pgsql-admin(at)postgresql(dot)org> |
Subject: | extracting the domain from an email address |
Date: | 2019-01-16 13:20:17 |
Message-ID: | CADyzmyx0hx6W3WL9ew7D2i404M=R0DDOLgdv7MhdMVPB-0-OgA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
Good morning,
I am searching for a better more efficient way to extract the domain
portion of an email address. The two I have been using are very expensive.
The one extract I mostly use: substring(email from '@(.*)$')
also has an index on the email column which is the full email
address:
btree (email DESC) WHERE email::text =
"substring"(email::text, '@(.*)$'::text) AND length(email::text) > 0,
The other extract:
substr(e.email,(strpos(e.email, '@') + 1))
Currently has no index.
The referenced table has 72 million rows. The email column can be empty
(ergo the length = 0 check. I am running postgresql 9.4.
Any insights/observations welcome.
--
*Mark Steben*
Database Administrator
@utoRevenue <http://www.autorevenue.com/> | Autobase
<http://www.autobase.net/>
CRM division of Dominion Dealer Solutions
95D Ashley Ave.
West Springfield, MA 01089
t: 413.327-3045
f: 413.383-9567
www.fb.com/DominionDealerSolutions
www.twitter.com/DominionDealer
www.drivedominion.com <http://www.autorevenue.com/>
From | Date | Subject | |
---|---|---|---|
Next Message | Achilleas Mantzios | 2019-01-16 13:47:12 | Re: extracting the domain from an email address |
Previous Message | Morris de Oryx | 2019-01-16 11:29:44 | Re: How to set default owner of objects in Postgresql |