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:
tablesview that contains all tables and views defined in the database
- and the
columnsview 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:
Let's say we want to get all tables with a
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_schemaas a prefix to the view name;
For the query to retrieve all tables with a
student_idcolumn it will have to join the two views that hold this information -
Postgres has its own internal schemas that you will want to exclude (e.g. the
information_schemaitself and the
pg_catalogschema). In this case, we want the
You can filter
tablesby table type:
BASE TABLEfor a persistent base table (the normal table type),
VIEWfor a view,
FOREIGN TABLEfor a foreign table, or
LOCAL TEMPORARYfor a temporary table. In this case, I only want the
Example of the query output:
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:
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.