Version 9.3 CREATE TABLE t ( field numeric NULL ); SELECT * FROM json_populate_record(null::t, '{ "field": "$18,665" }'::json); Error: invalid input syntax for type numeric: "$18,665"
I can accept the type of field being something like "numeric_cleaned" which has a custom input function that would strip away the symbols and commas (not too concerned about locale at the moment...) and am pondering writing my own custom type with supporting SQL function to accomplish that but I'm hoping the community can point me to something already existing.
I really want to avoid going through a staging table. I'm more inclined to brute force the source JSON using "jq" (or sed) before I would go that route.
Thoughts, suggestions, comments?
test=> CREATE TABLE t ( field money NULL ); CREATE TABLE test=> SELECT * FROM json_populate_record(null::t, '{ "field": "$18,665" }'::json); field ------------ $18,665.00 (1 row)
I wrote that type off as something I would never code into my own schema so basically forgot about its usability in other situations.
