Is there an easy way to convert name:value children to columns in postgresql?

Question

I have a schema where the EMP table has a child table EMP_ATTRIBUTE which is name value pairs, thus...

emp: id, name
emp_attribute:  id, emp_id, name, value 

A typical emp (id=1, name=Juan) will have child attributes title=manager, education=degree, nationality=spain, etc etc

Is there a query I can run which combines all of the emp_attribute rows into a single tuple

id   |    name     |   title     |   education    |    nationality
1    |    Juan     |   manager   |   degree       |    spain

By way of background, the reason I have taken this approach is that the list of attributes is constantly changing and is different for different categories of employee. I don't want to have to make a schema change to support new attributes. I did consider a JSON datatype, but I would prefer to remain as relational as possible.

Answer 1

You can do join the tables, and do conditional aggregation:

select
    e.id
    max(ea.value) filter(where ea.name = 'name') name,
    max(ea.value) filter(where ea.name = 'title') title,
    max(ea.value) filter(where ea.name = 'education') education,
    max(ea.value) filter(where ea.name = 'nationality') nationality
from emp e
inner join emp_attribute ea on ea.emp_id = e.id
group by e.id