Re: Converting between varchar and float when updating

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