"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.

Answer 1

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