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 object ID (row ID) column. When you use SQL to insert records into nonversioned tables in the geodatabase, you must provide a unique value for the object ID.

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.

Call the i<registration_id>_get_ids function in the INSERT statement to generate the next object ID for the inserted record.

Steps:

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

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

2. Query the sde_table_registry table to determine the registration ID of the business table in which you want to insert a record.

   SELECT registration_id 
   FROM sde.sde_table_registry 
   WHERE table_name = 'outbreak' and owner = 'hmoot'; 
   
   registration_id 
         49

   The registration ID for the hmoot.outbreak table is 49.

3. Select an id from the i49 table using the function i49_get_ids(2,1). This can be nested in your INSERT statement, as shown in the following example:

   In this example, the site_id column is the object ID.

   INSERT INTO hmoot.outbreak (site_id,med_code,num_affected,shape) 
   VALUES 
   ((SELECT o_base_id FROM hmoot.i49_get_ids(2,1)), 
   'v-22a', 
   3, 
   ST_Point('point (12 36))', 12);

   O_base_id is the object ID to be returned. The 2 in the function is a constant that indicates you want an object ID value. Do not use any other value in this place. The 1 indicates you want one object ID returned from the i49 table. Since you are updating just one record, you only need one object ID.