Обсуждение: Port Bug Report: Can't import nullable date field with COPY in psql
============================================================================
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:
---------
--------------------------------------------------------------------------
Re: [PORTS] Port Bug Report: Can't import nullable date field with COPY in psql
От
Bruce Momjian
Дата:
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
Re: [PORTS] Port Bug Report: Can't import nullable date field with COPY in psql
От
The Aztechian
Дата:
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
Re: [PORTS] Port Bug Report: Can't import nullable date field with COPY in psql
От
Bruce Momjian
Дата:
> 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. Input and output format are identical. -- 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
Re: [PORTS] Port Bug Report: Can't import nullable date field with COPY in psql
От
"Thomas G. Lockhart"
Дата:
> > > 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?
Douglas is not active in any Postgres support (and has not been for
quite some time). Where did you find the reference to him?
Also, we will eventually supplant the current man pages with text
derived from ref/copy.sgml, so if you want to suggest changes, fixes, or
improvements to the docs (or even better, submit patches! :) please look
there too.
- Tom