From several threads, I know there are two typical ways to write a pandas data frame to a Postgres database:
1 - Use pd.to_sql()
2 - Use psycopg2.copy_from (as suggested by this thread)
The consensus is that copy_from() is much faster. I would like to use this as I deal with millions of records.
However, copy_from() requires writing the dataframe to a buffer, such as StringIO first, in a csv format. My data frames always have a columns timestamp of Python datetime type. The csv format converts everything into string. This causes error when writing to a postgres table column timestamp of type timestamp. I would like to keep all columns in the Postgres tables in proper data types, including timestamp.
How can I use copy_from() for fast-writing and still maintain timestamp column in its proper format in the Postgres table?
