1
votes

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.

3
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
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
show us your table definition and import command please - Neil McGuigan
I've added an exact table definition. - Litty
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

3 Answers

2
votes

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 
0
votes

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.

0
votes

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.