Postgresql – How to loop through every table and alter the PK column like so? (Postgres)

dynamic-sqlplpgsqlpostgresqlpostgresql-12

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?

Best Answer

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!

Related Solutions

PostgreSQL – retrieving max primary key column to insert a new row – any alternatives

Postgres has the serial datatype which matches SQL Server's IDENTITY or MySQL's AUTO_INCREMENT.

Internally it is shorthand for a SEQUENCE but does that matter? It acts like IDENTITY/AUTO_INCREMENT:

The data types serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases). In the current implementation, specifying:

CREATE TABLE tablename (
    colname SERIAL
);

is equivalent to specifying:

CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
    colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

Edit,

I think what OP means is "is there SCOPE_IDENTITY or such" in PostgreSQL. Yes. You'd need currval or another one

Postgresql – > 30 sec slow query

Instead of what you've got, remove the virtual table and try to query directly.

SELECT DISTINCT id AS id10
FROM note j
WHERE j.modified_date >= '2016-10-01 23:12:34.000000'
  AND j.clientid = 16049
  AND j.n_type  IN ('n',
    'n_1',
    'n_custom',
    'n_standard',
    'n_status')
ORDER BY id10 ASC
LIMIT 20;

Best Answer

Related Solutions

PostgreSQL – retrieving max primary key column to insert a new row – any alternatives

Postgresql – > 30 sec slow query

Related Question