How do I select a postgres Many-to-One relationship as a single row?

Question

I have a many-to-one relationship between Animals and their attributes. Because different Animals have different attributes, I want to be able to select all animals with their attribute name as a column header and NULL values where that animal does not have that attribute.

Like so...

TABLE_ANIMALS
ID  | ANIMAL      | DATE          | MORE COLS....
1   | CAT         | 2012-01-10    | ....
2   | DOG         | 2012-01-10    | ....
3   | FROG        | 2012-01-10    | ....
...

TABLE_ATTRIBUTES
ID  | ANIMAL_ID | ATTRIBUE_NAME     | ATTRIBUTE_VALUE
1   | 1         | noise             | meow
2   | 1         | legs              | 4
3   | 1         | has_fur           | TRUE
4   | 2         | noise             | woof
5   | 2         | legs              | 4
6   | 3         | noise             | croak
7   | 3         | legs              | 2
8   | 3         | has_fur           | FALSE
...

QUERY RESULT
ID  | ANIMAL    | NOISE   | LEGS  | HAS_FUR 
1   | CAT       | meow    | 4     | TRUE
2   | DOG       | woof    | 4     | NULL
3   | FROG      | croak   | 2     | FALSE

How would I do this? To reiterate, it's important that all the columns are there even if one Animal doesn't have that attribute, such as "DOG" and "HAS_FUR" in this example. If it doesn't have the attribute, it should just be null.

score 0 · Accepted Answer · answered May 22 '19 at 20:48

How about a simple join, aggregation and group by?

create table table_animals(id int, animal varchar(10), date date);
create table table_attributes(id varchar(10), animal_id int, attribute_name varchar(10), attribute_value varchar(10));

insert into table_animals values (1, 'CAT',  '2012-01-10'),
                                 (2, 'DOG',  '2012-01-10'),
                                 (3, 'FROG',  '2012-01-10');

insert into table_attributes values (1, 1, 'noise', 'meow'),
                                    (2, 1, 'legs', 4),
                                    (3, 1, 'has_fur', TRUE),
                                    (4, 2, 'noise', 'woof'),
                                    (5, 2, 'legs', 4),
                                    (6, 3, 'noise', 'croak'),
                                    (7, 3, 'legs', 2),
                                    (8, 3, 'has_fur', FALSE);

select ta.animal,
       max(attribute_value) filter (where attribute_name = 'noise') as noise,
       max(attribute_value) filter (where attribute_name = 'legs') as legs,
       max(attribute_value) filter (where attribute_name = 'has_fur') as has_fur
from table_animals ta
left join table_attributes tat on tat.animal_id = ta.id
group by ta.animal

Here's a rextester sample

Additionally you can change the aggregation to MAX CASE WHEN... but MAX FILTER WHERE has better performance.

Worked like a charm, thanks! – superdee May 22 '19 at 20:52 — superdee, May 22 '19 at 20:52

How do I select a postgres Many-to-One relationship as a single row?

1 Answers1