Обсуждение: Casting to money
A pgAdmin user has just pointed out that the data editor doesn't work with money columns, apparently because it casts datawhen 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 varietyof 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
> -----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 money > > 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. 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 lookinglike an encoding issue in which pgAdmin (well, wxWidgets) isn't converting the £ to utf-8 and back correctly. It doesappear to be working correctly in psql. Sorry for the noise. Regards, Dave.