andrasz andrasz - 6 months ago 15
SQL Question

PostgreSQL - Select tables where specific column is not present

I have a schema with about 3000 tables in a PostgreSQL DB, all having one row with the following columns:

id
,
area
,
use
,
geom
,
site_id


However, I just discovered that there's at least one table that misses the column
site_id
. I'm wondering if there are more of those.. How can I query for tables NOT having the column
site_id
?

My following idea doesn't work. Any suggestions?

SELECT table_name
FROM information_schema.columns
WHERE table_schema = 'schema_A' AND column_name NOT LIKE 'site_id'
GROUP BY table_name;

Answer

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.