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

I have a Perl script which is to insert data into a postgrid according to a pipe delimited text file. Occasionally, a field is zero (as expected). However, Pearl makes this field in an empty string and the postgres insertion statement fails.

Here is a snippet of code:

  Usage DBI; # Connect to database $ DBH = DBI-> Connect ('dbi: Pg: dbname = mydb', 'mydb', 'mydb', {AutoCommit => 1, RaiseError => 1, PrintError => 1}); # Prepare an $ $ sth = $ dbh-> Ready ("Insert in mytable (Field 0, Field 1) SELECT?,?"); While (& lt;>) {# Whitespace Delete Chrome; #Keep the fields behind @ region = partition (/ \ | /, $ _); Print "$ _ \ n"; # Insert $ Sth-> to execute ($ area [0], $ area [1]); }  

and if the input is:

 a | 1b | C | 3 

Edit: Use this input instead.

 a | 1 | Xb || Xc | 3 | X 

this b. will fail.

 DBD :: Pg :: st Execution Failed: Error: invalid input syntax for integer: "" 

I just want to insert a null at Field 1. Any ideas?

EDIT: I made the input simpler in the last minute. The old input actually worked for some reason, so now I changed the input to something that would make the program fail. Also note that field 1 is an empty integer datatype.

I'm not sure that you have tested whether your pasted code and data are simultaneously, Work with 5.10.1, DBD :: PG 2.15.1 and PostGrace 8.4. Apart from this, you should use strict warnings and should not rely on the scope of the package for your variables.

If you change your code and data to use three or more fields, you can trigger the error with DBD :: PG, leaving a non-terminal blank. Before executing a prepared statement, add a line like this to your code:

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

to map empty string from field to field