pivotamazon-redshift

Redshift - Unsupported PIVOT column type: text

I had a look at this topic: ERROR: Unsupported PIVOT column type: text but unfortunately it didn't provide me with an answer.

I have a simple table that looks like the following:

user_id | type | reminder_type | sent_at
----------------------------------------------------
user_a  | MID  | REMINDER_1    | 2022-02-01 15:00:00
user_a  | MID  | REMINDER_2    | 2022-02-15 06:00:00

Then I try to perform this query:

SELECT
  *
FROM table
PIVOT (
  MIN(sent_at) FOR reminder_type IN('REMINDER_1', 'REMINDER_2')
)

In order to get the following result:

user_id | type | reminder_1          | reminder_2
----------------------------------------------------------
user_a  | MID  | 2022-02-01 15:00:00 | 2022-02-15 06:00:00

And it gives me the aforementioned error: enter image description here

Can't get my head wrapped around it and AWS documentation doesn't provide any details about the error

Solution

  • The column reminder_type was a result of REGEXP_REPLACE that resulted in type VARCHAR(101).

    Suddenly it worked when I explicitly cast the column to VARCHAR

    REGEXP_REPLACE(remin_type, '<regex>', '') AS reminder_type doesn't work

    REGEXP_REPLACE(remin_type, '<regex>', '')::VARCHAR AS reminder_type works perfectly