How to Implement Auto-Incrementing Columns in PostgreSQL?

Detailed explanation of how to implement PostgreSQL auto-incrementing columns

When creating a table in PostgreSQL, you may encounter errors when trying to use the AUTO_INCREMENT option for a primary key column. This article delves into this issue and provides a comprehensive solution for auto-incrementing table columns.

AUTO_INCREMENT syntax error

The error message encountered indicates a syntax error related to the AUTO_INCREMENT keyword. PostgreSQL does not support the AUTO_INCREMENT keyword. Instead, it uses different techniques to automatically generate primary key values.

PostgreSQL 10 and later: IDENTITY column

For PostgreSQL 10 and later, the preferred method of auto-incrementing primary keys is the IDENTITY column. An IDENTITY column is a special type of column that automatically generates a unique value for each row.

To create a table with an IDENTITY column:

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

The

GENERATED ALWAYS clause ensures that column values ​​are automatically generated even if values ​​are provided explicitly in the INSERT statement.

PostgreSQL 9.6 and lower: serial column

For PostgreSQL 9.6 and lower, the serial pseudo data type can be used to auto-increment primary keys. The serial column creates a sequence object behind the scenes and automatically sets the column's default value to the next value in the sequence.

To create a table with a serial column:

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

Override default value

Both IDENTITY and serial columns automatically generate values, but you can override these values ​​using the OVERRIDING SYSTEM|USER VALUE syntax in the INSERT statement.

Additional Notes

• It is recommended to use IDENTITY columns instead of serial columns in PostgreSQL 10 and above.
• In PostgreSQL, prefer legal, lowercase, unquoted identifiers to simplify database management.

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