How to Select * FROM all tables in a schema-CodePudding

I have 100s of tables with the same structure in the same schema. I would like to run a query to see all rows where the 'sqft' column is NULL

SELECT * FROM table WHERE sqft = NULL

The tables I would like to iterate over all begin with the prefix 'tb_'
e.g 'tb_115_spooner_st' After trying numerous solutions posted on here I cannot properly iterate over all these tables with a single script.

This is what I am currently working with

do $$
declare
    rec record;
    query text;
begin
    for rec in select * from pg_tables where schemaname = 'public'
    loop
        query = format('SELECT * FROM %s WHERE sqft = NULL LIMIT 1', rec.tablename);
        --raise notice '%', query;
        execute query;
    end loop;
end
$$ language plpgsql;

I am quite new to writing more complex SQL commands like this and have trouble understanding what is going wrong. I know there needs to be a section where the prefix is a condition, but the code running right now just returns a 'DO' in the console. Any help is appreciated.

CodePudding user response：

Consider using the INFORMATION_SCHEMA.COLUMNS view to find the tables you need to query.

SELECT 
  CONCAT('SELECT * FROM ', table_name,' WHERE sqft IS NULL;') 
FROM 
  INFORMATION_SCHEMA.COLUMNS 
WHERE 
  COLUMN_NAME = 'sqft'

This will get you a list of SQL statements you can copy and paste into a new terminal and just run as a normal query.