SOLVED: postgres / psycopg: Date format error importing CSV-CodePudding

Solution: Needed to add the line (FORMAT csv, DELIMITER ',', HEADER true); instead of DELIMITER... as in my original code. See below for more info

Original Post: I'm new to this so any help is greatly appreciated. I needed to use/learn a database and chose PostgreSQL - open to ideas and been hitting a wall importing a CSV.

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)

CodePudding user response：

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:

CodePudding user response：

I think you want SQL, not ISO:

db=> SET datestyle='SQL, MDY';
SET
db=> SELECT to_char('2/17/22'::DATE, 'day dd month YYYY') ;
           to_char           
-----------------------------
 thursday  17 february  2022
(1 row)

And "22"? Didn't we learn anything from the Y2K mess?