Insert NULL in PostgresSQL DB via PHP when date field is empty

Question

I have a csv dataset containing a date field, which may or may not be empty (='') . I installed Postgres to allow null in this field, and gave it a date format. When I run my import PHP script (below), import is not possible because the empty string is not in the date format. I understand that I have to set it to NULL , which I want to do only if the field is really empty. Therefore, I want to conditionally install it, which, as I thought, would work as follows:

  <?php if (empty($data[3])){ $data[3] = NULL; // (i've tried "null", 'null', "NULL", null, etc.) 

When I import using this method, whenever the date field is empty, I get PHP Warning: pg_query(): Query failed: ERROR: invalid input syntax for type date: "NULL" . Can PHP not pass NULL to pg_query ? Should I put conditional logic in a query? My code is below. Thanks so much for any advice.

 <?php $conn_string = "host=localhost port=5432 dbname=mydb user=myusername password=mypassword"; $_db = pg_connect($conn_string); $fileName = "../feeds/stale_prod_report.20111215.csv"; $row = 0; if ($_db->connect_error) { die('Connection Error (' . $_db->connect_errno . ') ' . $_db->connect_error); } if (($handle = fopen($fileName, "r")) !== FALSE) { while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) { $num = count($data); $row++; for ($c=0; $c < $num; $c++) { $data[$c] = pg_escape_string(utf8_encode($data[$c])); } //if date is empty, insert a dummy - not accepting null if (empty($data[16])){ $data[16] = NULL; } if($row !== 1){ pg_query($_db, "INSERT INTO product (first_field, second_field, third_field, my_date) VALUES ('$data[0]', '$data[1]', '$data[2]', '$data[3]')"; } fclose($handle); } else {echo "Could not find the file!";} 

Answer 1

Your problem is that you have single quotes inside your SQL:

 INSERT INTO product (first_field, second_field, third_field, my_date) VALUES ('$data[0]', '$data[1]', '$data[2]', '$data[3]') 

therefore, if $data[0] is the string "NULL" , you will get the following:

 INSERT INTO product (first_field, second_field, third_field, my_date) VALUES ('NULL', ... 

and you try to insert a string containing NULL, not a literal NULL. You will have to do your quotes inside the $data values, and not inside your SQL:

 # Warning: my PHP is a bit rusty but I think this is right if(empty($data[0])) { $data[0] = "NULL"; } else { $data[0] = "'" . pg_escape_string(utf8_encode($data[$c])) . "'"; } 

And then:

 pg_query($_db, "INSERT INTO product (first_field, second_field, third_field, my_date) VALUES ($data[0], $data[1], $data[2], $data[3])"; 

Or better, switch to PDO and use the prepared instructions.

Answer 2

Pay attention to the PostgreSQL NULLIF () function.

 <?php pg_query($_db, "INSERT INTO product (first_field, second_field, third_field, my_date) VALUES ('$data[0]', '$data[1]', '$data[2]', NULLIF('$data[3]', ''))"; ?> 

When the arguments are equal, that is, when '$ data [3]' == '', then NULL is returned.

Answer 3

Use $data[3] = 'null' - null as a string, so when you insert it into the query, it looks like null , not .

(Although, you said you tried this ... Are you sure that you have the same error?)

EDIT: Ah, take a closer look at your error message. Have you defined the date field in the database as a "possibly zero" value? I'm used to MySQL, mind you, so I could be wrong in this part. But either way, try pasting '0000-00-00 00:00:00' as a representation of the zero date.

Answer 4

The syntax for NULLIF is: NULLIF ('$ emptysstring', '') :: int. WHERE ":: int" is a relative integer.

Hope this helps you. Good luck.