How to merge polygons that touch and share some features
You only need ST_ClusterIntersectingWin().
If you're below PostGIS 3.4.0 but above 2.3.0, you can use ST_ClusterDBSCAN() with target distance of 0. Here's a related thread on SO from recently.
create table hrl_tst.your_shapes_back_together as
with both_tiles as (table hrl_tst.tile1 union all table hrl_tst.tile2)
,clustered as (
select *,ST_ClusterDBSCAN(geom,0,1)over(partition by cat) c_num
from both_tiles)
select cat
,array_agg(unid order by unid)--dissolve same `cat`s ignoring `unid` diff
,st_makevalid(st_union(geom)) as geom --it's healthy to `st_makevalid()`
from clustered
group by cat,c_num;
I spawned around 190 polygons assigned to 3 cat classes (it's a zoomed in on ~120):
And after running the query above, all polygons that share the cat feature and ST_Touch() (actually, they can ST_Intersect() in any way) directly or indirectly (through a neighbour they touch, or a chain of touching neighbours' neighbours) get merged.
It can stitch shapes broken along a tile boundary back together, but as you can see it works with arbitrarily shaped splits just as well - I just smashed my two squares into pieces, threw some away and shook everything hard to make mess*.
This does not attempt to simply ST_Union() all Polygons that share the same cat feature - that would be trivial, but result in one large MultiPolygon for each cat. The disjoint brown, blue and green shapes above are separate, standalone shapes unless they touch somehow.
If your unid field is unique but spans the sets, meaning that the same shape's split between the two tables but holds the same unid in both, and you don't want to merge shapes of differend unid, you can add unid anywhere on the group by list and swap out array_agg() for just unid.
*(Make some squares, spawn 90 random points in each, subdivide them with Voronoi cells around those, discard random 30%, wiggle the rest furiously with ST_Translate() with random() x&y offsets, then trim them with ST_Difference() so that they can touch, but not overlap).