Why Doesn't AUTO_INCREMENT Work in PostgreSQL, and How Can I Achieve Auto-Incrementing IDs?

Troubleshooting PostgreSQL's AUTO_INCREMENT

PostgreSQL doesn't support the AUTO_INCREMENT keyword used in other database systems like MySQL. Attempting to use it will result in a syntax error. This guide explains how to achieve auto-incrementing primary keys in PostgreSQL.

PostgreSQL 10 and Later: Using IDENTITY Columns

The recommended approach for PostgreSQL 10 and newer versions is to utilize the IDENTITY column. Unlike SERIAL columns, IDENTITY columns offer more flexibility with GENERATED BY DEFAULT and GENERATED ALWAYS options.

Creating 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>

Adding an IDENTITY Column to an Existing Table

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

PostgreSQL 9.6 and Older: Utilizing SERIAL

For older PostgreSQL versions (9.6 and below), the SERIAL pseudo-type provides a similar auto-incrementing functionality.

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

SERIAL automatically creates and manages an associated sequence, ensuring unique, incrementing values for the staff_id column.

Controlling Inserted Values

For fine-grained control over inserted values, even overriding system defaults or user-provided values, use OVERRIDING {SYSTEM|USER} VALUE in your INSERT statements. This offers advanced control beyond simple auto-incrementing.

The above is the detailed content of Why Doesn't AUTO_INCREMENT Work in PostgreSQL, and How Can I Achieve Auto-Incrementing IDs?. For more information, please follow other related articles on the PHP Chinese website!