Animals Sample Query, PostgreSQL

Language: Layout:

--PostgreSQL 9.6
--'\\' is a delimiter

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


absolute service time: 0,4 sec