Select orphaned rows (in a generic way)

Question

I have a table my_tables which gets referenced as foreign key in several tables.

I would like to select all rows of my_table which are not referenced in an other table.

AFAIK, it should be possible to do this in a generic way (with some introspection magic).

Answer 1

Quoting the manual:

A foreign key must reference columns that either are a primary key or form a unique constraint.

So that's not necessarily limited to the PK. But if we start with pg_constraint, we get all FK constraints pointing to the target table automatically. No need to provide any key columns - except if you want to limit to certain FKs.

Using the object identifiers type regclass and table aliases, we can keep the function short and the result safe and unambiguous:

Basic query

SELECT format(E'SELECT * FROM %s t\nWHERE  NOT EXISTS (SELECT FROM %s'
            , c.confrelid::regclass
            , string_agg(format('%s WHERE %s = %s)', c.conrelid::regclass, src.cols, tgt.cols)
                       , E'\nAND    NOT EXISTS (SELECT FROM '))
FROM   pg_constraint c
     , cardinality(c.conkey) AS col_ct
     , LATERAL (
   SELECT concat(CASE WHEN col_ct > 1 THEN '(' END
               , string_agg(quote_ident(attname), ', ' ORDER BY fld.ord) -- original order
               , CASE WHEN col_ct > 1 THEN ')' END) AS cols
   FROM   unnest(c.conkey) WITH ORDINALITY fld(attnum, ord)             -- possibly n cols
   JOIN   pg_catalog.pg_attribute a ON (a.attrelid, a.attnum) = (c.conrelid, fld.attnum)
   ) src
     , LATERAL (
   SELECT concat(CASE WHEN col_ct > 1 THEN '(' END     -- parentheses for multiple columns
               , string_agg('t.' || quote_ident(attname), ', t.' ORDER BY fld.ord)
               , CASE WHEN col_ct > 1 THEN ')' END) AS cols
   FROM   unnest(c.confkey) WITH ORDINALITY fld(attnum, ord)
   JOIN   pg_catalog.pg_attribute a ON (a.attrelid, a.attnum) = (c.confrelid, fld.attnum)
   ) tgt
WHERE  c.confrelid = 'my_table'::regclass -- target table name, optionally schema-qualified
AND    c.contype = 'f'  -- FK constraints
GROUP  BY c.confrelid;

Produces a query of the form:

SELECT * FROM my_table t
WHERE  NOT EXISTS (SELECT FROM schema1.tbl1 WHERE col1 = t.id)
AND    NOT EXISTS (SELECT FROM "tB-l2" WHERE ("COL2", col3) = (t.col4, t.col5));

Which returns all rows that are not currently referenced by any FK constraint.

If cardinality(c.conkey) > 1 then it's safe to also assume cardinality(c.confkey) > 1. So only count once to decide whether to add parentheses.

Full automation

To make this work for any input table dynamically, create a polymorphic function taking a row value of the table:

CREATE OR REPLACE FUNCTION f_orphans(_tbl anyelement)
  RETURNS SETOF anyelement AS
$func$
BEGIN

RETURN QUERY EXECUTE (  -- exactly the query from above
SELECT format(E'SELECT * FROM %s t\nWHERE  NOT EXISTS (SELECT FROM %s'
            , c.confrelid::regclass
            , string_agg(format('%s WHERE %s = %s)', c.conrelid::regclass, src.cols, tgt.cols)
                       , E'\nAND    NOT EXISTS (SELECT FROM '))
FROM   pg_constraint c
     , cardinality(c.conkey) AS col_ct
     , LATERAL (
   SELECT concat(CASE WHEN col_ct > 1 THEN '(' END
               , string_agg(quote_ident(attname), ', ' ORDER BY fld.ord)
               , CASE WHEN col_ct > 1 THEN ')' END) AS cols
   FROM   unnest(c.conkey) WITH ORDINALITY fld(attnum, ord)
   JOIN   pg_catalog.pg_attribute a ON (a.attrelid, a.attnum) = (c.conrelid, fld.attnum)
   ) src
     , LATERAL (
   SELECT concat(CASE WHEN col_ct > 1 THEN '(' END
               , string_agg('t.' || quote_ident(attname), ', t.' ORDER BY fld.ord)
               , CASE WHEN col_ct > 1 THEN ')' END) AS cols
   FROM   unnest(c.confkey) WITH ORDINALITY fld(attnum, ord)
   JOIN   pg_catalog.pg_attribute a ON (a.attrelid, a.attnum) = (c.confrelid, fld.attnum)
   ) tgt
WHERE  c.confrelid = pg_typeof(_tbl)::text::regclass  -- input goes here!
AND    c.contype = 'f'
GROUP  BY c.confrelid
);

