2011/4/28 Dmitriy Igrishin <dmit...@gmail.com> > > > 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); > 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.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. > > > -- // Dmitriy.
