Thread: Suggested (or existing) way to parse currency into numeric?
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?
Thank You!
David J.
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)
>
> Thank You!
>
> David J.
>
--
Adrian Klaver
adrian.klaver@aklaver.com
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.
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
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)
Except I have to define the table with money so that json_populate_record uses the correct cast. But yes, when actually using the data, or if I decide to make a view on top of the table, I can cast from money to numeric.
I guess I could define a custom type using money and then define the table using numeric and transform in between; but its yet another definition to keep in sync.
Money perfectly meets my current need and no longer has my scorn.
David J.