Enterprise geodatabase domains from source table are not applied to view

GreenCollarGroup

Enterprise geodatabase domains from source table are not applied to view.

I apply something like the following query using the 'create database view' tool.

SELECT t2.OBJECTID, t2.SHAPE, t2.attr, t2.type, t1.attr1, t3.attr2, t2.area_ha
FROM gis_project.sde.table1 t1
INNER JOIN gis_project.sde.table2 t2 ON t1.table1_id = t2.table1_id
INNER JOIN gis_project.sde.table3 t3 ON t2.table2_id = t3.table2_id

t2.type contains integers and the domain maps 0 = 'some text' and so on. The source table retains the domain but resulting view shows the integers.

I am going to see if I can create the view or the lookup reference in PgAdmin, but I would prefer to use esri tools.

ArcGIS Pro 3.2.0, PostgreSQL 15, PostGIS 3.4.1

GreenCollarGroup

I would still much prefer Esri tools but I have found a work around using PgAdmin.

Process modified from (https://www.the-art-of-web.com/sql/lookup-table/). I manually create a lookup table and set constraints on the original table. Something like this:

INSERT INTO lookup_table (name) SELECT DISTINCT type FROM table1;

 

UPDATE gisowner.lookup_table

SET description='string_value1'

WHERE name='0';

 

UPDATE gisowner.lookup_table

SET description='string_value2'

WHERE name!='0';

 

ALTER TABLE table1 ADD COLUMN typeid int;

 

UPDATE table1 SET typeid=lookup_table.id FROM lookup_table

    WHERE table1.type=lookup_table.name;

 

ALTER TABLE table1 ALTER typeid SET NOT NULL;

 

ALTER TABLE table1 ADD CONSTRAINT "table1_typeid_fkey"

    FOREIGN KEY (typeid) REFERENCES lookup_table(id);

 

ALTER TABLE table1 DROP COLUMN type;

 

SELECT * FROM gisowner.table1 LIMIT 20;

 

Once this is done I applied the lookup in the view definition:
SELECT t2.OBJECTID, t2.SHAPE, t2.attr, t2.type, t1.attr1, t3.attr2, t2.area_ha
FROM naperville.gisowner.table1 t1
INNER JOIN naperville.gisowner.table2 t2 ON t1.table1_id = t2.table1_id
INNER JOIN naperville.gisowner.table3 t3 ON t2.table2_id = t3.table2_id
JOIN naperville.gisowner.lookup_table lt ON t2.typeid = lt.id

I really hope there is a more straight forward approach using Pro...