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