Обсуждение: copy from file with null date fields
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
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
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