How should I replace the NaN or None values for inserting timestamptz columns?

[ykparkwixon]

Driver version

2.0.888

Redshift version

PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.31651

Client Operating System

CentOS 7.9 on AWS EC2

Python version

python 3.6

Problem description

I can't insert empty values to timestamptz columns.

This is flow of my logic;

• get some data with other company's API
• make it to dataframe
• replace the NaN or None to empty character by df.fillna('')
• execute redshift_connector.write_dataframe(df, 'my_table')

Then it returns an error like this;
redshift_connector.error.ProgrammingError: {'S': 'ERROR', 'C': '22007', 'M': 'invalid input syntax for type timestamp with time zone: ""', 'F': '/home/ec2-user/padb/src/pg/src/backend/utils/adt/adt_datetime.c', 'L': '3121', 'R': 'DateTimeParseError'}

All columns are nullable.
How should I replace the NaN or None values?

[Brooke-white]

Hey @ykparkwixon,

You'll want to replace NaN values with None. I've run the following example locally without issue

import redshift_connector
import pandas as pd
import numpy as np

df = pd.DataFrame([[np.nan],
                   [np.nan],
                   [np.nan],
                   [None]],
                  columns=list("a"))
df = df.replace({np.nan: None})

with redshift_connector.connect(...) as conn:
    with conn.cursor() as cursor:
        cursor.execute("create table test (c1 timestamptz)")
        cursor.write_dataframe(df, 'test')
        cursor.execute("select * from test")
        data = cursor.fetchall()
        print(data)

>>> ([None], [None], [None], [None])

reference