DBD::Pg exorts char columns with trailing blanks
Hello,
When we export char columns with our Perl tools, they come out with trailing blanks (in Sybase they don't). Can this be suppressed?
Thanks
matthias
--
Sent using Dekko from my Ubuntu device
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
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).
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
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)
"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
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)
"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.)
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)
"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
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)
"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