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
On Thu, Apr 28, 2011 at 5:40 PM, Alban Hertroys <
> 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.
In response to
pgsql-general by date
|Next:||From: Carlos Mennens||Date: 2011-04-28 16:19:16|
|Subject: Re: Switching Database Engines|
|Previous:||From: Alban Hertroys||Date: 2011-04-28 15:49:20|
|Subject: Re: GIN index not used|