Understanding PostgreSQL equivalent to MySQL AUTO_INCREMENT

PostgreSQL Equivalent of MySQL AUTO_INCREMENT

In MySQL, the AUTO_INCREMENT attribute is used to create a column that automatically generates a unique sequential number when a new record is inserted. In PostgreSQL, there are equivalent options to achieve the same functionality using the SERIAL and BIGSERIAL data types, or the more flexible GENERATED BY DEFAULT AS IDENTITY method introduced in PostgreSQL 10.

PostgreSQL AUTO_INCREMENT Equivalents

1. Using SERIAL and BIGSERIAL

• SERIAL: Automatically increments a 4-byte integer.
• BIGSERIAL: Automatically increments an 8-byte integer for larger ranges.

These types automatically set up a sequence for auto-increment functionality.

2. Using GENERATED BY DEFAULT AS IDENTITY

PostgreSQL 10 and later versions support this ANSI SQL-compliant method for defining auto-incrementing columns. This method provides more flexibility and is preferable for newer applications.

Syntax for PostgreSQL AUTO_INCREMENT Equivalents

1. Using SERIAL:

CREATE TABLE table_name (
    id SERIAL PRIMARY KEY,
    column_name data_type
);

2. Using BIGSERIAL:

CREATE TABLE table_name (
    id BIGSERIAL PRIMARY KEY,
    column_name data_type
);

3. Using GENERATED BY DEFAULT AS IDENTITY (Preferred for PostgreSQL 10+):

CREATE TABLE table_name (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    column_name data_type
);

Example Code and Explanation:

1. Using SERIAL for Auto-Incrementing ID

-- Create a table with an auto-incrementing primary key using SERIAL
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,    -- Auto-incrementing column for employee ID
    name VARCHAR(50),         -- Column for employee name
    department VARCHAR(50)    -- Column for department name
);

Explanation:

• id SERIAL PRIMARY KEY: Defines id as an auto-incrementing primary key.
• SERIAL automatically creates an integer sequence to generate unique values.

2. Using GENERATED BY DEFAULT AS IDENTITY (PostgreSQL 10+)

-- Create a table with auto-incrementing ID using GENERATED BY DEFAULT AS IDENTITY
CREATE TABLE products (
    product_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, -- Auto-incrementing product ID
    name VARCHAR(50),            -- Product name
    price DECIMAL(10, 2)         -- Product price
);

Explanation:

• product_id INTEGER GENERATED BY DEFAULT AS IDENTITY: Sets up an auto-incrementing ID.
• GENERATED BY DEFAULT AS IDENTITY provides greater control over sequence properties.

Important Notes:

• SERIAL vs. IDENTITY: SERIAL is easier to use but less flexible. IDENTITY is ANSI SQL-compliant and provides additional control, like specifying START WITH and INCREMENT BY options.
• Compatibility: For legacy applications, SERIAL might be more familiar. New applications should consider using IDENTITY for ANSI compliance and future compatibility.

All PostgreSQL Questions, Answers, and Code Snippets Collection.