Postgresql – How to remove duplicates and inc table records using postgres sql regex

1. There’s btrim() if you want to use both rtrim() and ltrim().
2. If you need multiple replace(), you can use a single translate().
3. You called concat() with a single argument, that you constructed by concatenating with ||. You only need one of these: separate arguments passed to concat() with commas, or remove concat() entirely and concatenate with only || instead.
4. [:word:] and [:alpha:] character classes are available in PostgreSQL 14 onwards. Assuming you’re on 9.5 (highest version tag you used), you can swap those out for a w, downgrading your regex a bit (it now allows identifiers to start with a digit, which is not allowed).
5. You’re currently catching only the schema in qualified names. You can expand the expression to tolerate dots in unquoted identifiers, or just an optional one in the middle of two quoted identifiers.
6. You can use distinct on(spotted_table)...order by spotted_table to get just one row for each distinct table.

Demo at db<>fiddle:

with cte as (
    select field1,field2
          ,translate(
                concat( '''' 
                   ,translate(
                        btrim(field2)
                       ,'"'''
                       ,'' )
                   ,'''' )
               ,E'n"'
               ,'' ) as edited_field2 
  from table1)
,cte2 as (
    select *,unnest(
                regexp_matches(
                    edited_field2
                   ,'(?:FROM)(?:s+ONLY)*s+([w.]+|"[^"]+"(?:."[^"]+")?)','gi' )
             ) AS spotted_table
    from cte)
select distinct on(spotted_table) field1,spotted_table,edited_field2
from cte2
where split_part(spotted_table,'.',1) !~* '_inc$'
order by spotted_table;

5. Consider the pre-built SQL parser tools recommended previously instead of the regular expressions.
6. All versions you tagged are no longer supported. Consider an update.