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

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

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

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