PostgreSQL Add Primary Key Autoincrement

As a database architect, I usually get a common requirement to set up auto-incrementing primary keys in Postgresql. In this article, I’ll walk you through various methods to implement auto-incrementing primary keys in PostgreSQL with real-time examples.

PostgreSQL Add Primary Key Autoincrement

Auto-increment in Postgresql will allow a unique number to be generated automatically when a new record is inserted into a table.

Frequently, this is the primary key field that is likely to be created automatically every time a new record is inserted. Let us discuss all the methods to do this.

Method 1: Using SERIAL or BIGSERIAL When Creating a Table

The most straightforward approach to implementing auto-incrementing primary keys in PostgreSQL is using the SERIAL or BIGSERIAL data types when creating your table.

SERIAL is a data type that creates an integer column with a sequence attached to it, automatically generating the next value when inserting new rows.

For example, create a table and use the SERIAL data type in the query below.

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100)
);

After executing the above query, I got the expected output, as shown in the screenshot below. The table was created successfully with the ID as the autoincrement column.

When you should use SERIAL vs. BIGSERIAL

• SERIAL: Creates a 4-byte integer
• BIGSERIAL: Creates an 8-byte integer

Method 2: Adding an Auto-Incrementing Primary Key to an Existing Table

Let’s create another table seller that will not have any primary key.

CREATE TABLE seller (name VARCHAR(255));

We will add some rows to the seller table using the INSERT statement.

INSERT INTO seller (NAME)
VALUES
	('ZARA'),
	('Old Navy'),
	('GAP'),
	('Hollister'),
        ('Coach');

Let’s check the output for the above table, which we have created.

We query data from the seller table using the following SELECT statement to check the insert operation.

SELECT * FROM seller;

Now, if we want to add a primary key name ID to the seller table, and the id field is auto-incremented by one, we will use the syntax below.

ALTER TABLE table_name ADD COLUMN id SERIAL PRIMARY KEY;

Now, let’s check the query for our table.

ALTER TABLE seller ADD COLUMN ID SERIAL PRIMARY KEY;

Let’s recheck the seller table.

SELECT id,name FROM seller;

Let’s check the output for the above query. You will see that we got the expected output, as shown in the screenshot below.

Method 3: Using IDENTITY Columns (PostgreSQL 10+)

If you’re using PostgreSQL 10 or later, you have another powerful option: IDENTITY columns. This approach follows the SQL standard and offers more control over the auto-increment behavior.

CREATE TABLE products (
    product_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_name VARCHAR(100),
    price DECIMAL(10,2)
);

After executing the above query, I got the expected output, as shown in the below screenshot.

Method 4: Using Sequences Manually

For more granular control, you can explicitly create and manage a sequence

As a first step, create a sequence using the below query.

CREATE SEQUENCE order_id_seq;

The sequence was created successfully after executing the above query, as shown in the screenshot below.

Now, let’s use the query below to create a table using the sequence we created above.

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY DEFAULT nextval('order_id_seq'),
    customer_id INTEGER,
    order_date DATE
);

After executing the above query, I got the expected output, as shown in the below screenshot.

Now, as the last step, let us set the sequence ownership using the below query.

ALTER SEQUENCE order_id_seq OWNED BY orders.order_id;

After executing the above query, I got the expected output, as shown in the below screenshot.

This approach gives you more flexibility, especially when you need to:

• Share a sequence across multiple tables
• Set custom increment values or starting points
• Implement more complex ID generation logic

Choosing the Right Auto-Increment Method

Each auto-increment method has its use cases. Here’s a comparison table to help you decide:

Method	Pros	Cons	Best For
SERIAL/BIGSERIAL	Simple, widely supported	Less standard-compliant	Quick development, backward compatibility
IDENTITY	SQL standard, better control	Requires PostgreSQL 10+	New applications, standards compliance
Manual Sequences	Maximum flexibility	More verbose, complex	Complex scenarios, shared sequences
UUID Generation	Globally unique, good for distributed systems	Larger storage, slower indexing	Distributed databases, merge scenarios

Conclusion

Adding auto-incrementing primary keys in PostgreSQL is essential for any database developer. As mentioned in this article, built-in features like SERIAL data types make implementation straightforward, while more advanced options give you flexibility for complex scenarios.

Whether creating new tables or modifying existing ones, PostgreSQL provides multiple approaches to ensure your data has reliable, efficient primary keys.

You may also like the articles below.

• PostgreSQL WHERE Clause
• PostgreSQL INSERT Multiple Rows (Complete tutorial)
• How to create a table in PostgreSQL
• Postgresql generate_series Examples