Обсуждение: COPY error with null date

Using 8.3.3 I am trying to import a CSV file using the following copy
command.

copy billing.contact from 'c:/export/contact.csv'
with delimiter as ','
null as ''
csv quote as '"';

The following record record causes an error because the third field, "",
is a null date and causes the error following the record. How can I
change the copy command above so that a null date or number will be
imported as null? I do not care if empty strings are imported as an
empty string or a null.

Bill

"IASAcctSys","09/09/1995","",...


ERROR:  invalid input syntax for type date: ""
CONTEXT:  COPY contact, line 6, column date: ""

********** Error **********

ERROR: invalid input syntax for type date: ""
SQL state: 22007
Context: COPY contact, line 6, column date: ""

On Thu, 2008-12-04 at 19:35 -0700, Bill Todd wrote:
> Using 8.3.3 I am trying to import a CSV file using the following copy
> command.
>
> copy billing.contact from 'c:/export/contact.csv'
> with delimiter as ','
> null as ''
> csv quote as '"';
>
> The following record record causes an error because the third field, "",
> is a null date and causes the error following the record. How can I
> change the copy command above so that a null date or number will be
> imported as null? I do not care if empty strings are imported as an
> empty string or a null.

null as IS NULL



>
> Bill
>
> "IASAcctSys","09/09/1995","",...
>
>
> ERROR:  invalid input syntax for type date: ""
> CONTEXT:  COPY contact, line 6, column date: ""
>
> ********** Error **********
>
> ERROR: invalid input syntax for type date: ""
> SQL state: 22007
> Context: COPY contact, line 6, column date: ""
>
>
--
PostgreSQL
   Consulting, Development, Support, Training
   503-667-4564 - http://www.commandprompt.com/
   The PostgreSQL Company, serving since 1997

Joshua D. Drake wrote:

On Fri, 2008-12-05 at 12:00 -0700, Bill Todd wrote: 

Joshua D. Drake wrote:    

On Thu, 2008-12-04 at 19:35 -0700, Bill Todd wrote:     

null as IS NULL results in the following error.

ERROR:  syntax error at or near "is"
LINE 5: null as is null               ^

********** Error **********

ERROR: syntax error at or near "is"
SQL state: 42601
Character: 109

Any other suggestions?   

COPY foo FROM '/tmp/bar' NULL as 'NULL'
 

copy billing.contact from 'c:/export/contact.csv'
with csv delimiter as ','
null as 'NULL'
quote as '"';

produces the same error. I am beginning to suspect this is impossible. :-(

Bill Todd <pg@dbginc.com> writes:
> I am beginning to suspect this is impossible.

That's correct: see the COPY reference page.  A quoted value is never
considered to match the NULL string.

            regards, tom lane