Text search on email domain with postgres

Question

I'd like to have a text search that can also retrieve results matching the domain name in an email address:

Say I have this table of tweets

create table tweets (
  id uuid primary key,
  tweet text not null,
)

and a seeding like

insert into tweets (id,tweet) values
(gen_random_uuid(), 'Hello friends! #example-tweet'),
(gen_random_uuid(), 'Reach me out [email protected]'),
(gen_random_uuid(), 'For example, I want you to finish this'),
(gen_random_uuid(), 'James is a bad guy #james'),
(gen_random_uuid(), 'Hahaha found the email: [email protected]');

the following query does NOT list the Reach me out [email protected] as result

SELECT tweet, ts_rank_cd(to_tsvector(t.tweet), query) AS rank
FROM tweets t, to_tsquery('example') query
WHERE query @@ to_tsvector(t.tweet)
ORDER BY rank DESC
LIMIT 10;

Can I make a text search on tweets returning also email domains or internal parts of a string?

Based on your example, it is not clear what you mean by 'domain'. Usually the domain would be 'example.com', not just 'example'. Do you mean to take only the first component of the domain, or is there some other rule? Also, your example has at most one email per value. Is that the rule? — jjanes
– jjanes, Commented Jul 6, 2023 at 20:16

jjanes · Accepted Answer · 2023-07-06 20:20:38Z

1

The default parser for FTS will yield emails addresses as a token type, but does not break them down to domains, as can be seen by doing select * from ts_debug('Reach me out [email protected]');

So doing what you want would require either writing a custom parser (not easy) or doing some kind of text processing of your own before, after, or instead of FTS processing. Maybe something like regexp_matches could be used for this, although it is not exactly clear what behavior you want.

answered Jul 6, 2023 at 20:20

jjanes

42.6k3 gold badges44 silver badges54 bronze badges

Add a comment |

Stack Exchange Network

Text search on email domain with postgres

1 Answer 1

Your Answer

Hot Network Questions

Text search on email domain with postgres

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions