I am migrating data from an older GIS system into an SDE system with PostgreSQL as the database engine. The current system generates a unique tag for several layers, I want to re-create this behavior using sequences in the PostgreSQL database. I have it working in a single user environment, but will it work in a versioned environment? Right now the field is populated by using Postgres' nextval() function as the field's default value. When the tables are versioned do those defaults go with them?

  • SDE no longer exists. The current term of art is "enterprise geodatabase". Sequences continue to work, but you need to study the implementation details to determine where the sequence should live. It may not be technically be supported to modify the An table to use a default value from a sequence, and it may be a bit of a challenge to prevent that column from being populated on an insert, but you can achieve your goal. But no, I don't believe the defaults will be inherited (and they need to be disabled from the base tables after versioning). – Vince Jul 12 '18 at 2:54
  • As far as I know a Postgres sequence can be called from anywhere by anyone who has permission to use it. It can live in any schema. – HeyOverThere Jul 12 '18 at 17:57
  • Certainly it can live in any schema, but it's dangerous to place it in any schema other than where it will be used. The schema is not my concern; it's the table from which it's invoked which is the issue. Note too that the Adds table is used for UPDATEs as well as INSERTs, so you cannot use a UNIQUE key. – Vince Jul 13 '18 at 2:28

I made some tests on a similar topic using Oracle, and it was not a real success. I played with the base table and the A table, adding an auto-generated unique key. It is important to stress that the uniqueness was enforced by a constraint.

I faced two issues (from the top of my head, it's a bit old): One that could be overcome, that made new records created in ArcMap have a Null value instead of the default sequence value, and saving this record would fail.

The second issue was more perverse, as it would occurred when duplicating a record. The current value would also be duplicated, creating a unique constraint error.

The only way I found at that time to have an independent sequence with a unique key was to make use of Oracle hidden column with an identity column. This new column would not be visible in ArcMap and was ignored when creating a new record. Of course ESRI said they didn't support it and therefore didn't recommend using it.

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.