Обсуждение: ERROR: invalid input syntax for integer: SQL state: 22P02

hi guys,
i am new to pgsql and i am facing a strange problem which i cannot solve.i  want to import data from a txt file to the table cust

the table is
 CREATE TABLE cust
(
   custid integer NOT NULL,
   custOccupation  varchar(30) NOT NULL,
  PRIMARY KEY(custid)
}  

and the txt file contains
13;developer
22;sales manager
  etc

i issue the following sql command
COPY cust
 FROM 'C:\Program Files\PostgreSQL\9.1\customers.txt' 
WITH DELIMITER ';'  ;

and i get an error
ERROR: invalid input syntax for integer: "13"
SQL state: 22P02

i would appreciate if you could help me

best regards and a happy new year!:)

yes that was i changed editor and worked !

On Sun, Jan 1, 2012 at 8:08 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

avalon78 geek <avalon78.gr@gmail.com> writes:
> i issue the following sql command
> COPY cust
>  FROM 'C:\Program Files\PostgreSQL\9.1\customers.txt'
> WITH DELIMITER ';'  ;

> and i get an error
> ERROR: invalid input syntax for integer: "13"
> SQL state: 22P02

That looks like you have a Unicode BOM marker at the start of the file.
There are some (pretty broken IMO) Windows editors that add such behind
your back in order to mark a text file as being text ... of course, it's
not plain text anymore, and BOM is useless/illegal in UTF-8 anyway, but
standards compliance was never Microsoft's strong suit.

Save the file with some other editor.

                       regards, tom lane

i haven't tried csv but i managed to make it work by  following tom's advice

On Sun, Jan 1, 2012 at 10:07 PM, Craig Barnes <cjbarnes18@gmail.com> wrote:

Hi,

On 1 January 2012 14:01, avalon78 geek <avalon78.gr@gmail.com> wrote:

hi guys,
i am new to pgsql and i am facing a strange problem which i cannot solve.i  want to import data from a txt file to the table cust

the table is
 CREATE TABLE cust
(
   custid integer NOT NULL,
   custOccupation  varchar(30) NOT NULL,
  PRIMARY KEY(custid)
}  

and the txt file contains
13;developer
22;sales manager
  etc

i issue the following sql command
COPY cust
 FROM 'C:\Program Files\PostgreSQL\9.1\customers.txt' 
WITH DELIMITER ';'  ;

and i get an error
ERROR: invalid input syntax for integer: "13"
SQL state: 22P02

i would appreciate if you could help me

best regards and a happy new year!:)

 

I have no experience using text mode but have you tried CSV Mode?

e.g.

COPY cust
 FROM 'C:\Program Files\PostgreSQL\9.1\

customers.txt' 
WITH DELIMITER ';' CSV ;

HTH, happy new year.


--
Craig

()  ascii ribbon campaign - against html mail
/\