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

Question

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.

My guess the blank values that are being supplied are more along the lines of ' ', more then one space. Your NULLIF(%s,'') is only going to catch true empty strings. A print(len(variable)) will help verify this. You will need to normalize the data to empty strings, either on the Python side or the SQL side. — Adrian Klaver
– Adrian Klaver, Commented May 4, 2023 at 18:30
You might also want to look at Postgres log to see what is actually hitting database. — Adrian Klaver
– Adrian Klaver, Commented May 4, 2023 at 18:38

Huzaifa · Accepted Answer · 2023-05-04 18:41:40Z

1

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

answered May 4, 2023 at 18:41

Huzaifa

4254 silver badges8 bronze badges

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

1 Comment

Adrian Klaver Over a year ago

1) Passing a string is permissable: select '2023-05-04'::date; 05/04/2023 2) Read the OP's question, in particular the query. It is already using NULLIF. 3) Change the 'answer' to one that is actually relevant or delete it.

Nupur · Accepted Answer · 2023-05-14 15:26:45Z

1

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]

answered May 14, 2023 at 15:26

Nupur

8910 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related