Date type NULL values are treated as '0000/00/00' on PostgreSQL COPY import

[Timic3]

What is the bug?

Importing a dataset that has a Date field with NULL values into PostgreSQL (PostGIS) database results in an error, possibly due to incorrect interpretation of field values. NULL values are treated as '0000/00/00' instead of actual NULL. This happens only when PG_USE_COPY is set to YES. Works fine when it is set to NO and produces correct PGSQL table with NULL values (not '0000/00/00').

ERROR 1: COPY statement failed.
ERROR:  date/time field value out of range: "0000/00/00"
CONTEXT:  COPY test_li_layer_shp_2, line 1, column datp_kon: "0000/00/00"

This is a regression in 3.8.1 onwards. It works on 3.8.0.

I tried with OGR2OGR_USE_ARROW_API set to NO as well, but it still doesn't work.

Steps to reproduce the issue

Download sample dataset: DRSV_POPDOG_TC.zip.

Raise a PostGIS container

docker run --rm -d --net=host -e POSTGRES_PASSWORD=very_secret postgis/postgis:16-3.4

Execute ogr2ogr commands

Following does not work:

# Version 3.8.1, PG_USE_COPY YES
docker run --rm --net=host -v $PWD:/data ghcr.io/osgeo/gdal:ubuntu-full-3.8.1 'ogr2ogr' '-overwrite' '-f' 'PostgreSQL' 'PG:host=localhost dbname=postgres user=postgres password=very_secret application_name='\''OGR2OGR'\''' '-lco' 'GEOMETRY_NAME=geom' '-lco' 'SPATIAL_INDEX=NONE' '-lco' 'LAUNDER=YES' '-unsetFid' '--config' 'PG_USE_COPY' 'YES' '/data/DRSV_POPDOG_TC.shp' '-nln' 'test_li_layer_shp_2' '-a_srs' 'EPSG:3794'

I tried with --config OGR2OGR_USE_ARROW_API NO as well, same error.

Following does work:

# Version 3.8.0, PG_USE_COPY YES
docker run --rm --net=host -v $PWD:/data ghcr.io/osgeo/gdal:ubuntu-full-3.8.0 'ogr2ogr' '-overwrite' '-f' 'PostgreSQL' 'PG:host=localhost dbname=postgres user=postgres password=very_secret application_name='\''OGR2OGR'\''' '-lco' 'GEOMETRY_NAME=geom' '-lco' 'SPATIAL_INDEX=NONE' '-lco' 'LAUNDER=YES' '-unsetFid' '--config' 'PG_USE_COPY' 'YES' '/data/DRSV_POPDOG_TC.shp' '-nln' 'test_li_layer_shp_2' '-a_srs' 'EPSG:3794'

# Version 3.8.1, PG_USE_COPY NO
docker run --rm --net=host -v $PWD:/data ghcr.io/osgeo/gdal:ubuntu-full-3.8.0 'ogr2ogr' '-overwrite' '-f' 'PostgreSQL' 'PG:host=localhost dbname=postgres user=postgres password=very_secret application_name='\''OGR2OGR'\''' '-lco' 'GEOMETRY_NAME=geom' '-lco' 'SPATIAL_INDEX=NONE' '-lco' 'LAUNDER=YES' '-unsetFid' '--config' 'PG_USE_COPY' 'NO' '/data/DRSV_POPDOG_TC.shp' '-nln' 'test_li_layer_shp_2' '-a_srs' 'EPSG:3794'

Versions and provenance

Works in: GDAL 3.8.0, released 2023/11/06
Regressed in: GDAL 3.8.1, released 2023/11/28

All ran in Docker, on Mac - Apple Silicon, x86 architecture (emulated in Rosetta).

Additional context

No response