Finding all tables with a specific column name (postgres)

The information schema

The information schema consists of a set of views that contain information about the objects defined in the current database.

This is how Postgres defines the information schema. If you look at the official documentation, you will find two relevant views in this schema:

• the tables view that contains all tables and views defined in the database
• and the columns view that contains all columns (or view columns) defined in the database

These two views will give you access to information on the tables and the columns in our database, so let's see how we can use them:

The query

Let's say we want to get all tables with a student_id column.

If you are working with a single public schema (the default schema that Postgres will use to hold your tables), this query will do the trick:

SELECT 
  tables.tables_name
FROM information_schema.tables AS tables
JOIN information_schema.columns AS columns
  ON columns.table_name = tables.table_name
  AND columns.table_schema = tables.table_schema
WHERE columns.column_name = 'student_id'
  AND tables.table_schema = 'public'
  AND tables.table_type = 'BASE TABLE'
ORDER BY tables.table_name

A few notes on this query:

• To access any of the views, you need to add information_schema as a prefix to the view name;

• For the query to retrieve all tables with a student_id column it will have to join the two views that hold this information - information_schema.tables and information_schema.columns;

• Postgres has its own internal schemas that you will want to exclude (e.g. the information_schema itself and the pg_catalog schema). In this case, we want the public schema only;

• You can filter tables by table type: BASE TABLE for a persistent base table (the normal table type), VIEW for a view, FOREIGN TABLE for a foreign table, or LOCAL TEMPORARY for a temporary table. In this case, I only want the BASE TABLE.

Example of the query output:

Multiple schemas

If you are working with multiple schemas, the query needs some adjustments.

You can filter the results to a specific schema or it might be useful to select the table names together with the corresponding schema name. In the latter case, you will need to add tables.table_schema to the SELECT statement and, instead of choosing a specific schema, exclude Postgres internal schemas:

SELECT
   tables.table_schema,
   tables.table_name
FROM information_schema.tables as tables
JOIN information_schema.columns AS columns
  ON columns.table_name = tables.table_name
  AND columns.table_schema = tables.table_schema
WHERE columns.column_name = 'student_id'
  AND tables.table_schema not in ('information_schema', 'pg_catalog')
  AND tables.table_type = 'BASE TABLE'
ORDER by tables.table_schema

Example of the query output:

Conclusion

The information_schema contains metadata about the data objects stored in a database. It provides plenty of information not only on tables and columns but also permissions, triggers, constraints, and others.

The example I've used in this article is a real case example. I have recently had to run a migration where all data of a student had to be migrated to another student. So instead of manually checking and writing down all tables with a student reference, I queried information_schema and was then able to iterate each one to perform an update.

The information schema views do not, however, contain information about PostgreSQL-specific features; to inquire about those you need to query the system catalogs or other PostgreSQL-specific views.

Whether you need this kind of data for information purposes, migrations, or other actions, know that you will most probably find the information you need by querying Postgres internal schemas.