How do I insert null fields with Perl's DBD::Pg?

Question

I have a Perl script inserting data into Postgres according to a pipe delimited text file. Sometimes, a field is null (as expected). However, Perl makes this field into an empty string and the Postgres insert statement fails.

Here's a snippet of code:


use DBI;

#Connect to the database.
$dbh=DBI->connect('dbi:Pg:dbname=mydb','mydb','mydb',{AutoCommit=>1,RaiseError=>1,PrintError=>1});

#Prepare an insert.
$sth=$dbh->prepare("INSERT INTO mytable (field0,field1) SELECT ?,?");

while (<>){
    #Remove the whitespace
    chomp;

    #Parse the fields.
    @field=split(/\|/,$_);

    print "$_\n";

    #Do the insert.
    $sth->execute($field[0],$field[1]);
}

And if the input is:

a|1
b|
c|3

EDIT: Use this input instead.

a|1|x
b||x
c|3|x

It will fail at b|.

DBD::Pg::st execute failed: ERROR:  invalid input syntax for integer: ""

I just want it to insert a null on field1 instead. Any ideas?

EDIT: I simplified the input at the last minute. The old input actually made it work for some reason. So now I changed the input to something that will make the program fail. Also note that field1 is a nullable integer datatype.

This code works here, Perl 5.10.1, DBD::Pg 2.15.1, Postgres 8.4. Also why are you using SELECT ?, ? instead of VALUES (?,?)? — MkV, May 18 '10 at 17:46
Also, use strict; use warnings; and fix your variable declarations — MkV, May 18 '10 at 17:51
About the SQL format..the real code actually has some joins on that select. — User1, May 18 '10 at 19:47

score 5 · Accepted Answer · answered May 18 '10 at 18:01

5

I'm not sure you tested whether your pasted code and data together, they work with Perl 5.10.1, DBD::Pg 2.15.1 and Postgres 8.4. Also you should use strict and warnings and not rely on package scope for your variables.

If you change your code and data to use three or more fields, leaving a non-terminal one empty, then you can trigger the error from DBD::Pg. Add a line like this to your code before executing the prepared statement:

map { $_ eq '' and $_ = undef } @field;

To map empty strings in @field to undef

answered May 18 '10 at 18:01

MkV

3,046
22
16

That took care of converting the empty strings to nulls. It's odd that removing a field in the input caused the empty string to become a null (at least that what appears to have happened). – User1 May 18 '10 at 19:41
If a string ends in the characters you are splitting on, like 'a|4|' then the final array element doesn't get filled in at all, not turned into an empty string, so it is undef, see perldoc -f split. – MkV May 19 '10 at 09:54

score 3 · Answer 2 · answered May 18 '10 at 17:31

3

The DBI package maps undef to NULL. (Perl's defined-ness vs. falseness logic is actually a pretty good fit for SQL's trinary logic.)

So, in your while loop, just check if the indicated field is an empty string, and if so, make it undef instead:

while (<>){
    ...
    #Parse the fields.
    @field=split(/\|/,$_);

    if ( $field[1] eq '' ) { 
        # handle NULLs
        $field[1] = undef;
    }

    #Do the insert.
    $sth->execute($field[0],$field[1]);
}

answered May 18 '10 at 17:31

friedo

65,762
16
114
184

your if is never true, and if you put use strict; use warnings; on your code you get 'Use of uninitialized value $field[1] in string eq' – MkV May 18 '10 at 17:53
1

@james, I'm not sure what you mean. It works fine with `strict` and `warnings` enabled and the conditional succeeds. `perl -Mstrict -Mwarnings -MData::Dumper -le 'my $str="a|b||d"; my @a = split /\|/, $str; if ( $a[2] eq "" ) { $a[2] = undef; }; print Dumper \@a'` – friedo May 18 '10 at 19:02
It works now, but as the question was originally posed (and as you answered it), there were only 2 fields and the empty field was at the end, returning undef for that index. – MkV May 19 '10 at 09:56

score 2 · Answer 3 · answered May 18 '10 at 16:50

2

undef usually maps to NULL. It looks like you're inserting the empty string which is not the same as undef.

answered May 18 '10 at 16:50

David M

4,325
2
28
40

this is true for oracle too {null maps to undef} it takes me long search to find and my code will look simpler because of you thank you – Mohannd Jul 15 '19 at 12:02

How do I insert null fields with Perl's DBD::Pg?

3 Answers3

Linked