CODEWITHSUNDEEP
postgresqlcsvpgadmin
Litty
Litty

Reputation: 1876

"Invalid Input Syntax for Integer" in pgAdmin

I'm migrating data into Postgresql. I can generate my data into CSV or tab-delimited files, and I'm trying to import these files using pgAdmin.

An example CSV file looks exactly like this:

86,72,1,test
72,64,1,another test

The table I'm importing into looks like this:

CREATE TABLE common.category
(
  id integer NOT NULL,
  parent integer,
  user_id integer,
  name character varying(128),
  CONSTRAINT category_pkey PRIMARY KEY (id),
  CONSTRAINT category_parent_fkey FOREIGN KEY (parent)
      REFERENCES common.category (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
)

However, upon importing this example, pgAdmin complains about an Invalid Input Syntax for Integer: "86" on the first line.

What am I missing here? I've tried performing the same import using a tab-delimited file, I've tried converting to both Windows and Unix EOLs.

Upvotes: 3

Views: 11933

Answers (3)

Kevin Palembas
Kevin Palembas

Reputation: 13

I had the same error after creating a new text file in Windows Explorer and changing the file extension to .csv.

I copied columns from an existing CSV file in Excel to the new one, also in Excel. After reading @Litty's comment about it not being tab-delimited it made me wonder if that was my problem.

Sure enough, opening the files in Excel hid the tab delimiting. When I opened it in Notepad++ it was obvious. I had to Export->Change File Type->CSV (Comma delimited) before I could import the file using pgAdmin as a default CSV file.

Upvotes: 0

gocam
gocam

Reputation: 93

I came across the same problem. After 2 hours of google, this did solve it. I just re-added the first line of the csv file, and every thing goes well now.

Upvotes: 0

Cetin Basoz
Cetin Basoz

Reputation: 23827

Your sample have dependencies in the order of data imported. There is a foreign key 'parent' referencing 'id'. Having id 64 already in table, changing the order of your sample lines it imports just fine with:

COPY common.category
    FROM  'd:\temp\importme.txt'
    WITH CSV

Upvotes: 2

Related Questions