Is there a work-around for Insights to be able to read PostgreSQL UUID fields?

157
1
Jump to solution
12-09-2021 08:11 AM
AspenN
by
New Contributor III

Fairly new to Insights and trying to create a relationship between a feature class and a table view stored in a PostgreSQL database. The common field is a GUID global-id in the feature class and a UUID field in the table. So far, it appears that Insights won't read these field types. Is there a work-around solution to make this relationship work so that we can start doing analytics in Insights?

0 Kudos
1 Solution

Accepted Solutions
ChakaClarke
Esri Contributor

A quick work around to access fields that are not supported by Insights; Like PostgresSQL UUID. Is to create a view of the table (with the GUUID) in the Postgres SQL Database, and cast the the GUID column to type text which will make it visible in Insights. These Views can be used to create the 

 

For Example:
To access a table such as :

CREATE TABLE contacts_guid (
contact_id uuid DEFAULT uuid_generate_v4 (),
first_name VARCHAR NOT NULL,
last_name VARCHAR NOT NULL,
PRIMARY KEY (contact_id)
);

 Create a View with the script as follows:

Create view now_I_see_guid as SELECT contact_id::text, first_name, last_name
FROM public.contacts_guid;

In Insights search for the view "now_I_see_guid", The table is visible and the GUID column can be used as a text field and can be used in relationships with other views that have UUID fields cast to Text.

 

 

View solution in original post

1 Reply
ChakaClarke
Esri Contributor

A quick work around to access fields that are not supported by Insights; Like PostgresSQL UUID. Is to create a view of the table (with the GUUID) in the Postgres SQL Database, and cast the the GUID column to type text which will make it visible in Insights. These Views can be used to create the 

 

For Example:
To access a table such as :

CREATE TABLE contacts_guid (
contact_id uuid DEFAULT uuid_generate_v4 (),
first_name VARCHAR NOT NULL,
last_name VARCHAR NOT NULL,
PRIMARY KEY (contact_id)
);

 Create a View with the script as follows:

Create view now_I_see_guid as SELECT contact_id::text, first_name, last_name
FROM public.contacts_guid;

In Insights search for the view "now_I_see_guid", The table is visible and the GUID column can be used as a text field and can be used in relationships with other views that have UUID fields cast to Text.