Store int, float and boolean in same database column

Question

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.

If you want to use >, how is that supposed to work with true? Is this really one field or two or even three? — Schwern
– Schwern, Commented Sep 30, 2022 at 18:27
Without seeing examples of value there is not really much that can be done in way of an answer. Also there is no explanation as to why you want to mix types in a column? To me that is a sign of poor design. — Adrian Klaver
– Adrian Klaver, Commented Sep 30, 2022 at 19:38

Schwern · Accepted Answer · 2022-09-30 18:36:31Z

2

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.

answered Sep 30, 2022 at 18:36

Schwern

167k28 gold badges225 silver badges370 bronze badges

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

Comments

klin · Accepted Answer · 2022-09-30 17:58:28Z

1

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)

answered Sep 30, 2022 at 17:58

klin

123k15 gold badges240 silver badges262 bronze badges

Comments

cmdjulian · Accepted Answer · 2022-10-03 20:13:48Z

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.

Collectives™ on Stack Overflow

Store int, float and boolean in same database column

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related