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 "blow-by-blow account" 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 "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!
