Thread: Converting between varchar and float when updating

Converting between varchar and float when updating

From

Thomas Larsen Wessel

Date:

28 April 2011, 09:26:55

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:

28 April 2011, 09:40:47

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:

28 April 2011, 09:42:17

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:

28 April 2011, 09:44:37

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:

28 April 2011, 09:52:26

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:

28 April 2011, 09:57:59

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:

28 April 2011, 10:06:06

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:

28 April 2011, 10:12:04

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:

28 April 2011, 10:13:45

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:

28 April 2011, 13:26:51

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:

28 April 2011, 13:41:08

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:

28 April 2011, 13:43:25

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:

28 April 2011, 15:42:18

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:

28 April 2011, 15:56:16

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!