Re: DBD::Pg exorts char columns with trailing blanks

On 10/18/19 8:15 AM, Rob Sargent wrote:
>
> On 10/18/19 8:51 AM, Adrian Klaver wrote:
>> On 10/18/19 7:42 AM, Matthias Apitz wrote:
>>> El día viernes, octubre 18, 2019 a las 03:01:58p. m. +0200, Tom Lane
>>> escribió:
>>>
>>>> Matthias Apitz <guru(at)unixarea(dot)de> writes:
>>>>> When we export char columns with our Perl tools, they come out with
>>>>> trailing blanks (in Sybase they don't). Can this be suppressed?
>>>>
>>>> Switch to varchar, perhaps?
>>>>
>>>>             regards, tom lane
>>>
>>> Sometimes people does not know, what they propose. We have a historical
>>> 25 years grown ILS which runs on top of Sybase, Oracle, Informix ... and
>>> should now be ported to PostgreSQL. We can't simple switch internal
>>> table structures and adopt some 10.000.000 lines of code (or debug while
>>> it is now crashing).
>>
>> That was not mentioned in the original post. Anyway:
>>
>> https://metacpan.org/pod/DBD::Pg#ChopBlanks-(boolean,-inherited)
>>
>> https://metacpan.org/pod/DBI
>>
>> "ChopBlanks
>>
>> Type: boolean, inherited
>>
>> The ChopBlanks attribute can be used to control the trimming of
>> trailing space characters from fixed width character (CHAR) fields. No
>> other field types are affected, even where field values have trailing
>> spaces.
>>
>> The default is false (although it is possible that the default may
>> change). Applications that need specific behaviour should set the
>> attribute as needed.
>>
>> Drivers are not required to support this attribute, but any driver
>> which does not support it must arrange to return undef as the
>> attribute value."
>>
>>>
>>> Thanks anyway.
>>>
>>>     matthias
>>>
>>>
>>
>>
> It seems to me you've simply exposed a bug in you ILS.  If blanks
> intentionally went in, would they not get truncated on the way out in
> the other systems?  If all trailing blanks are expendable perhaps your
> saves should remove them.  (And a trimming of the existing records is in
> order.)

The OP is dealing with char(acter) fields:

https://www.postgresql.org/docs/11/datatype-character.html

"If the string to be stored is shorter than the declared length, values
of type character will be space-padded; ..."

>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com