1

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)

Nimantha
  • 6,405
  • 6
  • 28
  • 69
dbkc
  • 13
  • 3
  • Given that you are using `psycopg3` I would take a look at [COPY v3](https://www.psycopg.org/psycopg3/docs/basic/copy.html). Pretty sure with the changes in v3 you need to go this route. Even in `psycopg2` there was a direct way of using [COPY v2](https://www.psycopg.org/docs/usage.html#using-copy-to-and-copy-from). – Adrian Klaver Feb 17 '22 at 05:27
  • I've tried using the COPY_v3 method, do not get an error, but have no data in my table after running. – dbkc Feb 17 '22 at 06:20
  • In `psql` do `select '01/31/22'::date;` and see what happens? Add to your question the values returned by `show lc_ctype ;`, `show lc_time` and `show client_encoding`. Also what OS and version are you working on and what is its locale/encoding set to? Did the CSV file originate on this machine or come from somewhere else? – Adrian Klaver Feb 17 '22 at 16:26
  • Thanks for the help Adrian. Adding the line `(FORMAT csv, DELIMITER ',', HEADER true);` (from below) solved the input issue, even without the `SET DateStyle...` line of code. It worked in psycopg and psycopg2 for reference. – dbkc Feb 17 '22 at 18:46
  • I doubt `HEADER true` has anything to do with it and more to the point if your file does not actually have a header it will skip the first line of data. The import part is `FORMAT csv` as I suspect the values where quoted in the CSV file. – Adrian Klaver Feb 17 '22 at 18:53
  • I got curious and played around more. The values aren't quoted in the csv. The values look like this '1/31/22,xyz,2/7/22,abc' I removed the header and changed that last line to `(FORMAT csv);` got the original error. Tried `(FORMAT csv, HEADER false);` and got original error. Added the header back and tried `(FORMAT csv, HEADER true);` and it imported the data again. In case you're interested, the `select '01/31/22'::date` produced '2022-01-31' which is what lead me to try removing the `SET DateStyle...` code (which worked), my lc_ctype is C, lc_time is C, and encoding is UTF8. – dbkc Feb 17 '22 at 23:04
  • Add to your question the header line and the first data line from the file. Not sure if it was a copy/paste error, but your error was for `01/31/22` yet the data you show is `1/31/22`. – Adrian Klaver Feb 17 '22 at 23:55

2 Answers2

0

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?

Ture Pålsson
  • 6,088
  • 2
  • 12
  • 15
  • Nah, it doesn't matter: `SET DateStyle = "ISO, MDY"; select '01/31/22'::date; 2022-01-31 ` vs `SET DateStyle = "SQL, MDY"; select '01/31/22'::date; 01/31/2022`. The first part just affects the output, not reading the month and date order. ` – Adrian Klaver Feb 17 '22 at 05:30
  • Tested to be thorough, and Adrian is correct that it doesn't matter. I agree with preferring YYYY, I'm just trying to work with these .csv files that have the date as MM/DD/YY already in two fields. – dbkc Feb 17 '22 at 06:22
0

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:

table

Ihor Konovalenko
  • 1,298
  • 2
  • 16
  • 21
  • Thanks for the reply. I modified to use psycopg2, but still received the same error. The first field in my table has a type of 'date', I'm not sure if your table has the same type for its first field. psycopg2==2.9.3. I'm going to try a new .csv with dummy data in one row just to eliminate the variable being the file itself. – dbkc Feb 17 '22 at 06:35
  • Added DDL for table: single `DATE` column. CSV file structure exactly as in my ansver. – Ihor Konovalenko Feb 17 '22 at 08:26
  • Thank you Ihor, for reference the solution was to add the `(FORMAT csv, DELIMITER ',', HEADER true);` solved the issue. Did not need the `SET DateStyle...` line either. Thank you very much for your help!! – dbkc Feb 17 '22 at 18:43
  • Also, this fix worked in both psycopg and psycopg2 for reference – dbkc Feb 17 '22 at 18:46