How to Implement AUTO_INCREMENT Functionality in PostgreSQL?

PostgreSQL automatic numbering: implement AUTO_INCREMENT function

Creating a table containing an AUTO_INCREMENT column in PostgreSQL can be challenging, as shown by the syntax error encountered in the provided example. To solve this problem, we need to know the correct syntax of AUTO_INCREMENT in PostgreSQL.

Unlike other database systems, PostgreSQL does not have a dedicated AUTO_INCREMENT data type. Instead, it provides two ways to automatically generate sequential values:

PostgreSQL 10 and above

IDENTITY column:

Starting in PostgreSQL 10, standard SQL IDENTITY columns can be used. These columns can be generated by default or always deterministically.

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>

PostgreSQL 9.6 and lower

SERIAL data type:

In these older versions, the SERIAL pseudo data type was used for auto-incrementing primary keys. It automatically creates a sequence and sets the DEFAULT value to the next value in the sequence.

To create a table with SERIAL columns:

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

Using these methods, you can create tables in PostgreSQL with automatically generated sequential values ​​as primary keys.

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