{"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":"<p>If you are trying to build pivot tables in Postgres, you may find examples online that incorrectly push you towards using the &#8220;ROW&#8221; type to build the pivot table. You might try something like the following (which will not work):<\/p>\n<pre lang=sql\">\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<p>Unfortunately, the above example won&#8217;t actually work &#8211; you can&#8217;t cast the row to event_counts in this case (tested in 9.3). If you use an anonymous row type, it won&#8217;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 &#8220;answers&#8221; blaming the asker.<\/p>\n<p>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<pre lang=\"sql\">\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":"<p>If you are trying to build pivot tables in Postgres, you may find examples online that incorrectly push you towards using the &#8220;ROW&#8221; 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,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[4],"tags":[435,437,461,523],"aioseo_notices":[],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/posts\/3214"}],"collection":[{"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/comments?post=3214"}],"version-history":[{"count":0,"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/posts\/3214\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/media?parent=3214"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/categories?post=3214"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/tags?post=3214"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}