Question 114384 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Store int, float and boolean in same database column

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

rid time value
2d9c5bdc-dfc5-4ce5-888f-59d06b5065d0 2021-01-01 00:00:10.000000 +00:00 true
039264ad-af42-43a0-806b-294c878827fe 2020-01-03 10:00:00.000000 +00:00 2
b3b1f808-d3c3-4b6a-8fe6-c9f5af61d517 2021-01-01 00:00:10.000000 +00:00 43.2

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 that is able to use all three types?

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

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    I actually found out, regardless of the jsonb fields value, that you can compare it to other jsonb in postgres. That means, I can for instance do the following:

    SELECT *
FROM points
WHERE val > '5'

    This correctly gives me back only the third row. It just ignores the bool value. To filter for a certain bool I can achieve that with the following query:

    SELECT *
FROM points
WHERE val = 'true'

    This is good enough for me. I even could hold timestamps in the json column and compare them using this methodology.
    Another way of solving the problem after all your comments seem to be to make the column a numeric. This would work as well, but requires more client side conversion, as I would have to have a second type column, remembering what the actual type is. This type should than be used on the client side to convert the value back into its og value. For integers its trivial, for booleans like @schwern suggested, one can use 1 and 0, for dates, one could use the unix timestamp representation.

    When I now want to search for a certain value, the type has to be contained in the where clause as well.


  2. 0

    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)
    Login or Signup to reply.
  3. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search