Returning a specific value if a joined row doesn't exist in a SELECT statement using PostgreSQL

Question

I have two PostgreSQL tables containing:

some objects
some data about the objects with the type of data

For example:

    |---ID---|---NAME---|
    |   1    |  name1   |
    |   2    |  name2   |
    |   3    |  name3   |

    |---ID_OBJ---|---TYPE---|---VALUE---|
    |      1     |  type_1  |    val1   | 
    |      1     |  type_2  |    val2   | 
    |      2     |  type_1  |    val3   | 
    |      3     |  type_2  |    val4   |

I want to obtain all the information about each objects on one line. The problem is that I want the values of the nonexistent rows too :

    |---NAME---|---TYPE1---|---TYPE2---|
    |  name1   |    val1   |    val1   |
    |  name2   |    val3   |     NA    |
    |  name3   |     NA    |    val4   |

I don't really understand how to proceed to get this unexistant relationships. Is the EXISTS keyword a solution?

Edit: I can't activate the tablefunc extension because I don't manage the server myself so the solution explain here can't be easily used.

Possible duplicate of [PostgreSQL Crosstab Query](http://stackoverflow.com/questions/3002499/postgresql-crosstab-query) — Juan Carlos Oropeza, Feb 24 '16 at 15:17
The problem is that the database was designed using the EAV pattern, which is almost always a mistake. Now you can see just a small part of the reason why. — Tom H, Feb 24 '16 at 15:21

score 2 · Accepted Answer · answered Feb 24 '16 at 15:20

Although Postgres supports crosstab, I think this is easily done using conditional aggregation if you know what columns you want:

select t1.name,
       coalesce(max(case when type = 'type_1' then value end), 'NA') as type_1,
       coalesce(max(case when type = 'type_2' then value end), 'NA') as type_2
from t1 left join
     t2
     on t1.id = t2.id_obj
group by t1.name;

Returning a specific value if a joined row doesn't exist in a SELECT statement using PostgreSQL

1 Answers1