pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Maurizio Ortolan <crix98__@tin.it>
To: pgsql-hackers@postgresql.org
Cc: pgsql-sql@postgresql.org
Cc: pgsql-general@postgresql.org
Cc: crix98@hotmail.com
Subject: Error in the date field (with NULL value...).Thanks!
Date: Thu, 29 Mar 2001 06:49:44 -0800
Message-ID: <5.0.2.1.2.20010329064845.00a9e9b0@box.tin.it> (raw)

Subject: Importing data from Informix to PostgreSQL.
              Error in the date field (WITH NULL value)

Hello!
I'll try to explain my little problem. Well, I have this table

create table  mytable
   ( codice     char(16) not null,
     dt_inizio  date,
     dt_fine    date,
     tipo_operazione char(1),
     causa_operazione integer
   );

ok!

I find out that pgsql:

In my example '' is the NULL value exported from Informix... !  (an ASCII file)

                    INFORMIX                       PostgreSQL
char(16)               ''              ------>       blank 
string         (I think it's   ok! )
char(1)                ''               ------>       blank 
string         (I think it's ok)
integer                ''               ------>         0              (is 
it an error? )
date                   ''                ------>      ERROR!  Bad date 
external representation ''


 >> select * from mytable ;

    codice                     | dt_inizio       | dt_fine | 
tipo_operazione | causa_operazione

  ABCEDEFFFFFFFFF | 
2001-03-28  |             |                       |            0
  XXXYYYAAA23C957Y | 
2001-03-28  |             |                      |            0


clinica=# insert into mytable values ( '','03/28/2001', '' , '' , ''  );
ERROR:  Bad date external representation ''                   ^^^

PostgreSQL doesn't want '' as an input of a date with NULL value:
it's necessary to use this kind of insert:

 >> insert into mytable values ( '','03/28/2001',null,'','');
                                                                 ^^^^^^

Now there is a new line in the table:

                                   | 
2001-03-28  |             |                      |            0


My question:
How can I resolv my problem?  I have a big data file to import where
in the 2nd date field there is '' instead of  null ....

How can I "binds" PostgreSQL to consider '' as null ?

Many thanks for any suggestions!

CIAO!
MAURIZIO

*******************************************
**  Happy surfing on THE NET !!      **
**           Ciao by                           **
**                       C R I X 98          **
*******************************************
AntiSpam: rimuovere il trattino basso
                 dall'indirizzo  per scrivermi...
(delete the underscore from the e-mail address to reply)




view thread (5+ messages)  latest in thread

Message-ID: <5.0.2.1.2.20010329064845.00a9e9b0@box.tin.it>
Permalink:  ../5.0.2.1.2.20010329064845.00a9e9b0@box.tin.it/
Also on:    postgresql.org/message-id/5.0.2.1.2.20010329064845.00a9e9b0@box.tin.it

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: crix98__@tin.it, pgsql-hackers@postgresql.org, pgsql-general@postgresql.org, crix98@hotmail.com
  Subject: Re: Error in the date field (with NULL value...).Thanks!
  In-Reply-To: <5.0.2.1.2.20010329064845.00a9e9b0@box.tin.it>
  References: <5.0.2.1.2.20010329064845.00a9e9b0@box.tin.it>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox