Thread: DBD::Pg (version 3.16.3) returns EMPTY char columns as 'undef'
Hello,
We're using the above DBD::Pg version on Linux together with PostgreSQL 15.1
On fetch empty char columns are returned as (Perl) 'undef'
while ( my @row_ary = $dba->FetchArray()) {
foreach my $i (0..$#row_ary) {
if ($row_ary[$i] eq undef) {
print $row_ary[1] . "\n";
next;
}
...
which later leads in our code to NULL values '\N' in the writing of a CSV-like export
files. Ofc NULL values in the database are something else as '' char
strings.
How this must be distinguished with DBD::Pg?
Thanks
matthias
--
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
2023年4月25日(火) 21:42 Matthias Apitz <guru@unixarea.de>:
>
>
> Hello,
>
> We're using the above DBD::Pg version on Linux together with PostgreSQL 15.1
> On fetch empty char columns are returned as (Perl) 'undef'
>
> while ( my @row_ary = $dba->FetchArray()) {
> foreach my $i (0..$#row_ary) {
> if ($row_ary[$i] eq undef) {
> print $row_ary[1] . "\n";
> next;
> }
> ...
> which later leads in our code to NULL values '\N' in the writing of a CSV-like export
> files. Ofc NULL values in the database are something else as '' char
> strings.
>
> How this must be distinguished with DBD::Pg?
"eq undef" looks very wrong there:
$ perl -e "printf(qq|%i\n|, '' eq undef);"
1
$ perl -e "printf(qq|%i\n|, defined '');"
1
You probably want "if (!defined $row_ary[$i])". And possibly warnings enabled:
$ perl -w -e "printf(qq|%i\n|, '' eq undef);"
Use of uninitialized value in string eq at -e line 1.
1
$ perl -w -e "printf(qq|%i\n|, defined '');"
1
Regards
Ian Barwick
On 2023-04-25 14:41:45 +0200, Matthias Apitz wrote:
> We're using the above DBD::Pg version on Linux together with PostgreSQL 15.1
> On fetch empty char columns are returned as (Perl) 'undef'
>
> while ( my @row_ary = $dba->FetchArray()) {
What is FetchArray? Neither perldoc DBI nor perldoc DBD::Pg mentions
this method. Did you use a wrapper around DBI? (I would have expected
fetchrow_array here)
> foreach my $i (0..$#row_ary) {
> if ($row_ary[$i] eq undef) {
> print $row_ary[1] . "\n";
> next;
So when any column is null you want to print the first one and skip to
the next one?
> }
> ...
> which later leads in our code to NULL values '\N' in the writing of a CSV-like export
> files. Ofc NULL values in the database are something else as '' char
> strings.
Works for me (PostgreSQL 14, Perl 5.34, DBI 1.643, DBD::Pg 3.15):
% cat empty_char
#!/usr/bin/perl
use v5.34;
use warnings;
use Data::Dumper;
use DBIx::SimpleConnect;
my $dbh = DBIx::SimpleConnect->connect("default");
$dbh->do("drop table if exists empty_char");
$dbh->do("create table empty_char (id serial primary key, t char(5))");
$dbh->do("insert into empty_char(t) values(null)");
$dbh->do("insert into empty_char(t) values('')");
$dbh->do("insert into empty_char(t) values(' ')");
$dbh->do("insert into empty_char(t) values('a')");
$dbh->do("insert into empty_char(t) values('a ')");
my $data = $dbh->selectall_arrayref(
"select * from empty_char",
{Slice => {}}
);
print Dumper($data);
(DBIx::SimpleConnect is just a simple wrapper which looks up connection
strings. It returns a normal DBI database handle object)
% ./empty_char
$VAR1 = [
{
't' => undef,
'id' => 1
},
{
'id' => 2,
't' => ' '
},
{
't' => ' ',
'id' => 3
},
{
't' => 'a ',
'id' => 4
},
{
'id' => 5,
't' => 'a '
}
];
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
Attachment
El día martes, abril 25, 2023 a las 09:58:10 +0900, Ian Lawrence Barwick escribió:
> "eq undef" looks very wrong there:
>
> $ perl -e "printf(qq|%i\n|, '' eq undef);"
> 1
> $ perl -e "printf(qq|%i\n|, defined '');"
> 1
>
> You probably want "if (!defined $row_ary[$i])". And possibly warnings enabled:
>
> ...
Ian,
Thanks for this hint. It fixed it. I modified the code to:
...
foreach my $i (0..$#row_ary) {
if (!defined $row_ary[$i]) {
printf $row_ary[0] . " | " . $row_ary[1] . " | " . $row_ary[2] . "\n";
next;
}
set one row to have a NULL value (others have '' or a real string for
the column varvalue):
testdb=# update adm_partab set varvalue = NULL where varname='DocumentUploadSshPassw';
UPDATE 1
testdb=# \pset null '<null>'
Null-Anzeige ist »<null>«.
testdb=# select * from adm_partab where varname='DocumentUploadSshPassw';
product | varname | varvalue
---------+------------------------+----------
1 | DocumentUploadSshPassw | <null>
and run the modiefied code which prints only the row with the NULL
value, exports everything fine and the NULL value as '\N':
~sisis/sc/dbtool < unl 2>&1 | more
connected to Pg:testdb
begin operation: UNLOAD (adm_partab)
1 | DocumentUploadSshPassw |
366 rows unloaded...
grep -C3 '|\\N' adm_partab.load
2|CIR_auto_idm_informieren|N
2|CIR_GebMahn_SO|N
2|CIR_BR_Immer_Benachrichtigen|N
1|DocumentUploadSshPassw|\N <***************
2|CIR_AutoTempMedien|N
2|CIR_PrintCmd_List_Ascii|/opt/lib/sisis/bin/AsciiPrint.sh
2|CIR_Such_Kategorie_4|902 Schlagwort
even the warning is printed for this perl line 1196:
~sisis/sc/dbtool < unl 2>&1 | grep 1196
Use of uninitialized value $row_ary[2] in concatenation (.) or string at /home/sisis/sc/dbtool.pl line 1196.
Thanks again and
Kind Regards
matthias
--
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub