Received: from postgresql.org.org (webmail.postgresql.org [216.126.85.28]) by postgresql.org (8.11.3/8.11.1) with SMTP id f2UETKK52150 for ; Fri, 30 Mar 2001 09:29:20 -0500 (EST) (envelope-from pgsql-admin-owner@postgresql.org) Received: from galactica.it (mail1.galactica.it [212.41.208.18] (may be forged)) by mail.postgresql.org (8.11.3/8.11.1) with ESMTP id f2SIlwU50308; Wed, 28 Mar 2001 13:47:58 -0500 (EST) (envelope-from crix98__@tin.it) Received: from dbsrv.tin.it ([62.122.22.160]) by galactica.it with Microsoft SMTPSVC(5.5.1877.537.53); Wed, 28 Mar 2001 20:55:26 +0200 Message-Id: <5.0.2.1.2.20010328204925.033d7e68@box.tin.it> X-Sender: crix98@box.tin.it X-Mailer: QUALCOMM Windows Eudora Version 5.0.2 Date: Wed, 28 Mar 2001 20:50:56 -0800 To: pgsql-hackers@postgresql.org, pgsql-sql@postgresql.org, pgsql-general@postgresql.org, pgsql-ports@postgreSQL.org, pgsql-admin@postgreSQL.org, pgsql-bugs@postgresql.org, pgsql-patches@postgresql.org From: Maurizio Ortolan Subject: Error in the date field (with NULL value...).Thanks! Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii"; format=flowed X-Archive-Number: 200103/255 X-Sequence-Number: 1115 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)