Postgres Pro
Downloads
Home   >   mailing lists

Thread: Error in the date field (with NULL value...).Thanks!

Error in the date field (with NULL value...).Thanks!

From
Maurizio Ortolan
Date:
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)

RE: Error in the date field (with NULL value...).Thanks!

From
"Gerald Gutierrez"
Date:
Perhaps you can run your file through a filter to replace '' to null.

Any of sed, perl, python and other scripting languages would do nicely.

e.g.

sed -e "s/''/null/g" < myfile > myfile2


-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Maurizio Ortolan
Sent: Thursday, March 29, 2001 6:50 AM
To: pgsql-hackers@postgresql.org
Cc: pgsql-sql@postgresql.org; pgsql-general@postgresql.org;
crix98@hotmail.com
Subject: [SQL] Error in the date field (with NULL value...).Thanks!

...

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)


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl