Обсуждение: 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

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

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


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

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

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

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

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

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.

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!