I have a table called device_info that looks like below (only a sample provided)
| device_ip | cpu | memory |
|---|---|---|
| 100.33.1.0 | 10.0 | 29.33 |
| 110.35.58.2 | 3.0, 2.0 | 20.47 |
| 220.17.58.3 | 4.0, 3.0 | 23.17 |
| 30.13.18.8 | -1 | 26.47 |
| 70.65.18.10 | -1 | 20.47 |
| 10.25.98.11 | 5.0, 7.0 | 19.88 |
| 12.15.38.10 | 7.0 | 22.45 |
Now I need to compare a number say 3 against the cpu column values and get the rows that are greater than that. Since the cpu column values are stored as a csv, I am not sure how to do the comparison.
I found there is a concept called string_to_array in Postgres which converts csv to array and accordingly tried the below query that didn't work out
select device_ip, cpu, memory
from device_info
where 3 > any(string_to_array(cpu, ',')::float[]);
What am I doing wrong?
Expected output
| device_ip | cpu | memory |
|---|---|---|
| 100.33.1.0 | 10.0 | 29.33 |
| 220.17.58.3 | 4.0, 3.0 | 23.17 |
| 10.25.98.11 | 5.0, 7.0 | 19.88 |
| 12.15.38.10 | 7.0 | 22.45 |
CodePudding user response：
The statement as-is is saying "3 is greater than my array value". What I think you want is "3 is less than my array value".
Switch > to <.
select device_ip, cpu
from device_info
where 3 < any(string_to_array(cpu, ',')::float[]);