How Do I Properly Implement Auto-Incrementing Primary Keys in PostgreSQL?

PostgreSQL auto-increment column troubleshooting

You may encounter errors when trying to use AUTO_INCREMENT to automatically number primary keys in PostgreSQL. Let’s dive into the possible causes and their solutions:

PostgreSQL 10 and above

In PostgreSQL 10 and above, it is recommended to use standard SQL's IDENTITY column instead of AUTO_INCREMENT.

Create table with IDENTITY column

<code class="language-sql">CREATE TABLE staff (  
 staff_id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY  
, staff text NOT NULL  
);</code>

Add IDENTITY column to existing table

<code class="language-sql">ALTER TABLE staff ADD COLUMN staff_id int GENERATED ALWAYS AS IDENTITY;</code>

Make this also the primary key:

<code class="language-sql">ALTER TABLE staff ADD COLUMN staff_id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY;</code>

PostgreSQL 9.6 or lower

For 9.6 or lower, use the serial pseudo-data type:

<code class="language-sql">CREATE TABLE staff (  
 staff_id serial PRIMARY KEY  
, staff text NOT NULL  
);</code>

Additional Notes

Please make sure to use valid identifiers (lowercase, no quotes) to prevent potential issues.

If you want to replace serial columns with IDENTITY columns, please refer to this document for guidance.

You can use OVERRIDING {SYSTEM|USER} VALUE to override system values ​​or user input in INSERT commands.

The above is the detailed content of How Do I Properly Implement Auto-Incrementing Primary Keys in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!