Postgresql: Loop through each table and check for column?

database, pgadmin, postgresql

Solution

You can skip `information_schema.tables` entirely. Just:

DO
$$
DECLARE
    rec record;
BEGIN
    FOR rec IN 
        SELECT table_schema, table_name, column_name
        FROM information_schema.columns 
        WHERE column_name = 'x'
    LOOP
        EXECUTE format('ALTER TABLE %I.%I RENAME COLUMN %I TO newname;',
            rec.table_schema, rec.table_name, rec.column_name);
    END LOOP;
END;
$$
LANGUAGE plpgsql;

with appropriate substitutions for `'x'` and `newname`. Or make it into a function that takes them as parameters, whatever.

Problem

I am trying to write a simple `SQL` query in `pgAdmin` to LOOP through each table in a database and change a specified column name IF it exists. I have never coded in `SQL` but after searching through many forums have managed to come up with: ``` DO BEGIN FOR i IN SELECT table_name FROM information_schema.tables LOOP IF SELECT column_name FROM information_schema.columns WHERE table_name = 'i.table_name' THEN ALTER TABLE i.table_name RENAME COLUMN old_column_name TO new_column_name END IF; END LOOP; ```

Original source