Thread: Migrating from Sybase
Hi,
I ran into some trouble moving my data from a Sybase ADA db to Postgresql.
I fixed most of the problems (to move the data I unloaded the db and edited
the scripts), but there's one I'm stuck with:
The command:
COPY probe FROM '/home/ice/felsdb/186.dat' using delimiters '|' with null as '' ;
produces
": can't parse "elsdb/import3.sql:419: ERROR: copy: line 1, pg_atoi: error in "":
The stuff in 186.dat:
WI-02.06.99-029|1999-06-02 11:04:13.184|Werk 1 Mue|KStK00-32/1|Mineralgemisch 0-30mm|||Halde B2, neue Anlage|1999-05-31
10:53:00.000|9922|1999-06-02||WP_B2||Manschke|3|1|A0170938384B|^M
[....]
The DB table "probe":
CREATE TABLE "probe"^M
(^M
"probennummer" char(20) NOT NULL,^M
"eingang_am" timestamp NOT NULL,^M
"kunden_nr" char(20) NULL,^M
"produktgruppe" char(20) NOT NULL,^M
"beschreibung" varchar(40) NULL,^M
"chargen_nr" varchar(20) NULL,^M
"lieferschein_nr" varchar(20) NULL,^M
"entnahmeort" varchar(40) NULL,^M
"entnommen_am" timestamp NULL,^M
"kalenderwoche" integer NULL,^M
"ankunft_am" date NULL,^M
"fertig_am" timestamp NULL,^M
"jobname" char(20) NULL,^M
"vermerk" varchar(40) NULL,^M
"registriert_von" varchar(25) NULL,^M
"type" smallint NOT NULL,^M
"status" smallint NOT NULL,^M
"barcode" char(12) NULL,^M
"mark" smallint NULL,^M
PRIMARY KEY ("probennummer", "eingang_am")^M
);
What am I doing wrong here?
JP
Had a similar experience in Postgresql 6.5.3 running in Linux with *.dat files
coming from Sybase running in win95.
Found that if I split the source file with a text editor that the individual
splits would process ok. Finally concluded that Sybase was putting a
character at end of file that 'COPY' could not interpret.
Also, had trouble with null fields. I had Sybase generate a unique date for
nulls and 0 for null integers. But then, my 'copy' command didn't include
'with null as ''.
Further, changed all char fields to varchar because postgres stumbled on
character lengths that didn't match char(length).
Hope this helps.
>===== Original Message From "Jens P. Elsner" <jpelsner@gmx.net> =====
>Hi,
>
>I ran into some trouble moving my data from a Sybase ADA db to Postgresql.
>I fixed most of the problems (to move the data I unloaded the db and edited
>the scripts), but there's one I'm stuck with:
>
>The command:
>
>COPY probe FROM '/home/ice/felsdb/186.dat' using delimiters '|' with null as
'' ;
>
>produces
>
>": can't parse "elsdb/import3.sql:419: ERROR: copy: line 1, pg_atoi: error
in "":
>
>The stuff in 186.dat:
>
>WI-02.06.99-029|1999-06-02 11:04:13.184|Werk 1 Mue|KStK00-32/1|Mineralgemisch
0-30mm|||Halde B2, neue Anlage|1999-05-31
10:53:00.000|9922|1999-06-02||WP_B2||Manschke|3|1|A0170938384B|^M
>
>[....]
>
>The DB table "probe":
>
>CREATE TABLE "probe"^M
>(^M
> "probennummer" char(20) NOT NULL,^M
> "eingang_am" timestamp NOT NULL,^M
> "kunden_nr" char(20) NULL,^M
> "produktgruppe" char(20) NOT NULL,^M
> "beschreibung" varchar(40) NULL,^M
> "chargen_nr" varchar(20) NULL,^M
> "lieferschein_nr" varchar(20) NULL,^M
> "entnahmeort" varchar(40) NULL,^M
> "entnommen_am" timestamp NULL,^M
> "kalenderwoche" integer NULL,^M
> "ankunft_am" date NULL,^M
> "fertig_am" timestamp NULL,^M
> "jobname" char(20) NULL,^M
> "vermerk" varchar(40) NULL,^M
> "registriert_von" varchar(25) NULL,^M
> "type" smallint NOT NULL,^M
> "status" smallint NOT NULL,^M
> "barcode" char(12) NULL,^M
> "mark" smallint NULL,^M
> PRIMARY KEY ("probennummer", "eingang_am")^M
>);
>
>What am I doing wrong here?
>
>JP
"Jens P. Elsner" <jpelsner@gmx.net> writes:
> ": can't parse "elsdb/import3.sql:419: ERROR: copy: line 1, pg_atoi: error in "":
> The stuff in 186.dat:
> WI-02.06.99-029|1999-06-02 11:04:13.184|Werk 1 Mue|KStK00-32/1|Mineralgemisch 0-30mm|||Halde B2, neue
Anlage|1999-05-3110:53:00.000|9922|1999-06-02||WP_B2||Manschke|3|1|A0170938384B|^M
I think it's unhappy about the last field, for which you're supplying an
empty string --- but the field is declared 'smallint' and so an empty
string isn't valid. Postgres does not consider an empty string to mean
NULL. You should write \N if you want COPY to produce a NULL.
BTW, I do not know whether the '^M's in your message were actually in
the original file, but that could be a problem too. I haven't tried it,
but I think that COPY wants Unix-style newlines (LF only) and is not
forgiving about accepting DOS-style newlines instead.
regards, tom lane
> Had a similar experience in Postgresql 6.5.3 running in Linux with *.dat files > coming from Sybase running in win95. > > Found that if I split the source file with a text editor that the individual > splits would process ok. Finally concluded that Sybase was putting a > character at end of file that 'COPY' could not interpret. At the end of each line, the <cr>. recode fixed it. > Also, had trouble with null fields. I had Sybase generate a unique date for > nulls and 0 for null integers. But then, my 'copy' command didn't include > 'with null as ''. Ok. > Further, changed all char fields to varchar because postgres stumbled on > character lengths that didn't match char(length). No problem with that. > Hope this helps. Yup, thanks a lot! JP
> > WI-02.06.99-029|1999-06-02 11:04:13.184|Werk 1 Mue|KStK00-32/1|Mineralgemisch 0-30mm|||Halde B2, neue Anlage|1999-05-3110:53:00.000|9922|1999-06-02||WP_B2||Manschke|3|1|A0170938384B|^M > > I think it's unhappy about the last field, for which you're supplying an > empty string --- but the field is declared 'smallint' and so an empty > string isn't valid. Postgres does not consider an empty string to mean > NULL. You should write \N if you want COPY to produce a NULL. I used " with null as ''". > BTW, I do not know whether the '^M's in your message were actually in > the original file, but that could be a problem too. I haven't tried it, > but I think that COPY wants Unix-style newlines (LF only) and is not > forgiving about accepting DOS-style newlines instead. That fixed it ! Thanks! recode d it and it was ok. JP