How to Find a Table Having a Specific Column in Postgresql

How to find a table having a specific column in postgresql

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'

sql fiddle demo

Find all tables that have X column name

Most, but not all, databases support the information_schema tables. If so, you can do:

select table_name
from information_schema.columns t
where column_name = YOURCOLUMNNAME;

If your database doesn't support the information_schema views, then any reasonable database has an alternative method for getting this information.

You may need to specify the database name, but that depends on the database. It could be:

select table_name
from YOURDATABASENAME.information_schema.columns t
where column_name = YOURCOLUMNNAME;

or

select table_name
from YOURDATABASENAME.information_schema.columns t
where column_name = YOURCOLUMNNAME and schema_name = YOURDATABASENAME;

Select tables that contains specific column name

Use a having clause:

select t.table_name
     , array_agg(c.column_name::text) as columns
  from information_schema.tables t 
  join information_schema.columns c 
    on t.table_name = c.table_name
   and t.table_schema = c.table_schema
 where t.table_schema = 'public' 
   and t.table_type = 'BASE TABLE' 
 group by t.table_name
 having 'email' = any(array_agg(c.column_name::text))

If you want to check for multiple columns you could do that like this:

select t.table_name
     , array_agg(c.column_name::text) as columns
  from information_schema.tables t 
  join information_schema.columns c 
    on t.table_name = c.table_name
   and t.table_schema = c.table_schema
 where t.table_schema = 'public' 
   and t.table_type = 'BASE TABLE' 
 group by t.table_name
 having array_agg(c.column_name::text) @> array['email', 'phone']

PostgreSQL - Select tables where specific column is not present

An easy way would be counting how many times side_id appears in that table's column listing. If the sum is zero, the table has no site_id:

SELECT table_name
FROM information_schema.columns 
WHERE table_schema = 'schema_A'
GROUP BY table_name
HAVING SUM(CASE WHEN column_name LIKE 'site_id' THEN 1 ELSE 0 END) = 0;

I'm also not sure if it was intentional or not, but LIKE 'site_id' will have the same effect as = 'site_id'. If you meant to check if it contains site_id, LIKE '%site_id%' would be more appropriate.

PostgreSQL: SQL script to get a list of all tables that have a particular column as foreign key

SELECT
    r.table_name
FROM information_schema.constraint_column_usage       u
INNER JOIN information_schema.referential_constraints fk
           ON u.constraint_catalog = fk.unique_constraint_catalog
               AND u.constraint_schema = fk.unique_constraint_schema
               AND u.constraint_name = fk.unique_constraint_name
INNER JOIN information_schema.key_column_usage        r
           ON r.constraint_catalog = fk.constraint_catalog
               AND r.constraint_schema = fk.constraint_schema
               AND r.constraint_name = fk.constraint_name
WHERE
    u.column_name = 'id' AND
    u.table_catalog = 'db_name' AND
    u.table_schema = 'public' AND
    u.table_name = 'table_a'

This uses the full catalog/schema/name triplet to identify a db table from all 3 information_schema views. You can drop one or two as required.

The query lists all tables that have a foreign key constraint against the column 'a' in table 'd'

Find values in specific column of unknown table in PostgreSQL from information_schema

This is what I came up with (thanks Denis for the advice)~

http://www.sqlfiddle.com/#!15/64e41/2/0

How to search for a column name - Postgresql DB - New one

You used a wildcard with an equality (=) operator:

c.column_name = '%actual%start%date%'

Use like instead:

c.column_name like '%actual%start%date%'

How to search a specific value in all tables (PostgreSQL)?

How about dumping the contents of the database, then using grep?

$ pg_dump --data-only --inserts -U postgres your-db-name > a.tmp
$ grep United a.tmp
INSERT INTO countries VALUES ('US', 'United States');
INSERT INTO countries VALUES ('GB', 'United Kingdom');

The same utility, pg_dump, can include column names in the output. Just change --inserts to --column-inserts. That way you can search for specific column names, too. But if I were looking for column names, I'd probably dump the schema instead of the data.

$ pg_dump --data-only --column-inserts -U postgres your-db-name > a.tmp
$ grep country_code a.tmp
INSERT INTO countries (iso_country_code, iso_country_name) VALUES ('US', 'United  States');
INSERT INTO countries (iso_country_code, iso_country_name) VALUES ('GB', 'United Kingdom');

---

Related Topics