Find all tables that do not have a given column in Postgres

Published: 2020-06-15
Tags: postgresql • sql

If you have hundreds of tables, it can be a pain to search each individual one to check if it has a given column. This query left joins all tables for a given schema, some_schema, against itself, then shows tables that do not have a given column, some_column.

select *
from (
  select distinct table_name
  from information_schema.columns
  where table_schema = 'some_schema'
) a
left join (
  select distinct table_name
  from information_schema.columns
  where table_schema = 'some_schema'
  and column_name = 'some_column'
) b
on a.table_name = b.table_name
where b.table_name is null;