How to Resolve PostgreSQL Primary Key Sequence Discrepancies?

Fixed PostgreSQL primary key sequence inconsistency issue

PostgreSQL’s primary key sequence provides a unique identifier for table rows. Primary key duplication errors can result when the sequence is out of sync with the actual row insertion operation. This issue usually occurs after data import/restore operations.

To resolve this issue, the following steps are recommended:

1. Check the maximum ID value in the table:

   <code class="language-sql"> SELECT MAX(id) FROM your_table;</code>

2. Get the next value of the sequence:

   <code class="language-sql"> SELECT nextval('your_table_id_seq');</code>

3. If the next value of the sequence is less than the maximum ID value, adjust the sequence to the last used ID value:

   <code class="language-sql"> BEGIN;
    LOCK TABLE your_table IN EXCLUSIVE MODE;
    SELECT setval('your_table_id_seq', (SELECT GREATEST(MAX(your_id), nextval('your_table_id_seq')-1) FROM your_table));
    COMMIT;</code>

This process ensures that the primary key sequence matches the existing table data, thereby resolving primary key duplication errors.

The above is the detailed content of How to Resolve PostgreSQL Primary Key Sequence Discrepancies?. For more information, please follow other related articles on the PHP Chinese website!