Re: [PORTS] Port Bug Report: Can't import nullable date field with COPY in psql

On Sun, 31 Jan 1999, Bruce Momjian wrote:

> Null in copy is input using \N.  See copy manual page.

No offense intended, but that is *certainly* not obvious in the man
page, as far as an *input* situation goes. The man page for copy
discusses the format of output files. Who do I contact with
suggestions for the man pages? Would that be Douglas J. Dunlop?

BTW - just so I don't appear inconsiderate... I'm really, really
pleased with PostgreSQL. I've messed about with several other
databases, both commercial (Informix) and "free" (MySQL) and IMHO what
you guys are doing is outstanding. I continually find myself using
PostgreSQL as an example of the quality of software Open Source can
provide.

Keep up the great work.

Jay MacDonald

>
>
> >
> > ============================================================================
> >                         POSTGRESQL BUG REPORT TEMPLATE
> > ============================================================================
> >
> >
> > Your name        : Jay MacDonald
> > Your email address    : jaym@aztech-cs.com
> >
> > Category        : runtime: back-end
> > Severity        : non-critical
> >
> > Summary: Can't import nullable date field with COPY in psql
> >
> > System Configuration
> > --------------------
> >   Operating System   : Linux 2.0.35 ELF
> >
> >   PostgreSQL version : 6.4
> >
> >   Compiler used      : gcc 2.7.2.1
> >
> > Hardware:
> > ---------
> > AMD K6 266, 128 MB RAM
> > S.u.S.E 5.2
> > Linux minotaur 2.0.35 #1 Tue Aug 11 14:25:16 PDT 1998 i586 unknown
> >
> > Versions of other tools:
> > ------------------------
> > GNU Make version 3.76.1
> >
> > --------------------------------------------------------------------------
> >
> > Problem Description:
> > --------------------
> > I have defined a date type field that can be null. When I
> > try to use COPY to import a text file it pukes if there
> > is no value in that field.
> >
> > --------------------------------------------------------------------------
> >
> > Test Case:
> > ----------
> > 1. Create a table test:
> > CREATE TABLE test (
> >         stock           char(6) NOT NULL,
> >         date_in         date NOT NULL,
> >         date_out        date,
> >         sort_date       date NOT NULL,
> >         PRIMARY KEY (stock)
> >         );
> >
> > 2. Create file test.dat with the following lines:
> >
> > M10004|19981219||19981219
> > M10008|19981219||19981219
> > M10011|19981219||19981219
> >
> > 3. Attempt to use COPY to import the data:
> > ram=> COPY test FROM '/disk2/data/ram/test.dat' USING DELIMITERS '|';
> > ERROR:  Bad date external representation
> > ram=>
> >
> > If I enter a date value into that null field in the data
> > file it imports okay. I tried filling it with the string
> > NULL, but no success.
> >
> > --------------------------------------------------------------------------
> >
> > Solution:
> > ---------
> >
> >
> > --------------------------------------------------------------------------
> >
> >
> >
>
>
> --
>   Bruce Momjian                        |  http://www.op.net/~candle
>   maillist@candle.pha.pa.us            |  (610) 853-3000
>   +  If your life is a hard drive,     |  830 Blythe Avenue
>   +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
>

------------------------------------------------------------------------
Jay MacDonald                                         jaym@aztech-cs.com
VP Technology                                   http://www.aztech-cs.com
Aztech Cyberspace, Inc                             702-332-3000 Ext. 227
------------------------------------------------------------------------
When I found out that electrons are fundamentally incapable of lying, I
was glad that I had chosen to be an engineer and not a lawyer.
                     - Paul Baran, Inventor of packet switching