Question 168690 in PostgreSQL Question posted in
The official documentation can be found here.

Can psycopg2 insert NULL for blank date/time in Postgresql?

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.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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]

  2. 0

    You here are passing "STRING" as value in date-time field.

    To insert "NULL" in this field you can use "NULLIF()" function(example here). This function will convert empty string to NULL. Now, you need to convert this to "date-time" type, for this you can use "to_date()" function or just typecast it.

    This query might help:

    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,
      CASE WHEN NULLIF(%s, '') = '' THEN NULL ELSE to_date(%s, 'YYYY-MM-DD') END
)

    you might need to use to_timestamp() function to convert a string in a specific time format

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search