topic Re: Postgresql, SQL, and global id's... in Data Management Questions

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

ParksCamp — Sat, 01 May 2010 21:30:44 GMT

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

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

VinceAngelo — Sun, 12 Dec 2021 05:37:52 GMT

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

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

ParksCamp — Sun, 12 Dec 2021 05:37:55 GMT

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.

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

VinceAngelo — Fri, 07 May 2010 11:55:09 GMT

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

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

AlexiaLau — Fri, 21 May 2010 14:40:00 GMT

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