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:

CREATE TABLE tst(
  id
    serial -- исследуемое поле
, val
    integer
);

INSERT INTO tst(val) VALUES(1),(2),(4),(8);

TABLE tst;

id | val
--------
 1 |   1
 2 |   2
 3 |   4
 4 |   8

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:

DELETE FROM tst WHERE id > 2;

id | val
--------
 1 |   1
 2 |   2

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

SELECT pg_get_serial_sequence('tst', 'id');

pg_get_serial_sequence
----------------------
public.tst_id_seq

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

SELECT * FROM public.tst_id_seq;

last_value | log_cnt | is_called
--------------------------------
         4 |      29 | t

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:

SELECT
  (
    SELECT
      nlv
    FROM
      dblink(
        'host=... port=5432 dbname=... user=... password=...'
      , $q$
          SELECT setval(pg_get_serial_sequence('$q$ || quote_ident(sequence_schema) || $q$.$q$ || quote_ident(sequence_table) || $q$', '$q$ || sequence_column || $q$'), $q$ || lv || $q$)
        $q$
      ) T(nlv bigint)
  ) nlv
, *
FROM
  (
    SELECT
      (
        SELECT
          relname
        FROM
          pg_class
        WHERE
          oid = (dp).refobjid
      ) sequence_table
    , (
        SELECT
          attname
        FROM
          pg_attribute
        WHERE
          (attrelid, attnum) = ((dp).refobjid, (dp).refobjsubid)
      ) sequence_column
    , *
    FROM
      (
        SELECT
          -- получаем значение на базе-источнике
          (
            SELECT
              lv
            FROM
              dblink(
                'dbname=' || current_database()
              , $q$
                  SELECT last_value FROM $q$ || quote_ident(sequence_schema) || $q$.$q$ || quote_ident(sequence_name) || $q$
                $q$
              ) T(lv bigint) 
          ) lv
        , (
            SELECT
              dp
            FROM
              pg_depend dp
            WHERE
              (classid, objid, refclassid, deptype) = ('pg_class'::regclass, (quote_ident(sequence_schema) || '.' || quote_ident(sequence_name))::regclass, 'pg_class'::regclass, 'a')
            LIMIT 1
          ) dp 
        , *
        FROM
          information_schema.sequences
        WHERE
          sequence_schema IN ('public') 
      ) T
  ) T
WHERE
  sequence_table !~ '^_';

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