How to set auto increment primary key in PostgreSQL?

sql

prompt-engineering

best-practices

data-integrity

byAnton Shumikhin·Feb 21, 2025

To set an auto-increment primary key in PostgreSQL, use the SERIAL or BIGSERIAL data type. This type will auto-create and auto-increment a SEQUENCE.

For instance:

CREATE TABLE users (
    id SERIAL PRIMARY KEY, -- auto-incrementing id here... no manual labor!
    username VARCHAR(50) NOT NULL
);

If you prefer setting up a custom sequence, do the following:

CREATE SEQUENCE user_id_seq; -- a sequence to count, not sheeps...
CREATE TABLE users (
    id INT DEFAULT nextval('user_id_seq') PRIMARY KEY, -- every user gets a number, like a bingo game!
    username VARCHAR(50) NOT NULL
);

Remember to use BIGSERIAL/BIGINT for larger tables.

Advanced Auto-Increment Handling

Auto-increment for existing tables

Say you're on a boat and forgot to set your auto-incrementing primary key before sailing. Don't panic! Use the ALTER TABLE:

ALTER TABLE users ADD COLUMN id BIGSERIAL PRIMARY KEY; -- we're sending a rescue boat here!

It will take care of creating the SEQUENCE and setting the default value for you.

Sequence Ownership Management

To remove any confusions in your RDBMS family, match ownership between SEQUENCE and their pertaining tables:

ALTER SEQUENCE user_id_seq OWNED BY users.id; -- keeping it all in the family!

Catering to atypical increment patterns

Your table may require a custom increment value or a different start value. That's no issue:

CREATE SEQUENCE user_id_seq START 1000 INCREMENT BY 10; -- we're starting from the future and taking bigger steps!
ALTER TABLE users ALTER COLUMN id SET DEFAULT nextval('user_id_seq'); -- don't worry, we'll reach there...

Don't forget to update your sequence if you've skipped the starting gun and already populated the table:

SELECT setval('user_id_seq', (SELECT MAX(id) FROM users)); -- remember, we don't want to lose the race!

Transitioning to IDENTITY:

For PostgreSQL 10+ fans, you can switch from SERIAL with this stage dive:

ALTER TABLE users ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY; -- think of it as an upgrade, like going from cassette to CD!

Additional tips, tricks and traps in Auto-Increment

Overcoming COPY command obstacle

Battling duplicate keys after a COPY command? Try this super-hero move:

-- 1. Create your fortress 
CREATE TEMP TABLE temp_users (LIKE users INCLUDING DEFAULTS);

-- 2. Teleport the data
COPY temp_users (username) FROM '/path/to/data.csv' WITH CSV;

-- 3. Populate original table with the new recruits
INSERT INTO users (username) SELECT username FROM temp_users;

-- 4. Dispose off the fortress, we're clean now
DROP TABLE temp_users; -- clean up after yourself, mother Earth would be proud!

Maximizing efficiency with INSERT INTO

Leverage auto-increments for performance with this swift move:

INSERT INTO users (id, username) VALUES (DEFAULT, 'johndoe'); -- let the autopilot handle the id

Maintaining data integrity

For manual sequence management, take control and ensure UNIQUE NOT NULL:

CREATE TABLE users (
    id INT UNIQUE NOT NULL DEFAULT nextval('user_id_seq'), -- because each and everyone is unique!
    username VARCHAR(50) NOT NULL
);