Casting to money
A pgAdmin user has just pointed out that the data editor doesn't work with money columns, apparently because it casts data when inserting/updating it. The docs for money say:
The money type stores a currency amount with a fixed fractional precision; see Table 8-3. Input is accepted in a variety of formats, including integer and floating-point literals, as well as "typical" currency formatting, such as '$1,000.00'. Output is generally in the latter form but depends on the locale.
Yet:
-- Executing query:
select 123.45::money
ERROR: cannot cast type numeric to money
SQL state: 42846
-- Executing query:
select $123.45::money
ERROR: syntax error at or near ".45"
SQL state: 42601
Character: 12
-- Executing query:
select '$123.45'::money
ERROR: invalid input syntax for type money: "$123.45"
SQL state: 22P02
-- Executing query:
select '£123.00'::money
ERROR: invalid input syntax for type money: "£123.00"
SQL state: 22P02
-- Executing query:
select '123.45'::money
Total query runtime: 0 ms.
[this one returns an empty value]
So my question is, how do we cast input to the money type? Or am I working too late again and being a plank?
Regards, Dave.
"Dave Page" <dpage@vale-housing.co.uk> writes:
select '$123.45'::money
ERROR: invalid input syntax for type money: "$123.45"
select '�123.00'::money
ERROR: invalid input syntax for type money: "�123.00"
So ... what locale are you trying this in?
regards, tom lane
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 09 October 2006 04:15
To: Dave Page
Cc: PostgreSQL Hackers
Subject: Re: [HACKERS] Casting to money"Dave Page" <dpage@vale-housing.co.uk> writes:
select '$123.45'::money
ERROR: invalid input syntax for type money: "$123.45"
select '£123.00'::money
ERROR: invalid input syntax for type money: "£123.00"So ... what locale are you trying this in?
Oh, sorry - English_United Kingdom.28591, on 8.1.4/win32.
Now I'm back at work I do see that
select '$123.45'::money
Works OK on 8.0.3/Slackware Linux in en_US.
Regards, Dave.
-----Original Message-----
From: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Dave Page
Sent: 09 October 2006 08:42
To: Tom Lane
Cc: PostgreSQL Hackers
Subject: Re: [HACKERS] Casting to money-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 09 October 2006 04:15
To: Dave Page
Cc: PostgreSQL Hackers
Subject: Re: [HACKERS] Casting to money"Dave Page" <dpage@vale-housing.co.uk> writes:
select '$123.45'::money
ERROR: invalid input syntax for type money: "$123.45"
select '£123.00'::money
ERROR: invalid input syntax for type money: "£123.00"So ... what locale are you trying this in?
Oh, sorry - English_United Kingdom.28591, on 8.1.4/win32.
Now I'm back at work I do see that
select '$123.45'::money
Works OK on 8.0.3/Slackware Linux in en_US.
As does
select '£123.44'::money
In en_GB on the same box, so I guess this is a windows issue.
/D
Tom Lane wrote:
"Dave Page" <dpage@vale-housing.co.uk> writes:
select '$123.45'::money
ERROR: invalid input syntax for type money: "$123.45"
select '�123.00'::money
ERROR: invalid input syntax for type money: "�123.00"So ... what locale are you trying this in?
I get the following from 8.2beta1 - looks like it doesn't like the
double quotes.
postgres=# select '123.45'::money;
money
---------
$123.45
(1 row)
postgres=# select '$123.45'::money;
money
---------
$123.45
(1 row)
postgres=# select "123.45"::money;
ERROR: column "123.45" does not exist
LINE 1: select "123.45"::money;
^
postgres=# select "$123.45"::money;
ERROR: column "$123.45" does not exist
LINE 1: select "$123.45"::money;
^
postgres=# select "123.45"::money;
ERROR: column "123.45" does not exist
LINE 1: select "123.45"::money;
^
postgres=# select "$123.45"::money;
ERROR: column "$123.45" does not exist
LINE 1: select "$123.45"::money;
^
You are on the wrong mailing list :^) Try pgsql-general.
http://www.postgresql.org/docs/8.1/static/sql-syntax.html#SQL-SYNTAX-IDE
NTIFIERS
Anything between double quotes is an identifier.
Yours,
Laurenz Albe
Import Notes
Resolved by subject fallback
-----Original Message-----
From: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Shane Ambler
Sent: 09 October 2006 09:06
To: PostgreSQL Hackers
Subject: Re: [HACKERS] Casting to moneyTom Lane wrote:
"Dave Page" <dpage@vale-housing.co.uk> writes:
select '$123.45'::money
ERROR: invalid input syntax for type money: "$123.45"
select '£123.00'::money
ERROR: invalid input syntax for type money: "£123.00"So ... what locale are you trying this in?
I get the following from 8.2beta1 - looks like it doesn't like the
double quotes.
Double quotes are used for object names - "MySchema"."MyTable" for example. I didn't even bother testing them.
postgres=# select '123.45'::money;
money
---------
$123.45
(1 row)
Now that's interesting - on the Windows server, pgAdmin gets a blank string back. On further investigation, this is looking like an encoding issue in which pgAdmin (well, wxWidgets) isn't converting the £ to utf-8 and back correctly. It does appear to be working correctly in psql.
Sorry for the noise.
Regards, Dave.