pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
DBD::Pg (version 3.16.3) returns EMPTY char columns as 'undef'
4+ messages / 3 participants
[nested] [flat]

* DBD::Pg (version 3.16.3) returns EMPTY char columns as 'undef'
@ 2023-04-25 12:41 Matthias Apitz <guru@unixarea.de>
  2023-04-25 12:58 ` Re: DBD::Pg (version 3.16.3) returns EMPTY char columns as 'undef' Ian Lawrence Barwick <barwick@gmail.com>
  2023-04-25 20:02 ` Re: DBD::Pg (version 3.16.3) returns EMPTY char columns as 'undef' Peter J. Holzer <hjp-pgsql@hjp.at>
  0 siblings, 2 replies; 4+ messages in thread

From: Matthias Apitz @ 2023-04-25 12:41 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org


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





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: DBD::Pg (version 3.16.3) returns EMPTY char columns as 'undef'
  2023-04-25 12:41 DBD::Pg (version 3.16.3) returns EMPTY char columns as 'undef' Matthias Apitz <guru@unixarea.de>
@ 2023-04-25 12:58 ` Ian Lawrence Barwick <barwick@gmail.com>
  2023-04-26 05:32   ` Re: DBD::Pg (version 3.16.3) returns EMPTY char columns as 'undef' Matthias Apitz <guru@unixarea.de>
  1 sibling, 1 reply; 4+ messages in thread

From: Ian Lawrence Barwick @ 2023-04-25 12:58 UTC (permalink / raw)
  To: Matthias Apitz <guru@unixarea.de>; +Cc: pgsql-general@lists.postgresql.org

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





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: DBD::Pg (version 3.16.3) returns EMPTY char columns as 'undef'
  2023-04-25 12:41 DBD::Pg (version 3.16.3) returns EMPTY char columns as 'undef' Matthias Apitz <guru@unixarea.de>
  2023-04-25 12:58 ` Re: DBD::Pg (version 3.16.3) returns EMPTY char columns as 'undef' Ian Lawrence Barwick <barwick@gmail.com>
@ 2023-04-26 05:32   ` Matthias Apitz <guru@unixarea.de>
  0 siblings, 0 replies; 4+ messages in thread

From: Matthias Apitz @ 2023-04-26 05:32 UTC (permalink / raw)
  To: Ian Lawrence Barwick <barwick@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

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





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: DBD::Pg (version 3.16.3) returns EMPTY char columns as 'undef'
  2023-04-25 12:41 DBD::Pg (version 3.16.3) returns EMPTY char columns as 'undef' Matthias Apitz <guru@unixarea.de>
@ 2023-04-25 20:02 ` Peter J. Holzer <hjp-pgsql@hjp.at>
  1 sibling, 0 replies; 4+ messages in thread

From: Peter J. Holzer @ 2023-04-25 20:02 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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!"

Attachments:

  signature.asc (application/pgp-signature, 832B) download

^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2023-04-26 05:32 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-04-25 12:41 DBD::Pg (version 3.16.3) returns EMPTY char columns as 'undef' Matthias Apitz <guru@unixarea.de>
2023-04-25 12:58 ` Ian Lawrence Barwick <barwick@gmail.com>
2023-04-26 05:32   ` Matthias Apitz <guru@unixarea.de>
2023-04-25 20:02 ` Peter J. Holzer <hjp-pgsql@hjp.at>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox