Skip site navigation (1) Skip section navigation (2)

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: (view raw, whole thread or download thread mbox)
Lists: pgsql-adminpgsql-bugspgsql-generalpgsql-hackerspgsql-patchespgsql-portspgsql-sql
Subject: Importing data from Informix to PostgreSQL.
              Error in the date field (WITH NULL value)

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


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

2001-03-28  |             |                       |            0
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!


**  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)


pgsql-patches by date

Next:From: Hiroshi InoueDate: 2001-03-30 03:46:00
Subject: Re: Re: [ODBC] Cleanup of ODBC
Previous:From: Bruce MomjianDate: 2001-03-28 21:03:10
Subject: Re: [PATCHES] Re: Cleanup of ODBC

pgsql-ports by date

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

pgsql-admin by date

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

pgsql-bugs by date

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

pgsql-hackers by date

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

pgsql-general by date

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

pgsql-sql by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group