Find tables without relationships - Loner Tables - in PostgreSQL database

Query below lists tables that are not referencing and are not referenced by other tables. We can call this type of tables a Loner Tables. Diagram below illustrates this concept:

Learn more about Loner Tables

Query

select distinct 
       'No FKs >-' as refs,
       tab.table_schema || '.' || tab.table_name as table,
       '>- no FKs' as foreign_keys
from information_schema.tables tab
left join(
          select distinct tco.table_schema,
                          tco.table_name
          from information_schema.referential_constraints rco
          join information_schema.table_constraints tco
               on rco.unique_constraint_name = tco.constraint_name
               and rco.unique_constraint_schema = tco.table_schema
          union all
          select distinct tco.table_schema,
                          tco.table_name
          from information_schema.referential_constraints rco
          join information_schema.table_constraints tco 
               on rco.constraint_name = tco.constraint_name
               and rco.constraint_schema = tco.table_schema
) rel on rel.table_name = tab.table_name
      and rel.table_schema = tab.table_schema
where tab.table_schema not in ('information_schema', 'pg_catalog') 
      and tab.table_type ='BASE TABLE'
      and rel.table_name is null
order by "table";

Columns

  • refs - icon indicating that there is no FK reference to this table
  • table - table name preceded by schema name
  • foreign_keys - icon indicating that there is no FK in this table

Rows

  • One row represents one table
  • Scope of rows: tables without relationships
  • Ordered by table schema and table name

Sample results

Loner Tables in pagila sample database:

0
There are no comments. Click here to write the first comment.