How to load csv with datetime into postgresql using copy_from

Question

I am trying to find a way to bulk load a csv file into postgresql. However, the data has datetime column with the format of "YYYY-MM-DD HH24:MI:SS". I couldn't find any documentation on how to bulk load date column using psycopg2 package in python 3.x. Can I get some help on this? Thanks in advance.

I am able to load the data using the below code:

cur.copy_from(dataIterator,'cmodm.patient_visit',sep=chr(31),size=8192,null='') conn.commit()

However, only the date part got loaded in the table. The time part was initialized:

2017-04-13 00:00:00 2017-04-13 00:00:00 2017-04-12 00:00:00

That format should not be a problem. Have you tried to load the data? If it did not work what was the error? For psycopg2 see Copy. — Adrian Klaver
– Adrian Klaver, Commented Oct 21, 2020 at 13:43
@AdrianKlaver, I am able to load the data using the below code: cur.copy_from(dataIterator,'cmodm.patient_visit',sep=chr(31),size=8192,null='') conn.commit() However, only the date part got loaded in the table. The time part was initialized: 2017-04-13 00:00:00 2017-04-13 00:00:00 2017-04-12 00:00:00 — Anand Rajakrishnan
– Anand Rajakrishnan, Commented Oct 21, 2020 at 22:00
Have you checked the CSV to make sure it actually contained timestamps and not just dates? — Belayer
– Belayer, Commented Oct 21, 2020 at 23:32
I have a basic rule: Never load a CSV into the destination table, always load to a staging table, even when an automated loads to the destination table directly and immediatly. I just do not trust the data content in spreadsheets as literately any junk can be thrown into any column. The staging table defines everything as text. I can validate and correct if necessary. In this case I would load to a staging table then edit/validate to the desired format. Then load into the final table knowing the data is valid. — Belayer
– Belayer, Commented Oct 23, 2020 at 0:34
@Belayer, I think I got my answer from our discussion. I'll funnel the date to copy_from after converting the date string to the format consumable by it. Thanks for your time and help. — Anand Rajakrishnan
– Anand Rajakrishnan, Commented Oct 23, 2020 at 20:15

Anand Rajakrishnan · Accepted Answer · 2020-10-29 16:30:01Z

1

After discussing with @Belayer, it was concluded that copy_from takes the timestamp input value in the format 'YYYY-MM-DD HH:MI:SS'. If the source has some other format, then that needs to be converted to the desired format mentioned before in this response before feeding it into copy_from.

answered Oct 29, 2020 at 16:30

Anand Rajakrishnan

1482 silver badges16 bronze badges

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

Collectives™ on Stack Overflow

How to load csv with datetime into postgresql using copy_from

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related