11

When trying to use the COPY command via SQL in Postgres 9.5.1 in a simple example database…

I am getting this error:

ERROR:  invalid input syntax for integer: "Sally"
CONTEXT:  COPY customer_, line 2, column id_: "Sally"

********** Error **********

ERROR: invalid input syntax for integer: "Sally"
SQL state: 22P02
Context: COPY customer_, line 2, column id_: "Sally"

…when importing this data in CSV (comma-separated value):

"first_name_","last_name_","phone_","email_"
"Sally","Jones","425.555.1324","s.jones@acme.com"
"Jarrod","Barkley","206.555.3454","j.barkley@example.com"
"Wendy","Melvin","415.555.2343","wendy@wendyandlisa.com"
"Lisa","Coleman","425.555.7282","lisa@wendyandlisa.com"
"Jesse","Johnson","507.555.7865","j.j@guitar.com"
"Jean-Luc","Martin","212.555.2244","jean-luc.martin@example.com"

…being imported via the following SQL executed in pgAdmin:

COPY customer_
FROM '/home/parallels/Downloads/customer_.csv'
CSV 
HEADER
;

…into this table:

-- Table: public.customer_

-- DROP TABLE public.customer_;

CREATE TABLE public.customer_
(
  id_ integer NOT NULL DEFAULT nextval('customer__id__seq'::regclass),
  first_name_ text NOT NULL,
  last_name_ text NOT NULL,
  phone_ text NOT NULL DEFAULT ''::text,
  email_ text NOT NULL DEFAULT ''::text,
  CONSTRAINT pkey_customer_ PRIMARY KEY (id_)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.customer_
  OWNER TO postgres;
COMMENT ON TABLE public.customer_
  IS 'Represents a person  whose pets visit our clinic.';

So it seems the first row containing the names of the columns is being processed successfully. The failure point is with the first data value in the first data line of the CSV. None of my imported data is of integer type, so the I am befuddled by the error message. The only integer is the id_ primary key, auto-incrementing SERIAL.

I did read the Question page on PG COPY error: invalid input syntax for integer. But that question did involve integer values, and the lack thereof in an empty quoted string being interpreted as a NULL. In my case here we have no integer values in the data; the only integer is the primary key SERIAL column with a DEFAULT generated value (not in the data being imported).

I also found the Question, PostgreSQL ERROR: invalid input syntax for integer. But it seems irrelevant.

Community
  • 1
  • 1
Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154

3 Answers3

17

Try specifying the columns . . . without the primary key:

COPY customer_ (first_name_ text, last_name_ text, phone_ text, email_ text)
FROM '/home/parallels/Downloads/customer_.csv'
CSV 
HEADER
;

Without the column list, it is looking for a value for id_.

The import data file’s first row of column names are not used for mapping to the table columns. The HEADER flag merely tells Postgres to skip over that first line, as documented:

HEADER

Specifies that… on input, the first line is ignored. …

Community
  • 1
  • 1
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    Isn’t the whole point of `HEADER` to not specify? Anyways, I tried your suggestion (but omitting the 'text' verbiage). Got a different error, still mysterious, but a different mystery as it seems to have processed the previously offending row. I ran `COPY customer_ ( first_name_ , last_name_ , phone_ , email_ ) FROM '/home/parallels/Downloads/customer_.csv' CSV HEADER ;` and got error `ERROR: missing data for column "last_name_" CONTEXT: COPY customer_, line 8: "" ********** Error ********** ERROR: missing data for column "last_name_" SQL state: 22P04 Context: COPY customer_, line 8: ""` – Basil Bourque Apr 02 '16 at 02:59
  • I discovered that last error was due to a **blank line at the end** of the CSV data file. Postgres is finicky about its CSV. So this Answer is a successful workaround. But I do not understand the point of `HEADER` in the `COPY` command if I am still required to spell out the column names. – Basil Bourque Apr 02 '16 at 03:07
  • 1
    Closer reading of the [doc on `COPY`](http://www.postgresql.org/docs/current/static/sql-copy.html) reveals that the column names are ignored when reading in a file. The `HEADER` flag merely tells Postgres to skip that first line (and on exporting tells Postgres to generate that first line). So specifying the column names as shown in this Answer is required as a feature, not a bug. – Basil Bourque Apr 02 '16 at 19:03
  • Thank you!! I was not specifying the column names and was getting the same error as the OP. After specifying the column names, this worked for me. – allardbrain Nov 12 '17 at 00:51
4
COPY table_name FROM 'C:\path\file.csv' DELIMITERS ',' CSV header;
Suraj Rao
  • 29,388
  • 11
  • 94
  • 103
Amar
  • 71
  • 4
  • yes when "csv header" is used then header is not read from csv file and successful import into postgres – Amar Jul 11 '19 at 11:21
  • @amardobliyal As harmonica141 suggested, you should include some explanation in your Answer. Stack Overflow is meant to be more than a snippet library. – Basil Bourque Jul 11 '19 at 19:16
  • @Amar note - `header` takes a boolean argument and defaults to `TRUE`, so this would actually specify that a header _is_ present – scry Feb 08 '22 at 14:31
0

This wasn't the OP's problem, but posting because this is one of the top results when I google the error message.

I was trying to import a .csv file with no header. Adding a header to the file and changing COPY ... CSV to COPY ... CSV HEADER in the sql command fixed the problem for me.

declan
  • 5,605
  • 3
  • 39
  • 43