Editing nonversioned geodatabase data in PostgreSQL using SQL

You can use SQL to update, insert data into, and delete data from nonversioned tables in the geodatabase if they do not participate in geodatabase behavior. See What type of data can be edited using SQL? for information on the types of data and geodatabase behavior you cannot edit with SQL.

All data that is registered with the geodatabase has a system-maintained, unique, not-null ObjectID (Row ID) field. When you use SQL to insert records into nonversioned tables in the geodatabase, you must provide a unique value for the ObjectID.

To get this information, query the sde_table_registry ArcSDE system table to get the registration ID of the table in which you want to insert a record. Once you have the registration ID, you can identify the table's corresponding i table, because i tables are associated with their business table through the registration ID. The i table is stored in the schema of the user who owns the corresponding table.

Include the Next_RowID function in an insert statement to populate the ObjectID field with the next valid value.

Note:

The next available ObjectID is not necessarily the next sequential number after the last inserted ObjectID. ObjectIDs for some client applications are assigned in batches, so the next available ObjectID may be many numbers higher than the last one you used. Also, any unused ObjectIDs from the batch are returned to the pool of available values, which means the next available ObjectID value may actually be a lower number than the one you inserted last.

Steps:

1. Log in to the database from an SQL editor such as psql.

   Be sure to log in to the database as a user who has permission to edit the data.

2. Use the Next_RowID function to populate the ObjectID field.

   In this example, the site_id column is the ObjectID field, the table is outbreak, and the table is stored in the hmoot schema.

   INSERT INTO hmoot.outbreak (site_id, med_code,num_affected, shape) 
   VALUES 
   (sde.next_rowid('hmoot', 'outbreak'), 
   'v-22a', 
   3, 
   ST_Point('point (12 36))', 12);
   

3. You can continue editing or, if you are done editing, commit your edits to the database.