How to find a table having a specific column in postgresql

Advanced methods of finding the elusive column

Filtering the schema universe

Sometimes, you need to tailor your search to a specific schema. Fear not, here's how:

SELECT table_schema, table_name
FROM information_schema.columns
WHERE column_name = 'i_am_here_somewhere'
AND table_schema = 'public'; --=sneaking in your schema here 


This is like searching a section of the library for your column. Be it "public" or any other schema of your choice.

Joining forces with system catalogs

Go the extra mile by pulling in pg_class and pg_namespace.

SELECT c.relname AS table_name
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_attribute a ON a.attrelid = c.oid
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE a.attname = 'col_here'
AND c.relkind = 'r'
AND n.nspname = 'public'; -- =change this to your specific schema


Joining these tables gives some extra array of details, with c.relname serving the table names and filtering done using pg_attribute and pg_namespace.

Going undercover due to limited access

If amidst all this, you hit a wall, it's probably access restrictions. Remember to check with your friendly neighborhood database admin for access rights.

Evading system tables

Not interested in system tables? Make use of these versions of queries that smartly exclude the pg_catalog and other system scheme culprits. We're keeping it clean!

Command-line tools at your service

psql and grep

Command-line aficionados, we got you too! Use psql and grep:

\dt+ *.* | grep -B 5 'column_searchlight'


Sift through the haystack effectively with \dt+ *.* (lists all tables and columns) teamed with grep.

Going wild with wildcard searches

To widen the net and catch columns containing a part of the column name you're fishing for, use:

SELECT table_name
FROM information_schema.columns
WHERE column_name LIKE '%gem_in_a_mine%';


Wildcard % helps dig out columns with the specified substring in their names.