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(at)aztech-cs(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)aztech-cs(dot)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