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

[ParksCamp]

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

[VinceAngelo]

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 <table> -c <column> -u <DB_User_name>
                        [-D <database>] [-p <DB_User_password>]
                        [-i <service>] [-s <server_name>] [-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

[ParksCamp]

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 <table> -c <column> -u <DB_User_name>
                        [-D <database>] [-p <DB_User_password>]
                        [-i <service>] [-s <server_name>] [-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.

[VinceAngelo]

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

[AlexiaLau]

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