Insert NULL into a PostgreSQL DB via PHP when a date field is empty

Question

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!";}

I think that is something specific to postgresql, probably related Postgresql: using 'NULL' value when insert and update rows with prepared statements - 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
– hakre, Commented Jan 2, 2012 at 17:40

mu is too short · Accepted Answer · 2012-01-02 17:50:49Z

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.

answered Jan 2, 2012 at 17:50

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Matt C Over a year ago

Various machinations with quote marks didn't help, but switching everything over to PDO and prepared statements worked a charm. Thanks.

Kouber Saparev · Accepted Answer · 2014-09-03 13:43:50Z

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.

edited Sep 3, 2014 at 13:43

answered Jan 2, 2012 at 19:42

Kouber Saparev

8,2552 gold badges32 silver badges28 bronze badges

Comments

Niet the Dark Absol · Accepted Answer · 2012-01-02 17:33:35Z

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.

answered Jan 2, 2012 at 17:33

Niet the Dark Absol

326k86 gold badges480 silver badges604 bronze badges

Comments

Sajed · Accepted Answer · 2022-03-14 09:26:20Z

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

edited Mar 14, 2022 at 9:26

Sajed

2,0362 gold badges11 silver badges22 bronze badges

answered Mar 13, 2022 at 7:22

Fahwi

312 bronze badges

2 Comments

Yunnosch Over a year ago

Welcome to Stack Overflow! While this code may solve the question, including an explanation 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.

Community Over a year ago

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.

phsaires · Accepted Answer · 2012-08-09 12:21:02Z

0

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

I hope this help you. Good luck.

answered Aug 9, 2012 at 12:21

phsaires

2,4281 gold badge16 silver badges11 bronze badges

Collectives™ on Stack Overflow

Insert NULL into a PostgreSQL DB via PHP when a date field is empty

5 Answers 5

1 Comment

Comments

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related