Re: varchar and spaces problem..

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