"Invalid Input Syntax for Integer" in pgAdmin

Question

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.

The example file? I didn't say it was tab delimited. In addition to trying this comma delimited file, I also tried using a tab-delimited file. — Litty
– Litty, Commented Jul 28, 2015 at 20:49
I'm using pgAdmin. I right click the table I want to import into, and select "import". I select "CSV" or "text" for the filetype, put in the correct delimiter, put in the correct character set, make sure the correct columns are checked, and click "import". — Litty
– Litty, Commented Jul 28, 2015 at 20:51
worked fine for me. in the import data window, i chose the file and set format to csv and that's it. what encoding is your file in? what encoding is your db in? — Neil McGuigan
– Neil McGuigan, Commented Jul 28, 2015 at 20:59

Cetin Basoz · Accepted Answer · 2015-07-28 21:15:14Z

2

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

answered Jul 28, 2015 at 21:15

Cetin Basoz

24k3 gold badges34 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

gocam · Accepted Answer · 2016-12-15 10:55:44Z

0

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.

answered Dec 15, 2016 at 10:55

gocam

934 bronze badges

Comments

Kevin Palembas · Accepted Answer · 2019-05-03 05:24:15Z

0

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.

answered May 3, 2019 at 5:24

Kevin Palembas

134 bronze badges

Collectives™ on Stack Overflow

"Invalid Input Syntax for Integer" in pgAdmin

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related