On 06/12/2015 10:11 AM, David G. Johnston wrote:
On Fri, Jun 12, 2015 at 12:57 PM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>>wrote: 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) I wrote that type off as something I would never code into my own schema so basically forgot about its usability in other situations.
Though if you do not want to use the money type in a table you could do: test=> select '$18,665'::money::numeric; numeric ---------- 18665.00 (1 row)
Thank you for the reminder. David J.
-- Adrian Klaver adrian.klaver@xxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
