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

From: "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Suggested (or existing) way to parse currency into numeric?
Date: 2015-06-12 16:46:30
Message-ID: CAKFQuwbDr3VfgpQKBKM7PNPUNuBLKf_JhFWJAAkhT1rgeeun-g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2015-06-12 16:57:28 Re: Suggested (or existing) way to parse currency into numeric?
Previous Message Adrian Klaver 2015-06-12 16:45:58 Re: cached row type not invalidated after DDL change