0

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.

Item
----
id (primary key), name

Attribute
---------
id (primary key), name

Item_Attribute_Relation
-----------------------
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.

Thanks!

  • 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.