Postgres: Update column with array, whose values are taken from lookup table

Question

I have a table with columns: email1, email2, ... email5, domain_freqs and a function get_domain: get_domain([email protected]) = gmail.com,

I have a lookup table which has the frequencies of all the email domains occurring in all emails in the database:

word         nentry
gmail.com    130879
hotmail.com  12981
...

I want to update the domain_freqs column to store an array of values pulled from the lookup table.

For example, if I have a record with:

email1 = [email protected] 
email2 = [email protected]

then I want to store ARRAY[130879, 12981, NULL, NULL, NULL] in the domain_freqs column

Please edit your question and add the create table statements for the tables in question. Formatted text please, no screen shots — user330315
– user330315, Commented Mar 6, 2018 at 15:40

swami · Accepted Answer · 2018-03-06 15:37:32Z

1

Figured it out. This took approx 6 mins for a database of around 1m contact records. If anyone knows a more efficient way of doing this...

update emails e
set domain_freqs = (    
    select array_agg(nentry) as dom_freqs from (
        select * from (
            select *, unnest(ARRAY[
              get_domain(email1),
              get_domain(email2),
              get_domain(email3),
              get_domain(email4),
              get_domain(email5)
            ]) as dom from emails e
        ) sub, LATERAL (
          select nentry from lookup_table lt 
            where word = dom
        ) lat
    ) agg group by id having e.id = agg.id
);

answered Mar 6, 2018 at 15:37

swami

7331 gold badge10 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres: Update column with array, whose values are taken from lookup table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related