Inserting a value into a global ID or GUID column in PostgreSQL using SQL

Global ID and GUID data types store registry-style strings consisting of 36 characters enclosed in curly brackets. These strings uniquely identify a feature or table row within a geodatabase and across geodatabases. Global IDs are used to maintain uniqueness of records in one-way and two-way geodatabase replicas. GUIDs can be added to any layer.

Therefore, if the table you want to edit participates in replication or contains a GUID, you must insert a unique value to the global ID or GUID column when you insert a new record in the table using SQL. To do this, use the retrieve_guid() function.

NoteNote:

You must install the uuid-ossp contrib module to the PostgreSQL database before you can run the retrieve_guid() function.

When editing a nonversioned table, you can insert the ID directly to the table. When editing a versioned table, use the retrieve_guid() function to insert a value to the GUID or global ID column in a versioned view of the table with SQL.

TipTip:

You can add global IDs to a feature class in the Catalog window by right-clicking the dataset and clicking Add Global IDs. The geodatabase maintains these values automatically when editing through ArcGIS.

Steps:
  1. If you are editing a table that was versioned using ArcGIS 10.1 or higher, a versioned view already exists in the database for that table. The view name will be the table name appended with _vw. If a versioned view does not already exist, you can create one from ArcGIS for Desktop. See Creating versioned views from ArcGIS for Desktop for instructions.
  2. Log in to the database with psql as a user with privileges to edit the table.

    If you are editing through a versioned view, the user must also have privileges to edit the view.

  3. If you are editing a versioned table, see Editing versioned data in PostgreSQL using SQL for instructions on using a versioned view to edit. If you are editing a nonversioned table, proceed to the next step.
  4. When inserting a record, use the retrieve_guid() function to insert the next global ID value.
    INSERT INTO jason.assets (asset_id,globalid) 
    VALUES (57,sde.retrieve_guid());
    
6/19/2015