Search code examples
postgresqlinformation-schema

PostgreSQL information schema query for tables without a specific data type

I'm trying to write a PostgreSQL query to get all the tables (from a specified schema) which don't have any columns of a specific data type, for example, show all tables without any integer type columns. so far I can manage to get only the table names, the data types of the columns they have and their count but I feel like that's the wrong direction in order to get what I want. any help appreciated, thanks

SELECT Table_Name, Data_Type, COUNT(Data_Type)
FROM Information_schema.Columns
WHERE Table_Schema = 'project'
GROUP BY Table_Name, Data_Type
Solution

  • You'll want to start with the tables table and then use an EXISTS subquery:

    SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'project'
  AND NOT EXISTS (
    SELECT *
    FROM information_schema.columns
    WHERE column.table_schema = tables.table_schema
      AND column.table_name = tables.table_name
      AND data_type = 'integer'
  )