From: | "Peter J(dot) Holzer" <hjp-pgsql(at)hjp(dot)at> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: DBD::Pg (version 3.16.3) returns EMPTY char columns as 'undef' |
Date: | 2023-04-25 20:02:24 |
Message-ID: | 20230425200224.vccz67t4t72iw3wi@hjp.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
From | Date | Subject | |
---|---|---|---|
Next Message | postgresql439848 | 2023-04-26 02:05:25 | Re: FW: Error! |
Previous Message | Christophe Pettus | 2023-04-25 16:42:12 | Re: VACUUM (INDEX_CLEANUP OFF) and GIN indexes |