[gdal-dev] Inserting into PostGIS table with primary key using ogr2ogr

[Luca Sigfrido Percich]

Hi Chaitanya,

I've made several tests, here are the results (the shapefile has a
id_arco integer field which is the true primary key):

1. ogr2ogr -overwrite (re)creates the table and adds a
  ogc_fid serial NOT NULL primary key
and the original PK field ("id_arco") is being correctly populated

2. ogr2ogr -overwrite --config PGSQL_OGR_FID id_arco
has no effect, ogc_fid is still being created along with id_arco

3. on existing PG table with id_arco integer primary key,
ogr2ogr -append -update 

with or without --config PGSQL_OGR_FID id_arco
gives again the error:

ERROR 1: INSERT command for new feature failed.
ERROR:  null value in column "id_arco" violates not-null constraint

Command: INSERT INTO "t_cmi_archi" ("geom" , "id_fermata", "id_ferma0")
VALUES (

As you can see, the column "id_arco" does not appear in the insert.

4. on existing PG table, with an additional column
fake_id serial primary key
ogr2ogr -append -update works, and the original PK field ("id_arco") is
being correctly populated

It is clear that the PostGIS driver automatically adds a OGC_FID serial
primary key when (re)creating the layer, but can only work with already
existing tables/primary keys if the PK values are generated by the
server.

Moreover it is not clear how and if the configuration option
PGSQL_OGR_FID works.

Many thanks,

Sig


Il giorno mar, 13/12/2011 alle 00.12 +0530, Chaitanya kumar CH ha
scritto:
> Sig,
> 
> Can you check if the fid column names match?
> 
> On Mon, Dec 12, 2011 at 11:25 PM, Luca Sigfrido Percich
> <sigfrido at tiscali.it> wrote:
>         Hi All,
>         
>         I'm using ogr2ogr to load a shapefile into an already existing
>         empty
>         PostGIS table, which has a primary key constraint on an
>         integer column.
>         
>         All the row in the shapefile contain valid unique values for
>         the PK
>         column, but when loading the shape with
>         
>         ogr2ogr -append -update -a_srs EPSG:3003 -f 'PostgreSQL'
>         "PG:host=$HOST
>         dbname=$DATABASE user=$USER active_schema=$SCHEMA"
>         "$PATH_IN_SHP/$SHP_FERMATE" -nlt Polygon  -nln t_cmi_fermate
>         
>         I get the following error:
>         
>         ERROR 1: INSERT command for new feature failed.
>         ERROR:  null value in column "id_fermata" violates not-null
>         constraint
>         
>         I also tried the --config PG_USE_COPY YES, and I get:
>         ERROR 1: COPY statement failed.
>         
>         I figured out that when ogr2ogr/PG finds a primary key, it
>         assumes that
>         it is an autoinc or serial column and does not provide the
>         values from
>         the source dataset for the INSERT or COPY instruction.
>         
>         If i drop the PK constraint from PostGIS, and reissue the
>         ogr2ogr
>         command, the shape gets loaded with no errors. So a temporary
>         workaround
>         is to drop the PK constraint, load data and re-create the
>         constraint.
>         
>         Is this the expected behaviour?
>         
>         I must add that I did the same on a SQLite/Spatialite DB, and
>         data got
>         loaded without raising this error.
>         
>         Thank you for your attention
>         
>         Sig
>         
>         
>         
>         _____________
>         PRIVACY
>         Le informazioni contenute in questo messaggio sono riservate e
>         confidenziali. Il loro utilizzo e' consentito esclusivamente
>         al destinatario del messaggio, per le finalità indicate nel
>         messaggio stesso. Qualora Lei non fosse la persona a cui il
>         presente messaggio è destinato, La invitiamo ad eliminarlo dal
>         Suo Sistema e a distruggere le varie copie o stampe, dandone
>         gentilmente comunicazione all’indirizzo mail del mittente.
>         Ogni utilizzo improprio e' contrario ai principi del D.lgs
>         196/03 e alla legislazione europea (Direttiva 2002/58/CE).
>         
>         PRIVACY
>         Le informazioni contenute in questo messaggio sono riservate e
>         confidenziali. Il loro utilizzo e' consentito esclusivamente
>         al destinatario del messaggio, per le finalità indicate nel
>         messaggio stesso. Qualora Lei non fosse la persona a cui il
>         presente messaggio è destinato, La invitiamo ad eliminarlo dal
>         Suo Sistema e a distruggere le varie copie o stampe, dandone
>         gentilmente comunicazione all’indirizzo mail del mittente.
>         Ogni utilizzo improprio e' contrario ai principi del D.lgs
>         196/03 e alla legislazione europea (Direttiva 2002/58/CE).
>         _______________________________________________
>         gdal-dev mailing list
>         gdal-dev at lists.osgeo.org
>         http://lists.osgeo.org/mailman/listinfo/gdal-dev
> 
> 
> 
> -- 
> Best regards,
> Chaitanya kumar CH.
> 
> +91-9494447584
> 17.2416N 80.1426E