given is the following database scheme and I am wondering how to build the appropriate query on a Postgres system.

It is defined that Items can have multiple (1 -> n) Attributes which relation is specified within the Relation table. This relation table also stores a value related to this connection. Also items and attributes can be added or removed any time, but the relation table will always only contain valid item and attribute ids.

id (primary key), name

id (primary key), name

Item.id, Attribute.id, value    

Now my task is to return a (sparse) table where items are shown as rows and where each attribute is given as a column. As an example following table shows the Attributes (A, B, C, and D) and the Items (a, b, c, and d). The entries relate to the value in the Item_Attribute_Relation. It is possible that an attribute (column) is NULL for every item and that an item has no attributes at all.

   | A | B | C | D |
 a | x | x | y |   |
 b | x | z |   |   |
 c | w |   |   |   |
 d |   |   |   |   |

Unfortunately my SQL knowledge is rusty and Postgres does not support PIVOT to my knowledge. Also I am not able (probably due to missing knowledge) to dynamically generate the columns when using the Postgres crosstab() function. My gut tells me that this is a common problem. Therefore help by providing the correct wording or by posting links to this problem explanations is also welcome.


  • What about returning the attribute/values in a single JSONB column instead? That would make things a lot easier – a_horse_with_no_name Mar 15 at 13:03
  • Oh, have not been aware of Postgres Json capabilites, yet :) Indeed, a row_to_json(item_attribute_relation) where I can merge all Json objects which have the same item_id would fulfill my current use case. – j0j0j0 Mar 15 at 14:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.