6

I have a csv data set containing a date field which my may or may not be empty (=''). I've set Postgres to allow null on this field, and have given it the Date format. When I run my PHP import script (below), the import fails because the empty string is not in the date format. My understanding is that I should set it to NULL, which I want to do only if the field is in fact empty. So I want to conditionally set it, which I thought would work like:

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

When I execute the import via 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 into a pg_query? Should I be putting the conditional logic inside the query instead? My code is below. Thank you very 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!";}
S-Man
  • 22,521
  • 7
  • 40
  • 63
Matt C
  • 903
  • 1
  • 8
  • 15
  • I think that is something specific to postgresql, probably related [Postgresql: using 'NULL' value when insert and update rows with prepared statements](http://stackoverflow.com/questions/1027499/postgresql-using-null-value-when-insert-and-update-rows-with-prepared-stateme) - So better use a prepared statement probably so it's more clear if it's the actual value that creates the error or the wrong table definition. – hakre Jan 02 '12 at 17:40

5 Answers5

4

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]')

so if $data[0] is the string "NULL", you'll end up with this:

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

and you'll be trying to insert a string that contains NULL rather than the NULL literal itself. You'll have to do your quoting inside the $data values rather than 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 later:

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 prepared statements.

mu is too short
  • 426,620
  • 70
  • 833
  • 800
  • Various machinations with quote marks didn't help, but switching everything over to PDO and prepared statements worked a charm. Thanks. – Matt C Jan 15 '12 at 15:35
4

Mind using the NULLIF() PostgreSQL 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, i.e. when '$data[3]' == '', then NULL value will be returned.

Kouber Saparev
  • 7,637
  • 2
  • 29
  • 26
1

To Insert Null Value to 'Date' Data-type Column are :

--Create Table zt  

create table zt (id serial primary key, datecol date NULL) ;
insert into zt (datecol) values (NULLIF(NULL,'NULL')::date) ; --OK   
insert into zt (datecol) values (NULLIF(NULL,NULL)::date)   ; --OK   
insert into zt (datecol) values (NULLIF('NULL',NULL)::date) ; --ERROR
insert into zt (datecol) values (NULLIF('NULL',NULL)::date) ; --ERROR
select * from zt
Sajed
  • 1,797
  • 2
  • 7
  • 21
Fahwi
  • 31
  • 2
  • 1
    Welcome to Stack Overflow! While this code may solve the question, [including an explanation](//meta.stackexchange.com/q/114762) of how and why this solves the problem would really help to improve the quality of your post, and probably result in more up-votes. Remember that you are answering the question for readers in the future, not just the person asking now. Please [edit] your answer to add explanations and give an indication of what limitations and assumptions apply. – Yunnosch Mar 13 '22 at 07:54
  • 1
    Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Mar 13 '22 at 12:14
1

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

(Although, you said you tried that... Are you sure you got the same error for that?)

EDIT: Ah, took a closer look at your error message. Did you define the date field in the database as a "possibly null" value? I'm used to MySQL, mind you, so I could be wrong on that part. But anyway, try inserting '0000-00-00 00:00:00' as a representation of a null date.

Niet the Dark Absol
  • 320,036
  • 81
  • 464
  • 592
0

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

I hope this help you. Good luck.

phsaires
  • 2,188
  • 1
  • 14
  • 11