COPY error with null 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