PostgreSQL populate a column based on other 2 columns

Question

I have the following table in Postgres 10.4.

Name   Email          EmailType 
--------------------------------------      
John   [email protected]   Primary Email   
John   [email protected]   Secondary Email
John   [email protected]  Work Email
Jack   [email protected]   Primary Email   
Jack   [email protected]   Secondary Email
Jim    [email protected]    Primary Email

How can I assign value in col4 based on the values in Name (assume that the name is UNIQUE in this table) and EmailType column. For every person (Name column), I want to check how many email types (EmailType column) they have and then sort of concatenate the person name and the email type into a new column. Something like below.

Name   Email          EmailType         Col4
-----------------------------------------------------------------------------------------
John   [email protected]   Primary Email     John: Primary Email, Secondary Email, Work Email
John   [email protected]   Secondary Email
John   [email protected]  Work Email
Jack   [email protected]   Primary Email     Jack: Primary Email, Secondary Email
Jack   [email protected]   Secondary Email
Jim    [email protected]    Primary Email     Jim: Primary Email

I thought of using a CASE statement, but unable to get it to work. Any help on this will be greatly appreciated.

Create a view. Or at least use triggers.

jarlh
– jarlh

2024-04-16 13:58:38 +00:00
Commented Apr 16, 2024 at 13:58 — jarlh
– jarlh, Commented Apr 16, 2024 at 13:58

Stefanov.sm · Accepted Answer · 2024-04-16 14:26:11Z

1

You can do this using window functions and conditional string aggregation.

select name, email, emailtype, 
 case when rn=1 then string_agg(emailtype,', ') over (partition by name) end col4
from
(
 select *, row_number() over (partition by name order by emailtype) rn
 from the_table
) t;

DB Fiddle

And - as @jarlh commented - create a view using the query.

answered Apr 16, 2024 at 14:26

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

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

3 Comments

Vivek Kumar Singh Over a year ago

Your solution works great. But I am trying to achieve this in Adobe's query service platform which does not support the string_agg function. [Here](experienceleague.adobe.com/en/docs/experience-platform/query/… are the list of functions which it supports. I was wondering if there is any other alternative to string_agg.

Stefanov.sm Over a year ago

Maybe collect_list(expr)?

Vivek Kumar Singh Over a year ago

It worked. I was so absorbed in work, that I missed going through other functions. But thank you for pointing me in the right direction. Much appreciated.

Collectives™ on Stack Overflow

PostgreSQL populate a column based on other 2 columns

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related