I'm using PostgreSQL 9.1. I have the column name of a table. Is it possible to find the table(s) that has/have this column? If so, how?
Add a comment
|
7 Answers
You can also do
select table_name from information_schema.columns where column_name = 'your_column_name'
3 Comments
Ken Bellows
oddly, I've seen instances where this query shows tables that @RomanPekar's query does not; I wonder why that would be
roman
@KenBellows I guess pg_class / pg_attirbute can change with new versions of Postgresql while information_schema is defined in ANSI specification. So for general queries I'd say this answer is better. Sometimes I need to have object id for example, in this case I need to use db-engine specific tables. Plus, information_schema views are always an additional step over db engine specific tables and sometimes can lead to a (slightly) worse performance
Thomas Altfather Good
This was the more accurate of the two solutions offered - in my case. The pg_class query missed two (of 150) tables. The information_schema query captured all of the tables. I'll have to dig around to see why two tables fell outside of the join. In any event thanks for the info!
you can query system catalogs:
select c.relname
from pg_class as c
inner join pg_attribute as a on a.attrelid = c.oid
where a.attname = <column name> and c.relkind = 'r'
3 Comments
Skippy le Grand Gourou
Note that this query doesn't seem to accept '%' wildcards, while the query in Ravi's answer does.
jutky
@SkippyleGrandGourou It does accepts " like 'id%' "
Lrawls
this didn't work for me with or without wildcards, i've had to use information.schema to search
I've used the query of @Roman Pekar as a base and added schema name (relevant in my case)
select n.nspname as schema ,c.relname
from pg_class as c
inner join pg_attribute as a on a.attrelid = c.oid
inner join pg_namespace as n on c.relnamespace = n.oid
where a.attname = 'id_number' and c.relkind = 'r'
Comments
Simply:
$ psql mydatabase -c '\d *' | grep -B10 'mycolname'
Enlarge -B offset to get table name, if need
1 Comment
lacostenycoder
kinda nifty little shortcut here, nice.
Wildcard Support Find the table schema and table name that contains the string you want to find.
select t.table_schema,
t.table_name
from information_schema.tables t
inner join information_schema.columns c on c.table_name = t.table_name
and c.table_schema = t.table_schema
where c.column_name like '%STRING%'
and t.table_schema not in ('information_schema', 'pg_catalog')
and t.table_type = 'BASE TABLE'
order by t.table_schema;
Comments
If you are unsure about the exact column name, and know only a part of it or having a wild guess; this might help.
select table_name, column_name from information_schema.columns where column_name like '%po%'
1 Comment
Praveen Agarwal
This is also working fine. I checked.
select t.table_schema,
t.table_name
from information_schema.tables t
inner join information_schema.columns c on c.table_name = t.table_name
and c.table_schema = t.table_schema
where c.column_name = 'name_colum'
and t.table_schema not in ('information_schema', 'pg_catalog')
and t.table_type = 'BASE TABLE'
order by t.table_schema;
1 Comment
Das_Geek
Please edit your answer to include an explanation for your code. The question is more than six years old, and already has an accepted answer in addition to several well-upvoted, well-explained ones. Without such an explanation on your answer, it stands to be downvoted or removed. Adding that extra info would help justify your answer's continued existence here.