3

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).

  • 1
    Do you have a fixed set of tables, or do you want to use meta-data from the catalog to determine the tables? – Lennart May 3 '18 at 10:37
  • @Lennart I would like to use meta-data. Our system has plugins. This means not all schemas are the same. Sometimes there are more foreign-keys to the table, sometime less. – guettli May 3 '18 at 11:12
  • 1
    Ok, I think you will have to use some small procedural part to get the meta-data (tables and corresponding columns for f.k.) out of the catalog and do a loop over those tables and create a union clause like: select 1 from T where not exists (select 1 from child1 c1 where c1.fkcols = T.pkcols union all select 1 from child2 c2 where c1.fkcols = T.pkcols union all ...) – Lennart May 3 '18 at 11:25
5
+50

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:

  • thank you very much . In my case all foreign keys reference a primary key. – guettli May 22 '18 at 9:25
4

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

  • Great, works good. This prints out sql and I just need to add an additional "where pk=X". Thank you! – guettli May 15 '18 at 12:24
  • Does the answer take care of multi-column foreign key constraints? – ypercubeᵀᴹ May 19 '18 at 20:42
  • No, This query definitely fails in that situation. – Sahap Asci May 19 '18 at 21:19
  • @ypercubeᵀᴹ: It added an answer to cover all possible FKs. – Erwin Brandstetter May 20 '18 at 4:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.