Thread: Converting between varchar and float when updating

Converting between varchar and float when updating

From
Thomas Larsen Wessel
Date:
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

Re: Converting between varchar and float when updating

From
Dmitriy Igrishin
Date:


2011/4/28 Thomas Larsen Wessel <mrvelle@gmail.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.


Re: Converting between varchar and float when updating

From
Szymon Guz
Date:


On 28 April 2011 11:26, Thomas Larsen Wessel <mrvelle@gmail.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

Re: Converting between varchar and float when updating

From
Vibhor Kumar
Date:
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.kumar@enterprisedb.com
Blog:http://vibhork.blogspot.com


Re: Converting between varchar and float when updating

From
Dmitriy Igrishin
Date:


2011/4/28 Vibhor Kumar <vibhor.kumar@enterprisedb.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.kumar@enterprisedb.com
Blog:http://vibhork.blogspot.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



--
// Dmitriy.


Re: Converting between varchar and float when updating

From
Dmitriy Igrishin
Date:


2011/4/28 Thomas Larsen Wessel <mrvelle@gmail.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.


Re: Converting between varchar and float when updating

From
Vibhor Kumar
Date:
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.kumar@enterprisedb.com
Blog:http://vibhork.blogspot.com


Re: Converting between varchar and float when updating

From
Dmitriy Igrishin
Date:


2011/4/28 Vibhor Kumar <vibhor.kumar@enterprisedb.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.kumar@enterprisedb.com
Blog:http://vibhork.blogspot.com




--
// Dmitriy.


Re: Converting between varchar and float when updating

From
Vibhor Kumar
Date:
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.kumar@enterprisedb.com
Blog:http://vibhork.blogspot.com


Re: Converting between varchar and float when updating

From
Thomas Larsen Wessel
Date:
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.kumar@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.kumar@enterprisedb.com
Blog:http://vibhork.blogspot.com


Re: Converting between varchar and float when updating

From
Dmitriy Igrishin
Date:


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





--
// Dmitriy.


Re: Converting between varchar and float when updating

From
Dmitriy Igrishin
Date:


2011/4/28 Dmitriy Igrishin <dmitigr@gmail.com>


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





--
// Dmitriy.





--
// Dmitriy.


Re: Converting between varchar and float when updating

From
Alban Hertroys
Date:
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
justsave 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
designthe 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!



Re: Converting between varchar and float when updating

From
Thomas Larsen Wessel
Date:
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@solfertje.student.utwente.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!