Inserting null fields with dbi:Pg
- by User1
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.
