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