2

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! :)

Syabster
  • 99
  • 2
  • 9
  • 2
    Generally this works: https://dbfiddle.uk/?rdbms=postgres_13&fiddle=2b9ae2e7f6c3008a916b0c8d34caa448 However, it seems like you have a record where you have an empty string, which - naturally - cannot be interpreted as a number – S-Man Mar 17 '21 at 14:50
  • Why not `select '45'::integer, nullif('','')::integer;`? – Adrian Klaver Mar 17 '21 at 14:55

1 Answers1

2

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:

demo:db<>fiddle

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;
S-Man
  • 22,521
  • 7
  • 40
  • 63