Re: [GENERAL] Converting between varchar and float when updating

2011-04-28 Thread Thomas Larsen Wessel
I appreciate the advice. But in this particular case, other people have decided for me that I should not change the schema. I guess they have their reasons :) On Thu, Apr 28, 2011 at 5:40 PM, Alban Hertroys < dal...@solfertje.student.utwente.nl> wrote: > On 28 Apr 2011, at 15:26, Thomas Larsen We

Re: [GENERAL] Converting between varchar and float when updating

2011-04-28 Thread Alban Hertroys
On 28 Apr 2011, at 15:26, Thomas Larsen Wessel wrote: > 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. but I want to just save 0.66. > > 2) Can I make a criteria that it should only update on

Re: [GENERAL] Converting between varchar and float when updating

2011-04-28 Thread Dmitriy Igrishin
2011/4/28 Dmitriy Igrishin > > > 2011/4/28 Thomas Larsen Wessel > >> 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); >>

Re: [GENERAL] Converting between varchar and float when updating

2011-04-28 Thread Dmitriy Igrishin
2011/4/28 Thomas Larsen Wessel > 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 > > Th

Re: [GENERAL] Converting between varchar and float when updating

2011-04-28 Thread Thomas Larsen Wessel
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 sp

Re: [GENERAL] Converting between varchar and float when updating

2011-04-28 Thread Vibhor Kumar
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 h

Re: [GENERAL] Converting between varchar and float when updating

2011-04-28 Thread Dmitriy Igrishin
2011/4/28 Vibhor Kumar > > On Apr 28, 2011, at 3:22 PM, Dmitriy Igrishin wrote: > > > NB: I am sure that OP is not sure :-) And since foo.bar is varchar, > > it is better to use numeric instead of float :-) > > > Now, this make to ask question, why numeric? How its better than float? > Only one

Re: [GENERAL] Converting between varchar and float when updating

2011-04-28 Thread Vibhor Kumar
On Apr 28, 2011, at 3:22 PM, Dmitriy Igrishin wrote: > NB: I am sure that OP is not sure :-) And since foo.bar is varchar, > it is better to use numeric instead of float :-) Now, this make to ask question, why numeric? How its better than float? Thanks & Regards, Vibhor Kumar EnterpriseDB Cor

Re: [GENERAL] Converting between varchar and float when updating

2011-04-28 Thread Dmitriy Igrishin
2011/4/28 Thomas Larsen Wessel > I have a table with the following schema: > CREATE TABLE foo (bar VARCHAR(32)); > > Every bar value has a format like a float, e.g. "2.5". Now I want that > value multiplied by two and saved again as varchar. I was hoping to do smth > like: > > UPDATE foo SET bar

Re: [GENERAL] Converting between varchar and float when updating

2011-04-28 Thread Dmitriy Igrishin
2011/4/28 Vibhor Kumar > > On Apr 28, 2011, at 2:56 PM, Thomas Larsen Wessel wrote: > > > UPDATE foo SET bar = TO_VARCHAR( TO_FLOAT(bar) * 2); -- INCORRECT > > If you are sure bar contains float value, then try following: > UPDATE foo SET bar = bar::float * 2; > NB: I am sure that OP is not

Re: [GENERAL] Converting between varchar and float when updating

2011-04-28 Thread Vibhor Kumar
On Apr 28, 2011, at 2:56 PM, Thomas Larsen Wessel wrote: > UPDATE foo SET bar = TO_VARCHAR( TO_FLOAT(bar) * 2); -- INCORRECT If you are sure bar contains float value, then try following: UPDATE foo SET bar = bar::float * 2; Thanks & Regards, Vibhor Kumar EnterpriseDB Corporation The Enterpr

Re: [GENERAL] Converting between varchar and float when updating

2011-04-28 Thread Szymon Guz
On 28 April 2011 11:26, Thomas Larsen Wessel wrote: > I have a table with the following schema: > CREATE TABLE foo (bar VARCHAR(32)); > > Every bar value has a format like a float, e.g. "2.5". Now I want that > value multiplied by two and saved again as varchar. I was hoping to do smth > like: >

Re: [GENERAL] Converting between varchar and float when updating

2011-04-28 Thread Dmitriy Igrishin
2011/4/28 Thomas Larsen Wessel > I have a table with the following schema: > CREATE TABLE foo (bar VARCHAR(32)); > > Every bar value has a format like a float, e.g. "2.5". Now I want that > value multiplied by two and saved again as varchar. I was hoping to do smth > like: > > UPDATE foo SET bar

[GENERAL] Converting between varchar and float when updating

2011-04-28 Thread Thomas Larsen Wessel
I have a table with the following schema: CREATE TABLE foo (bar VARCHAR(32)); Every bar value has a format like a float, e.g. "2.5". Now I want that value multiplied by two and saved again as varchar. I was hoping to do smth like: UPDATE foo SET bar = TO_VARCHAR( TO_FLOAT(bar) * 2); -- INCORRECT!