2011/4/28 Thomas Larsen Wessel <mrve...@gmail.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.ku...@enterprisedb.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.ku...@enterprisedb.com >> Blog:http://vibhork.blogspot.com >> >> > -- // Dmitriy.
