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

[Chaitanya kumar CH]

Sig,

The configuration option PGSQL_OGR_FID is only used for reading a postgis
table. Use the creation option FID to specify the FID column to create.
Also check that the primary key column in the postgis table is of type
serial and not just integer.

On Tue, Dec 13, 2011 at 3:43 AM, Luca Sigfrido Percich
<sigfrido at tiscali.it>wrote:

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


-- 
Best regards,
Chaitanya kumar CH.

+91-9494447584
17.2416N 80.1426E
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/gdal-dev/attachments/20111213/67abd75e/attachment-0001.html