Find islands using PostGIS

Assuming that you have a guaranteed topological equality between island and water polygon rings, using a simpler EXISTS filter should increase performance:

SELECT  a.id
FROM    islands AS a
WHERE   a.type <> 'water'
  AND   EXISTS (
    SELECT  1
    FROM    islands AS b,
            LATERAL ST_DumpRings(b.geom) AS dmp   -- rings to polygons
    WHERE   b.type = 'water'
      AND   dmp.path[1] > 0                       -- only interior rings
      AND   ST_Equals(a.geom, dmp.geom)
  )
;

• ST_DumpRings is relatively cheap compared to constructing geometries, and enables finding matches more generically for all inner rings of a water body polygon
• EXISTS will quit its execution once the first result row of its query is truthy
• no additional aggregation (GROUP BY) required

---

@Cyril's answer will also work great with ST_DumpPoints, i.e. using

...
tblb AS (SELECT dmp.geom FROM islands, LATERAL ST_DumpRings(geom) AS dmp WHERE type = 'water' AND dmp.path[1] > 0)
... 

---

If topological equality is not guaranteed, you could check for ST_Within on the exterior ring polygon:

SELECT  a.id
FROM    islands AS a
WHERE   a.type <> 'water'
  AND   EXISTS (
    SELECT  1
    FROM    islands AS b,
            LATERAL ST_DumpRings(b.geom) AS dmp  -- rings to polygon
    WHERE   b.type = 'water'
      AND   dmp.path[1] = 0                      -- only exterior ring
      AND   ST_Within(a.geom, dmp.geom)
);

Here's another way to solve the question asked.

Run the script:

WITH                                                                          
tbla AS (SELECT * FROM islands WHERE type = 'land'),
tblb AS (SELECT ST_InteriorRingN(geom,1) geom FROM islands WHERE type = 'water')
SELECT ID, type, (a.geom) geom FROM tbla a INNER JOIN tblb b ON ST_Intersects(a.geom, b.geom);

Check the result, and if I misunderstood the question, let me know.