Often in PostgreSQL you may want to find a table with a specific column name.
You can use the following syntax to do so:
SELECT table_name
FROM information_schema.columns
WHERE column_name = 'amount';
This particular example will return all tables that have a column named amount.
If multiple tables contain this column name then multiple table names will be returned.
The following example shows how to use this syntax in practice.
Example: How to Find Table with Specific Column Name in PostgreSQL
Suppose that we create the following table named sales that contains information about total sales made by various retail stores:
-- create table named 'sales'
CREATE TABLE sales (
store TEXT,
amount INTEGER
);
-- add rows to the table
INSERT INTO sales VALUES ('A', 130);
INSERT INTO sales VALUES ('B', 290);
INSERT INTO sales VALUES ('C', 1430);
INSERT INTO sales VALUES ('D', 447);
-- view all rows in table
SELECT * FROM sales;
store | amount
-------+--------
A | 130
B | 290
C | 1430
D | 447
And suppose that we create another table named athletes that contains information about various basketball players:
-- create table named 'athletes'
CREATE TABLE athletes (
id INTEGER,
team TEXT,
amount INTEGER
);
-- add rows to the table
INSERT INTO athletes VALUES (0001, 'Mavs', 22);
INSERT INTO athletes VALUES (0002, 'Lakers', 30);
INSERT INTO athletes VALUES (0003, 'Celtics', 12);
INSERT INTO athletes VALUES (0004, 'Rockets', 9);
-- view all rows in table
SELECT * FROM athletes;
id | team | amount
----+---------+--------
1 | Mavs | 22
2 | Lakers | 30
3 | Celtics | 12
4 | Rockets | 9
Now suppose that we would like to find the table that contains the column name team.
We can use the following syntax to do so:
SELECT table_name
FROM information_schema.columns
WHERE column_name = 'team';
table_name
------------
athletes
This returns the table named athletes since this is the table that contains a column named team.
Suppose instead that we searched for the table that contains the column name amount:
SELECT table_name
FROM information_schema.columns
WHERE column_name = 'amount';
table_name
------------
sales
athletes
Notice that this returns the tables named sales and athletes since both of these tables contain a column named amount.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to List Column Names and Types for Table
PostgreSQL: Count Occurrences of Each Value in Column
PostgreSQL: How to Select Rows Having Count Greater Than
