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.

Answer 1

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

Answer 2

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]);
}