How Do I Implement Auto-Incrementing Columns in PostgreSQL?

Implementing auto-increment columns in PostgreSQL

An error may occur when trying to use AUTO_INCREMENT to automatically number primary keys in PostgreSQL. The following SQL demonstrates an example of trying to create a table using AUTO_INCREMENT:

<code class="language-sql">CREATE TABLE Staff   (
  ID        INTEGER NOT NULL AUTO_INCREMENT,
  Name      VARCHAR(40) NOT NULL,
  PRIMARY KEY (ID)
);</code>

However, an error is returned:

<code>ERROR: ERROR: syntax error at or near "AUTO_INCREMENT"
SQL state: 42601
Character: 63</code>

PostgreSQL version and auto-increment

This error occurs because AUTO_INCREMENT is not valid syntax for auto-increment in PostgreSQL. Instead, different mechanisms are used depending on the PostgreSQL version:

PostgreSQL 10 or higher

For PostgreSQL 10 or later, use the IDENTITY column type. The syntax is as follows:

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

PostgreSQL 9.6 or lower

For PostgreSQL 9.6 or lower, use the serial data type:

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

The

serial pseudo-data type automatically creates a sequence object and sets DEFAULT to the next value of the sequence.

Other notes

• Use legal, lowercase, unquoted identifiers for easier use in PostgreSQL.
• Use OVERRIDING {SYSTEM|USER} VALUE as needed to override system values ​​or user input in INSERT commands.

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