How to get the intersection length of touching geometries with ST_Touches

I am trying to develop a query in Postgis, where it can solve this problem:

I have a geometry and I wanna know which of the polygons that touches it, there is the highest contact area of this geometry. After I recognize this polygon I will take its value in a specific column and put this value in the same column but in my geometry.

Someone know how can I do that? I am a new user in postgresql/postgis.

Answer

As pointed out by @JGH in the comments, the overlapping area will be zero if you use ST_Touches alone. What you can do is to filter out only the geometries that do touch your reference geometry and then use ST_Intersection to get the intersection area, so that you can finally calculate the length of the intersection with ST_Length.

Data Sample

The geometry values depicted above are inside the CTE:

WITH j (id,geom) AS (
  VALUES 
  (1,'POLYGON((-4.64 54.19,-4.59 54.19,-4.59 54.17,-4.64 54.17,-4.64 54.19))'),
  (2,'POLYGON((-4.59 54.19,-4.56 54.19,-4.56 54.17,-4.59 54.17,-4.59 54.19))'),
  (3,'LINESTRING(-4.65 54.19,-4.57 54.21)'),
  (4,'POLYGON((-4.66 54.21,-4.60 54.21,-4.60 54.20,-4.66 54.20,-4.66 54.21))'),
  (5,'POINT(-4.57 54.20)')
)
SELECT 
  id, 
  ST_Length(
    ST_Intersection(
      geom,
      'POLYGON((-4.62 54.22,-4.58 54.22,-4.58 54.19,
                -4.62 54.19,-4.62 54.22))')) AS touch_length
FROM j
WHERE 
  ST_Touches(
    geom,
    'POLYGON((-4.62 54.22,-4.58 54.22,-4.58 54.19,
              -4.62 54.19,-4.62 54.22))')
ORDER BY touch_length DESC
LIMIT 1;

 id |    touch_length     
----+---------------------
  1 | 0.03000000000000025
(1 Zeile)