Insert null values to postgresql timestamp data type using python

Question

I am tying to insert null value to a postgres timestamp datatype variable using python psycopg2.

The problem is the other data types such as char or int takes None, whereas the timestamp variable does not recognize None.

I tried to insert Null , null as a string because I am using a dictionary to get append the values for insert statement.

Below is the code.

queryDictOrdered[column] = queryDictOrdered[column] if isNull(queryDictOrdered[column]) is False else NULL

and the function is

def isNull(key):
    if str(key).lower() in ('null','n.a','none'):
        return True
    else:
        False

I get the below error messages:

DataError: invalid input syntax for type timestamp: "NULL"
DataError: invalid input syntax for type timestamp: "None"

Jennifer P Jennifer P · Accepted Answer · 2019-10-22T19:53:27

Empty timestamps in Pandas dataframes come through as NaT (not a time), which is NOT pg compatible with NULL. A quick work around is to send it as a varchar and then run these 2 queries:

 update <<schema.table_name>> set <<column_name>> = Null where
 <<column_name>> = 'NULL';

or (depending on what you hard coded empty values as)

update <<schema.table_name>> set <<column_name>> = Null where <<column_name>> = 'NaT';

Finally run:

alter table <<schema.table_name>> 
alter COLUMN <<column_name>>  TYPE timestamp USING <<column_name>>::timestamp without time zone;

Insert null values to postgresql timestamp data type using python

2 Answers