[gdal-dev] PostgreSQL [RESOLVED]

[Andy Colson]

On 9/29/2011 11:28 AM, Even Rouault wrote:
> Selon Andy Colson<andy at squeakycode.net>:
>
>> On 9/29/2011 1:36 AM, Even Rouault wrote:
>>> Le mercredi 28 septembre 2011 21:28:26, Even Rouault a écrit :
>>>> Le jeudi 22 septembre 2011 17:48:29, Andy Colson a écrit :
>>>>> Hi List,
>>>>>
>>>>> I'm using ogr2ogr to convert (and reproject) data from shapefile to PG,
>>>>> if I use insert statements the null dates are not a problem, but if I
>>>>> set PG_USE_COPY=YES, then I get errors:
>>>>>
>>>>> ERROR 1: COPY statement failed.
>>>>> ERROR:  date/time field value out of range: "0000/00/00"
>>>>> CONTEXT:  COPY parcelannov2, line 18, column recorded: "0000/00/00"
>>>>>
>>>>> Inserts are really slow, so I'd love to use copy instead.
>>>>
>>>> I didn't manage to get 0000/00/00 from a shapefile. I have done a few
>> tests
>>>> but I got null values as expected and they are correctly written as null
>>>> values in the PG COPY statement. Perhaps you should file a Trac ticket
>>>> with the shapefile attached and your exact ogr2ogr command line.
>>>>
>>>
>>> On inspection, the null values for Date in this dbf file were indicated by
>> a
>>> string filled with spaces. The fix is now in the shapefile driver in
>> r23127.
>>> _______________________________________________
>>> gdal-dev mailing list
>>> gdal-dev at lists.osgeo.org
>>> http://lists.osgeo.org/mailman/listinfo/gdal-dev
>>
>> Really?  Weird.  I have a little program you can write sql and hit .dbf
>> files, I can run:
>>
>> select count(*) from ParcelAnnoV2 where recorded is null
>>
>> and it returns 34490 rows.  But, it makes a lot more sense if they were
>> spaced and not nulled.
>
> Apparently, the "conventional" way of writing a null data in the DBF is
> "00000000", not spaces.
>
> So this was a shapefile fix not a PG driver fix?
>>    How did the PG as insert statements work, but PG as COPY didn't?
>
> Yes that was a bit surprising. I looked, and there is a historical test in the
> INSERT code path that tests for if the year is "0000" and if found, consider
> that the date is null. This test did not exist in the COPY code path. Hence the
> difference in behaviour.
>
> But the right place to fix was the shapefile driver, not adding a hack in the PG
> driver.
>
>>
>> Thank you very much for the help.  I'll check this out and run it.
>>
>> -Andy
>>
>
>

I can confirm this fixed my problem.  I mentioned $bounty$ before, and I 
meant it.  $50 sound ok?  Let me know (off list if you like) an address, 
and I'll get you a check.  (although its u.s. dollars, looks like you 
are from France?)  Got a wish list someplace?

Thank you again for the help.

-Andy