Postgresql, SQL, and global id's...

Question

I have a need to populate the globalid field of a geodatabase table  using SQL commands. I see that this process is documented for postgresql in version 10 [i.e. retrieve_guid()].  Is there an equivalent sde function to populate global id's for 9.3.1? (retrieve_guid does not work).  There are functions for SQL Server and Oracle, but I can find nothing for Postgresql in 9.3.1.

Thanks,
Parks

ArcGIS 9.3.1
Postgresql 8.3

AlexiaLau · Answer

Hi,

As Vince had mentioned with PostgreSQL 8.3 version, the uuid-ossp module has uuid-ossp functions stored under the public schema.  Below are supporting documentation from PostgreSQL 8.3 version on how to generate the appropriate global id that you need.  I hope you find this information helpful.

http://www.postgresql.org/docs/8.3/interactive/uuid-ossp.html
http://www.postgresql.org/docs/8.3/static/datatype-uuid.html

Regards,
Alexia

postgresql_uuid_functions.png

VinceAngelo · Answer

populate_uuid_column doesn't repopulate the entire table, just the rows with
the all-zeros placeholder, though it's more appropriate for batch operation than
for onesies and twosies.

- V

wxsouth · Answer

A pre-10 workflow for this could be to populate nominal UUID values ("{00000000-0000-0000-0000-000000000000}"), then run use "sdetable" to populate with "real" UUIDs. sdetable -o populate_uuid_column -t -c -u [-D ] [-p ] [-i ] [-s ] [-q] My 9.3.1sp1 instance has the default value populated correctly, so you might not need to set any value. Alternatively, you can use the contrib/uuid-ossp module to populate values in the varchar(38) column. - V Thanks for the suggestions. I don't want to have to repopulate the id's for the entire table for each new entry, so I'll probably look into the uuis-ossp module. Unfortunately, it isn't currently installed on the postgresql database I'm using.