END
$func$  LANGUAGE plpgsql;

Call (important)!

SELECT * FROM f_orphans(NULL::my_table);

Or:

SELECT * FROM f_orphans(NULL::myschema.my_table);

Related:

• PostgreSQL: Pass table as argument in function
• Retrieving all PK and FK
• SQL order by query results in any arbitrary (but reproducible) manner
• How to preserve the original order of elements in an unnested array?

Answer 2

Here is a sample query which allows you to create the sql statement:

WITH 
  fkey_fields AS (
    SELECT DISTINCT
        nt.nspname AS table_schema,
        t.relname AS table_name,
        unnest(c.conkey) AS field_index,
        nft.nspname AS foreign_table_schema,
        ft.relname AS foreign_table_name,
        unnest(c.confkey) AS foreign_field_index
      FROM pg_constraint c
      JOIN pg_class t ON t.oid = c.conrelid
      JOIN pg_namespace nt ON nt.oid = t.relnamespace
      JOIN pg_class ft ON ft.oid = c.confrelid
      JOIN pg_namespace nft ON nft.oid = ft.relnamespace
      WHERE
        c.contype = 'f'
  ),
  table_fields AS (
    SELECT
        rn.nspname AS table_schema,
        c.relname AS table_name,
        a.attname AS field_name,
        a.attnum AS field_index
      FROM pg_attribute a
      JOIN pg_class c ON a.attrelid = c.oid
      JOIN pg_namespace rn ON c.relnamespace = rn.oid
      WHERE 
        a.attnum > 0 AND
        a.attisdropped <> 't' 
  )
SELECT
  concat(
    format(
      'SELECT * FROM %I.%I',
      fkf.foreign_table_schema,
      fkf.foreign_table_name
    ),

    E'\nWHERE\n' ||
    string_agg(
      format('NOT EXISTS (SELECT 1 FROM %I.%I where %I.%I.%I = %I.%I.%I)',
        fkf.table_schema,
        fkf.table_name,
        fkf.table_schema,
        fkf.table_name,
        tf.field_name,    
        fkf.foreign_table_schema,
        fkf.foreign_table_name,
        foreign_tf.field_name  
      ),
      E' AND\n'
    )
  )
  FROM fkey_fields fkf
  JOIN table_fields tf ON
    tf.table_schema = fkf.table_schema AND
    tf.table_name = fkf.table_name AND 
    tf.field_index = fkf.field_index
  JOIN table_fields foreign_tf ON
    foreign_tf.table_schema = fkf.foreign_table_schema AND
    foreign_tf.table_name = fkf.foreign_table_name AND 
    foreign_tf.field_index = fkf.foreign_field_index
  WHERE
    fkf.foreign_table_schema = 'public' AND 
    fkf.foreign_table_name = 'my_table'
  GROUP BY
    fkf.foreign_table_schema,
    fkf.foreign_table_name;

The output example is below;

SELECT * FROM public.my_table
WHERE
NOT EXISTS (SELECT 1 FROM public.table2 where public.table2.my_table_id = public.my_table.id) AND 
NOT EXISTS (SELECT 1 FROM public.table3 where public.table3.my_table_id = public.my_table.id)

fiddle link is here