Postgres Identity/Generated Columns do not work with Append to Esri Enterprise Geodatabase

Zartico-GIS — Tue, 07 Mar 2023 15:24:18 GMT

I want to set a column as an integer primary key separate from what Esri maintains. This is so it can exist in non-spatial tables and the features retain the key as they exist across other tables.

The problem is, if a field is set to Identity or Generated column, it does not allow direct inserts/updates to write to the field. I want these values populated at the time of appending to the layer via ArcGIS Pro.

The way the append tool works is it generates a prepared statement listing all the fields and all the values in the destination table -- even if they aren't referenced/mapped during the append. This prevents the load from working because it is trying to insert/update on the Identity field instead of letting it do its own thing.

I am looking at DB triggers to do this but it doesn't seem like a clean approach. I've also thought about running the incrementing integer field on the table in pro but that only works on unregistered layers. Any ideas?

STATEMENT: INSERT INTO poi_gdb.poi.poi_raw (objectid, name, primary_category, secondary_category, osm_unique_id, shape, poi_id, processed_timestamp) VALUES ( $1, $2, $3, $4, $5, $6 , $7, $8)

Re: Postgres Identity/Generated Columns do not work with Append to Esri Enterprise Geodatabase

Waan — Thu, 01 Jun 2023 21:53:18 GMT

Have you considered an insert cursor? It would take arcpy but it may allow you to functionally append records without using the "Append" geoprocessing tool.

InsertCursor—ArcGIS Pro | Documentation

topic Postgres Identity/Generated Columns do not work with Append to Esri Enterprise Geodatabase in ArcGIS Enterprise Questions

Postgres Identity/Generated Columns do not work with Append to Esri Enterprise Geodatabase

Re: Postgres Identity/Generated Columns do not work with Append to Esri Enterprise Geodatabase