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

On 06/12/2015 10:11 AM, David G. Johnston wrote:
> On Fri, Jun 12, 2015 at 12:57 PM, Adrian Klaver
> <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>>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(dot)klaver(at)aklaver(dot)com