Re: Suggested (or existing) way to parse currency into numeric?

On 06/12/2015 09:46 AM, David G. Johnston wrote:

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)