[gdal-dev] append mode in postgresql

[Even Rouault]

Selon "Yves Jacolin (Free)" <yjacolin at free.fr>:

> 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)

You cannot use -lco (layer *creation* option) for an append/update. Well, you
can, but they will be ignored. As such, the schema that will be used will be the
public schema (since -lco SCHEMA=test is ignored). In your update ogr2ogr
command line, you have to specify either "-nln test.PARCELLE_id" or add "
active_schema=test" to your connexion string, i.e. "PG:dbname=test_edigeo
host='localhost' active_schema=test" (you could also have used active_schema for
the first ogr2ogr command that will create the table)

> >
> > 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).

This is the effect of the "laundering" that is done by default. See the LAUNDER
layer creation option documented in http://gdal.org/ogr/drv_pg.html

>
> 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:

See above explanation about active_schema.