============================================================================
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:
---------
--------------------------------------------------------------------------