Thread: varchar and spaces problem..
Hi all, first of all hello since i'm new to this list. I have the following problem: I created a table like this: create table test(a varchar(10), b int) If i insert the following data into the table : insert into test values ('test ',2) All the white spaces in the varchar string are inserted. Since the actual charachters in the string can vary, i cannot usechar for the datatype. I tried adding a rule that executes the following statement on insert: create rule rl_testins as on insert to test do update test set a=trim(trailing ' ' from a) where a=old.a and it actually works. The problem is that on my production db i have tables that have 50-60 columns and i insert into thesethousand of records in a short period of time and this slows down everything until it inserts only 1 record per second(without this rule it can insert ~100 records per second constantly). Is there a workaround for this so pg can behave like ms access which truncates trailing spaces from varchar columns? I cannot modifiy the sql statements because they are generated from a third party program which is written in cobol. Thank you very much for any help provided and hope you have some answers since we are migrating from access to postgres... Regards Fabrizio Mazzoni Macron Srl http://eteampoint.com
On Tue, 14 Jan 2003, Fabrizio Mazzoni wrote: > I have the following problem: > > I created a table like this: > > create table test(a varchar(10), b int) > > If i insert the following data into the table : > > insert into test values ('test ',2) > > All the white spaces in the varchar string are inserted. Since the > actual charachters in the string can vary, i cannot use char for the > datatype. I don't see necessarily why that'd be any different for char except that it pads with spaces rather than treating them as significant characters. > I tried adding a rule that executes the following statement on insert: > > create rule rl_testins as on insert to test do > update test set a=trim(trailing ' ' from a) where a=old.a > > and it actually works. The problem is that on my production db i have > tables that have 50-60 columns and i insert into these thousand of > records in a short period of time and this slows down everything until > it inserts only 1 record per second (without this rule it can insert > ~100 records per second constantly). You'd be better off doing a before trigger that does the modification rather than a rule. I think the rule will do an insert then search to find the row, mark that one as deleted and make entirely new row as opposed to a before trigger that just sets the value, especially if test.a isn't indexed. A function/trigger something like: create function trima() returns trigger as ' begin NEW.a := trim(trailing '' '' from NEW.a); return NEW; end;' language 'plpgsql'; create trigger testtrig before insert on test for each row execute procedure trima();
On Tuesday 14 Jan 2003 7:29 pm, Fabrizio Mazzoni wrote: > insert into test values ('test ',2) > > All the white spaces in the varchar string are inserted. Since the actual > charachters in the string can vary, i cannot use char for the datatype. I > tried adding a rule that executes the following statement on insert: > > create rule rl_testins as on insert to test do > update test set a=trim(trailing ' ' from a) where a=old.a > > and it actually works. The problem is that on my production db i have > tables that have 50-60 columns and i insert into these thousand of records > in a short period of time and this slows down everything until it inserts > only 1 record per second (without this rule it can insert ~100 records per > second constantly). That seems quite slow, although I haven't tested. Is the machine maxed out on CPU during these modified updates? If you manuall re-write a set of inserts do they run equally slowly? > Is there a workaround for this so pg can behave like ms access which > truncates trailing spaces from varchar columns? Well, you could write your own type, but that's probably more effort than you'd like. > I cannot modifiy the sql statements because they are generated from a third > party program which is written in cobol. > > Thank you very much for any help provided and hope you have some answers > since we are migrating from access to postgres... Are you connecting to the database directly from the cobol app? If not then it probably is practical to modify the SQL en-route. -- Richard Huxton