pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Error in the date field (with NULL value...).Thanks!
5+ messages / 4 participants
[nested] [flat]

* Error in the date field (with NULL value...).Thanks!
@ 2001-03-29 04:50 Maurizio Ortolan <crix98__@tin.it>
  2001-03-30 06:22 ` Re: [PATCHES] Error in the date field (with NULL value...).Thanks! Thomas Lockhart <lockhart@alumni.caltech.edu>
  2001-04-02 01:31 ` RE: [HACKERS] Error in the date field (with NULL value...).Thanks! Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  0 siblings, 2 replies; 5+ messages in thread

From: Maurizio Ortolan @ 2001-03-29 04:50 UTC (permalink / raw)
  To: pgsql-hackers@postgresql.org; pgsql-sql; pgsql-general@postgresql.org; pgsql-ports@postgreSQL.org; pgsql-admin@postgreSQL.org; pgsql-bugs@postgresql.org; pgsql-patches@postgresql.org

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)




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: [PATCHES] Error in the date field (with NULL value...).Thanks!
  2001-03-29 04:50 Error in the date field (with NULL value...).Thanks! Maurizio Ortolan <crix98__@tin.it>
@ 2001-03-30 06:22 ` Thomas Lockhart <lockhart@alumni.caltech.edu>
  1 sibling, 0 replies; 5+ messages in thread

From: Thomas Lockhart @ 2001-03-30 06:22 UTC (permalink / raw)
  To: Maurizio Ortolan <crix98__@tin.it>; +Cc: pgsql-hackers@postgresql.org; pgsql-sql; pgsql-general@postgresql.org; pgsql-ports@postgresql.org; pgsql-admin@postgresql.org; pgsql-bugs@postgresql.org; pgsql-patches@postgresql.org

> 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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* RE: [HACKERS] Error in the date field (with NULL value...).Thanks!
  2001-03-29 04:50 Error in the date field (with NULL value...).Thanks! Maurizio Ortolan <crix98__@tin.it>
@ 2001-04-02 01:31 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  1 sibling, 0 replies; 5+ messages in thread

From: Christopher Kings-Lynne @ 2001-04-02 01:31 UTC (permalink / raw)
  To: Maurizio Ortolan <crix98__@tin.it>; pgsql-general@postgresql.org

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




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Error in the date field (with NULL value...).Thanks!
@ 2001-03-29 14:49 Maurizio Ortolan <crix98__@tin.it>
  2001-03-30 21:36 ` RE: Error in the date field (with NULL value...).Thanks! Gerald Gutierrez <gutz@kalador.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Maurizio Ortolan @ 2001-03-29 14:49 UTC (permalink / raw)
  To: pgsql-hackers@postgresql.org; +Cc: pgsql-sql; pgsql-general@postgresql.org; crix98@hotmail.com

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)




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* RE: Error in the date field (with NULL value...).Thanks!
  2001-03-29 14:49 Error in the date field (with NULL value...).Thanks! Maurizio Ortolan <crix98__@tin.it>
@ 2001-03-30 21:36 ` Gerald Gutierrez <gutz@kalador.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Gerald Gutierrez @ 2001-03-30 21:36 UTC (permalink / raw)
  To: Maurizio Ortolan <crix98__@tin.it>; pgsql-sql


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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2001-04-02 01:31 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-03-29 04:50 Error in the date field (with NULL value...).Thanks! Maurizio Ortolan <crix98__@tin.it>
2001-03-30 06:22 ` Thomas Lockhart <lockhart@alumni.caltech.edu>
2001-04-02 01:31 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2001-03-29 14:49 Error in the date field (with NULL value...).Thanks! Maurizio Ortolan <crix98__@tin.it>
2001-03-30 21:36 ` Gerald Gutierrez <gutz@kalador.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox