From: | Dmitriy Igrishin <dmitigr(at)gmail(dot)com> |
---|---|
To: | Thomas Larsen Wessel <mrvelle(at)gmail(dot)com> |
Cc: | Vibhor Kumar <vibhor(dot)kumar(at)enterprisedb(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Converting between varchar and float when updating |
Date: | 2011-04-28 13:40:59 |
Message-ID: | BANLkTikNP2heQ8kaYQTuekWCUet4Mv45zA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
2011/4/28 Thomas Larsen Wessel <mrvelle(at)gmail(dot)com>
> Thanks a lot :)
>
> Both of the following work
>
> UPDATE foo SET bar = (bar::float * 2);
> removes trailing zeros on the decimal side, if no decimals dont show any
> "."
>
> UPDATE foo SET bar = (bar::numeric * 2);
> keeps decimals, i.e. 2.000 * 2 -> 4.000
>
> That leads me to two additional questions:
>
> 1) Can I specify how many decimals I want to be stored back from the
> result? E.g. 2 / 3 = 0.66666666 but I want to just save 0.66.
>
Try UPDATE foo SET bar = (bar::numeric(1000,2) * 2);
> 2) Can I make a criteria that it should only update on the strings that can
> be converted. Maybe smth. like:
> UPDATE foo SET bar = (bar::numeric * 2) WHERE bar::is_numeric;
>
Try for example WHERE bar ~ E'^\\s*[-+e\\.0-9]+\\s*$'
>
>
> Thomas
>
> P.S.: Dmitriy asked why I save these values in VarChar. Well, I agree, that
> they should be numeric, but I did not design the schema which is btw 10
> years old.
>
You can try change data type of the column, e.g.:
ALTER TABLE foo SET DATA TYPE numeric(10, 2) USING bar::numeric(10,2);
>
> On Thu, Apr 28, 2011 at 12:18 PM, Vibhor Kumar <
> vibhor(dot)kumar(at)enterprisedb(dot)com> wrote:
>
>>
>> On Apr 28, 2011, at 3:41 PM, Dmitriy Igrishin wrote:
>>
>> > Only one point, Vibhor. I believe that varchar data type was chosen for
>> > exact storage of numeric values. According to chapter 8.1.3 of the doc.
>> > for this case the usage of numeric is preferred over floating data
>> types.
>> Ah! Got it. This I have missed.
>>
>> Thanks & Regards,
>> Vibhor Kumar
>> EnterpriseDB Corporation
>> The Enterprise PostgreSQL Company
>> vibhor(dot)kumar(at)enterprisedb(dot)com
>> Blog:http://vibhork.blogspot.com
>>
>>
>
--
// Dmitriy.
From | Date | Subject | |
---|---|---|---|
Next Message | Dmitriy Igrishin | 2011-04-28 13:43:06 | Re: Converting between varchar and float when updating |
Previous Message | Karsten Hilbert | 2011-04-28 13:30:55 | Re: plpython module import errors |