Cannot cast varbinary to varchar in presto

user_5798109 0
Viewed 214

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?

presto mysql sql
1 Answers

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
user_25012790
Related