0

I am trying to copy data from a PostgreSQL (9.4) DB table to an SDE table. Both have an objectid field. If I copy the data from the source table with the OID field, I get an error because the values in OID in the destination table have duplicates. If I copy the data without the OID field, I get an error for not respecting the not null constraint on the destination OID.

I tried to remove the primary key constraint and the not null constraint on the OID with an alter table and even if through PGadmin I can see that there is no pkey constraint, as I run my script, I still get the error saying that the not null constraint can't be removed because objectid is in a primary key.

I would like to avoid reassigning a new OID to the entries with a cursor in the source table, as there is a lot of them. Is there any way I could bypass those constraints?

  • How exactly are you copying the data? SELECT INTO? – matt.f.c Aug 3 '18 at 22:34
1

The term of art for an "SDE table" is a "geodatabase registered" table. The registration process creates the "i table" that is used to drive population of the registered rowid column (e.g., "objectid").

Best practice for enterprise geodatabase feature class management is to perform all operations with ArcObjects (which includes the UI and arcpy). It is certainly not recommended to modify the table properties using SQL (which basically voids your warranty).

If you're willing to use Query Layers to access your table, you can create a new one, with a serial objectid, and then use database tools to copy rows to your heart's content. The key here is that the table isn't registered with the geodatabase, so it can't participate in geodatabase behavior (domains, relationship classes, feature datasets, versioned editing,...)

It should be possible to use the functions created to manage the "iN" table to populate non-conflicting objectid values, but I never wanted to swim in waters that deep, and use SQL manage non-registered tables and arcpy to manage registered ones.

I regularly process hundreds of thousands to tens of millions of rows in PostgreSQL databases, and only experience performance issues if I've screwed up something with my indexes. I recommend you use Append or a pair of Data Access cursors to transfer your data, or create a new table from scratch and use SQL to manage the rows.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.