Re: Converting between varchar and float when updating

2011/4/28 Dmitriy Igrishin <dmitigr(at)gmail(dot)com>

>
>
> 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);
>
Oh, sorry
ALTER TABLE foo ALTER bar 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.
>
>
>

--
// Dmitriy.