List field with only one element casting problem-CodePudding

I have a column in postgresql table that is a list with only one element and this element is always an integer or null.

I am trying to use this field in a query like this:

select
sum(case when value = 1  then 1 else 0 end) as count_of1
sum(case when value = 2  then 1 else 0 end) as count_of2
from tbl

and returns: operator does not exist: text=integer but as mentioned above I cant't cast it to numeric for some unknown reasos.

I am trying to cast this field and I always get an error. Tried:

value::numeric,  
value::float, 
value::integer

and I always get an error of casting.

pg_typeof(value) ->>  'text'

CodePudding user response：

If the column is defined as text, then compare it to a text value:

sum(case when value = '1' then 1 else 0 end)

alternatively:

count(*) filter (where value = '1')

But value::integer should work if all values in that column can be cast to an integer if there is at least one row with a value that can't be converted, this will fail.