[gdal-dev] append mode in postgresql

[Yves Jacolin (Free)]

Le mercredi 6 mars 2013 15:38:11 Yves Jacolin a écrit :
> Hello,
> 
> I am trying to import EDIGEO files in append mode. The doc said to use
> -append flag with ogr2ogr commande.
> 
> ogr2ogr -gt 65536 -f "PostgreSQL" "PG:dbname=test_edigeo  host='localhost'"
> - lco "GEOMETRY_NAME=the_geom" -lco "SCHEMA=test" -lco "OVERWRITE=YES" -nln
> EDIGEO_parcelle  E0001.THF PARCELLE_id
> 
> => ok
> 
> ogr2ogr -append -update -gt 65536 -f "PostgreSQL" "PG:dbname=test_edigeo
> host='localhost'" -lco "GEOMETRY_NAME=the_geom" -lco "SCHEMA=test" -nln
> EDIGEO_parcelle  E0002.THF PARCELLE_id
> 
> => nok I get this error message:
> 
> ERROR 1: Layer test.edigeo_parcelle already exists, CreateLayer failed.
> Use the layer creation option OVERWRITE=YES to replace it.
> ERROR 1: Terminating translation prematurely after failed
> translation of layer PARCELLE_id (use -skipfailures to skip errors)
> 
> Postgresql log file shows:
> 2013-03-06 15:35:44 CET LOG:  could not receive data from client: Connection
> reset by peer
> 2013-03-06 15:35:44 CET LOG:  unexpected EOF on client connection
> 
> and other information which doesn't seem interesting to me.
> 
> Any idea whe append mode is not wroking in my test?
> 
> Thanks,
> 
> Y.
Here some information about my issue:

1/ ogr2ogr is not protecting layer name. If I use EDIGEO_parcelle as a 
layername, Postgresql will create "edigeo_parcelle" table which is incorrect 
(even if this is not a good practice to set up an uppercase table name).

2/ In SQL request sent by ogr2ogr I find this one:
SELECT a.attname, a.attnum, t.typname, t.typname = 
ANY(ARRAY['int2','int4','serial']) AS isfid FROM pg_class c, pg_attribute a, 
pg_type t, pg_namespace n, pg_index i WHERE a.attnum > 0 AND a.attrelid = 
c.oid AND a.atttypid = t.oid AND c.relnamespace = n.oid AND c.oid = i.indrelid 
AND i.indisprimary = 't' AND t.typname !~ '^geom' AND c.relname = 
'edigeo_parcelle' AND (i.indkey[0]=a.attnum OR i.indkey[1]=a.attnum OR 
i.indkey[2]=a.attnum OR i.indkey[3]=a.attnum OR i.indkey[4]=a.attnum OR 
i.indkey[5]=a.attnum OR i.indkey[6]=a.attnum OR i.indkey[7]=a.attnum OR 
i.indkey[8]=a.attnum OR i.indkey[9]=a.attnum) AND n.nspname='public' ORDER BY 
a.attnum;

But my layer should be in "test" schema. Correct SQL request should be:

SELECT a.attname, a.attnum, t.typname, t.typname = 
ANY(ARRAY['int2','int4','serial']) AS isfid FROM pg_class c, pg_attribute a, 
pg_type t, pg_namespace n, pg_index i WHERE a.attnum > 0 AND a.attrelid = 
c.oid AND a.atttypid = t.oid AND c.relnamespace = n.oid AND c.oid = i.indrelid 
AND i.indisprimary = 't' AND t.typname !~ '^geom' AND c.relname = 
'edigeo_parcelle' AND (i.indkey[0]=a.attnum OR i.indkey[1]=a.attnum OR 
i.indkey[2]=a.attnum OR i.indkey[3]=a.attnum OR i.indkey[4]=a.attnum OR 
i.indkey[5]=a.attnum OR i.indkey[6]=a.attnum OR i.indkey[7]=a.attnum OR 
i.indkey[8]=a.attnum OR i.indkey[9]=a.attnum) AND n.nspname='test' ORDER BY 
a.attnum;

I am not sure how to report the last one.

Y.