Hello everyone, there was a very nontrivial task. There is a large table with data, data from it is constantly "left" to other places permanently, there are no foreign keys on the ID of this table, however in this process the post-auto auto increment (it’s the sequence) continues to be wound and now I have integer out of range, and there are only a few million entries in the table. Actually the question is how to rebuild the table ID and sequence in the postgrece? Ie, roughly speaking, so that all current records have ID from 1st to count (records)?

thank

    1 answer 1

    Option One

    to throw out sequence generally and to use primary key from data domain.

    Option Two

    To quickly revive the project: restart the sequence again.

    ALTER SEQUENCE sequence_name RESTART WITH 1;

    Since he judging you on the issue is objectively not needed at all. And you can ask the sequence to walk independently in a circle:

     alter sequence seqtest_i_seq maxvalue 2147483647 cycle;

    Option Three

    Change field type to 64-bit bigint . During the lifetime of the application, you will not exhaust it.

    Fourth option

    What you are asking for is to renumber the entire table and set the current value of the sequence to the maximum number of existing ones. But why? Operation clumsy, repeat every 2 billion lines. Well, if you really want, then something like this :

     UPDATE thetable SET rowid=col_serial FROM (SELECT rowid, row_number() OVER ( ORDER BY lngid) AS col_serial FROM thetable ORDER BY lngid) AS t1 WHERE thetable.rowid=t1.rowid; select setval(seqtest_i_seq, max(rowid) + 1) from thetable;
    • As far as I imagined, a sequence is a separate entity from the table, and if it walks in a circle, then by logic it can produce a value that repeats the id of the existing record. Or in postgresql this problem is somehow solved and it will jump over existing id? - Mike
    • @Mike will of course repeat the values ​​and provoke an error of unique constraint. Therefore, by default, they have no cycle. But if this is repeated, according to the author's description, it seems that this transit label and the data in it do not linger on the closure time of int32. Universal option suitable for all - the third. Right from the point of view of the theory of RDBMS - the first. - Fine
    • @ Small thanks for the detailed response. The first option does not fit, you will have to redo too much in the systems that use this label and are tied to this ID. The second one also, since the table is not only a transit function, and the consistency of the primary key is important. Therefore, I chose the third option for myself, but now I’ll probably have to recreate the table, ALTER COLUMN for 2 billion records will never be executed ... - Zvook
    • So you wrote that only 2 million values, but not billion? :) It depends on the disks and the total volume (including indexes!) In general, at least once the table will have to be completely rewritten in any case. - Small