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

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.

Simon · Accepted Answer · 2019-05-22 20:48:19Z

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.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related