使用psycopg3导入CSV到PostgreSQL时出现InvalidDatetimeFormat错误

我有一个名为my_table.csv的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"

使用psycopg3（版本psycopg==3.1.3、psycopg-binary==3.1.3）将文件导入PostgreSQL的my_table表时，创建表、单条插入、查询操作均正常，但CSV导入时报错：

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"«

错误原因是PostgreSQL默认将CSV的第一行视为数据行，而你的CSV第一行是表头，字符串"dt_start"无法解析为timestamp类型。解决核心是告诉数据库跳过表头行，以下是两种可行实现方式：

方式1：使用copy_from并指定header=True #

import psycopg

# 替换为你的数据库连接参数
conn_params = "dbname=your_database user=your_user password=your_password host=your_host"

with psycopg.connect(conn_params) as conn:
    with conn.cursor() as cur:
        with open("my_table.csv", "r", encoding="utf-8") as f:
            cur.copy_from(
                file=f,
                table="my_table",
                sep=",",
                header=True,  # 关键：告知数据库第一行是表头
                columns=["dt_start", "my_int_value", "my_double_value", "dt_version"]  # 确保列顺序匹配
            )
        conn.commit()

方式2：使用copy_expert执行原生COPY命令 #

通过原生SQL明确指定CSV格式和表头选项：

import psycopg

conn_params = "dbname=your_database user=your_user password=your_password host=your_host"

with psycopg.connect(conn_params) as conn:
    with conn.cursor() as cur:
        with open("my_table.csv", "r", encoding="utf-8") as f:
            cur.copy_expert(
                sql="""
                COPY my_table FROM STDIN WITH (
                    FORMAT CSV,
                    HEADER,  # 跳过表头行
                    DELIMITER ',',
                    QUOTE '"'
                )
                """,
                file=f
            )
        conn.commit()

额外注意事项 #

• 确保CSV文件的列顺序与数据库表的列顺序一致；若不一致，需在copy_from的columns参数中明确对应关系，或在COPY命令中指定列列表（如COPY my_table (dt_start, my_int_value, ...) FROM STDIN ...）。
• 确认数据库表的字段类型与CSV数据类型匹配：dt_start和dt_version为timestamp without time zone，my_int_value为integer，my_double_value为double precision。

内容的提问来源于stack exchange，提问作者Cord Kaldemeyer