RE: Migrating from Sybase

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