Find tables without relationships - Loner Tables - in PostgreSQL database

Bart Gawrych - Dataedo Team Bart Gawrych 2019-06-03

Table of Contents:


    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.

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy (updated 28-04-2020).
    Accept