1

I have a CSV file file.csv.

In Postgres, I have made a table named grants:

CREATE TABLE grants
(

)
WITH (
  OIDS=FALSE
);
ALTER TABLE grants
  OWNER TO postgres;

I want to import file.csv data without having to specify columns in Postgres.

But if I run COPY grants FROM '/PATH/TO/grants.csv' CSV HEADER;, I get this error: ERROR: extra data after last expected column.

How do I import the CSV data without having to specify columns and types?

Username
  • 3,463
  • 11
  • 68
  • 111
  • 1
    http://stackoverflow.com/questions/21018256/can-i-automatically-create-a-table-in-postgresql-from-a-csv-file-with-headers – Mihai Feb 11 '17 at 20:29
  • 1
    Possible duplicate of [Can I automatically create a table in PostgreSQL from a csv file with headers?](http://stackoverflow.com/questions/21018256/can-i-automatically-create-a-table-in-postgresql-from-a-csv-file-with-headers) – Renzo Feb 11 '17 at 20:41

3 Answers3

1

The error is normal. You created a table with no column. The COPY command try to import data into the table with the good structure. So you have to create the table corresponding to your csv file before execute the COPY command.

I discovered pgfutter :

"Import CSV and JSON into PostgreSQL the easy way. This small tool abstract all the hassles and swearing you normally have to deal with when you just want to dump some data into the database"

Perhaps a solution ...

Romain MAZIERE
  • 46
  • 5
0

No, it is not possible using the COPY command

If a list of columns is specified, COPY will only copy the data in the specified columns to or from the file. If there are any columns in the table that are not in the column list, COPY FROM will insert the default values for those columns.

COPY does not create columns for you.

ACV
  • 9,964
  • 5
  • 76
  • 81
-1

The best method for me was to convert the csv to dataframe and then follow

https://github.com/sp-anna-jones/data_science/wiki/Importing-pandas-dataframe-to-postgres

Rony Armon
  • 178
  • 1
  • 3
  • 8