sql - pgAdmin4: Importing a CSV

Question

I am trying to import a CSV using pgAdmin4. I created the table using the query,

CREATE TABLE i210_2017_02_18
(
  PROBE_ID character varying(255),
  SAMPLE_DATE timestamp without time zone,
  LAT numeric,
  LON numeric,
  HEADING integer,
  SPEED integer,
  PROBE_DATA_PROVIDER character varying(255),
  SYSTEM_DATE timestamp without time zone
)

The header and first line of my CSV read is...

PROBE_ID,SAMPLE_DATE,LAT,LON,HEADING,SPEED,PROBE_DATA_PROVIDER,SYSTEM_DATE
841625st,2017-02-18 00:58:19,34.11968,-117.80855,91.0,9.0,FLEET53,2017-02-18 00:58:58

When I try to use the import dialogue, the process fails with Error Code 1:

ERROR:  invalid input syntax for type timestamp: "SAMPLE_DATE"
CONTEXT:  COPY i210_2017_02_18, line 1, column sample_date: "SAMPLE_DATE"

Nothing seems wrong to me - any ideas?

Answer 1

According to your table structure, this import will fail in the columns HEADING and SPEED, since their values have decimals and you declared them as INTEGER. Either remove the decimals or change the column type to e.g. NUMERIC.

Having said that, just try this from pgAdmin (considering that file and database are in the same server):

COPY i210_2017_02_18 FROM '/home/jones/file.csv' CSV HEADER;

In case you're dealing with a remote server, try this using psql from your console:

$ cat file.csv | psql yourdb -c "COPY i210_2017_02_18 FROM STDIN CSV HEADER;"

You can also check this answer.

In case you really want to stick to the pgAdmin import tool, which I discourage, just select the Header option and the proper Delimiter: