How to find tables with specific column data types in a PostgreSQL

Thanks to Bart Gawrych for blogging this very useful post on which my own post is based on.

I needed to find all tables in a specific schema that used json or jsonb data type columns. Here is my query, saved in a file called "find_cols.sql":

select col.table_schema,
       col.table_name,
       col.ordinal_position as column_id,
       col.column_name,
       col.data_type
from information_schema.columns col
join information_schema.tables tab on tab.table_schema = col.table_schema
                                   and tab.table_name = col.table_name
                                   and tab.table_type = 'BASE TABLE'
where col.table_schema in ('myschema')
      and col.data_type IN ( 'json','jsonb' )
order by col.table_schema,
         col.table_name,
         col.ordinal_position;

Execution in psql directly on server like this:

psql mydb -f find_cols.sql