In postgresql, after importing the csv file, the serial number is not synchronized

Question

I moved the existing MSSQL data to postgreSQL.

I imported / exported the csv file during this process.

And the serial number entered data sequentially.

The serial number did not work properly afterwards when I put the new value in the application.

As you can see, the sequence in postgresql is still fixed at 1.

I wonder why it's not syncing.

Thank you.

Answer 1

Problem Statement

In your case, it sounds like the issue you're experiencing is that you've done the following:

1. Exported data which already has a serial number
2. Imported the export into a Postgres database
3. Tried to insert additional data, finding that the serial number either:
   a) generated a conflict because it tried to insert a duplicate of an existing serial number; or
   b) successfully inserted a duplicate of an existing serial number

Background on Serials in Postgres

In PostgreSQL, the serial data type is really short-hand for an integer column with an associated sequence which controls the default behavior for the column.

From the documentation:

The data types smallserial, serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases).

You can verify that behavior by examining the default value for the column (e.g. by using \d+ in psql). The Default column should look something like: nextval('schema.id_seq'::regclass). It's worth highlighting from the documentation that:

In most cases you would also want to attach a UNIQUE or PRIMARY KEY constraint to prevent duplicate values from being inserted by accident, but this is not automatic.

As a side note, it's important to note that sequences do not promise that values will be consecutive.

Solution

The reason why it's possible to end up in the situation you've described is that the sequence which generates new values is only called when the value is not explicitly provided.

When you think about serials as integers and defaults, this starts to make more sense. The way that Postgres is tracking which value it should use next is through the sequence, and it is only assigning new values from the sequence when you don't explicitly provide a value.

The way to solve for this is to set the nextval using the setval function in Postgres. There are many different strategies for exactly how to do this, depending on whether you want to:

• Reset all of the serial numbers
  • Example from Stack Overflow
  • Another Example
• Keep all of the existing serial numbers and start from the next value
• Some other, custom solution

Here's an answer to the related question of how to set the sequence to a specific value.

A Trivial Example

The example below shows how providing an explicit value to the insert/upload does not use the default/sequence, and how the next insert that does will continue along the existing sequence.

CREATE TABLE test (id serial, sample integer);
INSERT INTO test (sample) VALUES (101); -- assigns default, next value from id_seq (1)
INSERT INTO test (id, sample) VALUES (3, 102); -- assigns explicit value (3)
INSERT INTO test (sample) VALUES (103); -- assigns default, next value from id_seq (2)