Select to view content in your preferred language

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

580
1
03-07-2023 07:24 AM
Zartico-GIS
Occasional Contributor

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)

0 Kudos
1 Reply
Waan
by
Regular Contributor

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

0 Kudos