The Ruby Craftsman

Postgres Regex Parsing
For some reason a Ruby array of BigDecimals was being inserted into a String column in our Portgres DB. With a lot of trying out different things and some help from another software developer and DBA we came up with a solution to parse out the numbers. The goal was to convert a string of ["83000.0", "119000.0"]
to an PG array {83000.0, 119000.0}
. It Take some regex, but it’s doable.
string_to_array(regexp_replace(["83000.0", "119000.0"], '[\[\]" ]', '', 'g'), ',')
string_to_array
--------------------
{83000.0,119000.0}
(1 row)
Breaking apart the parts:
Clean up string and remove square brackets.
regexp_replace(["83000.0", "119000.0"], '[\[\]" ]', '', 'g')
Split string into array by comma
string_to_array('"83000.0", "119000.0"'), ',')
Conclusion
I would’nt recommend this for use in production, but works great for some ad hoc querying.