Auto Increment Values in PostgreSQL

PostgreSQL does not use MySQL’s AUTO_INCREMENT column syntax. For automatically generated identifiers, use an identity column for most new tables, or use the older SERIAL family or a sequence with nextval. These mechanisms supply values; they do not by themselves guarantee uniqueness. Add a PRIMARY KEY or UNIQUE constraint when duplicate identifiers must be rejected.

Use an Identity Column for New Tables

An identity column is backed by an associated sequence. Declare the column as GENERATED BY DEFAULT AS IDENTITY when inserts may supply their own identifier. Use GENERATED ALWAYS AS IDENTITY when PostgreSQL should normally provide the value and reject a caller-supplied value. Both forms generate a value when an insert omits the identity column or uses DEFAULT.

Create the table with a complete statement, then insert rows while omitting the identity column:

CREATE TABLE cat (id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, color varchar NOT NULL);

INSERT INTO cat (color) VALUES ('black'), ('white'), ('brown'), ('tuxedo');

Because id is omitted, PostgreSQL draws a value for each row. The exact starting value depends on the identity sequence configuration; callers should use RETURNING id when they need the identifiers assigned by a particular insert. These SQL statements are illustrative and were not run against a PostgreSQL server here.

BY DEFAULT accepts an explicit identifier in an insert, while ALWAYS protects generated values from accidental replacement. The choice is about how explicit values are handled, not whether the generated values are unique. The primary key in this example enforces uniqueness.

A small Python model illustrates the idea of assigning the next value when a caller omits an identifier. It is only a model; it does not connect to PostgreSQL or reproduce a database sequence.

Run this example:

next_id = 1
rows = []
for color in ["black", "white", "brown", "tuxedo"]:
    rows.append((next_id, color))
    next_id += 1
print(rows)

Use SERIAL or a Sequence

SERIAL and BIGSERIAL are concise, older alternatives to identity columns. SERIAL creates an integer column with a default that draws from an associated sequence; BIGSERIAL does the same with a larger integer type. They are pseudo-types used in table definitions, not actual integer types. SERIAL uses a 4-byte integer, while BIGSERIAL uses an 8-byte integer. The sequence does not automatically make the column a primary key, so add a key or uniqueness constraint if required.

For a cat table, id SERIAL PRIMARY KEY and color varchar NOT NULL define a generated identifier and a uniqueness constraint. An insert that names only color, such as the one above, receives sequence values. Sequence values can have gaps: values consumed by rolled-back transactions or inserts that later fail are not reused. Do not treat them as a gapless row count.

Use an explicit sequence when you need to configure or share sequence behavior directly. For example, CREATE SEQUENCE cat_id_seq START WITH 100; creates a sequence starting at 100, and id integer NOT NULL DEFAULT nextval('cat_id_seq') uses its next value. nextval advances the sequence; it is not a random generator. When using a manually created sequence, consider ownership and lifecycle: ALTER SEQUENCE cat_id_seq OWNED BY cat.id; associates it with the column so it can be managed with that column.

Insert Explicit Values into an Identity Column

With GENERATED BY DEFAULT AS IDENTITY, a supplied identifier takes precedence for that row. With GENERATED ALWAYS AS IDENTITY, PostgreSQL ordinarily rejects an explicit value. To intentionally load one, use OVERRIDING SYSTEM VALUE, for example: INSERT INTO cat (id, color) OVERRIDING SYSTEM VALUE VALUES (20, 'black');. This clause tells PostgreSQL to use the supplied value for that insert.

An explicit value does not automatically advance the identity’s associated sequence. If manually inserted values may overlap later generated values, adjust the sequence before resuming generated inserts. For routine inserts, omit the identity column and let PostgreSQL choose the value. Use RETURNING id to retrieve generated identifiers rather than predicting the next value; concurrent transactions and sequence gaps make prediction unreliable.

Add Automatic Values to an Existing Table

For an existing table, add an identity column with ALTER TABLE cat ADD COLUMN id integer GENERATED BY DEFAULT AS IDENTITY;. PostgreSQL supplies identity values for rows as the column is added, but the statement does not make the column a primary key. Add a primary key only after confirming that the data satisfies its constraints and that the table does not already have another primary key.

To give an existing ordinary column a sequence-backed default, create a sequence and set the column default, for example CREATE SEQUENCE cat_id_seq; followed by ALTER TABLE cat ALTER COLUMN id SET DEFAULT nextval('cat_id_seq');. This affects future inserts that omit id; it does not fill existing nulls or ensure existing values are unique. Before relying on generated values, initialize the sequence beyond the table’s current maximum identifier, accounting for an empty table and the desired starting value. Then add NOT NULL or a uniqueness constraint if the application requires those guarantees. Choose identity for standard generated-identifier behavior, and use SERIAL or an explicit sequence when maintaining an existing sequence-based design or when its configuration is needed directly.