PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Column is imported as row in CSV file

When I try to copy a CSV file with pgAdmin or just COPY command it shows me this error

ERROR:  nieprawidłowa składnia wejścia dla typu integer: "1,"A Dijiang","M",24,180,80,"China","CHN","1992 Summer","1992","Summer","Barcelona","Basketball","Basketball Men's Basketball",NA"
CONTEXT:  COPY olympics_history, line 2, column ID: "1,"A Dijiang","M",24,180,80,"China","CHN","1992 Summer","1992","Summer","Barcelona","Basketball","Ba..." 

BŁĄD:  nieprawidłowa składnia wejścia dla typu integer: "1,"A Dijiang","M",24,180,80,"China","CHN","1992 Summer","1992","Summer","Barcelona","Basketball","Basketball Men's Basketball",NA"
SQL state: 22P02

I don’t know why it’s in Polish, but it just means invalid input syntax for integer type.

Here is table schema (ultra basic but I just need it for training)

 Column |       Type        | Collation | Nullable | Default
--------+-------------------+-----------+----------+---------
 ID     | integer           |           |          |
 name   | character varying |           |          |
 sex    | character varying |           |          |
 age    | character varying |           |          |
 height | character varying |           |          |
 weight | character varying |           |          |
 team   | character varying |           |          |
 noc    | character varying |           |          |
 games  | character varying |           |          |
 year   | integer           |           |          |
 season | character varying |           |          |
 city   | character varying |           |          |
 sport  | character varying |           |          |
 event  | character varying |           |          |
 medal  | character varying |           |          |

In the CSV file everything seems fine; the data is separated by commas etc. I just started learning and have no idea what can be wrong here.

I tried putting every header in "", recreating table.

Tags:

2

Answers


  1. 0

    Q: In the CSV file everything seems fine; the data is separated by commas
    etc. I just started learning and have no idea what can be wrong here.

    No the quoting is incorrect: the entire row of data is enclosed by double quotes, which is not permitted. How to quote is explained in RFC 4180, see in particular rules #5 and #7.

    The first line instead of being

    "1,"A Dijiang","M",24,180,80,"China","CHN","1992 Summer","1992","Summer","Barcelona","Basketball","Basketball Men's Basketball",NA"

    should be

    1,"A Dijiang","M",24,180,80,"China","CHN","1992 Summer","1992","Summer","Barcelona","Basketball","Basketball Men's Basketball",NA

    and whenever double quotes are present in the fields, they must be doubled.

    Login or Signup to reply.
  2. 0

    You probably forgot to specify the format when calling copy. By default, when you run something like this:

    copy test from '/tmp/payload.csv';

    COPY assumes your FORMAT is text, not csv, even if you feed it a file with a .csv extension:

    FORMAT
    Selects the data format to be read or written: text, csv (Comma Separated Values), or binary. The default is text.

    Problem with that is default delimiter for text is a tab, not a comma, and quote isn’t supported:

    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.

    QUOTE
    Specifies the quoting character to be used when a data value is quoted. The default is double-quote. This must be a single one-byte character. This option is allowed only when using CSV format.

    My bet is you just need explicitly specify the format, and it’ll work fine:

    copy test from '/tmp/payload.csv' csv quote '"' delimiter ',';

    See this example:

    copy (select $$1,"A Dijiang","M",24,180,80,"China","CHN","1992 Summer","1992","Summer","Barcelona","Basketball","Basketball Men's Basketball",NA$$)
to '/tmp/payload.csv';

create table test(
 ID     integer          ,
 name   character varying,
 sex    character varying,
 age    character varying,
 height character varying,
 weight character varying,
 team   character varying,
 noc    character varying,
 games  character varying,
 year   integer          ,
 season character varying,
 city   character varying,
 sport  character varying,
 event  character varying,
 medal  character varying); 

copy test from '/tmp/payload.csv';

    ERROR:  invalid input syntax for type integer: "1,"A Dijiang","M",24,180,80,"China","CHN","1992 Summer","1992","Summer","Barcelona","Basketball","Basketball Men's Basketball",NA"
CONTEXT:  COPY test, line 1, column id: "1,"A Dijiang","M",24,180,80,"China","CHN","1992 Summer","1992","Summer","Barcelona","Basketball","Ba..."

    copy test from '/tmp/payload.csv' csv quote '"' delimiter ',';

    COPY 1
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search