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.