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

[Even Rouault]

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
>>
-- 
http://www.spatialys.com
My software is free, but my time generally not.