Re: Converting between varchar and float when updating

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!!!!

How is that done?

I know that the bar attribute ought to have type FLOAT, but I have to work
with this legacy database. And anyway this table will rarely be updated.

Sincerely, Thomas

2011/4/28 Thomas Larsen Wessel <mrvelle(at)gmail(dot)com>

> 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!!!!
>
Try UPDATE foo SET bar = (bar::numeric * 2);

>
> How is that done?
>
> I know that the bar attribute ought to have type FLOAT, but I have to work
> with this legacy database. And anyway this table will rarely be updated.
>
> Sincerely, Thomas
>
>

--
// Dmitriy.

On 28 April 2011 11:26, Thomas Larsen Wessel <mrvelle(at)gmail(dot)com> 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:
>
> UPDATE foo SET bar = TO_VARCHAR( TO_FLOAT(bar) * 2); -- INCORRECT!!!!
>
> How is that done?
>
> I know that the bar attribute ought to have type FLOAT, but I have to work
> with this legacy database. And anyway this table will rarely be updated.
>
> Sincerely, Thomas
>
>
Hi,
maybe try this instead:

UPDATE foo SET bar = (bar::float * 2) :: text;

regards
Szymon

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 Enterprise PostgreSQL Company
vibhor(dot)kumar(at)enterprisedb(dot)com
Blog:http://vibhork.blogspot.com

2011/4/28 Vibhor Kumar <vibhor(dot)kumar(at)enterprisedb(dot)com>

>
> 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 sure :-) And since foo.bar is varchar,
it is better to use numeric instead of float :-)

>
> Thanks & Regards,
> Vibhor Kumar
> EnterpriseDB Corporation
> The Enterprise PostgreSQL Company
> vibhor(dot)kumar(at)enterprisedb(dot)com
> Blog:http://vibhork.blogspot.com
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
// Dmitriy.

2011/4/28 Thomas Larsen Wessel <mrvelle(at)gmail(dot)com>

> 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!!!!
>
> How is that done?
>
> I know that the bar attribute ought to have type FLOAT, but I have to work
> with this legacy database. And anyway this table will rarely be updated.
>
> Sincerely, Thomas
>
> Btw, why are you store numeric values in varchar instead of numeric?

--
// Dmitriy.

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 Corporation
The Enterprise PostgreSQL Company
vibhor(dot)kumar(at)enterprisedb(dot)com
Blog:http://vibhork.blogspot.com

2011/4/28 Vibhor Kumar <vibhor(dot)kumar(at)enterprisedb(dot)com>

>
> 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 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(dot)kumar(at)enterprisedb(dot)com
> Blog:http://vibhork.blogspot.com
>
>

--
// Dmitriy.

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

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.

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;

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.

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
>
>

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);

>
> 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.

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.

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.66666666 but I want to just save 0.66.
>
> 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;
>
>
> 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.

Why don't you change that column to a new one with type numeric and offer your application a view that converts it to varchar? With some rules (see manuals), you could even make that "virtual column" writable.
It's quite possible that you'll have to rename the table as well, so that the new view can have the name of the current table.

ALTER TABLE foo RENAME TO realfoo;
ALTER TABLE realfoo ADD COLUMN realbar numeric(6,2);
UPDATE realfoo SET realbar = bar::numeric;
ALTER TABLE realfoo DROP bar;
CREATE VIEW foo AS SELECT foo, realbar::text as bar, baz FROM realbar;
CREATE RULE foo_insert AS ON INSERT TO foo
DO INSTEAD
INSERT INTO realfoo (foo, realbar, baz) VALUES (NEW.foo, NEW.bar::numeric, NEW.baz);
CREATE RULE foo_update ...etc.

That way you're calculating and sorting with actual numeric values, but your application still sees a varchar field.

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.

!DSPAM:737,4db98ac612121628848733!

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 <
dalroi(at)solfertje(dot)student(dot)utwente(dot)nl> wrote:

> 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.66666666 but I want to just save 0.66.
> >
> > 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;
> >
> >
> > 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.
>
> Why don't you change that column to a new one with type numeric and offer
> your application a view that converts it to varchar? With some rules (see
> manuals), you could even make that "virtual column" writable.
> It's quite possible that you'll have to rename the table as well, so that
> the new view can have the name of the current table.
>
> ALTER TABLE foo RENAME TO realfoo;
> ALTER TABLE realfoo ADD COLUMN realbar numeric(6,2);
> UPDATE realfoo SET realbar = bar::numeric;
> ALTER TABLE realfoo DROP bar;
> CREATE VIEW foo AS SELECT foo, realbar::text as bar, baz FROM realbar;
> CREATE RULE foo_insert AS ON INSERT TO foo
> DO INSTEAD
> INSERT INTO realfoo (foo, realbar, baz) VALUES (NEW.foo,
> NEW.bar::numeric, NEW.baz);
> CREATE RULE foo_update ...etc.
>
> That way you're calculating and sorting with actual numeric values, but
> your application still sees a varchar field.
>
> Alban Hertroys
>
> --
> Screwing up is an excellent way to attach something to the ceiling.
>
>
> !DSPAM:1258,4db98ab912121905226675!
>
>
>