From stewes@tewes.homelinux.net Fri Apr  30 22:46:57 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 4E4C7D1E7A8
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 30 Apr 2004 19:46:57 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 82236-08
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 30 Apr 2004 19:46:38 -0300 (ADT)
Received: from iomega.cfc.org (port-212-202-190-202.reverse.qdsl-home.de
	[212.202.190.202])
	by svr1.postgresql.org (Postfix) with ESMTP id 43D03D1E8D1
	for <pgsql-sql@postgresql.org>; Fri, 30 Apr 2004 19:46:35 -0300 (ADT)
Received: from 2864id (tenshi@[172.16.0.10])
	by iomega.cfc.org (8.12.9/8.12.4) with SMTP id i3UMkYQv004866
	for <pgsql-sql@postgresql.org>; Sat, 1 May 2004 00:46:34 +0200
Message-ID: <000801c42f04$ba09b6f0$0a0010ac@2864id>
From: "Sebastian Tewes" <stewes@tewes.homelinux.net>
To: <pgsql-sql@postgresql.org>
Subject: invalid input syntax for integer: ""
Date: Sat, 1 May 2004 00:44:40 +0200
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0005_01C42F15.7CED6D50"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Spam-Status: No, hits=3.5 tagged_above=0.0 required=5.0 tests=HTML_30_40,
	HTML_MESSAGE, RCVD_IN_DYNABLOCK, RCVD_IN_SORBS
X-Spam-Level: ***
X-Archive-Number: 200405/28
X-Sequence-Number: 17742

This is a multi-part message in MIME format.

------=_NextPart_000_0005_01C42F15.7CED6D50
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hello :-)

got a little prob...=20

so my table=20

create table warengruppen ( kennung char (1),
                                holder int,
                     HauptWarenGruppe int,
                        BezHWG varchar (50),
                          WarenGruppe int,
                         BezWG varchar (50));


the copy syntax=20

copy warengruppen FROM '/usr/pgsql/datanorm.wrg' with DELIMITER ';';

and a smal part of my 'datanorm.wrg'


S;;011;Dachsteine+Formst.;;;
S;;011;;0111;Dachst.glatt(Biber);
S;;011;;0112;Dachst.glatt(Tegal.);
S;;011;;0114;Dachst.mulde(Donau);
S;;011;;0116;Dachst.symm.(Frankf);
S;;011;;0118;Dachst.asym.(Dop.-S);
S;;011;;0119;Dachst.Sonstige;
S;;012;Dachziegel+Formst.;;;
S;;012;;0121;Biberschwanzziegel;
S;;012;;0122;Hohlz.+Krempz.;
S;;012;;0123;Mnch + Nonne;
S;;012;;0124;Strangfalzziegel;
S;;012;;0125;Falzz.(Doppelmulde);
S;;012;;0126;Flachdachziegel;
S;;012;;0127;Verschiebeziegel;
S;;012;;0129;Ziegel Sonstige;
S;;013;Faserzementplatten;;;
S;;013;;0131;Dach+Fassadenplatten;
S;;013;;0133;Wellplatten;
S;;013;;0135;Tafeln;
S;;013;;0137;Elemente;
S;;014;Fassadenpl.speziell;;;
S;;014;;0141;Asphalt;

and the error message=20

ERROR:  invalid input syntax for integer: ""
CONTEXT:  COPY warengruppen, line 1, column holder: ""

i know it's hard to fill the integer colume    holder    with nothing out o=
f csv table.. but it's the same with the colume   WarenGruppe so i can't us=
e a char type... so mybe someone know a litte trick to save me ?? don't lik=
e to reformat 10MB datasource ...

thank's=20

Sebastian
=20

