PostgreSQL auto-increment in pgAdmin: identity columns
For an automatically numbered PostgreSQL column, use a GENERATED identity definition. MySQL’s AUTO_INCREMENT syntax won’t work here. You can run the PostgreSQL statement from pgAdmin or another compatible SQL client.
Try an isolated example
The example creates a temporary table, inserts two sample rows and rolls the transaction back. Run it in a scratch session where you are allowed to create temporary tables.
BEGIN;
CREATE TEMP TABLE identity_demo (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
title text NOT NULL
);
INSERT INTO identity_demo (title) VALUES ('First'), ('Second');
SELECT id, title FROM identity_demo ORDER BY id;
ROLLBACK;The insert omits id so the database generates its value. The PRIMARY KEY separately enforces uniqueness and non-nullness. Identity generation alone is not a substitute for the intended key constraint.
ALWAYS versus BY DEFAULT
ALWAYS is the stricter choice for ordinary inserts that should not supply their own identifier. BY DEFAULT permits an explicitly supplied value to take precedence. PostgreSQL also provides explicit override syntax for relevant operations. Consult the identity-column documentation before designing an import that supplies IDs.
Do not treat IDs as a row count
Generated identifiers can contain gaps. Do not use the largest ID as the number of rows or promise customers that numbering will be consecutive. Use a separate business process if a document sequence has requirements beyond a database key.
Existing tables require a migration plan
Adding generation to an existing populated table is different from creating the temporary example. Check existing values, dependencies, constraints and sequence state in an isolated copy. Do not paste a sequence-reset statement into production without understanding concurrent writes and the current data.
You can run PostgreSQL SQL in pgAdmin or plainDB’s SQL editor. Inspect the current schema and constraints first, and keep database permissions appropriate to the task.