From: | Olivier Gautherot <ogautherot(at)gautherot(dot)net> |
---|---|
To: | Andrus <kobruleht2(at)hot(dot)ee> |
Cc: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>, pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Using compression on TCP transfer |
Date: | 2020-04-05 08:59:02 |
Message-ID: | CAJ7S9TWSG3M0o2tGucxjB_adHeRgLbpNg-y+y=THfW2U2nHf8w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi Andrus,
Le sam. 4 avr. 2020 à 10:09, Andrus <kobruleht2(at)hot(dot)ee> a écrit :
> Hi!
>
> >> In case of varchar field values will appear in database sometimes with
> >> trailing spaces and sometimes without.
> >> This requires major application re-design which much is more expensive
> than
> >> continuing using char fields.
> >A simple BEFORE INSERT OR UPDATE trigger would take care of that.
>
> Changing char to varchar will break commands where trailing space is used
> in comparison.
>
> For example query
>
> create table test ( test char(10) );
> insert into test values ('test');
> select * from test where test ='test '; -- note trailing space
>
> does not return data anymore if your recommendation is used:
>
> create table test ( test varchar );
> insert into test values ('test');
> select * from test where test ='test '; -- note trailing space
>
> In production 'test ' is query parameter coming from application with
> possible trailing space(s).
>
> Adding trigger does not fix this.
> How to fix this without re-writing huge number of sql commands?
>
In the end, your question is more at application level than database
itself. The real question is: which one is correct? With or without
trailing space?
If you decide that it's without, you could apply a TRIM in a trigger on
each INSERT and UPDATE. Then, you could replace the table by a view of the
same name and implement the TRIM on SELECT there. This way, you don't have
to touch anything in the application.
Hope it helps
Olivier
>
From | Date | Subject | |
---|---|---|---|
Next Message | arden liu | 2020-04-05 12:50:12 | psql show me the : and ask user input, when running one sql file |
Previous Message | Peter J. Holzer | 2020-04-04 14:05:57 | Re: Why there is 30000 rows is sample |