This question already has an answer here:

I have come across a problem where PostgreSQL does not insert records at the end of the table (according to id, which is the primary key)

So if, for example I have ids like 1521, 1522, 1527, the id of the next insert will be 1523 and not 1528 as I expected and would like to occur.

The missing ids may be due to the fact that the database that was migrated from MySQL. The id has the following definition: Data type: bigint Default value: nextval('fleetmanager.workorderjob_id_seq'::regclass) Not null Storage: Plain

I use PostgreSQL version 10 on Windows.

How can I get it to always do insert after the last id?

marked as duplicate by dezso postgresql Jan 12 '18 at 12:19

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • 1
    Your inserts are apparently not using the sequence to generate the ID, thus the sequence is not incremented. Check your code – a_horse_with_no_name Jan 12 '18 at 6:42

There is absolutely nothing wrong with Postgres to act like this and it has a reason. According to Postgres's documentation (in Notes section):

sequence objects cannot be used if "gapless" assignment of sequence numbers is needed. It is possible to build gapless assignment by using exclusive locking of a table containing a counter; but this solution is much more expensive than sequence objects, especially if many transactions need sequence numbers concurrently.

And again in another page it says:

Important: To avoid blocking concurrent transactions that obtain numbers from the same sequence, a nextval operation is never rolled back; that is, once a value has been fetched it is considered used and will not be returned again. This is true even if the surrounding transaction later aborts, or if the calling query ends up not using the value. For example an INSERT with an ON CONFLICT clause will compute the to-be-inserted tuple, including doing any required nextval calls, before detecting any conflict that would cause it to follow the ON CONFLICT rule instead. Such cases will leave unused "holes" in the sequence of assigned values. Thus, PostgreSQL sequence objects cannot be used to obtain "gapless" sequences.

  • The purpose of the question was not to criticize Postgres, but to find out how I could solve the problem at hand, which really boiled down to the fact that I could not use order by id to get the results in the order they were created. – jdoer1997 Jan 15 '18 at 0:03

Looks like there is wrong current value of sequense. Alter your sequence to max value in table:

SELECT setval('fleetmanager.workorderjob_id_seq', max(id)) FROM 'fleetmanager.workorderjob;

Not the answer you're looking for? Browse other questions tagged or ask your own question.