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

DBD::Pg exorts char columns with trailing blanks

From
Matthias Apitz
Date:
Hello,

When we export char columns with our Perl tools, they come out  with trailing blanks (in Sybase they don't). Can this
besuppressed? 

Thanks

matthias


--
Sent using Dekko from my Ubuntu device



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

From
Tom Lane
Date:
Matthias Apitz <guru@unixarea.de> writes:
> When we export char columns with our Perl tools, they come out  with trailing blanks (in Sybase they don't). Can this
besuppressed? 

Switch to varchar, perhaps?

            regards, tom lane



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

From
Matthias Apitz
Date:
El día viernes, octubre 18, 2019 a las 03:01:58p. m. +0200, Tom Lane escribió:

> Matthias Apitz <guru@unixarea.de> writes:
> > When we export char columns with our Perl tools, they come out  with trailing blanks (in Sybase they don't). Can
thisbe 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).

Thanks anyway.

    matthias


-- 
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub

3. Oktober! Wir gratulieren! Der Berliner Fernsehturm wird 50 
aus: https://www.jungewelt.de/2019/10-02/index.php



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

From
Adrian Klaver
Date:
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@unixarea.de> writes:
>>> When we export char columns with our Perl tools, they come out  with trailing blanks (in Sybase they don't). Can
thisbe 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
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com



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

From
Rob Sargent
Date:
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@unixarea.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.)



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

From
Adrian Klaver
Date:
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@unixarea.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.klaver@aklaver.com



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

From
Rob Sargent
Date:
On 10/18/19 9:19 AM, Adrian Klaver wrote:
> 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@unixarea.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; ..."
>
>>
>>
Understood
>>
>
>