Skip site navigation (1) Skip section navigation (2)

Re: Converting between varchar and float when updating

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 (view raw or flat)
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!
>
>
>

In response to

pgsql-general by date

Next:From: Carlos MennensDate: 2011-04-28 16:19:16
Subject: Re: Switching Database Engines
Previous:From: Alban HertroysDate: 2011-04-28 15:49:20
Subject: Re: GIN index not used

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group