[gdal-dev] COPY statement failed with PG_USE_COPY=YES

[Matteo Ghetta]

Hi Even,

many thanks for the fix!

Cheers

Matteo

On 11/27/23 16:37, Even Rouault wrote:
> Hi Matteo,
> 
> I've pushed a fix in https://github.com/OSGeo/gdal/pull/8840 for that. 
> The null dates were formatted in a unusual way. I assume that file was 
> produced for another dataset where null dates were set to "0000/00/00" 
> rather than an appropriate null value, which ended in a non-typical null 
> date being written.
> 
> Even
> 
> Le 27/11/2023 à 16:04, Matteo Ghetta a écrit :
>> Hi Even,
>>
>> sorry for the delay. In attach you can find a shapefile and here the 
>> command I'm using:
>>
>> ogr2ogr -progress --config PG_USE_COPY YES --config SHAPE_ENCODING 
>> LATIN1 -f PostgreSQL PG:"host=XX port=XX dbname=XX user=XX password=XX 
>> active_schema=gis" -lco DIM=4 /path/to/FOG_NODI_PT_GIS.shp 
>> FOG_NODI_PT_GIS -lco LAUNDER=NO -overwrite -nlt POINT -lco 
>> GEOMETRY_NAME=geom -lco FID=id -nln gis."FOG_NODI_PT_GIS" -s_srs 
>> EPSG:23032 -t_srs EPSG:4326
>>
>> Cheers and thanks!
>>
>> Matteo
>>
>> On 11/21/23 12:24, Even Rouault wrote:
>>> Matteo,
>>>
>>>  From a quick test, I can't reproduce. Please provide a minimum 
>>> shapefile that reproduces the issue
>>>
>>> $ cat test.csv
>>> id,dt
>>> 1,"0000/00/00"
>>> $ cat test.csvt
>>> Integer,Date
>>> $ ogr2ogr date_null.shp test.csv
>>> Warning 1: Invalid value type found in record 1 for field dt. This 
>>> warning will no longer be emitted
>>> $ ogr2ogr pg:dbname=autotest date_null.dbf --config PG_USE_COPY YES 
>>> -overwrite
>>> $ ogrinfo pg:dbname=autotest date_null -al
>>> INFO: Open of `pg:dbname=autotest'
>>>        using driver `PostgreSQL' successful.
>>>
>>> Layer name: date_null
>>> Geometry: None
>>> Feature Count: 1
>>> Layer SRS WKT:
>>> (unknown)
>>> FID Column = ogc_fid
>>> id: Integer (9.0)
>>> dt: Date
>>> OGRFeature(date_null):1
>>>    id (Integer) = 1
>>>    dt (Date) = (null)
>>>
>>> Even
>>>
>>> Le 21/11/2023 à 09:24, Matteo Ghetta via gdal-dev a écrit :
>>>> Hi all,
>>>>
>>>> I'm trying to import (many) shapefiles into a PG database. I'm 
>>>> facing the following error, but only when PG_USE_COPY is set to YES:
>>>>
>>>> ERROR 1: COPY statement failed.
>>>> ERROR:  date/time field value out of range: "0000/00/00"
>>>> CONTEXT:  COPY TABLE, line 12682, column DATA_INI: "0000/00/00"
>>>>
>>>> The value in the source shapefile is NULL for some rows. With 
>>>> PG_USE_COPY NO the import works perfectly.
>>>>
>>>> I've found this old ticket, that seems to describe this exact issue, 
>>>> and is marked as resolved:
>>>>
>>>> https://trac.osgeo.org/gdal/ticket/4265
>>>>
>>>> Tested with:
>>>>
>>>> * GDAL 3.2.2, released 2021/03/05
>>>> * GDAL 3.7.3, released 2023/10/30
>>>>
>>>> Do you have any ideas about what is causing this?
>>>>
>>>> Cheers and thanks
>>>>
>>>> Matteo
>>>> _______________________________________________
>>>> gdal-dev mailing list
>>>> gdal-dev at lists.osgeo.org
>>>> https://lists.osgeo.org/mailman/listinfo/gdal-dev
>>>