Presto SQL filter by data type in where clause-CodePudding

I have a column, which should be VARCHAR; I need to cast these as doubles. Some values, however, are Booleans and trigger the error Cannot cast false to DOUBLE. How do I prevent this is the WHERE cause?

What's the easiest way to accomplish the below in presto?

...
WHERE Type(col) != BOOL

...
WHERE type(col) = VARCHAR

CodePudding user response：

You can use try_cast and filter out nulls:

-- sample data1
WITH dataset (column) AS (
    VALUES ('1'),
        ('not a double')
) 
--query
select * 
from (
        select try_cast(column as double) as column
        from dataset
    )
where column is not null

Output:

column
1.0

Or use it in where (where try_cast(...) is not null)