Postgres Pro
Downloads
Home   >   mailing lists

Thread: copy from file with null date fields

copy from file with null date fields

From
"William D. McCoy"
Date:
I'm running Postgres 6.3.2 on a Sparc20 running Solaris 2.5.1 and I'm
having difficulty copying data from certain text files where fields
expected to be type 'date' are null.  When the copy encounters the
null field it stops and prints the message:

ERROR:  Bad date external representation

I have purposely _not_ defined the field as 'not null'.  I have not
had trouble reading from files where other types of fields are null.

Any ideas, or is this a known bug?

--
William D. McCoy
Geosciences
University of Massachusetts
Amherst, MA  01003

Re: [GENERAL] copy from file with null date fields

From
David Hartwig
Date:
How are you representing NULL in the source file.   Blank or zero length
strings are not NULL.   They are treated as illegal dates.   I believe
"\N" is used to represent NULL in the copy in/out.

William D. McCoy wrote:

> I'm running Postgres 6.3.2 on a Sparc20 running Solaris 2.5.1 and I'm
> having difficulty copying data from certain text files where fields
> expected to be type 'date' are null.  When the copy encounters the
> null field it stops and prints the message:
>
> ERROR:  Bad date external representation
>
> I have purposely _not_ defined the field as 'not null'.  I have not
> had trouble reading from files where other types of fields are null.
>
> Any ideas, or is this a known bug?
>
> --
> William D. McCoy
> Geosciences
> University of Massachusetts
> Amherst, MA  01003

Re: [GENERAL] copy from file with null date fields

From
"William D. McCoy"
Date:
David,  Thanks for your help.  I did have null values represented as
zero-length strings.  I'll just run these files through a sed script
to replace the zero-length strings with "\N".


David Hartwig writes:
 > How are you representing NULL in the source file.   Blank or zero length
 > strings are not NULL.   They are treated as illegal dates.   I believe
 > "\N" is used to represent NULL in the copy in/out.
 >
 > William D. McCoy wrote:
 >
 > > I'm running Postgres 6.3.2 on a Sparc20 running Solaris 2.5.1 and I'm
 > > having difficulty copying data from certain text files where fields
 > > expected to be type 'date' are null.  When the copy encounters the
 > > null field it stops and prints the message:
 > >
 > > ERROR:  Bad date external representation
 > >
 > > I have purposely _not_ defined the field as 'not null'.  I have not
 > > had trouble reading from files where other types of fields are null.
 > >
 > > Any ideas, or is this a known bug?

--
William D. McCoy
Geosciences
University of Massachusetts
Amherst, MA  01003