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

From: Maurizio Ortolan <crix98__(at)tin(dot)it>
To: pgsql-hackers(at)postgresql(dot)org, pgsql-sql(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org, pgsql-ports(at)postgreSQL(dot)org, pgsql-admin(at)postgreSQL(dot)org, pgsql-bugs(at)postgresql(dot)org, pgsql-patches(at)postgresql(dot)org
Subject: Error in the date field (with NULL value...).Thanks!
Date: 2001-03-29 04:50:56
Message-ID: 5.0.2.1.2.20010328204925.033d7e68@box.tin.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin pgsql-bugs pgsql-general pgsql-hackers pgsql-patches pgsql-ports pgsql-sql

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)

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Natalya Pyalling 2001-03-29 07:53:32 Please Help Can not connect to any database
Previous Message Christian Anton 2001-03-29 03:05:47 Is PostgreSQL Journalized

Browse pgsql-bugs by date

  From Date Subject
Next Message Marcin Kowalski 2001-03-29 08:13:58 Re: pg_dump potential bug
Previous Message pgsql-bugs 2001-03-28 19:15:13 Option in pg_hba.conf to specify permissions and owner for the socket file

Browse pgsql-general by date

  From Date Subject
Next Message will trillich 2001-03-29 04:57:40 Re: Determine Time in other Time Zone
Previous Message Sean Harding 2001-03-29 04:42:22 full table scan on 'select max(value) from table'?

Browse pgsql-hackers by date

  From Date Subject
Next Message Marcin Kowalski 2001-03-29 08:13:58 Re: pg_dump potential bug
Previous Message Tom Lane 2001-03-29 04:06:42 Re: Re: Call for platforms

Browse pgsql-patches by date

  From Date Subject
Next Message Hiroshi Inoue 2001-03-30 03:46:00 Re: Re: [ODBC] Cleanup of ODBC
Previous Message Bruce Momjian 2001-03-28 21:03:10 Re: [PATCHES] Re: Cleanup of ODBC

Browse pgsql-ports by date

  From Date Subject
Next Message Tomas Vacek 2001-03-29 06:05:19 Compiling PostgreSQL 7.x on BeOS R5?
Previous Message Bruce Momjian 2001-03-29 01:59:18 Re: [HACKERS] Re: pgmonitor and Solaris

Browse pgsql-sql by date

  From Date Subject
Next Message Martijn van Dijk 2001-03-29 08:05:48 Escaping \
Previous Message Tom Lane 2001-03-29 00:20:31 Re: Can a SELECT block?