[Fwd: Re: [Fwd: Re: Questions, help!!]] - Mailing list pgsql-novice

Thanks for the help, I just solved the situation by replacing the null
values with '\N' in my text file. And I added an extra column in my
postgreSQL table for the text files generated by Informix.

Regards'
Jaime

-------- Original Message --------
Subject: Re: [Fwd: Re: [NOVICE] Questions, help!!]
From: Bruce Momjian <pgman@candle.pha.pa.us>
Date: Mon, November 10, 2003 5:41 pm
To: barron@clubinter.net

barron@clubinter.net wrote:
> 1.ERROR: line 1, "Bad date external representation" when executing
> copy cliente from '/home/txtfiles/cliente.asc' with delimiter as '|'
>
> a. This is the line in text file 'cliente.asc':
>
> 4|010001|01|01 COMPUTACION|SEV. # 30|JUAREZ|MEX, DF|||||||||0.0||||

OK, || isn't a NULL, it is a '', and that can't be represented as a
date.  You have to use perhaps WITH NULL AS '' or something like that in
the COPY command.  By default, NULLs are represented as \N, which is a
unique value in the copy file (backslash is \\).


> b. The table 'cliente' has columns:
>
> cvesuc int2, cvecli char(7), inicli char(3), rsocli char(50), domcli
> char(70), colcli char(50), pobcli char(10), copcli char(10), edocli
> char(15),
> paicli char(15), ladcli char(8), telcli char(40), faxcli char(10),
> texcli char(10), rfccli char(20), descli float4, estcli char(1),
> datereg date, cveant char(16), nada text


>
> -------- Original Message --------
> Subject: Re: [NOVICE] Questions, help!!
> From: Bruce Momjian <pgman@candle.pha.pa.us>
> Date: Sat, November 8, 2003 8:31 pm
> To: barron@clubinter.net
>
> barron@clubinter.net wrote:
> > 1.ERROR: line 1, "Bad date external representation" when executing
> copy cliente from '/home/txtfiles/cliente.asc' with delimiter as
> '|'.
> >
> > a. RH Linux 9.0
> > b. PostgreSQL 7.3
> > c. Importing text file from SQL informix Online 5.0
> > d. Configured datestyle: "ISO with US (NonEuropean) conventions
>
> Please show us the date value on line 1.
>
> > 2. ERROR: copy: line 1, "Extra data after last expected column" when
> executing copy maxtrn from '/home/txtfiles/maxtrn.asc' with
> delimiter as '|'
>
> Yes, this is a known problem.  Informix puts the delimiter at the end
> of the line, while we don't.  You have to strip them, perhap with sed:
>
>     sed 's/|$//' file
>
> > a. The text file "maxtrn.asc" was generated by Informix Online 5.0,
> and its  structure is:
> > 5|02|C|286|
> > 5|40|C|287|
> > b. The table "maxtrn" has columns:
> > cvesuc int2
> > cuenta char(2)
> > cvetrn char(1)
> > folio int4
> >
> > 3. I cannot connect to PostgreSQL database from Kylix 3. I got the
> error message "DbExpress Error: Invalid username/password"
> >
> > a. I'm trying to connect with the account "postgres" which was
> generated by RH Linux 9.0 and I verified entrance to the account
> with the password I assigned to it previously.
>
> Not sure on that one.  Perhaps it is using an older PostgreSQL client
> library and you have to use 'password' or 'crypt' rather than 'md5'.
>
> --
>   Bruce Momjian                        |  http://candle.pha.pa.us
> pgman@candle.pha.pa.us               |  (610) 359-1001
>   +  If your life is a hard drive,     |  13 Roberts Road
>   +  Christ can be your backup.        |  Newtown Square, Pennsylvania
> 19073
>
> ---------------------------(end of
> broadcast)--------------------------- TIP 3: if posting/reading
> through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that
> your
> message can get through to the mailing list cleanly
>
>
>
>
> ---------------------------(end of
> broadcast)--------------------------- TIP 9: the planner will ignore
> your desire to choose an index scan if your
>       joining column's datatypes do not match
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania
19073

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if
your
      joining column's datatypes do not match