Thread
-
Error in the date field (with NULL value...).Thanks!
Maurizio Ortolan <crix98__@tin.it> — 2001-03-29T04:50:56Z
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: [PATCHES] Error in the date field (with NULL value...).Thanks!
Thomas Lockhart <lockhart@alumni.caltech.edu> — 2001-03-30T06:22:21Z
> 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 -
RE: [HACKERS] Error in the date field (with NULL value...).Thanks!
Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2001-04-02T01:31:43Z
Just do a search-replace on your source file and replace all occurrences of '' with NULL. Chris -----Original Message----- From: pgsql-hackers-owner@postgresql.org [mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Maurizio Ortolan Sent: Thursday, 29 March 2001 12:51 PM 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 Subject: [HACKERS] Error in the date field (with NULL value...).Thanks! 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) ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly