Re: Converting between varchar and float when updating

[Thomas Larsen Wessel <mrvelle@xxxxxxxxx>]

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