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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.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.klaver@aklaver.com