FAQ
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)

Search Discussions

  • Thomas Lockhart at Mar 30, 2001 at 6:22 am
    How can I "binds" PostgreSQL to consider '' as null ?
    You can modify src/backend/utils/{datetime,date,timestamp}.c to accept
    an empty string as a null value. But imho it is better to fix this in
    your input file, perhaps using sed:

    sed "s/''/NULL/g" < informix.dump > pg.dump

    - Thomas

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-bugs @
categoriespostgresql
postedMar 30, '01 at 6:22a
activeMar 30, '01 at 1:17p
posts2
users2
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase