test=> CREATE TABLE t ( field money NULL );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?
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.
Thank you for the reminder.
David J.
