PostgreSQL AUTO INCREMENT

Overview

PostgreSQL uses SERIAL and IDENTITY columns for auto-incrementing values.

SERIAL

-- Creates sequence and auto-increment column
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

-- Equivalent to:
CREATE SEQUENCE users_id_seq;
CREATE TABLE users (
    id INTEGER NOT NULL DEFAULT nextval('users_id_seq') PRIMARY KEY
);

BIGSERIAL

-- For large tables
CREATE TABLE logs (
    id BIGSERIAL PRIMARY KEY,
    message TEXT
);

SMALLSERIAL

-- For small ranges
CREATE TABLE small_table (
    id SMALLSERIAL PRIMARY KEY,
    name VARCHAR(50)
);

IDENTITY Column

-- Standard SQL identity column
CREATE TABLE users (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

-- Generated by default
CREATE TABLE orders (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    user_id INTEGER NOT NULL
);

Sequence Functions

-- Get next value
SELECT nextval('users_id_seq');

-- Get current value
SELECT currval('users_id_seq');

-- Set value
SELECT setval('users_id_seq', 1000);

-- Get last value from sequence
SELECT lastval();

Managing Sequences

-- Alter sequence
ALTER SEQUENCE users_id_seq RESTART WITH 1000;
ALTER SEQUENCE users_id_seq INCREMENT BY 2;
ALTER SEQUENCE users_id_seq MINVALUE 1 NO MAXVALUE;

Summary

AUTO INCREMENT methods include:

• SERIAL (INTEGER), BIGSERIAL, SMALLSERIAL
• IDENTITY (standard SQL)
• Sequence functions: nextval, currval, setval
• ALTER SEQUENCE for management