{"id":3214,"date":"2016-03-05T16:53:56","date_gmt":"2016-03-05T16:53:56","guid":{"rendered":"http:\/\/www.garysieling.com\/blog\/?p=3214"},"modified":"2016-03-05T16:53:56","modified_gmt":"2016-03-05T16:53:56","slug":"accessing-values-in-anonymous-row-types-in-postgres","status":"publish","type":"post","link":"https:\/\/www.garysieling.com\/blog\/accessing-values-in-anonymous-row-types-in-postgres\/","title":{"rendered":"Accessing values in anonymous row types in Postgres"},"content":{"rendered":"

If you are trying to build pivot tables in Postgres, you may find examples online that incorrectly push you towards using the “ROW” type to build the pivot table. You might try something like the following (which will not work):<\/p>\n

\ncreate type event_counts as (event_type_1, event_type_2);\n\nselect \n  user,\n  event_count[0].event_type1,\n  event_count[0].event_type2\nfrom (\n  select \n    user, \n    array_agg(ROW(events)::event_counts order by event_type ASC) event_count\n  from ( \n    select user, event_type, count(*) events\n    from (\n      select 'Event type 1' event_type, 'gsieling' as user\n      union all\n      select 'Event type 2' event_type, 'gsieling' as user\n      union all \n      select 'Event type 1' event_type, 'otheruser' as user\n    ) b\n    group by 1, 2\n  ) a\n<\/pre>\n
Unfortunately, the above example won’t actually work – you can’t cast the row to event_counts in this case (tested in 9.3). If you use an anonymous row type, it won’t work at all (there seems to be no way to get the contents).  There are many confused stackoverflow posts on the subject, with the people providing “answers” blaming the asker.<\/p>\n
The correct way to fix this is to switch to using an array and skip the row type entirely, which you can access by index:<\/p>\n
\nselect \n  user,\n  event_count[1] event_1,\n  event_count[2] event_2\nfrom (\n  select \n    user, \n    array_agg(events order by event_type ASC) event_count\n  from ( \n    select user, event_type, count(*) events from (\n      select 'Event type 1' event_type, 'gsieling' as user\n      union all\n      select 'Event type 2' event_type, 'gsieling' as user\n      union all \n      select 'Event type 1' event_type, 'otheruser' as user\n    ) b\n    group by 1, 2\n  ) a\n) c\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
If you are trying to build pivot tables in Postgres, you may find examples online that incorrectly push you towards using the “ROW” type to build the pivot table. You might try something like the following (which will not work):<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"footnotes":""},"categories":[4],"tags":[435,437,461,523],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t