------=_NextPart_000_0005_01C42F15.7CED6D50
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1400" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV>Hello :-)</DIV>
<DIV>&nbsp;</DIV>
<DIV>got a little prob... </DIV>
<DIV>&nbsp;</DIV>
<DIV>so my table </DIV>
<DIV>&nbsp;</DIV>
<DIV><EM>create table warengruppen ( kennung char=20
(1),<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
holder=20
int,<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
HauptWarenGruppe=20
int,<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
BezHWG varchar=20
(50),<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;=20
WarenGruppe=20
int,<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;=20
BezWG varchar (50));</EM></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV>the copy syntax </DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><EM>copy warengruppen FROM '/usr/pgsql/datanorm.wrg' with DELIMITER=20
';';</EM></DIV>
<DIV><EM></EM>&nbsp;</DIV>
<DIV>and a smal part of my 'datanorm.wrg'</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial=20
size=3D2><EM>S;;011;Dachsteine+Formst.;;;<BR>S;;011;;0111;Dachst.glatt(Bibe=
r);<BR>S;;011;;0112;Dachst.glatt(Tegal.);<BR>S;;011;;0114;Dachst.mulde(Dona=
u);<BR>S;;011;;0116;Dachst.symm.(Frankf);<BR>S;;011;;0118;Dachst.asym.(Dop.=
-S);<BR>S;;011;;0119;Dachst.Sonstige;<BR>S;;012;Dachziegel+Formst.;;;<BR>S;=
;012;;0121;Biberschwanzziegel;<BR>S;;012;;0122;Hohlz.+Krempz.;<BR>S;;012;;0=
123;Mnch=20
+=20
Nonne;<BR>S;;012;;0124;Strangfalzziegel;<BR>S;;012;;0125;Falzz.(Doppelmulde=
);<BR>S;;012;;0126;Flachdachziegel;<BR>S;;012;;0127;Verschiebeziegel;<BR>S;=
;012;;0129;Ziegel=20
Sonstige;<BR>S;;013;Faserzementplatten;;;<BR>S;;013;;0131;Dach+Fassadenplat=
ten;<BR>S;;013;;0133;Wellplatten;<BR>S;;013;;0135;Tafeln;<BR>S;;013;;0137;E=
lemente;<BR>S;;014;Fassadenpl.speziell;;;<BR>S;;014;;0141;Asphalt;</EM></FO=
NT></DIV>
<DIV><FONT face=3DArial size=3D2><EM></EM></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>and the error message </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2><EM>ERROR:&nbsp; invalid input syntax for =
integer:=20
""<BR>CONTEXT:&nbsp; COPY warengruppen, line 1, column holder:=20
""<BR></EM></FONT></DIV>
<DIV><FONT face=3DArial size=3D2>i know it's hard to fill the integer=20
colume&nbsp;&nbsp; &nbsp;<EM>holder</EM>&nbsp;&nbsp;&nbsp; with nothing out=
 of=20
csv table.. but it's the same with the colume&nbsp;&nbsp; WarenGruppe so i =
can't=20
use a char type... so mybe someone know a litte trick to save me ?? don't l=
ike=20
to reformat 10MB datasource ...</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>thank's&nbsp;</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Sebastian</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;<BR></DIV></FONT></BODY></HTML>

------=_NextPart_000_0005_01C42F15.7CED6D50--




From scott.marlowe@ihs.com Tue May  4 15:02:45 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 10231D1F025
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  4 May 2004 12:02:45 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 96905-10
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  4 May 2004 12:02:22 -0300 (ADT)
Received: from mail1.ihs.com (mail1.ihs.com [170.207.70.222])
	by svr1.postgresql.org (Postfix) with ESMTP id 1D9C7D1CAB9
	for <pgsql-sql@postgresql.org>; Tue,  4 May 2004 12:02:22 -0300 (ADT)
Received: from css120.ihs.com (css120.ihs.com [170.207.105.120])
	by mail1.ihs.com (8.12.10/8.12.10) with ESMTP id i44F1WBO017166;
	Tue, 4 May 2004 09:01:33 -0600 (MDT)
Date: Tue, 4 May 2004 08:58:25 -0600 (MDT)
From: "scott.marlowe" <scott.marlowe@ihs.com>
To: Sebastian Tewes <stewes@tewes.homelinux.net>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: invalid input syntax for integer: ""
In-Reply-To: <000801c42f04$ba09b6f0$0a0010ac@2864id>
Message-ID: <Pine.LNX.4.33.0405040857010.30999-100000@css120.ihs.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-IHS-MailScanner: Found to be clean
X-IHS-MailScanner-SpamCheck: 
X-IHS-MailScanner-Envelope-Sender: scott.marlowe@ihs.com
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests=
X-Spam-Level: 
X-Archive-Number: 200405/36
X-Sequence-Number: 17750

On Sat, 1 May 2004, Sebastian Tewes wrote:

> Hello :-)
> 
> got a little prob... 
> 
> so my table 
> 
> create table warengruppen ( kennung char (1),
>                                 holder int,
>                      HauptWarenGruppe int,
>                         BezHWG varchar (50),
>                           WarenGruppe int,
>                          BezWG varchar (50));
> 
> 
> the copy syntax 
> 
> copy warengruppen FROM '/usr/pgsql/datanorm.wrg' with DELIMITER ';';
> 
> and a smal part of my 'datanorm.wrg'
> 
> 
> S;;011;Dachsteine+Formst.;;;
> S;;011;;0111;Dachst.glatt(Biber);
> S;;011;;0112;Dachst.glatt(Tegal.);

the problem here is that "" is not an integer. 

0 is.  If you were to write a simple script file to process the input file 
and turn the empty fields into 0s where they should be because they're 
integers you're set.

OR, you can build a temp table with all text columns, import into that, 
then use a select query to change the blank text columns to the character 
0, then import that into the target table.