postgres / psycopg: Date format error importing CSV

Question

I'm using psycopg (v3) in python 3.10, working in Pycharm. I made a table and am trying to import a .csv; I'm getting this error:

invalid input syntax for type date: "01/31/22"

CONTEXT: COPY test_table, line 1, column quote_date: "01/31/22"

First I thought the DateStyle was incorrect so I added:

cur.execute('SET DateStyle = "ISO, MDY";')

Here's my full code:

import psycopg
from config import config

# Connect to an existing database
try:
    params = config()
    with psycopg.connect(**params) as conn:

        # Open a cursor to perform database operations
        with conn.cursor() as cur:
            cur.execute('SELECT version()')
            db_version = cur.fetchone()
            print(f'PostgreSQL database version: {db_version}')
            print('Connected to database.')

            cur.execute('SET DateStyle = "ISO, MDY";')

            cur.execute("""
                COPY test_table 
                FROM '/Users/.../copy.csv' 
                DELIMITER ',';""")

except(Exception, psycopg.DatabaseError) as error:
    print(error)

I'm still getting the same error. I checked the .csv in a text editor and it looks fine.
(The '...' in the directory was truncated in this post)

Answer 1

In my case it works:

    import psycopg2
    try:

        with psycopg2.connect(
            dbname="postgres",
            user="postgres",
            password="password",
            host="localhost",
            port=54321,
        ) as conn:

            # Open a cursor to perform database operations
            with conn.cursor() as cur:
                cur.execute('SELECT version()')
                db_version = cur.fetchone()
                print(f'PostgreSQL database version: {db_version}')
                print('Connected to database.')

                cur.execute('SET DateStyle = "ISO, MDY";')

                cur.execute("""
                    COPY test_table 
                    FROM '/tmp/dt.csv' 
                    (FORMAT csv, DELIMITER ',', HEADER true);""")

    except(Exception, psycopg2.DatabaseError) as error:
        print(error)

But i used psycopg2 instead psycopg. Also my dt.csv file has header:

dt
01/31/22
02/22/23

So i added HEADER true. DDL for table

CREATE TABLE test_table(
    dt DATE
)

PostgreSQL 11.4, psycopg2==2.8.6

Result table in pgAdmin: