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 < dal...@solfertje.student.utwente.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! > > >
