How to compare a value against a column value containing csv in Postgres?

Question

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

it is never a good idea to store mutiple values as csv in one column. You should read about normalization — Jens
– Jens, Commented Sep 15, 2022 at 15:53
@Jens I agree. But the table is designed by somebody else and I just need to get some numbers out. But I will definitely pass on the advice to that guy. Thanks. — Souvik Ray
– Souvik Ray, Commented Sep 15, 2022 at 16:00
@Isolated this is it! Now I know where its going wrong. Great! — Souvik Ray
– Souvik Ray, Commented Sep 15, 2022 at 16:25

Isolated · Accepted Answer · 2022-09-15 16:22:14Z

1

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[]);

View on DB Fiddle

answered Sep 15, 2022 at 16:22

Isolated

6,4942 gold badges12 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Souvik Ray Over a year ago

thanks! < and > is where I screwed up. Great work!

Collectives™ on Stack Overflow

How to compare a value against a column value containing csv in Postgres?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related