Invalid Input Syntax when Uploading CSV to Postgres Table

Question

I'm fairly new to postgres. I am trying to copy over a file from my computer to a postgres server. I first initialize the table with

CREATE TABLE coredb (
id text, lng numeric(6,4), lat numeric(6,4),
score1 numeric(5,4), score2 numeric(5,4));

And my CSV looks like this:

     ID       lng      lat    score1   score2
      1     -72.298  43.218   0.561     0.894
      2     -72.298  43.218   0.472     0.970
      3     -72.285  43.250   0.322     0.959
      4     -72.285  43.250   0.370     0.934
      5     -72.325  43.173   0.099     0.976
      6     -72.325  43.173   0.099     0.985

However, when I try to copy the CSV over, I get the following error

COPY coredb FROM '/home/usr/Documents/filefordb.csv' DELIMITER ',' CSV;
ERROR:  invalid input syntax for type numeric: "lng"
CONTEXT:  COPY nhcore, line 1, column lng: "lng"

Oddly enough the csv imports just fine when I set the CREATE TABLE parameters to text for all the columns. Could someone explain why this is happening? I am using psql 9.4.1

Are you sure your CSV looks like that? That has no commas in it. It looks like you'd need to change your DELIMITER ',' to something better and use the WITH HEADER true option — vol7ron
– vol7ron, Commented Apr 23, 2015 at 18:41

Dmitri · Accepted Answer · 2015-04-23 18:41:13Z

2

You have to use HEADER true to tell COPY to skip the header line.

answered Apr 23, 2015 at 18:41

Dmitri

9,1675 gold badges39 silver badges43 bronze badges

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

Collectives™ on Stack Overflow

Invalid Input Syntax when Uploading CSV to Postgres Table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related