Search code examples
pythonpostgresqlpsycopg2

Insert NULL for date /or time in Postgres when blank using psycopg2

I'm inserting records read from a file in date /or time field in Postgres. Many of the date/or time fields are blank in file. I want null to be inserted for the corresponding table fields. Postgres table col for date /or time is defined as date/ or time respectively. Using the query as below. I'm getting error : psycopg2.errors.InvalidDatetimeFormat: invalid input syntax for type date: " " Any directions as to where lies the problem will be helpful.

INSERT INTO userbasic
(name, create_date, owner_id, adsp, special,
 oper, revoke, grpacc, pwd_interval, pwd_date,
 programmer, defgrp_id, lastjob_time, lastjob_date, install_data)
VALUES 
(%s, NULLIF(%s,'')::date, %s, %s, %s, 
%s, %s, %s, %s, NULLIF(%s,'')::date, 
%s, %s,NULLIF(%s,'')::time, NULLIF(%s,'')::date, %s)

Expect the records with no value for date/ or time col to be inserted to table with NULL.

Solution

  • Adrian Klaver, many thanks for your inputs. I used the below code and NULLIF(%s,'')::date for the corresponding field in VALUES clause. This solved the problem.

    contain_space = True
for i in raw_record[14:24]:
    if i != ' ':
        contain_space = False
        break
if contain_space:
   self.create_date = ''
else:
   self.create_date = raw_record[14:24]