Re: - Mailing list pgsql-general

Laurenz Albe wrote:

> On Tue, 2019-07-02 at 13:09 +0530, Prakash Ramakrishnan wrote:
> > We are getting below error while during import the csv file please do the needful.
>
> I'd say the needful thing here is for you to read the documentation...
>
> > -bash-4.2$ more ckr_sto.csv
> >  4937880 |     12 | 2015-01-05  |            | 2015-01-05 05:51:47 |         |        | 5000128790679 |           |           |           |
> >  4939355 |     12 | 2015-01-05  |            | 2015-01-05 05:51:47 |         |        | 5000128639345 |           |           |           |
> >  4939744 |     12 | 2015-01-05  |            | 2015-01-05 05:51:47 |         |        | 5000128684510 |           |           |           |
> >  4939750 |     12 | 2015-01-05  |            | 2015-01-05 05:51:47 |         |        | 5000128683100 |           |           |           |
> >  4936360 |     12 | 2015-01-05  |            | 2015-01-05 05:51:47 |         |        | 5000128567527 |           |           |           |
> >  4940308 |     12 | 2015-01-05  |            | 2015-01-05 05:51:47 |         |        | 5000128781329 |           |           |           |
> >  4938006 |     12 | 2015-01-05  |            | 2015-01-05 05:51:47 |         |        | 4000128912554 |           |           |           |
> >  4937457 |     12 | 2015-01-05  |            | 2015-01-05 05:51:47 |         |        | 5000128426574 |           |           |           |
> >
> > error
> > ----------
> > postgres=# \copy ckr_sto from /data/ckr_sto.csv CSV HEADER;
> > ERROR:  invalid input syntax for integer: " 4939355 |     12 | 2015-01-05  |            | 2015-01-05 05:51:47 |         |        | 5000128639345 |           |           |           | "
> > CONTEXT:  COPY ckr_sto, line 2, column pod_id: " 4939355 |     12 | 2015-01-05  |            | 2015-01-05 05:51:47 |         |        | 500012863934..."
>
> >From the documentation of COPY:
>
> DELIMITER
>
>     Specifies the character that separates columns within each row (line) of the file.
>     The default is a tab character in text format, a comma in CSV format.
>     This must be a single one-byte character. This option is not allowed when using binary format.
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com

in other words, ckr_sto.csv is not a csv file.
it just has .csv at the end of its name.
that's why psql tried to interpret the entire
line as the first column: there were no commas.

its contents should look something like:

4937880,12,2015-01-05,,2015-01-05 05:51:47,,,5000128790679,,,,
4939355,12,2015-01-05,,2015-01-05 05:51:47,,,5000128639345,,,,
4939744,12,2015-01-05,,2015-01-05 05:51:47,,,5000128684510,,,,
4939750,12,2015-01-05,,2015-01-05 05:51:47,,,5000128683100,,,,
4936360,12,2015-01-05,,2015-01-05 05:51:47,,,5000128567527,,,,
4940308,12,2015-01-05,,2015-01-05 05:51:47,,,5000128781329,,,,
4938006,12,2015-01-05,,2015-01-05 05:51:47,,,4000128912554,,,,
4937457,12,2015-01-05,,2015-01-05 05:51:47,,,5000128426574,,,,