PostgreSQL populate a column based on other 2 columns

Question

I have the following table in Postgres 10.4.

Name   Email          EmailType 
--------------------------------------      
John   john@abc.com   Primary Email   
John   john@xyz.com   Secondary Email
John   john@work.com  Work Email
Jack   jack@abc.com   Primary Email   
Jack   jack@xyz.com   Secondary Email
Jim    jim@abc.com    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   john@abc.com   Primary Email     John: Primary Email, Secondary Email, Work Email
John   john@xyz.com   Secondary Email
John   john@work.com  Work Email
Jack   jack@abc.com   Primary Email     Jack: Primary Email, Secondary Email
Jack   jack@xyz.com   Secondary Email
Jim    jim@abc.com    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.

Answer 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.