Search code examples
pivotamazon-redshiftcrosstab

Tablefunc in Redshift to pivot table not supported?

I am trying to pivot a table in Redshift. This answer is instructive: https://stackoverflow.com/a/11751905/3063339 However, when I run the

CREATE EXTENSION tablefunc;

command I get the error:

syntax error at or near "EXTENSION"

Does redShift not support the syntax above, or tablefunc, or neither? If so, are there any RedShift functions that could act as a workaround? I am aware a table can be pivoted with basic postgresql commands SUM(CASE ...) etc. These do not scale well for large tables though. Many thanks.

Solution

  • CREATE EXTENSION was introduced with Postgres 9.1.
    Amazon Redshift is based on PostgreSQL 8.0.2. Only selected features from later versions were implemented.

    I does not seem like Redshift supports the extension tablefunc at all. You'll have to use CASE statements to emulate the functionality. Here is a related post on the AWS (that you already found in your comment).