How can I transfer to the other PostgreSQL database the last assigned value of the “auto-increment” field of the serial type if there could be any deletions in the table, and “just substituting max (pk)” no longer works?

Few people know that even though PG does not provide up to version 10 functions in order to find out the last value of a sequence for such a field from another session, this can still be done.

PostgreSQL supports “auto-increment” fields defined by the serial pseudo-type:

The data types smallserial, serial and bigserial are not real types, but are just a convenient tool for creating columns with unique identifiers (similar to the AUTO_INCREMENT property in some DBMSs).

Technically, this is just an automatic linking of the DEFAULT definition for the field and the values ​​generated by the SEQUENCE object. And sometimes you want to look into these values ​​- for example, to transfer them to a database similar in structure.

But this is not easy to do:

currval

Returns the value returned from the last nextval call for this sequence in the current session. (If nextval has never been called for a given sequence in a given session, an error is returned.) Since this value is limited by the scope of the session, this function gives a predictable result, regardless of whether nextval was subsequently called in other sessions or not.

Let’s try to pull this information out nevertheless:

This is the id = 4 value that we want to get. But someone deleted part of the records, and in the table it is no more:

First, we’ll find out the name of the sequence corresponding to our field:

Now use the resulting sequence name as a table in the query:

Actually, the last_value field stores the very “last” value that the sequence managed to generate at the time of our call.

Now let’s build a simple script for transferring sequence values from one database to another using the dblink module:

But remember that if there is any activity on the source base, the result will be undefined.

Privacy Preference Center