Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE dish_types (
  id  serial primary key,
  name varchar
);

CREATE TABLE dishes (
  id  serial primary key,
  type bigint,
  foreign key (type) references dish_types(id)
);

CREATE TABLE attributes (
  id serial primary key,
  name varchar,
  unit varchar
);

create table dish_attributes (
  dish_id bigint,
  attr_id bigint,
  foreign key(dish_id) references dishes(id),
  foreign key(attr_id) references attributes(id),
  value float
);

INSERT INTO dish_types (id, name) values 
  (1, 'bowl'),
  (2, 'plate'),
  (3, 'glass');

INSERT INTO attributes (id, name, unit) values 
  (1, 'diameter', 'mm'), 
  (2, 'height', 'mm'), 
  (3, 'volume', 'ml');

INSERT INTO dishes (id, type) VALUES 
(1, 1), (2, 2), (3, 3);

INSERT INTO dish_attributes(dish_id, attr_id, value) values 
(1, 1, 10),
(1, 3, 750),
(2, 1, 8.5), 
(3, 3, 250);

SELECT dish_types.name,  
  string_agg(concat(
    attributes.name, ' ',
    dish_attributes.value, ' ',
    attributes.unit),
    ', '
  ) AS attributes
FROM dishes 
JOIN dish_types ON dishes.type = dish_types.id
JOIN dish_attributes ON dish_attributes.dish_id = dishes.id 
JOIN attributes ON dish_attributes.attr_id = attributes.id 
GROUP BY dishes.id, dish_types.name ;

CREATE TABLE

CREATE TABLE

CREATE TABLE

CREATE TABLE

INSERT 0 3

INSERT 0 3

INSERT 0 3

INSERT 0 4

name	attributes
bowl	diameter 10 mm, volume 750 ml
plate	diameter 8.5 mm
glass	volume 250 ml

SELECT 3