pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedError 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>
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
* Error in the date field (with NULL value...).Thanks!
@ 2001-03-29 14:49 Maurizio Ortolan <crix98__@tin.it>
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: [PATCHES] Error in the date field (with NULL value...).Thanks!
@ 2001-03-30 06:22 Thomas Lockhart <lockhart@alumni.caltech.edu>
parent: Maurizio Ortolan <crix98__@tin.it>
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: Error in the date field (with NULL value...).Thanks!
@ 2001-03-30 21:36 Gerald Gutierrez <gutz@kalador.com>
parent: Maurizio Ortolan <crix98__@tin.it>
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
* RE: [HACKERS] Error in the date field (with NULL value...).Thanks!
@ 2001-04-02 01:31 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: Maurizio Ortolan <crix98__@tin.it>
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
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