'Cannot cast varbinary to varchar in presto

I have a column which is in varbinary. I tried

select cast(msg as varchar) 
from table 
WHERE DATE(datestr) > CURRENT_DATE - INTERVAL '7' DAY

which threw

Cannot cast row(row1 varbinary) to varchar

and when I try

from table 
WHERE DATE(datestr) > CURRENT_DATE - INTERVAL '7' DAY

I get

Unexpected parameters (row(row1 varbinary)) for function from_utf8. Expected: from_utf8(varbinary, varchar(x)) , from_utf8(varbinary, bigint) , from_utf8(varbinary) "

I also tried,

select from_utf8(msg) 
from table 
WHERE DATE(datestr) > CURRENT_DATE - INTERVAL '7' DAY

which threw

Unexpected parameters (row(row1 varbinary)) for function from_utf8. Expected: from_utf8(varbinary, varchar(x)) , from_utf8(varbinary, bigint) , from_utf8(varbinary) "

I have tried several things online but doesn't have seemed to help. Can anyone help me out?

mysqlsqlpresto


Solution 1:[1]

Based on exception - msg column is not a varbinary but rather a ROW with one varbinary field named row1. You can access it with field reference operator . via name (msg.row1):

-- sample data
WITH dataset (msg, id) AS (
    VALUES (cast(row (to_utf8(cast(now() as varchar))) as row(row1 varbinary)), 1)
)

-- query
select from_utf8(msg.row1) 
from dataset

Output:

_col0
2022-05-14 14:09:30.391 UTC

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Guru Stron

Related Questions