PostgreSQL bulk update sequence IDs

Image from: https://rb.gy/oy1eeg

If you don’t understand Hindi, you’ll not get the featured meme here. And obviously, I am very bad at describing the role in that meme. Sorry, jokes on me as well. 😬

Back in my B.Sc. days, our RDBMS teacher, Mahfuz Reza Shuvo sir taught us a lot of things. Cursor, function, trigger, and a lot more. Can’t even name them now. Most of them are yet not being used now. But, when in trouble, then these things come up as blessings. Just a year ago, within a few hours of learning, I wrote a few cursors and functions that saved someone the next day. Sometimes writing DB queries are fun.

The problem

When exporting tables from the server using TablePlus, I found that the last sequence ids are not reserved and it becomes the DBs default value on the first run after importing those files. I had more than 50 tables and manually changing those sequence values is obnoxious. And for different projects doing the same is of course cumbersome. Thus I was looking for a bulk update option for those sequence ids. Then had to write my own logic to do so. The solution is given and explained below. Bear with me.

The solution

The following solution worked for every PostgreSQL dump I downloaded using table plus. Hope if you reach here will work for you as well.

The idea behind the solution was

• Regardless of the numbers of the tables, it should update all the tables.
• The next sequence ids will be the very next id of the max available sequence id.
• Skip a few tables which I know that they don’t contain sequence ids.
• And, can use different sequence name over convention.

Let’s get into it.

For points 3 & 4, we’ll need to create a temporary table. And the table will be like the following.

CREATE TEMPORARY TABLE kvstore (     
    table_name  TEXT PRIMARY KEY,
    pk_field  TEXT, 
    seq_name  TEXT,
    skip   BOOLEAN default false 
);

A temporary table is a table that stays alive for the session you’re running. As soon as you close the session, it gets deleted. In our table_name field, we’ll store the table that either has different naming rather than the convention table_name_pk_colname_seq or we want to skip.

So, insert the skippable tables or the tables with unconventional sequence names in that table.

INSERT into kvstore values ('password_resets', '', '', TRUE);-- skip
INSERT into kvstore values ('permission_role', '', '', TRUE);-- skip 
INSERT into kvstore values ('role_user', '', '', TRUE);-- skip
INSERT into kvstore values ('another_table', 'id', 'custom_seq_name', FALSE); -- won't skip

Next, we’ll create a function that will change the ID’s value and will take the table name, primary key field, and the sequence name as parameter.

DROP FUNCTION if exists alter_seq(text, text,text);
CREATE or REPLACE function alter_seq(table_name text, pk_field text, seq_name text) returns text as $$
DECLARE
  next_pk_value integer;
  q text;
BEGIN
  q := format('select coalesce(max(%s), 0) + 1 from "%s"', pk_field, table_name);
  EXECUTE q into next_pk_value;
  q := format('ALTER SEQUENCE %s RESTART WITH %s', seq_name, next_pk_value);
  EXECUTE q;
  RETURN q;
END;
$$ LANGUAGE plpgsql;

The lines after BEGIN

• Formats the query into the q variable.
• Executes the statement and stores the value in next_pk_value variable.
• Formats another query into q variable to update the sequence value.
• Executes the statement and returns it.

For the drop function, you can check possible scenarios here.

As we have our function ready to alter the sequence values, we can now create another function, which will fetch the available tables, and for each table execute the above function. And our function is like below.

DROP FUNCTION if exists change_table_seq();
CREATE or REPLACE function change_table_seq() returns void as $$
DECLARE
  tbl text;
  q text;
  pk_field text;
  seq_name text;
  skip BOOLEAN;
BEGIN
  FOR tbl IN SELECT table_name FROM information_schema.tables WHERE  table_schema = 'public' ORDER  BY 1
  LOOP
    q := format('SELECT pk_field, seq_name, skip from kvstore where table_name = %L', tbl);
    EXECUTE q into pk_field, seq_name, skip;
    CONTINUE when skip = true;

    pk_field = COALESCE(pk_field, 'id');
    seq_name = COALESCE(seq_name, tbl || '_id_seq');
    PERFORM alter_seq(tbl, pk_field, seq_name);
    END LOOP;
END;
$$ LANGUAGE plpgsql;

The lines after BEGIN

• We loop over the available tables in the public schema. You should change your schema name according to your need.
• Next, we format a query that will look for the table name in your temporary table.
• Then we execute the query and save it into those variables.
• If a row is returned and the value for that row in the skip field has true then we’ll not run the following statements. Otherwise, we’ll proceed.
• Next, if any row was returned that shouldn’t be skipped, then we will look for the primary field’s name. If not row was returned, then it’ll guess its primary key as id. Change the default value (id) according to your need.
• Next, like the previous point, we guess the sequence name as table_name_id_seq and the _id_seq is the default value. Change according to your need.
• Then we call our previously defined function that will alter the sequence value of that table. We call with the table name, primary key field name, and the sequence name. And this method call changes the sequence value based on the last max value of the primary key.

Now, it’s time to call our change_table_seq function to alter those sequence values.

select change_table_seq();

That’s all. It’ll now change all the available tables with these guesses defined in the change_table_seq function. And will overwrite things based on what’s defined in your temporary table. The whole snippet is given below.

Hope this helps. Thanks.

Happy coding. ❤