What I suggest you do is to start up
psql using the -E option.
This will give you a "blow-by-blow account" of the SQL that's used to perform operations in
From issuing the command
- Echo the actual queries generated by \d and other backslash commands. You can use this to study psql's internal operations. This
is equivalent to setting the variable ECHO_HIDDEN to on.
Then, perform your desired operations on a sample table in
psql and copy and paste the resultant "background" SQL into your editor of choice. Then write a
PL/pgSQL routine that cycles through the tables you wish to change which does the same thing - monkey-see, monkey-do!
As @a_horse_with_no_name pointed out, this request has been cross-posted (to StackOverflow here). As an advanced PL/pgSQL question, it probably belongs here. So, could you please put a line at the top of your question on StackOverflow pointing here and telling people that any answers are to be put here and not on StackOverflow. This eliminates duplication of effort on the part of those trying to help you - thanks! Please see this post on the topic.
p.s. welcome to the forum!