CODEWITHSUNDEEP
rubypostgresqllistnumbersstorage
Ferenc Deak
Ferenc Deak

Reputation: 35408

How to store a list of small numbers in Postgres

I have a long list of small numbers, all of them < 16 but there can be more than 10000 of them in a unique list.

I get the values as a comma separated list, like:

6,12,10,2,2,2,6,12,8,2,2,6,10,2,4,12,14,10,2, .... lots and lots of numbers

And finally I need to store the values in a database in the most efficient way in order to be read back and processed again ... as a string, comma separated values.

I was thinking of sort of storing them in a big TEXT field ... however I find that adding all the commas in there would be a waste of space.

I am wondering if there is any best practice for this scenario.

For more technical details:

for Database I have to use Postgres (and I am sort of beginner in this field), and the programming language is Ruby (also beginner :) )

Upvotes: 0

Views: 288

Answers (2)

Eric Duminil
Eric Duminil

Reputation: 54233

For a fast and reasonably space efficient solution, you could simply write a hexadecimal string :

string = '6,12,10,2,2,2,6,12,8,2,2,6,10,2,4,12,14,10,2'

p string.split(',').map { |v| v.to_i.to_s(16) }.join
# "6ca2226c8226a24cea2"
p '6ca2226c8226a24cea2'.each_char.map { |c| c.to_i(16) }.join(',')
# "6,12,10,2,2,2,6,12,8,2,2,6,10,2,4,12,14,10,2"

It brings the advantage of being easily readable by any DB and any program.

Also, it works even if there are leading 0s in the string : "0,0,6".

If you have an even number of elements, you could pack 2 hexa characters into one byte, to divide the string length by 2.

Upvotes: 2

Aleksei Matiushkin
Aleksei Matiushkin

Reputation: 121000

numbers = "6,12,10,2,2,2,6,12,8,2,2,6,10,2,4,12,14,10,2"

numbers.split(',')
       .map { |n| n.to_i.to_s(2).rjust(4, '0') }
       .join
       .to_i(2)
       .to_s(36)
#⇒ "57ymwcgbl1umt2a"

"57ymwcgbl1umt2a".to_i(36)
                 .to_s(2)
                 .tap { |e| e.prepend('0') until (e.length % 4).zero? }
                 .scan(/.{4}/)
                 .map { |e| e.to_i(2).to_s }
                 .join(',')
#⇒ "6,12,10,2,2,2,6,12,8,2,2,6,10,2,4,12,14,10,2"

Upvotes: 1

Related Questions