From: | Thomas Larsen Wessel <mrvelle(at)gmail(dot)com> |
---|---|
To: | Alban Hertroys <dalroi(at)solfertje(dot)student(dot)utwente(dot)nl> |
Cc: | Vibhor Kumar <vibhor(dot)kumar(at)enterprisedb(dot)com>, Dmitriy Igrishin <dmitigr(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Converting between varchar and float when updating |
Date: | 2011-04-28 15:55:55 |
Message-ID: | BANLkTi=Seaq9=gh61wYhhTyUOFP49N4zHg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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!
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Carlos Mennens | 2011-04-28 16:19:16 | Re: Switching Database Engines |
Previous Message | Alban Hertroys | 2011-04-28 15:49:20 | Re: GIN index not used |