Thread: 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!:)
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!:)
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
Hi,
e.g.
COPY cust
FROM 'C:\Program Files\PostgreSQL\9.1\
--
Craig
() ascii ribbon campaign - against html mail
/\
On 1 January 2012 14:01, avalon78 geek <avalon78.gr@gmail.com> wrote:
I have no experience using text mode but have you tried CSV Mode?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!:)
e.g.
COPY cust
FROM 'C:\Program Files\PostgreSQL\9.1\
customers.txt'HTH, happy new year.
WITH DELIMITER ';' CSV ;
--
Craig
() ascii ribbon campaign - against html mail
/\
On Tue, Jan 3, 2012 at 1:30 PM, avalon78 geek <avalon78.gr@gmail.com> wrote:
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:That looks like you have a Unicode BOM marker at the start of the file.
> 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
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
On Tue, Jan 3, 2012 at 1:36 PM, avalon78 geek <avalon78.gr@gmail.com> wrote:
i haven't tried csv but i managed to make it work by following tom's adviceOn Sun, Jan 1, 2012 at 10:07 PM, Craig Barnes <cjbarnes18@gmail.com> wrote:Hi,I have no experience using text mode but have you tried CSV Mode?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!:)
e.g.
COPY cust
FROM 'C:\Program Files\PostgreSQL\9.1\customers.txt'HTH, happy new year.
WITH DELIMITER ';' CSV ;
--
Craig
() ascii ribbon campaign - against html mail
/\