Error on CSV import into PostgreSQL: ERROR: invalid input syntax for type integer

Question

I'm very unfamiliar with SQL. This is my first time really using it.

I'm trying to import sales data (CSV), but keep getting this error when trying to import a csv file:

ERROR: invalid input syntax for type integer: "SalesID"CONTEXT: COPY sales_data, line 1, column salesid: "SalesID"

I created a table:

CREATE TABLE sales_data
(
    salesid SERIAL PRIMARY KEY,
    customerid INT,
    productid INT,
    sale_date DATE,
    sale_amount INT
);

I've also tried:

CREATE TABLE sales_data
(
    salesid INT,
    customerid INT,
    productid INT,
    sale_date DATE,
    sale_amount INT
);

I'm not even sure if I did that correctly, but it seems so to me.

I can't get past this error for each table I make.

I create the table, right click and use the import/export option. I get the error when I try importing.

I'm using PostgreSQL.

You should include a sample of the data being imported. There could be a type mismatch. — Tim Biegeleisen
– Tim Biegeleisen, Commented Aug 9, 2024 at 4:54
My guess, based from your limited information, is that you are trying to import data from a CSV file that has a header line, and you didn't tell Postgres that it had a header line. — Tim Roberts
– Tim Roberts, Commented Aug 9, 2024 at 4:58
@TimBiegeleisen I doubled checked my spreadsheet to clear any mistypes, including extra spaces. I copied and pasted from the spreadsheet as well. That was the only thing I thought it could be, but the error continues to pop up. — Ashley Molina Diaz
– Ashley Molina Diaz, Commented Aug 9, 2024 at 5:00
What data do you try to import? A csv file? I would just copy the file you can't import. Remove all rows but the header (if one exists) and the first row with data. Check if you can import it. If so, add some rows back until the issue appears again. Thus you see what row(s) have issues. — Jonas Metzler
– Jonas Metzler, Commented Aug 9, 2024 at 5:07
Comming from your Create Table, you defined your salesid as "SERIAL PRIMARY KEY". Not an expert on PostgreSQL but i think your salesid is an auto increment field. Meaning you can skip the salesid when you're importing your data and it will automatically assign an id for the record. You can read here as a reference. postgresqltutorial.com/postgresql-tutorial/postgresql-serial — Ikyong
– Ikyong, Commented Aug 9, 2024 at 5:10

Laurenz Albe · Accepted Answer · 2024-08-09 05:18:57Z

3

To skip a header line in a CSV file during import, use the HEADER option:

COPY tab FROM 'somefile' (FORMAT 'csv', DELIMITER ';', HEADER);

answered Aug 9, 2024 at 5:18

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

2 Comments

Matthew Flaschen Over a year ago

The delimiter may need to be a comma - ','

Laurenz Albe Over a year ago

@MatthewFlaschen Yes. I added the semicolon because the question has "excel" tagged, and I heard that the Microsoft program with that name produces CSV files with that separator.

Collectives™ on Stack Overflow

Error on CSV import into PostgreSQL: ERROR: invalid input syntax for type integer

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related