How do I loop through every table and alter the PK column like so? (Postgres) Using psql in Postgres 12, I need to alter every column in every table of my database that's:

int/bigint

not null

primary key


to:

identity column

auto-increment

with default value starting at max value of current rows


I know this should be doable using PL/PgSQL, but I'm not familiar with it.
Can someone help me out?
What I suggest you do is to start up psql using the -E option.
This will give you a &quot;blow-by-blow account&quot; of the SQL that's used to perform operations in psql.
From issuing the command man psql:

-E
--echo-hidden

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 &quot;background&quot; 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!