Null in copy is input using \N. See copy manual page.
>
> ============================================================================
> 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
