Friday, February 27, 2009

in case you need it: finding postgresql tables with a specific column

I have a database with a bunch of tables that all have "username" columns (actual varchars, not foreign keys). Two or three times a year, I end up having to change that for a user (usernames are email addresses, and users change their email accounts, or switch jobs or whatever). Previously I'd relied on a bunch of update statements that blindly attempted to change that field in all tables, but I finally decided today to write a script to automate that task.

Finding all the tables with username as a column:

select c.relname from pg_class c
    join pg_attribute a on (c.oid = a.attrelid)
    join pg_roles r2 on (c.relowner = r2.oid)
where a.attname = 'username' and c.relkind = 'r' and r2.rolname = 'pkf';

Time to write the script (with error checking, tests, etc): about an hour. Time I usually spend changing usernames without the script: about 10 minutes a year. So, a net win, or net loss?

No comments: