Is there any way to specify a field as unsigned type?

There is one field in our database define as int unsigned, if we want to keep the same range (0-2^32-1) in CrateDB, the type should be bigint, integer(4bytes) type will lose some value. But bigint will waste a lot of space. Can we define unsigned int in CrateDB?

Hi @liu_yang

The space wasted by using bigint probably is less, than you expect, due to compression in the storage engine.
Have you tried it with real world data?

It is unlikely that we will soon support unsigned integer, as the underlying storage - at least as far as I know -doesn’t support unsigned number types either right know.

But we gladly take any feature request at https://github.com/crate/crate/issues

best regards
Georg

What will be recommended for 1 byte unsigned field (tinyint in mysql) in CrateDB, smallint or char?

Hi @proddata Since we have a lot of 1 byte unsigned field represented IoT flag (tinyint in mysql), if we use smallint in CrateDB, the storage space will be doubled(no idea about the compression ratio).

Can you share your planned table schema. How many fields would you want to store per column/doc?
Due to compression, it shouldn’t really matter too much.

example with 1000 values per row/doc (numbers from 0-99)

byte size is pretty similar

Got it. Thanks. The table schema is not ready yet. We have 124 fields per row/doc, 30 fields take 1 byte, 74 fields take 2 bytes, 20 fields take 4 bytes. Some fields store signed integer, some store unsigned integer.

1 Like