Postgres: INVALID input syntax for type numeric: "2021-02-14" ... but it's in datetime format?

Question

I'm very confused about this error I'm getting in my Query Tool in PgAdmin. I've been working on this for days, and cannot find a solution to fixing this error when attempting to upload this csv file to my Postgres table.

ERROR:  invalid input syntax for type numeric: "2021-02-14"
CONTEXT:  COPY CardData, line 2, column sold_price: "2021-02-14"
SQL state: 22P02

Here is my code in the Query Tool that I am running

CREATE TABLE Public."CardData"(Title text, Sold_Price decimal, Bids int, Sold_Date date, Card_Link text, Image_Link text)

select * from Public."CardData"

COPY Public."CardData" FROM 'W:\Python_Projects\cardscrapper_project\ebay_api\card_data_test.csv' DELIMITER ',' CSV HEADER ;

Here is a sample from the first row of my csv file.

Title,Sold_Date,Sold_Price,Bids,Card_Link,Image_Link
2018 Contenders Optic Sam Darnold #103 Red Rookie #/99 PSA 8 NM-MT AUTO 10,2021-02-14,104.5,26,https://www.ebay.com/itm/2018-Contenders-Optic-Sam-Darnold-103-Red-Rookie-99-PSA-8-NM-MT-AUTO-10/143935698791?hash=item21833c7767%3Ag%3AjewAAOSwNb9gGEvi&LH_Auction=1,https://i.ebayimg.com/thumbs/images/g/jewAAOSwNb9gGEvi/s-l225.jpg

The "Sold_Date" column is in the correct datetime format that is easy for Postgres to understand, but the error is calling on the "Sold-Price" column?

I'm very confused. Any help is greatly appreciated.

JGH · Accepted Answer · 2021-02-16 19:43:13Z

4

Notice that the columns are not in the same order in the csv file and in the table.

You would have to specify the proper column order

COPY Public."CardData" (Title,Sold_Date,Sold_Price,Bids,Card_Link,Image_Link) 
FROM 'W:\Python_Projects\cardscrapper_project\ebay_api\card_data_test.csv' 
DELIMITER ',' CSV HEADER ;

answered Feb 16, 2021 at 19:43

JGH

18.2k5 gold badges41 silver badges58 bronze badges

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

1 Comment

JonShish Over a year ago

You are a life saver. Thank you!

richyen · Accepted Answer · 2021-02-16 19:52:11Z

0

You have created the table with sold_price as the second column, so the COPY command will expect a price/number to be the second column in your CSV file. Your CSV file, however has sold_date as the second column, which will lead to the data type mismatch error that you see.

Either you can re-define your CREATE TABLE statement with the sold_date as second column and sold_price as 4th column, or you can specify the column parsing order in your COPY statement as COPY public."CardData" (<column order>)

Another option is to open up the CSV file in Excel and re-order the columns and do a Save As...

answered Feb 16, 2021 at 19:52

richyen

10.3k4 gold badges18 silver badges36 bronze badges

Collectives™ on Stack Overflow

Postgres: INVALID input syntax for type numeric: "2021-02-14" ... but it's in datetime format?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related