2011/4/28 Thomas Larsen Wessel <mrvelle@xxxxxxxxx>
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);
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.kumar@xxxxxxxxxxxxxxxx> wrote:
Ah! Got it. This I have missed.
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.
Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
vibhor.kumar@xxxxxxxxxxxxxxxx
Blog:http://vibhork.blogspot.com
--
// Dmitriy.
