Is there a sane way of storing int, float and boolean values in the same column in postgres?
If have something like that:

Currently I'm using jsonb to store it, the problem however now is, that I can't filter in the table with for instance the greater operator.

The query SELECT * FROM points WHERE value > 0; gives back the error: ERROR: operator does not exist: jsonb > integer: No operator matches the given name and argument types. You might need to add explicit type casts.

For me it's okay to handle boolean as 1 or 0 in case of true or false. Is there any possibility to achieve that with jsonb or is there maybe another super type which lets me use a column which is able to use all three types?

Performance is not so much of a concern here, as I'm going to have very little records inside of that table, max 5k I guess.

Thanks in advance.

CodePudding user response：

If you were just storing integers and floats, normally you'd use a float or numeric column.

But there's that pesky true.

You could cast the JSON...

select *
from test
where value::float > 1;

...but there's that pesky true.

You have to convert the boolean to a number to make it work.

select *
from test
where
  (case when value = 'true' then 1.0 when value = 'false' then 0.0 else value::float end) >= 1;

Or ignore it.

This having to work around the type system suggests that value is actually two or even three different fields crammed into one. Consider separating them into multiple columns.

CodePudding user response：

You should skip the rows where value is not number and cast the value to numeric, e.g.:

with points(id, value) as (
values
    (1, 'true'::jsonb),
    (2, '2'),
    (3, '43.2')
)

select *
from points
where jsonb_typeof(value) = 'number'
and value::text::numeric > 0;

 id | value
---- -------
  2 | 2
  3 | 43.2
(2 rows)