Re: varchar and spaces problem..

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();