Import of CSV data into PostgreSQL using psycopg3 results in psycopg.errors.InvalidDatetimeFormat

Question

I have a CSV file my_table.csv that looks like the following:

"dt_start","my_int_value","my_double_value","dt_version"
"2022-01-02 00:00:00",2,2.2,"2022-01-02 00:00:00"
"2022-01-03 00:00:00",3,3.3,"2022-01-03 00:00:00"

Now I simply want to import this file into a table my_table of my PostgreSQL database from Python using the instructions from the psycopg3 package (using psycopg==3.1.3 and psycopg-binary==3.1.3).

My code looks as follows:

import os
import psycopg


table_name = "my_table"

conn = psycopg.connect(
    dbname="MY_DB",
    user="MY_USER",
    password="MY_PW",
    host="MY_HOST",
    port="MY_PORT",
)

with conn:
    with conn.cursor() as cur:

        # create table
        cur.execute(
            f"""
            CREATE TABLE IF NOT EXISTS {table_name} (
                dt_start TIMESTAMP NOT NULL,
                my_int_value INT NOT NULL,
                my_double_value DOUBLE PRECISION NOT NULL,
                dt_version TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY(dt_start, my_int_value, my_double_value, dt_version)
            )
            """
        )

        # clear table
        cur.execute(f"TRUNCATE {table_name}")
        conn.commit()

        # insert one row
        cur.execute(
            f"""INSERT INTO {table_name}"""
            + f""" (dt_start, my_int_value, my_double_value, dt_version)"""
            + f""" VALUES (%s, %s, %s, %s)""",
            ("2022-01-01 00:00:00", 1, 1.1, "2022-01-01 00:00:00"),
        )
        conn.commit()

        # fetch it
        cur.execute(f"""SELECT * FROM {table_name}""")
        print(cur.fetchall())

        # this breaks with "psycopg.errors.InvalidDatetimeFormat"
        with open(f"""{table_name}.csv""", "r") as f:
            with cur.copy(f"COPY {table_name} FROM STDIN") as copy:
                while data := f.read(100):
                    copy.write(data)
        conn.commit()

The first steps with some sample data work perfectly, but the CSV import breaks with an error such as:

psycopg.errors.InvalidDatetimeFormat: invalid syntax for type timestamp without time zone: »"dt_start","my_int_value","my_double_value","dt_version"«    
CONTEXT:  COPY my_table, Row 1, Column dt_start: »"dt_start","my_int_value","my_double_value","dt_version"«

Meanwhile, I have also tried different import variants from the docs and different datetime formats, but all result in the same error.

Any hints on how to fix this problem?

1) Do not use f strings to build dynamic SQL Use the psycopg sql module. 2) The issue is that "COPY {table_name} FROM STDIN assumes the CSV is in text format as defined here COPY and in that case separator is the tab character, \t not ,. Which is why you get »"dt_start","my_int_value","my_double_value","dt_version"« as being the value for column dt_start. Also you are reading the header as the first data line. — Adrian Klaver
– Adrian Klaver, Commented Oct 26, 2022 at 15:28
Thanks a lot, I totally missed the header :-) And good to know that there are better ways to build the queries which is why I chose your answer. — Cord Kaldemeyer
– Cord Kaldemeyer, Commented Oct 26, 2022 at 20:40
I think your comment would create additional value in your answer. — Cord Kaldemeyer
– Cord Kaldemeyer, Commented Oct 26, 2022 at 20:41

Adrian Klaver · Accepted Answer · 2022-10-26 17:41:56Z

4

create table import_test(dt_start timestamp, my_int_value integer, my_double_value float, dt_version timestamp);

cat import_test.csv
"dt_start","my_int_value","my_double_value","dt_version"
"2022-01-02 00:00:00",2,2.2,"2022-01-02 00:00:00"
"2022-01-03 00:00:00",3,3.3,"2022-01-03 00:00:00"

import psycopg
from psycopg import sql

con = psycopg.connect("dbname=test user=postgres host=localhost port=5432")

with open('import_test.csv') as f:
    with cur.copy(sql.SQL('COPY {} FROM STDIN WITH(FORMAT CSV, HEADER)').format(sql.Identifier('import_test'))) as copy:
                while data := f.read(100):
                    copy.write(data)

con.commit()

select * from import_test ;
      dt_start       | my_int_value | my_double_value |     dt_version      
---------------------+--------------+-----------------+---------------------
 2022-01-02 00:00:00 |            2 |             2.2 | 2022-01-02 00:00:00
 2022-01-03 00:00:00 |            3 |             3.3 | 2022-01-03 00:00:00

edited Oct 26, 2022 at 17:41

answered Oct 26, 2022 at 17:36

Adrian Klaver

20.3k3 gold badges24 silver badges40 bronze badges

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

3 Comments

GollyJer Over a year ago

Super helpful. Couldn't find this anywhere in the docs. Thanks. 💪

Adrian Klaver Over a year ago

Actually it was almost a direct lift from here Copy.

GollyJer Over a year ago

Yeah, I see what you mean. I wasn't able to put it all together until I read your answer. Thanks.

klin · Accepted Answer · 2022-10-26 14:51:23Z

1

The file contains a header, tell Postgres about that:

COPY my_table FROM STDIN (FORMAT csv, HEADER true)

Check the syntax in the documentation.

edited Oct 26, 2022 at 14:51

answered Oct 26, 2022 at 14:41

klin

123k15 gold badges240 silver badges262 bronze badges

3 Comments

Cord Kaldemeyer Over a year ago

Thanks for your answer. I had also tried without header before and somehow added it again. Nonetheless, this throws psycopg.errors.FeatureNotSupported: COPY HEADER only available in CSV mode.

Cord Kaldemeyer Over a year ago

And it does not work when I remove the header, either.

klin Over a year ago

I forgot to specify the format, answer updated.

Collectives™ on Stack Overflow

Import of CSV data into PostgreSQL using psycopg3 results in psycopg.errors.InvalidDatetimeFormat

2 Answers 2

3 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related