[gdal-dev] PostgreSQL

[Even Rouault]

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
>