I'm trying to run a query as such
select * from mytable where to_number(minutesintext, '9999') >= 10;
expected result:
col1 | col2 | col3 | minutesintext
-------------------------------------
a | b | c | 11
f | g | e | 98
But I'm constantly hit with this error:
[22P02] ERROR: invalid input syntax for type numeric: " "
I've seen the example usage for to_number() have all been:
select to_number('45', '9999');
results in:
45
And cant seem to find examples where to_number() function is used as part of the conditional clause. What am I doing wrong? and how can I query all rows while converting integers (stored as text) into numbers during the query.
*Changing the data type of the column is out of the question as it needs to be stored as text.
I'm using PostgreSQL 13.1 on my local and PostgreSQL 11 on remote environment
Any help is appreciated! :)
Generally this works: demo:db<>fiddle
However, your error message says: It seems like you have a record where you have an empty string, which - naturally - cannot be interpreted as a number.
To avoid this, you could add some fixing logic, like adding NULLIF() function:
select * from mytable
where to_number(
NULLIF(minutesintext, ' '), '9999'
) >= 10;
If you don't need to parse a specific number format, you can simply cast the (escaped) text into type int:
select * from mytable
where NULLIF(minutesintext, ' ')::int >= 10;